SQL expression error on upgrade

[hpl2001]

Hello:

I'm upgrading some older reports to Crystal 2008. I'm having trouble with my SQL expressions.

This expression:

NVL2((Select oc.Case_Status
From Offender_Cases oc
Where oc.OFFENDER_BOOK_ID = 'OFFENDER_BOOKINGS'.'OFFENDER_BOOK_ID'
And oc.Case_Status = 'ACTIVE'
And Rownum < 2), 'Yes', 'No')


Gives me an error ORA-00907: missing right parenthesis.

Obviously there is no missing parenthesis. The problem seems to be aroung where I am trying to reference a value from my main report ('OFFENDER_BOOKINGS'.'OFFENDER_BOOK_ID'). I've tried a bunch of different syntax ideas, but I'm not having any luck.

Anyone have any thoughts on this?

TIA!!

Holly

 

[IanWaterman]

Not sure you can embed a field from Report inside your SQL expression.

Why not just join the table Offender_Cases oc with a left outer join from 'OFFENDER_BOOKINGS'.'OFFENDER_BOOK_ID'

And then use a formula to return yes/no
eg

If Case_Status = 'ACTIVE' and isnull('OFFENDER_BOOKINGS'.'OFFENDER_BOOK_ID') then 'Yes' else 'No'

Ian

 

[lbass]

What type of database? If Access-type, then the punctuation on the field should use ` not ', as in:

NVL2(
(
Select oc.Case_Status
From Offender_Cases oc
Where oc.`OFFENDER_BOOK_ID` = `OFFENDER_BOOKINGS`.`OFFENDER_BOOK_ID`
And oc.Case_Status = 'ACTIVE'
And Rownum < 2
), 'Yes', 'No'
)

Or just add the field by double clicking on the field in the field list to get the punctuation right.

-LB

 

[hpl2001]

It's an oracle database. I have tried entering the feild by picking it from the list. Doesn't seem to make any difference. Unfortunately this is one of several SQL functions in my reports that have the same problem. They worked against our previous crystal/oracle environment...just not since we upgraded.

 

[lbass]

What happens if you remove the NVL2 function? Do you get a result?

-LB