Skip to Content
0
Former Member
Dec 22, 2007 at 11:08 AM

SQL Command - Paramater and wildcard

71 Views

Post Author: edy80y

CA Forum: Crystal Reports

Hi All,

I have successfully dumped an SQL into a 'Add Command' function and it returns the data exactly how i want it. But now i want to add a parameter so that i dont have to manually update the date which is referenced 8 times.

The TIME_KEY field is in this format YYYYMMDDHHMMWST (for 20/12/2007 2:30PM) = 200712201430WST

I have used LIKE '20071220%' so it returns all rows within that one date.

Now, i created a string parameter {?date) so i can enter '20071220' and i want to attach the wildcard (%) to the parameter.

If i do any of the following it doesn't work:

LIKE {?date} + '%'

LIKE {?date} || '%'

LIKE {?date} & '%'

Here is the code:

SELECT "T1"."OBJECT_ID", "T1"."TIME_KEY", (SELECT SUM("N_ANSWERED") AS "ANSWERED_ROLLING" FROM "GENESYSIINET"."R_WFM_GRO_Q_NO_AGG" "T2" WHERE "T1"."OBJECT_ID" = "T2"."OBJECT_ID" AND "T2"."TIME_KEY" <= "T1"."TIME_KEY" AND "T2"."TIME_KEY" LIKE '20071220%'), (SELECT SUM("N_ABANDONED_RING") AS "ABANDONED_RING_ROLLING" FROM "GENESYSIINET"."R_WFM_GRO_Q_NO_AGG" "T2" WHERE "T1"."OBJECT_ID" = "T2"."OBJECT_ID" AND "T2"."TIME_KEY" <= "T1"."TIME_KEY" AND "T2"."TIME_KEY" LIKE '20071220%'), (SELECT SUM("N_ABANDONED_QUEUE") AS "ABANDONED_QUEUE_ROLLING" FROM "GENESYSIINET"."R_WFM_GRO_Q_NO_AGG" "T2" WHERE "T1"."OBJECT_ID" = "T2"."OBJECT_ID" AND "T2"."TIME_KEY" <= "T1"."TIME_KEY" AND "T2"."TIME_KEY" LIKE '20071220%'), (SELECT SUM("N_ENTERED") AS "ENTERED_ROLLING" FROM "GENESYSIINET"."R_WFM_GRO_Q_NO_AGG" "T2" WHERE "T1"."OBJECT_ID" = "T2"."OBJECT_ID" AND "T2"."TIME_KEY" <= "T1"."TIME_KEY" AND "T2"."TIME_KEY" LIKE '20071220%'), (SELECT SUM("N_ABANDONED_SHORT") AS "ABANDONED_SHORT_ROLLING" FROM "GENESYSIINET"."R_WFM_GRO_Q_NO_AGG" "T2" WHERE "T1"."OBJECT_ID" = "T2"."OBJECT_ID" AND "T2"."TIME_KEY" <= "T1"."TIME_KEY" AND "T2"."TIME_KEY" LIKE '20071220%'), (SELECT SUM("N_ANS_IN_THRESH") AS "ANS_IN_THRESH_ROLLING" FROM "GENESYSIINET"."R_WFM_GRO_Q_NO_AGG" "T2" WHERE "T1"."OBJECT_ID" = "T2"."OBJECT_ID" AND "T2"."TIME_KEY" <= "T1"."TIME_KEY" AND "T2"."TIME_KEY" LIKE '20071220%'), (SELECT SUM("N_ABANDONED") AS "ABANDONED_ROLLING" FROM "GENESYSIINET"."R_WFM_GRO_Q_NO_AGG" "T2" WHERE "T1"."OBJECT_ID" = "T2"."OBJECT_ID" AND "T2"."TIME_KEY" <= "T1"."TIME_KEY" AND "T2"."TIME_KEY" LIKE '20071220%')FROM "GENESYSIINET"."R_WFM_GRO_Q_NO_AGG" "T1"WHERE "T1"."TIME_KEY" LIKE '20071220%'GROUP BY "T1"."OBJECT_ID", "T1"."TIME_KEY"ORDER BY "T1"."OBJECT_ID", "T1"."TIME_KEY"