SQL Expression Field - disabled 1

[oh704]

HI All,

for some odd reason that i cannot figure out, my sql expression field options are disabled on my crystal report 12 application.

Does anyone know the cause of this? i can create new fields fine with all the other categories.

and just so happens, creating a sql expression field is the last thing i need to complete this report.

Once upon a time, i was able to create SQL ex fields... the field has the following code.

{Q.Vendor Number} & iif(len({Q.Vendor Number})> 0,chr(13),"")
& {Q.ADDRESS1} & iif(len({Q.ADDRESS1})>0,chr(13),"")
& {Q.ADDRESS2} & iif(len({Q.ADDRESS2})>0,chr(13),"")
& {Q.ADDRESS3} & iif(len({Q.ADDRESS3})>0,chr(13),"")
& {Q.CITY} & ", " & {Q.STATE} & " " & {Q.ZIP} & " " & {Q.COUNTRY}

i tried converting this to a formula field, but i dont know the syntax for adding a carriage return with crystal syntax.

so if anyone knows how to convert this statement to formula field... it will be good for a workaround, but i'd still much prefer to "unlock" my sql expression field.

thanks

 

[lbass]

You should be able to copy your formula directly into the formula area and use it as is--as long as the fields in the formula can never be null. It might be better to write it like this:

(
if isnull({Q.Vendor Number}) or
trim({Q.Vendor Number}) = "" then
"" else
{Q.Vendor Number} + chr(13)
) +
(
if isnull({Q.Address1}) or
trim({Q.Address1}) = "" then
"" else
{Q.Address1}+chr(13)
) + //etc.

If I'm remembering correctly iif doesn't work well with nulls in Crystal. You would have to format the formula to "can grow".

SQL expressions would not be available if you were using one or more commands in your report or if you were using a stored procedure as your datasource. There may be other reasons as well.

-LB

 

[oh704]

thanks for the information lbass.

i am indeed using a command as a datasource for the report. i did not know that it will lead to disabling SQL expressions fields.

the syntax from your suggestions also works wonderfully as a formula field.

 

[abc567]

Yes if you use command as a datasource or even if you use multiple databases, it will result in disabling SQL Expression field.