SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Create table from 2 queries, problem "WITH" clause
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

genlovesmusic09
Starting Member

USA
5 Posts

Posted - 06/20/2013 :  10:18:15  Show Profile  Reply with Quote
Good Morning!

I am trying to combine two queries to make a table. (Please see code below)

CREATE TABLE Layer_Loss
(
dYear INT NOT NULL,
EventNum INT NOT NULL,
Loss INT NULL,
Rec_L1 BIGINT NULL,
Rec_L2 BIGINT NULL,
Rec_L3 BIGINT NULL,
Cap_CML_L1 BIGINT NULL,
Cap_CML_L2 BIGINT NULL,
Cap_CML_L3 BIGINT NULL,


)
INSERT INTO Layer_Loss (dYear,EventNum, Loss, Rec_L1, Rec_L2, Rec_L3, Capped_CML_L1, Capped_CML_L2, Capped_CML_L3)


WITH c AS (SELECT Row_number() OVER (ORDER BY dYear) AS rownum,*
FROM Layer_Loss_Capped2)
SELECT *
FROM
(
SELECT dYear, ROW_NUMBER() OVER (Partition by dYear Order by dYear) as Event_Number, Loss
, 'Recovery_L1'=CASE
WHEN Loss<10000000 THEN 0
WHEN Loss<30000000 THEN 20000000-(30000000-Loss)
ELSE 20000000
END
, 'Recovery_L2'=CASE
WHEN Loss<30000000 THEN 0
WHEN Loss<60000000 THEN 30000000-(60000000-Loss)
ELSE 30000000
END
, 'Recovery_L3'=CASE
WHEN Loss<60000000 THEN 0
WHEN Loss<100000000 THEN 40000000-(100000000-Loss)
ELSE 40000000
END
, (SELECT *, 'Capped_CML_L1'=CASE
WHEN d.CML_L1>40000000 THEN 4000000
ELSE d.CML_L1
END
, (SELECT *, 'Capped_CML_L2'=CASE
WHEN d.CML_L2>60000000 THEN 6000000
ELSE d.CML_L1
END
, (SELECT *, 'Capped_CML_L3'=CASE
WHEN d.CML_L1>80000000 THEN 8000000
ELSE d.CML_L1
END
FROM
(
SELECT a.dYear, a.EventNum, a.Loss, a.Rec_L1, SUM(b.Rec_L1) AS CML_L1, SUM(b.Rec_L2) AS CML_L2, SUM(b.Rec_L3) as CML_L3
FROM c a
LEFT JOIN c b ON a.dYear = b.dYear AND b.rownum <= a.rownum
GROUP BY a.dYear, a.rownum, a.EventNum, a.Rec_L1, a.Loss
) AS d
) AS e
FROM ['04_AIR_StdHU_DS_noSS_ByTerr$']
) AS a






DROP TABLE Layer_Loss


I have it so that the query about 'Recovery_L1', 'Recovery_L2', and 'Recovery_L3' are about of table "Layer_Loss", which I've called "Rec_L1", "Rec_L2", and "Rec_L3". When I try to add the query that leads to "Capped_CML_L1", "Capped_CML_L2", and "Capped_CML_L3" I get the following error:

"Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon."

I have tried moving 'WITH' clause around but end up with the same result.

Also, this is not my end result. My next step would be to subtract the current row from the previous row from the columns "Capped_CML_L1", "Capped_CML_L2", and "Capped_CML_L3" into a column called "Inc_Rec_L1", "Inc_Rec_L2", and "Inc_Rec_L3". I was thinking about using a cursor, but I have never used one before, so if you have any suggestions on this, that would be great too!

Thank you for your help!

genlovesmusic09
Starting Member

USA
5 Posts

Posted - 06/20/2013 :  11:01:17  Show Profile  Reply with Quote
Okay, so I've found out I need to move the "WITH" clause before "INSERT" and put a semi colon in front of it as such:
"
CREATE TABLE Layer_Loss_Capped2
(
dYear INT NOT NULL,
EventNum INT NOT NULL,
Loss INT NULL,
Rec_L1 BIGINT NULL,
Rec_L2 BIGINT NULL,
Rec_L3 BIGINT NULL,
Capped_CML_L1 BIGINT NULL,
Capped_CML_L2 BIGINT NULL,
Capped_CML_L3 BIGINT NULL,


)
;WITH c AS (SELECT Row_number() OVER (ORDER BY dYear) AS rownum,*
FROM Layer_Loss_Capped2)

INSERT INTO Layer_Loss_Capped2 (dYear,EventNum, Loss, Rec_L1, Rec_L2, Rec_L3, Capped_CML_L1, Capped_CML_L2, Capped_CML_L3)

SELECT *
FROM
(
SELECT dYear, ROW_NUMBER() OVER (Partition by dYear Order by dYear) as Event_Number, Loss
, 'Recovery_L1'=CASE
WHEN Loss<10000000 THEN 0
WHEN Loss<30000000 THEN 20000000-(30000000-Loss)
ELSE 20000000
END
, 'Recovery_L2'=CASE
WHEN Loss<30000000 THEN 0
WHEN Loss<60000000 THEN 30000000-(60000000-Loss)
ELSE 30000000
END
, 'Recovery_L3'=CASE
WHEN Loss<60000000 THEN 0
WHEN Loss<100000000 THEN 40000000-(100000000-Loss)
ELSE 40000000
END
FROM ['04_AIR_StdHU_DS_noSS_ByTerr$']
, (SELECT *, 'Capped_CML_L1'=CASE
WHEN d.CML_L1>40000000 THEN 4000000
ELSE d.CML_L1
END
, (SELECT *, 'Capped_CML_L2'=CASE
WHEN d.CML_L2>60000000 THEN 6000000
ELSE d.CML_L1
END
, (SELECT *, 'Capped_CML_L3'=CASE
WHEN d.CML_L1>80000000 THEN 8000000
ELSE d.CML_L1
END
FROM
(
SELECT a.dYear, a.EventNum, a.Loss, a.Rec_L1, SUM(b.Rec_L1) AS CML_L1, SUM(b.Rec_L2) AS CML_L2, SUM(b.Rec_L3) as CML_L3
FROM c a, ['04_AIR_StdHU_DS_noSS_ByTerr$']
LEFT JOIN c b ON a.dYear = b.dYear AND b.rownum <= a.rownum
GROUP BY a.dYear, a.rownum, a.EventNum, a.Rec_L1, a.Loss
) AS d
) AS e
) AS f
) AS g
) AS a

DROP TABLE Layer_Loss_Capped2
"

But, I am still having trouble getting the code to work. I have changed a few things on the bottom part, and still no resolve.

The FROM ['04_AIR_StdHU_DS_noSS_ByTerr$'] is for the 'Recovery_L1','Recovery_L2', and 'Recovery_L3'. I have tried moving it with the FROM c a (as seen above)

would like to be able to reference Capped_CML_L1, Capped_CML_L2, and Capped_CML_L3 in another query or table or cursor later on. I wanted it to be under just 'e' but I'm not sure how with the parentheses
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1050 Posts

Posted - 06/20/2013 :  11:06:03  Show Profile  Reply with Quote
Its hard to understand what you're trying to perform. What I've understood is that you want to insert into a table.
Can you illustrate your issue with the help of an example? Provide sample data in consumable format and the desired ouput you're looking for.

Cheers
MIK
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000