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
 

 

Determining input and output fields in a SELECT statement

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

New User


Joined: 18 Jan 2006
Posts: 11

PostPosted: Fri Nov 16, 2007 8:48 am    Post subject: Determining input and output fields in a SELECT statement
Reply with quote

Hi,

How can I determine programatically all input and output fields of a SELECT statement? Is there any DB2 function for this?

Thanks,
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: Fri Nov 16, 2007 9:13 am    Post subject:
Reply with quote

Hello,

Please clarify your question.

An sql select doesn't have "input" and "output" fields.

If you show a small example, we may be able to provide answers.

Keep in mind that while your question is quite clear to you, it may not be to others.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Fri Nov 16, 2007 9:13 am    Post subject:
Reply with quote

Given the following 2 queries, what would you define as the inputs and the outputs?

Code:
 
SELECT *
FROM TABLE
WHERE COLA > 0 AND COLB < 100000 OR COLC = 100
ORDER BY COLA
FOR READ ONLY WITH UR;

SELECT COLA, COLB, COLD, COLE, COLF, SUM(1)
FROM TABLE
WHERE COLA > 0 AND COLB < 100000 OR COLC = 100
HAVING SUM(1) > 1
GROUP BY COLA, COLB, COLD, COLE, COLF
ORDER BY COLA
FOR READ ONLY WITH UR;


A simple, but not always applicable answer is:
Anything between SELECT and FROM would be the output fields. If you have scalars or a *, it won't be easy to figure them out. The input is anything between the WHERE and the next non-logic keyword (AND/OR)and also anything between HAVING and the next non-logic keyword. But would you consider an ORDER BY or GROUP BY an input to the statement?
Back to top
View user's profile Send private message
obner

New User


Joined: 18 Jan 2006
Posts: 11

PostPosted: Fri Nov 16, 2007 11:32 am    Post subject: Reply to: Determining input and output fields in a SELECT st
Reply with quote

Hi,

From the select statement below I consider COLA, COLB, COLD, COLE, COLF, SUM(1) as output fields and COLA > 0 AND COLB < 100000 OR COLC = 100 as input fields.

SELECT COLA, COLB, COLD, COLE, COLF, SUM(1)
FROM TABLE
WHERE COLA > 0 AND COLB < 100000 OR COLC = 100

Thanks!
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Fri Nov 16, 2007 6:42 pm    Post subject:
Reply with quote

But the ORDER BY, HAVING, and GROUP BY statements mention some field in the database and also affect how the output looks, so doesn't that make them input as well? Also, what about the first statement?

Here is one more. What are you input and output fields in this case? This is a slightly modified version of a production query we have in my shop. I haven't seen anything that can figure this sort of thing out automagically.
Code:

SELECT  A.TICKET_ID                                 
        ,A.TICKET_SRC_CD                             
        ,A.LAST_USER_ID                             
        ,A.TICKET_STATUS_CD                         
        ,A.ACCESS_ID                         
        ,DATE(A.FIRST_EFF_TS) AS CLOSED_DT           
        ,A.RECEIVED_TS                               
        ,A.SPONSOR_ID                                 
FROM TBQUAL.TICKET_T A                           
JOIN (SELECT Y.TICKET_ID, Y.FIRST_EFF_TS             
      FROM TBQUAL.TICKET_T Y                     
      JOIN (SELECT Z.TICKET_ID,                     
                   MIN(Z.FIRST_EFF_TS) AS minEFFDATE 
            FROM TBQUAL.TICKET_T Z               
            WHERE Z.TICKET_STATUS_CD = 'C'           
            GROUP BY TICKET_ID) MT                   
            ON Y.TICKET_ID = MT.TICKET_ID           
            AND Y.FIRST_EFF_TS = MT.minEFFDATE) B     
      ON A.TICKET_ID = B.TICKET_ID                 
      AND A.FIRST_EFF_TS = B.FIRST_EFF_TS             
WHERE DATE(A.FIRST_EFF_TS) >= (CURRENT date - 45 DAYS)
    WITH UR;
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: Fri Nov 16, 2007 9:21 pm    Post subject: Reply to: Determining input and output fields in a SELECT st
Reply with quote

Hello,

I believe the answer to your original question is that - no, you cannot find that programmatically (at least not easily - you would have to write a new sql syntax parser).

There is also no built-in command to do this.
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 Incorrect output when trying to add n... monica1 PL/I & Assembler 6 Fri Jan 13, 2017 5:02 pm
No new posts Syncsort Help to group fields sudhakarraju SYNCSORT 6 Thu Dec 29, 2016 1:38 am
No new posts Performing arithmetic on input field zh_lad DFSORT/ICETOOL 31 Tue Dec 06, 2016 8:04 pm
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 15 Thu Dec 01, 2016 8:28 pm
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am


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