Local Variables in DB2 SQL

Hi,
  I'm on DB2/AIX64 8.2.9
   I wrote a Freehand SQL which runs fine.But it has some hard-coded dates, which i want to declare on top and be able to change at a single place instead of changing it at several places manually.
   A small bit of code which i'm using.
SELECT
l.case_id
FROM tp_Cases
WHERE Start_Date >= '05/01/2009' AND End_Date <= '12/31/2009'.

Now, what i want to achieve is in the following lines :

DECLARE v_startdate date,v_enddate date;
set v_startdate = '05/01/2009', v_enddate = '12/31/2009';
SELECT
l.case_id
FROM tp_Cases
WHERE Start_Date >= v_startdate AND End_Date <= v_enddate.

How can i do it? Please advise.

Thanks!


 
pvsbandiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
you will have to use a stored procedure
or, if you want to be a bit more flexiable, try this

with a as (
 select '05/01/2009' startDate, '12/31/2009' endDate
 from sysibm.sysdummy1
)

SELECT
l.case_id
FROM tp_Cases, a
WHERE Start_Date >= a.startdate AND End_Date <= a.enddate.
0
Kent OlsenDBACommented:
Hi pvsbandi,

Most DB2 clients don't support local variables.  In fact, I don't know any that do.

You'll have to edit the script or build a stored procedure and pass the dates to it.


Kent
0
pvsbandiAuthor Commented:
Momi_Sebag,

   Thanks! It works. But i have a small problem and i'm hoping ,would be answered as well.
   I have joins in the ANSI SQL form.In that case, how to use this? For instance, in the below query.
   Where to join the "dates" query, that you suggested? Please advise.

SELECT
pd.case_id,
FROM PAYMENT_DETAIL PD
JOIN PAYMENT_STATUS PS
ON PD.PAYMENT_ID=PS.PAYMENT_ID
JOIN TB_PAYMENT_HEADER PH
WHERE PS.DELETE_SW='N' AND PD.DELETE_SW='N'
AND PAYMENT_STATUS_DT BETWEEN '05/01/2009' AND '12/31/2009'  
0
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Kent OlsenDBACommented:
Hi pvsbandi,

I've used the query that momi suggests quite a few times.  It works well on small data sets.  If you've got a lot of data (or the query filters a lot of data) you should avoid this style query as it adds at least 1 full table scan to the query over hard-coding the dates into the query.

But the join is easy to do.

with a as (
 select '05/01/2009' startDate, '12/31/2009' endDate
 from sysibm.sysdummy1
)
SELECT l.case_id
FROM tp_Cases,
CROSS JOIN a
WHERE Start_Date >= a.startdate AND End_Date <= a.enddate.



Good Luck,
Kent
0
pvsbandiAuthor Commented:
Hi Kent,

  Doesn't work for me.IT's throwing an error, when i'm trying to do the following.

with ttt as (
 select '07/01/2009' v_startDate, '12/31/2009' v_endDate
 from sysibm.sysdummy1)
SELECT
pd.case_id,
FROM PAYMENT_DETAIL PD
JOIN PAYMENT_STATUS PS
ON PD.PAYMENT_ID=PS.PAYMENT_ID
JOIN ttt
WHERE PS.DELETE_SW='N' AND PD.DELETE_SW='N'
AND PAYMENT_STATUS_DT BETWEEN v_startdate AND v_enddate
0
momi_sabagCommented:
try

with ttt as (
 select '07/01/2009' v_startDate, '12/31/2009' v_endDate
 from sysibm.sysdummy1)

SELECT
pd.case_id,
FROM PAYMENT_DETAIL PD
JOIN PAYMENT_STATUS PS
ON PD.PAYMENT_ID=PS.PAYMENT_ID
JOIN ttt
ON PAYMENT_STATUS_DT BETWEEN v_startdate AND v_enddate
WHERE PS.DELETE_SW='N' AND PD.DELETE_SW='N'

and by the way, this query should be as efficient as the one with the hard coded values since you use a between predicate, db2 should be able to use an index for this
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pvsbandiAuthor Commented:
You are Excellent!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.