SQL expression (1 Viewer)

[figaro1]

HI All,

I am using crystal report XI with SQL as the database, I am using 2 tables from a database namely OEORDH and OEORDHO, the fields of the table are like this....

OEORDH

ORDUNIQ ORDNUMBR CUSTOMER CUSTGROUP BILLNAME. ...
4546 QUT02 CUST01 GRI XYZ
4610 QUT03 CUST02 GR2 YYY
4738 QUT03 CUST01 GR1 XYZ


OEORDHO

ORDUNIQ OPTFIELD VALUE
4546 OPTFLD1 ABCDE
4546 OPTFLD2 DEFG
4610 OPTFLD3 ASDF
4738 OPTFLD1 ABCD

these r the contents of the two tables, actually I am preparing a sales order report, when i select a report of ORDNUMBR QUT02,I want the value in OEORDHO to be printed of the of the respective ORDUNIQ (ie value of 4546)

To be clear when I select QUTO02 I want "ABCD" to be printed on my report.

It will be very useful if any one please help out..

regards.

 

[Ian Mac]

Hi,

Sorry I'm little confused here, you say

To be clear when I select QUTO02 I want "ABCD" to be printed on my report.

Do you mean you want "ABCDE" on the report as that's the ORDUNIQ 4546 corresponding value in the OEORDHO table.
Also do you now need to rows of data, "ABCDE" and "DEFG", as there are 2 VALUEs in OEORDHO, which relate to 4546.

I haven't got XI one this machine, or any Crystal at the moment (It's coming in the next week, hurrah!) but you can set up a join when you go through the report wizard you should need to have to write the SQL.

 

[figaro1]

Hi,

Sorry I'm little confused here, you say



Do you mean you want "ABCDE" on the report as that's the ORDUNIQ 4546 corresponding value in the OEORDHO table.
Also do you now need to rows of data, "ABCDE" and "DEFG", as there are 2 VALUEs in OEORDHO, which relate to 4546.

I haven't got XI one this machine, or any Crystal at the moment (It's coming in the next week, hurrah!) but you can set up a join when you go through the report wizard you should need to have to write the SQL.

Sorry wat you said is correct, I want those two values to be printed on my report, I exactlty tried wat you said but thats not working......

after yoy get crystal reports please help me out.

regards.

 

[Kempes]

The solution is as follows.

Go to your table relationships, which should have the two tables in there. Link OEORDH
to OEORDHO by the ORDUNIQ (Primary Key) as a one to many relationship.
Then, in your report, add the fields required from the OEORDHO.
If you want this as a manual selection process, you will need to add ORDNUMBR from the OEORDH table as a prompted field, then include this prompt in your selection criteria.

ie, ORDNUMBR is equal to "please choose an order number".

Otherwise you can just add a group by on this field which will group all orders against all order numbers.

Hope this is of help to you.

Kempes