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
 

 

2 Where clauses inside a query in Cobol db2 pgm ??

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

New User


Joined: 16 Mar 2009
Posts: 13
Location: Chennai

PostPosted: Fri Jul 18, 2014 10:26 am    Post subject: 2 Where clauses inside a query in Cobol db2 pgm ??
Reply with quote

Hi All

Is it possible to have multiple where clause inside a query based on the WS variable inside a cobol db2 program.

Currently i use this way, but i wanted to know if it could be written better.

Code:
IF WS-A = 'Type 1'             
   EXEC SQL                                   
     SELECT FIELD1                               
      INTO :WS-FIELD1         
       FROM TABLENAME                   
      WHERE FIELD2 IN('X','Y')   AND
                  FIELD3 = :WS-FIELD3                   
   END-EXEC                                   
ELSE IF WS-A = 'Type 2'             
   EXEC SQL                                   
     SELECT FIELD1                               
      INTO :WS-FIELD1         
       FROM TABLENAME                   
      WHERE FIELD2 = 'Z'   AND
                  FIELD3 = :WS-FIELD3                   
   END-EXEC   
END-IF
Back to top
View user's profile Send private message

chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Fri Jul 18, 2014 12:07 pm    Post subject:
Reply with quote

Hi,

Can you elaborate in which way you want it better?

Persoanlly I could not see any issue in a way logic is written

One I can think is instead of nested IF's use EVALUATE.

Regards,
Chandan
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Fri Jul 18, 2014 12:12 pm    Post subject: Reply to: 2 Where clauses inside a query in Cobol db2 pgm ??
Reply with quote

Yes, I'm also not sure what you want to achieve. You want to make it more difficult to understand: so for what reason? Less to type?

I'd make those literals into 88's on WS-A (which I hope is not really called WS-A) and use EVALUATE TRUE. Everything with nice descriptive names. Simple SQL. Try it. Give it to a colleague and see which way they prefer. Give it to an analyst.
Back to top
View user's profile Send private message
gch77

New User


Joined: 16 Mar 2009
Posts: 13
Location: Chennai

PostPosted: Fri Jul 18, 2014 2:16 pm    Post subject:
Reply with quote

Sorry for the confusion. My question is, the correct code is like this and it works absolutely fine. My i want to optimize it further.

Code:
IF WS-A = 'Type 1'             
   EXEC SQL                                   
     SELECT FIELD1                               
      INTO :WS-FIELD1         
       FROM TABLENAME                   
      WHERE FIELD2 IN('X','Y')   AND
                  FIELD3 = :WS-FIELD3                   
   END-EXEC                                   
ELSE IF WS-A = 'Type 2'             
   EXEC SQL                                   
     SELECT FIELD1                               
      INTO :WS-FIELD1         
       FROM TABLENAME                   
      WHERE FIELD2 = 'Z'   AND
                  FIELD3 = :WS-FIELD3                   
   END-EXEC   
END-IF
END-IF


I would like to avoid repeating the SQL queries twice. Because the queries are one and the same except for the below change.
If WS-A is 'Type 1' the value of FIELD2 to be looked for is in('X','Y').
If WS-A is 'Type 2' the value of FIELD2 to be looked for is 'Z'.

I tried using an 88 variable like
Code:
01 WS-FIELD2  PIC(X) VALUE SPACES.
    88 WS-XY    PIC(X)  VALUE 'X','Y'.
    88 WS-Z      PIC(X) VALUE 'Z'.


and the code as:

Code:
IF WS-A = 'Type 1'   
    SET  WS-XY TO TRUE
ELSE IF WS-A = 'Type 2'
    SET WS-Z TO TRUE
END-IF
END-IF 


and query as :
Code:
EXEC SQL                                   
     SELECT FIELD1                               
      INTO :WS-FIELD1         
       FROM TABLENAME                   
      WHERE FIELD2 = :WS-FIELD2   AND
                  FIELD3 = :WS-FIELD3                   
END-EXEC


But it doesn't work correctly. Always Field2 is looked only for 'X'.

Hope the explanation is clear, Kindly advise.
Back to top
View user's profile Send private message
gch77

New User


Joined: 16 Mar 2009
Posts: 13
Location: Chennai

PostPosted: Fri Jul 18, 2014 4:00 pm    Post subject:
Reply with quote

Any help on the above pls. icon_sad.gif icon_sad.gif
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Fri Jul 18, 2014 5:08 pm    Post subject: Reply to: 2 Where clauses inside a query in Cobol db2 pgm ??
Reply with quote

Why do you want to "optimize it further"? Optimize it in terms of what?

You should read up on the SET and ponder upon how you thought that a one byte field could simultaneously contain two value (X and Y). After the SET to TRUE it is only ever going to contain X. That's not optimisation, that's breaking a working program - why?
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 642
Location: Whitby, ON, Canada

PostPosted: Fri Jul 18, 2014 6:10 pm    Post subject:
Reply with quote

I think that the TS wants to have 1 SQL statement with the entire WHERE clause defined by a variable.

That can be done, but it requires that you get into the topic of dynamic SQL, which will require a lot more coding.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Fri Jul 18, 2014 6:38 pm    Post subject: Reply to: 2 Where clauses inside a query in Cobol db2 pgm ??
Reply with quote

Yes, Don, I think you're right. But what does that optimize?
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1238
Location: Richfield, MN, USA

PostPosted: Fri Jul 18, 2014 6:46 pm    Post subject:
Reply with quote

Don't value "optimization" over "maintainability". Your successors will appreciate it.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Fri Jul 18, 2014 6:47 pm    Post subject: Reply to: 2 Where clauses inside a query in Cobol db2 pgm ??
Reply with quote

Hi,

I am also not sure what is meant by optimize further..

At a given time only one Sql query going to be executed so it will not cause any performance issue with having extra piece of code

if TS want to achieve above in one query I can see one way but I would not prefer to achieve it in that way. This is untested

Code:
EXEC SQL                                   
     SELECT FIELD1                               
      INTO :WS-FIELD1         
       FROM TABLENAME                   
      WHERE FIELD2 IN(:WS-X,:WS-Y,:WS-Z)   AND
                  FIELD3 = :WS-FIELD3                   
   END-EXEC     


Code:
IF WS-A = 'Type 1'
    Populate WS-X and WS-Y with  X and Y repectively.
    Populate WS-Z with X or high values provided high values not present in table
ELSE
  IF WS-A = 'Type 2'
      populate WS-X,WS-Y with high values or Z
      populate WS-Z with value Z


Regards,
Chandan
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Fri Jul 18, 2014 6:48 pm    Post subject:
Reply with quote

Don suggested to use a Dynamic SQL and that is the perfect choice here for the requirement as well it is already simplified as you have shown us but still if you want to rewrite it in one then may be below one can suit you but you need to test as you wanted,

Code:
   EXEC SQL                                   
     SELECT FIELD1 
           ,FIELD2                             
      INTO :WS-FIELD1
          ,:WS-FIELD2         
       FROM TABLENAME                   
      WHERE FIELD2 IN('X','Y', 'Z')   AND
            FIELD3 = :WS-FIELD3     

             
IF WS-FIELD2 = "Z"
   Process "Type 1" records
Else
  Process "Type 2" Records
END-IF
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Jul 24, 2014 12:57 pm    Post subject:
Reply with quote

what chandan.inst said, but using a group-field for criteria.

Code:
01 ws-criteria.
  03 ws-crit1 pic X.
  03 ws-crit2 pic X.

IF WS-A = 'Type 1'
     MOVE 'X' to ws-crit1
     MOVE 'Y' to ws-crit2
else
     MOVE 'Z' to ws-crit1
     MOVE 'Z' to ws-crit2
end-if

EXEC SQL                                   
      SELECT FIELD1                               
       INTO :WS-FIELD1         
        FROM TABLENAME                   
       WHERE FIELD2 IN :WS-CRIT AND
                   FIELD3 = :WS-FIELD3                   
    END-EXEC     
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1219
Location: Israel

PostPosted: Thu Jul 24, 2014 2:53 pm    Post subject: Reply to: 2 Where clauses inside a query in Cobol db2 pgm ??
Reply with quote

The compiler accepted this statement but I'm not sure it really works (I didn't execute it).
Not sure either it can be called 'optimization'
Code:
   EXEC SQL                                   
     SELECT FIELD1                               
     INTO :WS-FIELD1         
     FROM TABLENAME                   
     WHERE FIELD3 = :WS-FIELD3 AND
       (   (:WS-A = 'Type 1' AND FIELD2 IN ('X','Y'))
        OR (:WS-A = 'Type 2' AND FIELD2 = 'Z'       ) )
   END-EXEC
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
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
This topic is locked: you cannot edit posts or make replies. RANDOM Function in COBOL swapnil781 COBOL Programming 2 Tue Nov 15, 2016 6:17 pm
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts COBOL Version 6.1 Virendra Shambharkar COBOL Programming 5 Tue Nov 01, 2016 11:24 am


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