Joined: 13 Jun 2007 Posts: 632 Location: Wisconsin
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?
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
Joined: 13 Jun 2007 Posts: 632 Location: Wisconsin
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;
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
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).
