MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

SQL vs Cobol loop

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
vak255
Intermediate


Joined: 10 Sep 2004
Posts: 384
Topics: 79

PostPosted: Tue May 24, 2016 10:43 am    Post subject: SQL vs Cobol loop Reply with quote

Code:

SELECT D7218.COMM_RULE_ID                       
     , D7218.MANF_REP_NUM                       
     , D7218.ACCT_NUM               
     , D7218.PROD_LINE_CD                       
     , D7218.STOCK_NUM           
     , D7218.COMM_PCT                           
     , D7218.GROSS_NET_IND                       
     , D7218.RULE_EFF_BEG_DT                     
     , D7218.RULE_EFF_END_DT                     
 FROM COMM_RULE D7218             
 WHERE ((D7218.RULE_EFF_BEG_DT <= CURRENT_DATE   
   AND  D7218.RULE_EFF_END_DT >= CURRENT_DATE) AND


   ( (D7218.MANF_REP_NUM = :WS-MANF  AND         
      D7218.ACCT_NUM = :WS-ACCT AND                   
     D7218.STOCK_NUM =  :WS-STOCK AND           
     D7218.PROD_LINE_CD = :WS-PROD )



I need to check the above query for four conditions.
either or both D7128.ACCT, STOCK can be zeros.

So First passing all the four values WS-MANF, WS-ACCT, WS-STOCK, and WS-PROD.
then passing zeros to STOCK, then for ACCT, then zeros for both

In cobol, I can put the above sql in a loop and execute it until one of the four condition is met like below in cobol.
Code:


IF
  ( (D7218.MANF_REP_NUM = :WS-MANF  AND         
      D7218.ACCT_NUM = :WS-ACCT AND                   
     D7218.STOCK_NUM =  :WS-STOCK AND           
     D7218.PROD_LINE_CD = :WS-PROD ) OR     
           
ELSE-IF
       (D7218.MANF_REP_NUM = :WS-MANF  AND         
      D7218.ACCT_NUM = :WS-ACCT AND                   
     D7218.STOCK_NUM = '0000000' AND           
     D7218.PROD_LINE_CD =  :WS-PROD) OR   

ELSE-IF
    (D7218.MANF_REP_NUM = :WS-MANF  AND         
      D7218.ACCT_NUM = 00 AND                   
     D7218.STOCK_NUM = :WS-STOCK AND           
     D7218.PROD_LINE_CD =  :WS-PROD) OR

ELSE-IF
      (D7218.MANF_REP_NUM = :WS-MANF  AND         
      D7218.ACCT_NUM = 00 AND                   
     D7218.STOCK_NUM = '0000000' AND           
     D7218.PROD_LINE_CD =  :WS-PROD) );
END-IF.


I like to check if this could be done using case\switch in the above SQL itself without checking it outside the query.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Tue May 24, 2016 11:25 am    Post subject: Re: SQL vs Cobol loop Reply with quote

vak255 wrote:

I need to check the above query for four conditions.
either or both D7128.ACCT, STOCK can be zeros.

So First passing all the four values WS-MANF, WS-ACCT, WS-STOCK, and WS-PROD.
then passing zeros to STOCK, then for ACCT, then zeros for both

I like to check if this could be done using case\switch in the above SQL itself without checking it outside the query.


Vak255,

Why complicate a simple requirement? If your intention is to get the record when ACCT and STOCK are missing the values then change those two columns to IN and use that in your where clause. something like this

Code:

  D7218.ACCT_NUM  IN (:WS-zero-acct,:WS-ACCT) AND 
  D7218.STOCK_NUM In (:Ws-zero-stock,:WS-STOCK) AND


Once you get the results you can pick the record that you want first. You can add an order by to put zero records at the bottom of the result or at top of the result list.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
vak255
Intermediate


Joined: 10 Sep 2004
Posts: 384
Topics: 79

PostPosted: Tue May 24, 2016 12:11 pm    Post subject: Reply with quote

Thanks Kolusu, I agree.


I was trying to fetch only one record out of the SQL as I need to process this one record further and the priority is it should first check without zeros, if there is a hit then fetch the row and stop,
else try with zeros on STOCK, if no hit,
then try with zeros on ACCT, if no hit
then try with zeros on both.

You are telling that I could fetch the first row by using order by...I got it. thanks.
Back to top
View user's profile Send private message
vak255
Intermediate


Joined: 10 Sep 2004
Posts: 384
Topics: 79

PostPosted: Tue May 24, 2016 12:40 pm    Post subject: Reply with quote

D7218.ACCT_NUM IN (:WS-zero-acct,:WS-ACCT) AND
D7218.STOCK_NUM In (:Ws-zero-stock,:WS-STOCK) AND

How should I include the NULLs?

ACCT - Numeric
STOCK - AlphaNumeric.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Tue May 24, 2016 6:08 pm    Post subject: Reply with quote

vak255 wrote:

How should I include the NULLs?

ACCT - Numeric
STOCK - AlphaNumeric.


vak255,

If you looked up the syntax for IN predicate or NULL predicate you would have easily figured it out.

Code:

(D7218.ACCT_NUM  IN (:WS-zero-acct,:WS-ACCT) OR
 D7218.ACCT_NUM  IS NULL) AND
(D7218.STOCK_NUM In (:Ws-zero-stock,:WS-STOCK) OR
 D7218.STOCK_NUM IS NULL) AND

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group