Left Join with condition

[pkohli88]

Hi,
is it a way to join a main table to secondary table.
like this without using command

select
*
from table_A A
left join table_B B on A.field1 = B.Field1 where B.field2 = 'ABC'

so my objective is to get all records from table A and I want to show only those fields from table B where B.Field2 ='ABC' rest will be shown as null.

Thanks


I am using crystal version 10 and oacle 10g as a database

 

[synapsevampire]

Below your data source you'll see Add Command, you can paste SQL in there as the data source.

This started in CR 9.

-k

 

[pkohli88]

I can't use COMMAND option because I am using multivalue parameters in the record selection formula

any other way....besides using Command

 

[Madawc]

Normally Crystal won't let you have a left-outer join from A to B and also do a selection on B. If you do a selection on B, then A's without a B get excluded.

What are you trying to do? You may need to select records you don't need and then do a test to exclude them from the display, e.g.

 if not IsNull (B.Field1)
and {B.field2} = 'ABC'
then "Found"
else "Not"

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[IdoMillet]

If you can't use a command, you can remove the condition from the record selection formula and apply it (in reverse) to the section suppression expression:
NOT(IsNull(B.field2) OR B.field2 = 'ABC')

Note: perhaps you can achieve the same thing by using the Group Selection formula. Worth a try...

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com