Conversion failed when converting the varchar value '$contentid' to data type int.

Rhythmyx Technical Discussion
1

All,

i have a sql statement that works as it should when i paste it into sql server 2005, however, when i try and use the statement in one of my templates im getting this error:

"Conversion failed when converting the varchar value ‘$contentid’ to data type int. see log for stack trace "

SQL:

SELECT FIELD_OPTION_CHILDTABLE.OPTION_TRANSLATION FROM FIELD_OPTION_CHILDTABLE WHERE CONTENTID in (select PHRASE_TO_APPEAR_ON_LABEL from CT_PRODUCT_DATA1_LABEL_PHRASES Join contentstatus On CT_PRODUCT_DATA1_LABEL_PHRASES.contentid = contentstatus.contentid where CT_PRODUCT_DATA1_LABEL_PHRASES.revisionid = contentstatus.currentrevision and CT_PRODUCT_DATA1_LABEL_PHRASES.contentid = ('$pcssContentID')")

Any ideas greatly appreciated.

2

If a $ variable is in single quotes Velocity won’t process it.

You could try something like:

#set( $quot = “’” )

SELECT FIELD_OPTION_CHILDTABLE.OPTION_TRANSLATION FROM FIELD_OPTION_CHILDTABLE WHERE CONTENTID in (select PHRASE_TO_APPEAR_ON_LABEL from CT_PRODUCT_DATA1_LABEL_PHRASES Join contentstatus On CT_PRODUCT_DATA1_LABEL_PHRASES.contentid = contentstatus.contentid where CT_PRODUCT_DATA1_LABEL_PHRASES.revisionid = contentstatus.currentrevision and CT_PRODUCT_DATA1_LABEL_PHRASES.contentid = (${quot}${pcssContentID}${quot})")

http://velocity.apache.org/engine/releases/velocity-1.5/user-guide.html

3

Actually that will fix the variable substitution but probably won’t solve the error. sys_contentid is an int data type so I am pretty sure that you don’t want to use quotes in your SQL statement at all. By putting it in quotes you are converting it to a string.

so something like this might be better:

#set( $query = “SELECT FIELD_OPTION_CHILDTABLE.OPTION_TRANSLATION FROM FIELD_OPTION_CHILDTABLE WHERE CONTENTID in (select PHRASE_TO_APPEAR_ON_LABEL from CT_PRODUCT_DATA1_LABEL_PHRASES Join contentstatus On CT_PRODUCT_DATA1_LABEL_PHRASES.contentid = contentstatus.contentid where CT_PRODUCT_DATA1_LABEL_PHRASES.revisionid = contentstatus.currentrevision and CT_PRODUCT_DATA1_LABEL_PHRASES.contentid = $pcssContentID)” )

[QUOTE=natechadwick;19288]If a $ variable is in single quotes Velocity won’t process it.

You could try something like:

#set( $quot = “’” )

SELECT FIELD_OPTION_CHILDTABLE.OPTION_TRANSLATION FROM FIELD_OPTION_CHILDTABLE WHERE CONTENTID in (select PHRASE_TO_APPEAR_ON_LABEL from CT_PRODUCT_DATA1_LABEL_PHRASES Join contentstatus On CT_PRODUCT_DATA1_LABEL_PHRASES.contentid = contentstatus.contentid where CT_PRODUCT_DATA1_LABEL_PHRASES.revisionid = contentstatus.currentrevision and CT_PRODUCT_DATA1_LABEL_PHRASES.contentid = (${quot}${pcssContentID}${quot})")

http://velocity.apache.org/engine/releases/velocity-1.5/user-guide.html[/QUOTE]

4

Thanks for the repsonse, however, the second select statement in the brackets:

select PHRASE_TO_APPEAR_ON_LABEL from CT_PRODUCT_DATA1_LABEL_PHRASES Join contentstatus On CT_PRODUCT_DATA1_LABEL_PHRASES.contentid = contentstatus.contentid where CT_PRODUCT_DATA1_LABEL_PHRASES.revisionid = contentstatus.currentrevision and CT_PRODUCT_DATA1_LABEL_PHRASES.contentid = ('$pcssContentID'

works fine on its own when previewing the template so im not convinced its the quotes, but as soon as i add the first select in to get the label based on the id within the select that is when i get the conversion error. All the contentid data types appear to be the same. Is there a better way of writing the query?

5

I think your sub query is wrong, in your outer query you are using contentid IN but your sub query is selecting PHRASE_TO_APPEAR_ON_LABEL without seing your schema I can’t be sure but I would guess that the sub query should be

select CT_PRODUCT_DATA1_LABEL_PHRASES.contentid from CT_PRODUCT_DATA1_LABEL_PHRASES Join contentstatus On CT_PRODUCT_DATA1_LABEL_PHRASES.contentid = contentstatus.contentid where CT_PRODUCT_DATA1_LABEL_PHRASES.revisionid = contentstatus.currentrevision and CT_PRODUCT_DATA1_LABEL_PHRASES.contentid = $pcssContentID

6

Thanks for the response, the second select statement brings back all contentid’s (CT_PRODUCT_DATA1_LABEL_PHRASES.PHRASE_TO_APPEAR_ON_LABEL) or children associated with a parent for eaxmple - in this case all slot content. Each of these ids has a corresponding label(FIELD_OPTION_CHILDTABLE.OPTION_TRANSLATION) which i want to display but are in the other table.

I can pull back all the id’s and then use a separate macro to get the label but i need to filter the slot results.

Hope that maeks sense.

7

So PHRASE_TO_APPEAR_ON_LABEL is from a child table now that makes sense.

The only other thing is as nate suggested removing the single quotes and enclosing the whole statement in double quotes

8

Hi there, thanks again for all the responses, i have finally found and fixed the issue. Turns out ‘PHRASE_TO_APPEAR_ON_LABEL’ was being called into a macro further down the template which is what was used to display the results. I changed this to OPTION_TRANSLATION and it worked