Link to home
Start Free TrialLog in
Avatar of Steve McFarland
Steve McFarlandFlag for United States of America

asked on

SSRS - change like to an exact match

I am trying to change an SSRS report so that the PROJECT parameter needs to be an exact match or if left blank returns all.

WHERE (V_WO.CCN=:CCN) AND (V_WO.MAS_LOC=:ML) AND (V_WO.STATUS <>'C') AND (V_WO.ORD_QTY-V_WO.COMPLETE_QTY>=0) AND (V_WO.MFG_CLOSE_DATE Is Null OR V_WO.FIN_CLOSE_DATE Is Null) AND (A.CUSTOMER LIKE '%'||:CUST||'%' OR A.CUSTOMER IS NULL) AND C.RN=1 AND ((:DUE ='LATE') AND (CASE WHEN(A.PO_DATE2 is NULL) THEN(SYSDATE) ELSE(A.PO_DATE2) END-V_WO.DUE_DATE<0) OR (:DUE='ANY')) AND (TRIM(V_WO.WO_NUM||'-'||V_WO.WO_LINE) LIKE '%'||:ORD_NUM||'%') AND (' '||A.PROJECT LIKE '%'||:PROJECT||'%') AND (A.QTY<>0 or A.QTY IS NULL)
Avatar of Bill Prew
Bill Prew
Change:

AND (' '||A.PROJECT LIKE '%'||:PROJECT||'%')

to:

AND (A.PROJECT=:PROJECT OR A.PROJECT='')

Note that this won't handle NULL values for that column, I assumed that when you said "blank" you meant an empty string.

»bp
Avatar of Steve McFarland
Steve McFarland
Flag of United States of America image

ASKER

Sorry.  Yes I do mean null values.  This works for everything but that now.
Avatar of Bill Prew
Bill Prew
Okay, how about:

AND (A.PROJECT=:PROJECT OR A.PROJECT='' OR A.PROJECT IS NULL)

Open in new window

»bp
Avatar of Steve McFarland
Steve McFarland
Flag of United States of America image

ASKER

When I put this in and leave the PROJECT parameter blank the only returns I get are the nulls.

WHERE (V_WO.CCN=:CCN) AND (V_WO.MAS_LOC=:ML) AND (V_WO.STATUS <>'C') AND (V_WO.ORD_QTY-V_WO.COMPLETE_QTY>=0) AND (V_WO.MFG_CLOSE_DATE Is Null OR V_WO.FIN_CLOSE_DATE Is Null) AND (A.CUSTOMER LIKE '%'||:CUST||'%' OR A.CUSTOMER IS NULL) AND C.RN=1 AND ((:DUE ='LATE') AND (CASE WHEN(A.PO_DATE2 is NULL) THEN(SYSDATE) ELSE(A.PO_DATE2) END-V_WO.DUE_DATE<0) OR (:DUE='ANY')) AND (TRIM(V_WO.WO_NUM||'-'||V_WO.WO_LINE) LIKE '%'||:ORD_NUM||'%') AND (A.PROJECT=:PROJECT OR A.PROJECT=' ' OR A.PROJECT IS NULL) AND (A.QTY<>0 or A.QTY IS NULL)
ASKER CERTIFIED SOLUTION
Avatar of Bill Prew
Bill Prew
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Steve McFarland
Steve McFarland
Flag of United States of America image

ASKER

That is exactly what I am looking for.  Thank you.