Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Need to write a complex sql query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sathish_rathinam

New User


Joined: 22 Aug 2005
Posts: 59
Location: india

PostPosted: Fri Jun 04, 2010 1:33 pm    Post subject: Need to write a complex sql query
Reply with quote

Hi ,
We have 4 fields in search criteria, user can feed these 4 fields in any probability (all the possible ways). So I need to write one query in DB2, it will satisfy all the possible probabilities of search, user may enter.

Give the suggestion whether the below query satisfies my above criteria or not.
-------------------------------------------------------------------------------------
SELECT A.GNS_ORGN_ID,
A.PART_PRO_SETUP_CRE_TIMESTAMP,
D.REQ_STA_TYPE_DESC,
E.IDENTIFIER_ID
FROM
PPR_ORG_PROFILE A,
PPR_SETUP B,
PPR_SETUP_REQ_ACTION C,
REQ_STA_TYPE D,
P04.CURRENCY E,
PPR_CURRENCY F
WHERE
A.GNS_ORGN_ID = B.GNS_ORGN_ID
AND
C.REQ_STA_TYP_CD = D.REQ_STA_TYP_CD
AND
F.CURR_CD = E.IDENTIFIER_ID
AND
A.GNS_ORGN_ID = :WS-ORG-ID OR E.IDENTIFIER_ID = :WS-ID OR B.ACT_EFF_TIMESTAMP = :WS-TIME-STAMP OR B.END_TIMESTAMP = :WS-END-TIME
-------------------------------------------------------------------------------------


Thanks,
Sathish
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Jun 04, 2010 1:48 pm    Post subject:
Reply with quote

Can the user fill in two conditions at the same time ? if so, then the query is incorrect.

you'll need something of this construct :

Code:
(:HV1 = 'notfilled' or :HV1 = column1) AND
(:HV2 = 'notfilled' or :HV2 = column2)

'notfilled' is something you need to know. it could be spaces or 0 or something like :hvind = -1

Ofcourse these kind of where clauses are terrible for performance.
If that is an issue you'll need to write different queries for the different possibilities.
Code:
evaluate hv1 = 'notfilled'  also hv2 = 'notfilled'
when true  also true  then display 'at least one criterium needed'
when true  also false then exec SQL ... and col2 = :HV2 end-exec
when false also true  then exec SQL ... and col1 = :hv1 end-exec
when false also false then exec SQL ... and col1 = :hv1 and co21 = :hv2 end-exec
end-evaluatue
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Jun 04, 2010 1:50 pm    Post subject:
Reply with quote

1. hope the 6 tables in your join are small.

2. you have timestamp host variables. if you have no user input for one of these, with what will you populate the timestamp host variable?

one way to handle queries like this is to use a between.

Code:

AND A.GNS_ORGN_ID = :WS-ORG-ID
  OR E.IDENTIFIER_ID = :WS-ID
  OR B.ACT_EFF_TIMESTAMP = :WS-TIME-STAMP
  OR B.END_TIMESTAMP = :WS-END-TIME


have a high and low timestamp variable.
Code:

05  WS-TIME-STAMP-low  pic x(26).
      88  low-range-time-stamp value '0001-01-01-00:00:00.000000'
05  WS-TIME-STAMP-hi  pic x(26).
      88  low-range-time-stamp value '3000-01-01-00:00:00.000000'

(may not be correct value for the timestamps, the - and :.)


if there is input, populate both hi and low with the input
if there is no input, populate both by SETing the corresponding lvl88's to true.

code could be:
Code:

  OR B.ACT_EFF_TIMESTAMP between :WS-TIME-STAMP-low and :ws-time-stamp-hi


you can do the same thing for the other columns.

you could also stop being so stubborn and write multiple sql's.

but, since you did not bother to tell us anything about the columns
and their interrelationships, we have to guess what solution would be applicable.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Jun 04, 2010 1:52 pm    Post subject:
Reply with quote

between solution I always forget. Nice one dbz
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Jun 04, 2010 1:55 pm    Post subject:
Reply with quote

GuyC,

I will always remember the between,
because I had a situation years ago where the 'leader' insisted we have
only 1 sql per module, in order to keep the dbrm size down
regardless of complexity of the sql.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us