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 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: 1281
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: 1281
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. Cobol to fetch the content by using s... arunc55 COBOL Programming 4 Fri Nov 17, 2017 1:25 pm
No new posts VBS and VB, COBOL syntax is the same ... natt.sut COBOL Programming 3 Sun Nov 12, 2017 6:36 am
No new posts COBOL - EXIT statement weird behavior anthony.pangestu COBOL Programming 0 Fri Oct 27, 2017 9:57 am
No new posts IEW2456E error when link-editing a C ... Senthilraj JCL & VSAM 0 Fri Oct 13, 2017 3:12 pm
No new posts Assembler Macro Copybook converted to... Ziquilix PL/I & Assembler 4 Tue Sep 26, 2017 3:07 am

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