converting exists clause to crystal reports 1

[Michali]

Hello all
I have tried to exec a report with this record selection formula - it doesn’t accept "exists". Any solution will by helpful.

 F_STRUCTURE.LIFE_STEP_ID} = 1 and 
{F_STRUCTURE.STRUCTURE_STATUS} = 1 
AND  exsists (select 1 from F_BUILDING  where {F_STRUCTURE.INT_STRUCTURE_KEY} = F_BUILDING.INT_STRUCTURE_KEY  
and {F_BUILDING.int_building_type_key} = 11 ) ]

Thanks

 

[paulmarr]

Did you spell it right?

Your code shows exsists not exists. I tried an EXIST query using the Xtremem sample database and it worked fine.

SELECT *
FROM Supplier
WHERE EXISTS
(select *
from Product
);


Cheers,

paulmarr

 

[Michali]

to paulmarr
thank u.
i have spelled it right in my report in the record selection formula.
finally i changed my view in the DB.
naturally it works fine now.

 

[marlis]

Could somebody please post the entire formula?

I've been trying to put exists in my Record Selection formula, but keep failing. What am I missing?
My formula looks like this:

{LOC.LOC_NAME} = {?Location}
and
exists
(SELECT 1
FROM tran intran
WHERE intran.tran_id = {tran.tran_id}
and intran.detail_type = 1)

 

[vidru]

You can't use EXISTS in the record selection formula. You can use it in a SQL Expression Field though. In your case, you could create a SQL Expression like this, although I wouldn't use EXISTS (*** this assumes that the 'tran' table is already added to the main report, and note the use of parentheses surrounding the query ***):

(SELECT COUNT(intran.tran_id)
FROM tran intran
WHERE intran.tran_id = "tran"."tran_id")

Your record selection formula would be:

{LOC.LOC_NAME} = {?Location}
and
{%YourSQLExpr} > 0

If you go to Database > Show SQL Query, you'll see that the SQL Expression is passed as a correlated subquery in the WHERE clause.

All of that being said, you can get away with that in CR 8.5 (and lower), but in CR 9, they did some work on their SQL parser component, and made it difficult to use subqueries that reference report fields in SQL Expressions.

If you're using CR 9, you can handle that by basing the report off of a Command, with which you can use "real" SQL to create your query.

-dave

 

[marlis]

Thanks Dave for such a fast reply.
I am on Crystal 10 and as you mentioned the correlated subquery wouldn't work there.
I'll see what I can do with the command.
marina