iSeries DB2 SQL subquery to compare yesterday sales to 120 day ternd

20 pts.
Tags:
Date format
Date variable
DB2/400
SQL Query
SQL Subselect
Hi, I have an SQL query that I would like to populate with veriable date information each time it runs. If I hard code the dates, I get the results I expect. However, I can not get the date parameters to work with my query. Here's the " Hard coded version (Works) SELECT SLSBIGD.R1SSID,SLSBIGD.prdistynm,SLSBIGD.prdiclrd,SLSBIGD.prdiclrnm, sum(SLSBIGD.MNET$) as StdNetSales, (SELECT sum(SLSBIGDOG120.MNET$) as Net120Trend FROM BICOGNOS.SLSBIGDOG as SLSBIGDOG120 WHERE SLSBIGDOG120.TRXDTE >= 20080131 AND SLSBIGDOG120.R1SSID =SLSBIGD.R1SSID AND SLSBIGDOG120.prdiclrd = SLSBIGD.prdiclrd AND SLSBIGDOG120.INTRCMPY = 'N' and SLSBIGDOG120.HDIVCODE <> 'SAMP') AS N120Trend FROM BICOGNOS.SLSBIGDOG as SLSBIGD WHERE SLSBIGD.TRXDTE = 20080528 AND SLSBIGD.PRDCAT = 'B' and SLSBIGD.INTRCMPY = 'N' and SLSBIGD.R1SSID <> ' ' and SLSBIGD.HDIVCODE <> 'SAMP' GROUP BY SLSBIGD.R1SSID,SLSBIGD.prdistynm,SLSBIGD.prdiclrd,SLSBIGD.prdiclrnm As you can see the date field TRXDTE has a date format of YYYYMMDD, so filtering the data requires the date parameter to be passed to the SQL in the same format. I have tried a few of the following: SELECT SLSBIGD.TRXDTE, REPLACE(CHAR((current date - 120 days),iso),'-','') as D120DaysAgo FROM BICOGNOS.SLSBIGDOG SLSBIGD WHERE SLSBIGD.TRXDTE =20080528 and I get the result I expect...but how do I use the result in my original query? I feel like I'm so close on this, but would really appriciate any help. Thanks, Marc Green
ASKED: May 30, 2008  2:31 PM
UPDATED: May 30, 2008  6:28 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Looks like I posted before trying all options. Here’s the result:

SELECT SLSBIGD.R1SSID,SLSBIGD.prdistynm,SLSBIGD.prdiclrd,SLSBIGD.prdiclrnm,
sum(SLSBIGD.MNET$) as StdNetSales,
(SELECT sum(SLSBIGDOG.MNET$) as Net120Trend
FROM BICOGNOS.SLSBIGDOG SLSBIGDOG
WHERE SLSBIGDOG.TRXDTE >= (SELECT REPLACE(CHAR((current_date – 120 days),iso),’-',”) as D120DaysAgo from qsys2.qsqptabl ) AND
SLSBIGDOG.R1SSID =SLSBIGD.R1SSID AND
SLSBIGDOG.prdiclrd = SLSBIGD.prdiclrd AND
SLSBIGDOG.INTRCMPY = ‘N’ and
SLSBIGDOG.HDIVCODE <> ‘SAMP’) AS N120Trend
FROM BICOGNOS.SLSBIGDOG SLSBIGD
WHERE SLSBIGD.TRXDTE = (SELECT REPLACE(CHAR((current_date – 1 days),iso),’-',”) as D1DaysAgo from qsys2.qsqptabl) AND
SLSBIGD.PRDCAT = ‘B’ and
SLSBIGD.INTRCMPY = ‘N’ and
SLSBIGD.R1SSID <> ‘ ‘ and
SLSBIGD.HDIVCODE <> ‘SAMP’
GROUP BY SLSBIGD.R1SSID,SLSBIGD.prdistynm,SLSBIGD.prdiclrd,SLSBIGD.prdiclrnm

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following