View previous topic :: View next topic
|
Author |
Message |
sathish_rathinam
New User
Joined: 22 Aug 2005 Posts: 59 Location: india
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
between solution I always forget. Nice one dbz |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
|