How to Create a View with a with Statement?
I Have a Query Which Contains the Following in the Beginning: ; with Xxx as Xxxxx I Am Unable to Create the View with the Statement and It Is Vital That I Keep...
I have a query which contains the following in the beginning:
; With xxx as xxxxx
I am unable to create the view with the statement and it is vital that I keep this statement.
Edit: here is the code:
; with numbered as (
Select part,taarich,hulia,mesirakabala,
rowno = row_number() OVER (Partition by parit order.by taarich)
From tblMK)
Select a.rowno-1,a.part, a.Julia,b.taarich,as.taarich_kabala,a.taarich, a.mesirakabala,getdatediff(b.taarich,a.taarich) as due
From numbered a
Left join numbered b ON b.parit=a.parit
And b.rowno = a.rowno - 1
Where b.taarich is not null
2 Answers
Put the with after the create view statement:
create view t2 as
with t as (select 1 as col)
select * from t;
Here is a SQL Fiddle showing this example.
I think your problem is the leading semicolon. Remove it and try again.
In SQL, the semicolon at the end of a line is optional, and usually omitted.
The WITH statement, however, REQUIRES the previous statement, if one exists, be terminated with a semicolon.
Since most people omit the semicolons, many authors will put the semicolon at the beginning of the WITH so it closes whatever statement came before.
If your WITH is the first line of defining your view, the semicolon is trying to terminate the view definition.
EDIT: Responding to comment
This may take a couple of passes, but the next step is to clean up your ROW_NUMBER column. replace your
rowno = row_number() OVER (Partition by parit order.by taarich)
with
row_number() OVER (Partition by part order by taarich) as rowno
and try again. The rowno = is an assignment, the as rowno is an alias, which I'm pretty sure is what you really want. And note that it's ORDER BY not ORDER.BY. And check spelling of "part" /"parit" are they the same field spelled differently or are both valid?
Clean those up and see if it chokes on anything else.
David Miller brings 15 years of experience in global economics, personal finance strategy, and market dynamics. He specializes in turning complex economic trends into actionable insights for everyday readers.
