Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 1281
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: 6968
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: 1281
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: 6968
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 Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Match two files using multiple keys a... santoshks1987 SYNCSORT 11 Fri Aug 18, 2017 10:50 am
No new posts Read two inputs and write into output... murali.andaluri DFSORT/ICETOOL 6 Wed Jul 26, 2017 7:35 pm
No new posts Write out NODUPS but just from one file Jay Villaverde DFSORT/ICETOOL 8 Fri Jul 14, 2017 12:44 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us