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
 

 

Need your comment on 2 COBOL-DB2 SQL statement

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

New User


Joined: 15 Apr 2007
Posts: 3
Location: Vietnam

PostPosted: Wed Oct 28, 2009 8:52 pm    Post subject: Need your comment on 2 COBOL-DB2 SQL statement
Reply with quote

Hello,

Thank you for your comment on these two SQL statements, that the purpose is "get information of the last created appendix of type 'A' (from table APPENDIX) that have CLIENT, CONT and NUM_OP predefined in host variable H-CLIENT, H-CONT, H-NUM-OP"

1st SQL is
Code:
SELECT  T1.* INTO (:APPENDIX)
FROM APPENDIX    AS   T1
WHERE   T1.CLIENT        = :H-CLIENT
  AND   T1.CONT          = :H-CONT
  AND   T1.NUM_OP        = :H-NUM-OP
  AND   T1.TYPE_APPENDIX = 'A'
  AND  EXISTS
  ( SELECT 1
   FROM APPENDIX AS   T2
   WHERE   T2.CLI           = T1.CLI
     AND   T2.CONT          = T1.CONT
     AND   T2.NUM_OP        = T1.NUM_OP
     AND   T2.TYPE_APPENDIX = 'A'
   HAVING  T1.DATE_APPENDIX = MAX(T2.DATE_APPENDIX)
   )


2nd SQL is:
Code:
SELECT  *     INTO (:APPENDIX)
FROM APPENDIX 
WHERE  CLIENT        = :H-CLIENT
  AND  CONT          = :H-CONT
  AND  NUM_OP        = :H-NUM-OP
  AND  TYPE_APPENDIX = 'A'
  AND  DATE_APPENDIX = (SELECT MAX(DATE_APPENDIX)
                        FROM APPENDIX
                        WHERE CLIENT          = :H-CLIENT
                           AND  CONT          = :H-CONT
                           AND  NUM_OP        = :H-NUM-OP
                           AND  TYPE_APPENDIX = 'A')


For me, the 2nd statement is better, but I would like to hear your ideas and explication.

Thank you and nice night (it's 22:10 now here).
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Oct 29, 2009 1:34 pm    Post subject:
Reply with quote

sql 2 is definitely better.
I would have coded it correlated though (for maintainability), and not with "select *" but specifying all columns. (but that's another discussion)
Code:
SELECT  *     INTO (:APPENDIX)
FROM APPENDIX  T1
WHERE  CLIENT        = :H-CLIENT
  AND  CONT          = :H-CONT
  AND  NUM_OP        = :H-NUM-OP
  AND  TYPE_APPENDIX = 'A'
  AND  DATE_APPENDIX = (SELECT MAX(DATE_APPENDIX)
                   FROM APPENDIX T2
                   WHERE   T2.CLIENT        = T1.CLIENT
                     AND   T2.CONT          = T1.CONT
                     AND   T2.NUM_OP        = T1.NUM_OP
                     AND   T2.TYPE_APPENDIX = T1.TYPE_APPENDIX
                          )
Back to top
View user's profile Send private message
plvien

New User


Joined: 15 Apr 2007
Posts: 3
Location: Vietnam

PostPosted: Mon Nov 02, 2009 9:15 am    Post subject:
Reply with quote

Thank you for your response, GuyC.

Yes, "select *" is not good, I used it just for simplify the statement.

I don't know why my question has too few answer. If I made a mistake, please anyone tell me, thank you.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Nov 02, 2009 7:59 pm    Post subject:
Reply with quote

plvien wrote:
Thank you for your response, GuyC.

Yes, "select *" is not good, I used it just for simplify the statement.

I don't know why my question has too few answer. If I made a mistake, please anyone tell me, thank you.

Well,
1) you posted in cobol section and not in DB2.

2) which alternative of two working SQLs is "the best" is a matter of taste

3) explaining the difference in accesspath and performance in a few words is expecting more knowledge from the reader. If he would understand the explanation, he wouldn't have asked the question to begin with.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Nov 02, 2009 9:22 pm    Post subject:
Reply with quote

Hello,

Quote:
I don't know why my question has too few answer.
Because there was no need for lots of people to "chime in". . .

Usually, when one of the more knowledgable people post a direct answer to some question, there is not so much need for "thoughts from all over".

I'll move this to the DB2 part of the forum. . . icon_smile.gif
Back to top
View user's profile Send private message
plvien

New User


Joined: 15 Apr 2007
Posts: 3
Location: Vietnam

PostPosted: Tue Nov 03, 2009 1:07 pm    Post subject:
Reply with quote

Thanks GuyC and Dick icon_smile.gif
I'll be more careful the next time.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Nov 03, 2009 8:13 pm    Post subject: Reply to: Need your comment on 2 COBOL-DB2 SQL statement
Reply with quote

You're welcome icon_smile.gif

Quote:
I'll be more careful the next time.
Your post was fine - no problem.

As you spend more time here, the forum will become more familiar/comfortable.

d
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. Full Time COBOL Software Development ... shally Mainframe Jobs 0 Fri Jan 20, 2017 5:24 am
This topic is locked: you cannot edit posts or make replies. COBOL Software Development Lead at Fi... shally Mainframe Jobs 0 Fri Jan 20, 2017 5:21 am
No new posts Executing OO COBOL program invoking J... Virendra Shambharkar COBOL Programming 2 Tue Jan 10, 2017 6:37 pm
No new posts OO COBOL compile error Virendra Shambharkar COBOL Programming 3 Tue Jan 10, 2017 6:05 pm
No new posts Need Suggestion on COBOL program vickey_dw COBOL Programming 5 Thu Jan 05, 2017 10:55 pm


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