IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Need to write a complex sql query


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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: 1281
Location: Belgium

PostPosted: Fri Jun 04, 2010 1:48 pm
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
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: 1281
Location: Belgium

PostPosted: Fri Jun 04, 2010 1:52 pm
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Write line by line from two files DFSORT/ICETOOL 7
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Query on edit primary command CLIST & REXX 5
No new posts Query on edit primary command CLIST & REXX 1
Search our Forums:

Back to Top