ReUsing Calculated Columns - SQL Server Forums

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

ReUsing Calculated Columns

Author

Topic

luminal69
Starting Member

3 Posts

Posted - 2008-02-20 : 10:42:39

Okay, so yes, I am new to SQL server...

I have this SP below, and I am trying to reuse the value returned by the Dateofplanningdate column so that I don't have to enter the code for each additional column I create. I have tried temp tables and derived tables with no luck.

REATE Proc CreateMasterSchedule
as

Select

dbo.[MOP_Planning Overview].warehouse,
dbo.[MOP_Planning Overview].[Item Number],
dbo.[MOP_Planning Overview].[Planning Date],
CAST (Convert (char(10),[Planning Date], 110)as DateTime)as DateofPlanningDate,

(case when dbo.[MOP_Planning Overview].[Order Category]='101' AND CAST (Convert (char(10),[Planning Date], 110)as DateTime)
<= (CAST (Convert (char(10),(dateadd(day, 8 - DATEPART(dw, dateadd(d,@@DATEFIRST-8,getdate())) ,getdate())-7),110) as DateTime)-1) then dbo.[MOP_Planning Overview].[Transaction Quantity - Basic U/M] else 0 end)as PriorInProc,

If I try to use DateofPlanningDate in the above case statement, I get the invalid column name error.

Basically, I just need a way to reuse the value returned by this column.

Can anyone help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-20 : 10:54:31

You cannot use alias created in select list inside the CASE ststement. You need to repeat the entire calaculated statement there.

luminal69
Starting Member

3 Posts

Posted - 2008-02-20 : 10:57:34

Okay, thank you very much. It seemed like this was the case....

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-20 : 10:58:42

Or use derived table as below:

select 
	warehouse,
	[Item Number],
	[Planning Date],
	(case when [Order Category]='101' AND DateofPlanningDate<= (CAST (Convert (char(10),(dateadd(day, 8 - DATEPART(dw, dateadd(d,@@DATEFIRST-8,getdate())) ,getdate())-7),110) as DateTime)-1) then [Transaction Quantity - Basic U/M] else 0 end)as PriorInProc
from
(Select 
dbo.[MOP_Planning Overview].warehouse,
dbo.[MOP_Planning Overview].[Item Number],
dbo.[MOP_Planning Overview].[Planning Date],
CAST (Convert (char(10),[Planning Date], 110)as DateTime)as DateofPlanningDate
from table
) t

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-02-20 : 11:34:11

Whay are you casting a date to a string and then performing date manipulation code on it?
leave it as a date....and tackle any conversion (presentation issues) in your UI.

luminal69
Starting Member

3 Posts

Posted - 2008-02-20 : 11:38:21

I used the derived table, and it almost works. When I exec the SP, it returns over 9 million records, yet I know there are only 25. It appears that some type of lopp is happening.

Any thoughts?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-20 : 11:41:26

You need to give the SP code for that along with table structures & o/p you are trying to get

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources