Create table from 2 queries, problem "WITH" clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Create table from 2 queries, problem "WITH" clause

Author

Topic

genlovesmusic09
Starting Member

5 Posts

Posted - 2013-06-20 : 10:18:15

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

5 Posts

Posted - 2013-06-20 : 11:01:17

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

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-20 : 11:06:03

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources