queries 1

[susann105]

Does anyone have e-mailable doc on writing crystal queries? I need to do an "or" on 2 different table getting data from 1 table OR the other. How do I write that?

 

[Madawc]

Easiest to select from both tables, which means that details from both end up as a single 'row'. You can then use a formula field that will use data from one or the other, based on some test. E.g.

If isnull({table1.data}) 
then {table2.data}
else {table1.data}

It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options.


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

 

[susann105]

To add to my original post. Here's a sample of the query and please reply back with any tips on making it work.
My crystal version is 8.5.0.218 thank you.

 

[susann105]

{oeordhdr_sql.ord_dt} in {?Starting Date} to {?Ending Date} and
{oeordhdr_sql.ord_type} = "Q" and
{oeordlin_sql.item_desc_2} startswith ["15", "20", "30", "BM", "EX", "HP", "MF", "MPF", "SO", "VD", "VP"] OR
({oeordhdr_sql.oe_po_no} startswith ["LD", "BM","EX","HP","MF","MPF","VD","VP"] and
{oeordhdr_sql.discount_pct} in [10.00, 15.00, 20.00, 25.00, 30.00, 35.00, 40.00, 5.00, 50.00] and
{oeordhdr_sql.ord_dt} in {?Starting Date} to {?Ending Date})

 

[Madawc]

I'd make each statement a single formula field. Without an IF, it will be a Boolian, returning True or False. Put these beside your data and see which isn't doing what you expect.

If you hit a null without doing an ISNULL first, you'll get a blank rather than True or False. If say {oeordhdr_sql.ord_type} were null, the statement would fail to select records that would meet the second set of criteria.

Once you have your boolians producing the right result, you can use them by name in the selection and other places. Saying @As_Q or not @As_Q rather than @As_Q = True etc.

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