Click here to Skip to main content
15,880,543 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have the following SQL select statement - how do I turn it into a common table expression (CTE)?

SQL
SELECT  D.AIDNO      AS RIDNO      ,
	D.ALOANUMBER AS RLOANUMBER ,
	D.AIDNOLOAN  AS RIDNOLOAN  ,
	D.ACUSTOMER  AS RCUSTOMER  ,
	SUM( CASE E.BTRANS_TYPE WHEN 'TOTAL REPAYMENT'     THEN BAMOUNT ELSE 0 END ) AS RACT_AMT  ,
	SUM( CASE E.BTRANS_TYPE WHEN 'PRINCIPAL REPAYMENT' THEN BAMOUNT ELSE 0 END ) AS RACT_PRIN ,
	SUM( CASE E.BTRANS_TYPE WHEN 'INTEREST REPAYMENT'  THEN BAMOUNT ELSE 0 END ) AS RACT_INT  ,
	SUM(D.APAY_AMT-CASE E.BTRANS_TYPE WHEN 'TOTAL REPAYMENT' THEN BAMOUNT ELSE 0 END) AS RARR_AMT,
	COUNT(D.APAY_AMT) AS RAVG_PAY  , 
	D.AINT_NO    AS RINT_NO   ,
	D.APAY_TYPE  AS RPAY_TYPE   
	FROM #VLOANDED  D  JOIN #VTRANS E
	ON 	D.AIDNOLOAN = E.BIDNOLOAN 
 GROUP BY  D.AIDNOLOAN , D.AIDNO , D.ALOANUMBER, D.ACUSTOMER,
	D.AINT_NO , D.AINT_NAME , D.ABANKCD ,  D.ABANKNAME,  
        D.APAY_TYPE
 ORDER BY  D.AIDNOLOAN
Posted
Updated 23-Apr-15 6:20am
v3

1 solution

Every SELECT statement can be turned into a constant table expression by putting the syntax around it as documented here[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900