MVSFORUMS.com

hisabarinath · Posted: Mon Apr 06, 2009 12:40 pm Post subject: Need Help to get this SQL Query

Hi All,

I have requirement like this,

From Enrolment Table, I need to get those records that who are all enrolled in last 1 or more than that in the SYSTEM.
Requirement details are

kolusu · Posted: Mon Apr 06, 2009 1:30 pm Post subject:

hisabarinath,

Couple of questions.

1. You have a record with 88 1982-07-01 1988-09-31 which is not a valid date. what would you do in that case?

2. when you say you need to get unique sysid what is the criteria to pick which one takes precedence ?

of ex:

hisabarinath · Posted: Mon Apr 06, 2009 2:47 pm Post subject:

Thanks Kolusu,

For your question
1. You have a record with 88 1982-07-01 1988-09-31 which is not a valid date. what would you do in that case?

Ans - In this case the End-date is NOT >= Current-date, So, this records is NOT eligible. So we should NOT select this SYSID (ID).

2. when you say you need to get unique sysid what is the criteria to pick which one takes precedence ?

Ans - Since, i need only the SYSID (ID), so there is NO specific criteria. Any one SYSID (ID) is enough with the eligible criteria.

The Bottom line is we need those IDs who enrolled in the system in the >= 1 year. & the ID should be active ie., end-date Shouldnt less than today's date.

kolusu · Posted: Mon Apr 06, 2009 3:13 pm Post subject:

hisabarinath,

Try this untested sql

hisabarinath · Posted: Mon Apr 06, 2009 3:34 pm Post subject:

Thanks Kolusu,

I'm getting -905 due to resource limit was exceeded

kolusu · Posted: Mon Apr 06, 2009 4:04 pm Post subject:

hisabarinath,

You only got 31 seconds of CPU time which is not sufficient for your query to run. You need to ask your DBA to raise the limit or define an index on it.

Check this link for a detail explanation of the error

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNMCJ12/2.3.430?

hisabarinath · Posted: Mon Apr 06, 2009 4:36 pm Post subject:

Thanks Kolusu, we got an issue with the SQL Logic, this time i have removed the keyword DISTINCT to test the SQL.

Since, the below 3 input records are NOT eligible. But the given SQL returns in the Output which is NOT Correct. Since, 2008-06,05,10 are with in 1 Year that mean they join less than 1 year span which should NOT select in the resultant QUERY.

kolusu · Posted: Mon Apr 06, 2009 4:52 pm Post subject:

hisabarinath,

huh? You want to test every START_DT with every other START_DT in the table? Assuming you have 5 million rows and you take the first row and compare it against 4 million 999 thousand 999 rows? bonk

. You seriously need to re think your logic

hisabarinath · Posted: Mon Apr 06, 2009 5:06 pm Post subject:

See i dont want to test every START_DT with every other START_DT in the table ie., as per your eg- i dont want to test first row and compare it against 4 milion 999 thousand 999 rows.

The requirement is,
Each record have a start-date and an end-date. Here we are comparing each record's End-date should compare with that corrsponding record's Start date NOT all the other.

Here by i'm giving detail scenerio,

hisabarinath · Posted: Mon Apr 06, 2009 5:27 pm Post subject:

Kolusu,

Is there any way that we can fine tune the below SQL

hisabarinath · Posted: Mon Apr 06, 2009 5:29 pm Post subject:

Opps Sorry.... last statement with START_DT

kolusu · Posted: Mon Apr 06, 2009 5:43 pm Post subject:

hisabarinath,

I give up. Your requirement and your output DOES NOT match. I am still not sure as to how you picked 90 1984-01-01 9999-12-31 with your last sql shown.

Anyways if that is what you want use this

hisabarinath · Posted: Tue Apr 07, 2009 10:33 am Post subject:

Kolusu, Thanks for your time. My requirement is satisfying with the below SQL. How ever, you're really great guy.