Can I use a substring function in the code (Crystal Reports)?

[Apollo6]

I am using Crystal Reports v7. The following is code that works provided that there is only one value returned from the UOM2."UOM_TYPE_CODE"(+) table.field, which is currently '20'. My problem is that an additional value of '25' has been added and could be returned as well.

I need to code where only '20' or '25' get returned or possibly '2*'.

Can I use a substring in the WHERE statement???

Crystal Reports seems to be very particular with it's rules and I am not fluent in SQL. Is this possible? I have tried to organize the layout of the code as best I could to be read.


SELECT

RECEIVER_DETAIL."REC_ID",
RECEIVER_DETAIL."PRODUCT_ID", RECEIVER_DETAIL."RECEIVED_QTY",
RECEIVER_DETAIL."RECEIVED_UOM", RECEIVER_DETAIL."UOM_FAMILY", RECEIVER_DETAIL."MATERIAL_STATUS_CODE",
PRODUCT_MASTER."PMST_ERP_GN_04",
PRODUCT_UOM."EACH_CONVERSION_FACTOR",
UOM2."UOM_FAMILY", UOM2."EACH_CONVERSION_FACTOR"

FROM
"MOVE"."RECEIVER_DETAIL" RECEIVER_DETAIL,
"MOVE"."PRODUCT_MASTER" PRODUCT_MASTER,
"MOVE"."PRODUCT_UOM" PRODUCT_UOM,
"MOVE"."PRODUCT_UOM" UOM2

WHERE
RECEIVER_DETAIL."PRODUCT_ID" = PRODUCT_MASTER."PRODUCT_ID" AND
(RECEIVER_DETAIL."PRODUCT_ID"=
PRODUCT_UOM."PRODUCT_ID" AND
RECEIVER_DETAIL."UOM_FAMILY" = PRODUCT_UOM."UOM_FAMILY" AND
RECEIVER_DETAIL."RECEIVED_UOM"= PRODUCT_UOM."UOM_TYPE_CODE&quot AND
(RECEIVER_DETAIL."PRODUCT_ID"=
UOM2."PRODUCT_ID"(+) AND
RECEIVER_DETAIL."UOM_FAMILY"=
UOM2."UOM_FAMILY"(+) AND
UOM2."UOM_TYPE_CODE"(+) = '20') AND
RECEIVER_DETAIL.MATERIAL_STATUS_CODE = ANY ('DMG' , 'FRT', 'AVL')

ORDER BY
RECEIVER_DETAIL."PRODUCT_ID" ASC,
RECEIVER_DETAIL."UOM_FAMILY" ASC,
RECEIVER_DETAIL."RECEIVED_UOM" ASC,
RECEIVER_DETAIL."MATERIAL_STATUS_CODE" ASC

 

[MalcolmW]

The simplest solution I can see is to take
....
UOM2."UOM_FAMILY"(+) AND
UOM2."UOM_TYPE_CODE"(+) = '20') AND
....
and change to
....
UOM2."UOM_FAMILY"(+)) AND
UOM2."UOM_TYPE_CODE" = ANY('20', '25') AND
....
However, your syntax in not standard SQL, so I'm kinda guessing as to what works.
Also, in the future, you may want to consider doing your joins in the FROM clause, rather than the WHERE clause, as this is the (currently) preferred method in SQL, and makes the SQL more readable.
Malcolm
wynden@telus.net
November is "be kind to dogs and programmers" month. Or is that "dogs or programmers"?

 

[THoey]

You might be able to use the LIKE key word. Try:

UOM2."UOM_FAMILY"(+) AND
  UOM2."UOM_TYPE_CODE"(+) LIKE '2*') AND

I am not sure what wild card character you use, if it is not an * then try a %.

Hope this helps...


Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?