SQL expression

[YANKRAY]

I am using CR10.

How can I write the following SQL Query to run as a Crystal Report?

select a.*
from sr_work_card a
where not exists (select 'x'
from sr_wc_task b
where b.mpd_cd = a.mpd_cd
and b.work_card_no = a.work_card_no
and b.revision_no = a.revision_no)

What I am trying to retrieve are the records from sr_work_card that do not have a matching record in sr_wc_task for the fields mpd_cd, work_card_no, and revision_no.

 

[pgtek]

hi
try changing not exist to not in

cheers

pg

pgtek

 

[YANKRAY]

I am not very experienced in converting SQL queries to Crystal Reports.

Are there any types of special joins needed for the data links?

 

[pgtek]

hi
did you try running this query on sql analyser?
try it

cheers

pg


pgtek

 

[YANKRAY]

I am not familiar with sql analyser.

I did not see anything in CE10 help about this either.

 

[fredp1]

Okay,If i'm not mistaken, if thats the whole query for your report, then you do not really need to have a sql expression. It should be able to be done with a simple left outer join.
Is there any thing else needed with the report?
if not then i i'll propose a left out join solution and the sql expression solution.
You need to specify your database, i.e. SQl server, oracel etc as there are always exceptions.
I'll assume SQL server as the db.
LOJ:- add sr_work_card and sr_wc_task tables to the main report. Join the 3 fields mpd_cd, work_card_no, revision_no and speficy a left outer join.
In the record selection have isnull({sr_wc_task.mpd_cd}).
This will give you records in sr_work_card that do not have a record in sr_wc_task.
The sql will now look similar to this
select a.*
from sr_work_card a
left outer Join sr_wc_task b
on b.mpd_cd = a.mpd_cd
and b.work_card_no = a.work_card_no
and b.revision_no = a.revision_no
where a.mpd_cd is null

SQL Expression. One use of this is you had a filter on your sr_wc_task table. The above approach would act like a inner join and not return the results that your need. A sql expression will help.
Only add the sr_work_card in the main report.
Create a sql expression (including the brackets), call say
flag
(
Select 1 from sr_wc_task t2
where t2.mpd_cd = sr_work_card.mpd_cd
and t2.work_card_no = sr_work_card.work_card_no
and t2.revision_no = sr_work_card.revision_no
)
This creates a corrolated sub query returning a 1 for matching records and a null for non matching records for every reord in sr_work_card.
Note: the alias name of table sr_work_card is sr_work_card. Crystal defaults the alias name to the same name as the table name. Its the alias name thats is used in the sql expression.
Then you can use a simple filter in your record selection on the sql expression e.g. isnull({%flag})The sql generated should be
select a.*, (Select 1 from sr_wc_task t2
where t2.mpd_cd = sr_work_card.mpd_cd
and t2.work_card_no = sr_work_card.work_card_no
and t2.revision_no = sr_work_card.revision_no) as flag
from sr_work_card sr_work_card
where (Select 1 from sr_wc_task t2
where t2.mpd_cd = sr_work_card.mpd_cd
and t2.work_card_no = sr_work_card.work_card_no
and t2.revision_no = sr_work_card.revision_no) is null
Note: There sould only be 1 reord in the sr_wc_task for every sr_work_card. If there is more than one reord, then you'll need to but some grouping to ensure there is only one record returned from the sql expression.
I hope this helps.
if you need further clarification, feel free to ask.

Cheers
Fred