WITH Clause - CTE

Trash
Previous topicNext topic

Topic Author
Quasar Chunawala
Registered Member
Posts: 35
Joined: Sun Aug 11, 2013 4:48 pm
Location: Pune
Zodiac: Aquarius

WITH Clause - CTE

Post by Quasar Chunawala » Tue Mar 11, 2014 3:44 pm

I thought of sharing this tip about Common table expressions in SQL. Its a very handy thing.

Very often, you might have used in-line SELECT's in SQL Queries(look at Query X and Query Y) -

Code: Select all

SELECT X.col1,X.col2,Y.col1,Y.col2
FROM
(SELECT a,b,c
 FROM tbl1
) AS X,
(SELECT d,e,f
FROM tbl2
) AS Y
With common table expressions allow you can re-write this in a neat way. Declare all temporary tables, you'd like to have in the WITH Clause. You can then use them anywhere in the main query. Makes up for much more readable code.

Code: Select all

WITH
  UNPOOLED (UNP_TRADES) AS
           (SELECT ACTN_ARG_TX
            FROM TBL
            WHERE ...),

  IN_PROG  (IPG_TRADES) AS
           (SELECT ACTN_ARG_TX
            FROM TBL
            WHERE ...),

  POOLED   (END_TRADES) AS
           (SELECT ACTN_ARG_TX
            FROM A_GSF_GNL_CTRL
            WHERE ...)
SELECT *
FROM UNPOOLED, IN_PROG, POOLED



User avatar

Anuj Dhawan
Founder
Posts: 2624
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Zodiac: Sagittarius

Re: WITH Clause - CTE

Post by Anuj Dhawan » Wed Mar 12, 2014 12:46 pm

Hi Quasar,

As we talked over the PM, could you please make this post under the DB2 Tips Section. Once you do that, I'll delete this thread.

I can make this post there, however, then it'll come under my name and it's essentially not my contribution, it's yours. :)

Regards,




Topic Author
Quasar Chunawala
Registered Member
Posts: 35
Joined: Sun Aug 11, 2013 4:48 pm
Location: Pune
Zodiac: Aquarius

Re: WITH Clause - CTE

Post by Quasar Chunawala » Wed Mar 12, 2014 4:25 pm

Anuj,

How do you manage to do all the glittery formatting - the boxes that you put? :D

Quasar.



Previous topicNext topic

Return to “Trash.”