Refer to alias in the same view

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

Refer to alias in the same view

Author

Topic

Chaoboy
Starting Member

15 Posts

Posted - 2010-02-11 : 15:31:14

Hello,
I am trying to create a view and am running into an issue.

in the view I create an alias for a formula

(CAST(left((right("PordTranDate", 4)), 2) + '/' + right("PordTranDate", 2) + '/' + left("PordTranDate", 4) AS datetime)) AS "Receipt Date",

Then i try to do a formula on the alias

(CASE
WHEN datepart(weekday, [Receipt Date]) = 2 THEN dateadd(day, -1, [Receipt Date])
ELSE (CASE
WHEN datepart(weekday, [Receipt Date]) = 3 THEN dateadd(day, -2, [Receipt Date])
ELSE (CASE
WHEN datepart(weekday, [Receipt Date]) = 4 THEN dateadd(day, -3, [Receipt Date])
ELSE (CASE
WHEN datepart(weekday, [Receipt Date]) = 5 THEN dateadd(day, -4, [Receipt Date])
ELSE (CASE
WHEN datepart(weekday, [Receipt Date]) = 6 THEN dateadd(day, -5, [Receipt Date])
ELSE (CASE
WHEN datepart(weekday, [Receipt Date]) = 7 THEN dateadd(day, -6, [Receipt Date])
ELSE [Receipt Date]
END)
END)
END)
END)
END)
END) AS [Forecast Week],

but it is not working. I do this kind of thing in Access all the time but am new to SQL and not sure how it is done here. Any help would be appreciated

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-11 : 15:44:13

Based on your formula to calculate Receiptdate, I believe the value for PordTranDate is in the "yyyyMMdd" format..if yes,..then you do not have to do all the substring and concat to convert to datetime..
just

CAST(PordTranDate as datetime) AS [Receipt_Date]

should do it.

Also in that case, your whole CASE statemet can be replaced with this line of code..

SELECT dateadd(day, 1-datepart(weekday, PordTranDate), PordTranDate) AS [Forecast Week]

Chaoboy
Starting Member

15 Posts

Posted - 2010-02-11 : 15:57:26

ok, I tried using

CAST(PordTranDate as datetime) AS [Receipt_Date]

and just removed the second portion to verify that the above code worked. It created the view, but when I tried to view the contents of the query I got this error

CAST(PordTranDate as datetime) AS [Receipt_Date]

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-11 : 15:59:57

Whats the error?

Chaoboy
Starting Member

15 Posts

Posted - 2010-02-11 : 16:04:41

sorry, mis-paste

ERROR: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic
overflow error converting expression to data type datetime.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-11 : 16:13:58

It appears PordTranDate has some values which cannot be converted to a date..
Can you try running this and see what it returns
select PordTranDate from
<urtable>
where isdate(PordTranDate) <> 1

It should return the values that cannot be converted and throwing the error.

Chaoboy
Starting Member

15 Posts

Posted - 2010-02-11 : 16:28:53

I ran it and it does not return any records

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-11 : 16:31:38

Hmm..can you post the full query you used to create the view?

Chaoboy
Starting Member

15 Posts

Posted - 2010-02-11 : 16:34:25

SELECT
root_PO_RECEIPT_DET.PordNbr AS [PO#],
root_PO_RECEIPT_DET.PordCtrlNbr AS [Control #],
root_PO_RECEIPT_DET.PordLine AS [Line#],
root_PO_RECEIPT_DET.InitItemNbr AS [Item ID],
root_PO_RECEIPT_DET.PordSeq,
root_PO_RECEIPT_DET.PordRef,
root_PO_RECEIPT_DET.PordTranDate,
CAST(PordTranDate as datetime) AS [Receipt_Date]
root_PO_RECEIPT_DET.PordGlPd,
root_PO_RECEIPT_DET.PordQtyRec,
root_PO_RECEIPT_DET.PordCostTot
FROM root_PO_RECEIPT_DET

as a side note, when i use my original code for the first part

(CAST(left((right([PordTranDate], 4)), 2) + '/' + right([PordTranDate], 2) + '/' + left([PordTranDate], 4) AS datetime)) AS [Receipt Date],

and leave the second part off it works just fine and i can view the contents of the Query

if it maters the data type in the source table of PordTranDate is int

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-11 : 17:02:45

quote:
if it maters the data type in the source table of PordTranDate is int

Great...you tols us now

Converting to varchar before casting as datetime should do it.

CAST(convert(varchar(10),PordTranDate) as datetime) AS [Receipt_Date]

EDIT : You can also change

SELECT dateadd(day, 1-datepart(weekday, PordTranDate), PordTranDate) AS [Forecast Week]

SELECT dateadd(day, 1-datepart(weekday, convert(varchar(10),PordTranDate)), convert(varchar(10),PordTranDate)) AS [Forecast Week]

Chaoboy
Starting Member

15 Posts

Posted - 2010-02-11 : 17:43:33

Thanks for all the help. Just to be sure; that second line

SELECT dateadd(day, 1-datepart(weekday, convert(varchar(10),PordTranDate)), convert(varchar(10),PordTranDate)) AS [Forecast Week]

That converts the date [receipt date] to the previous sunday of the week it falls in.

Chaoboy
Starting Member

15 Posts

Posted - 2010-02-11 : 17:59:04

Awesome. That worked great. Problem solved. I really appreciate it.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-11 : 18:10:43

Np. You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources