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.
| Author |
Topic |
|
jlbfor
Starting Member
3 Posts |
 Posted - 2008-05-31 : 16:24:44
|
| I created a query in Sybase SQL Anywhere and used the following column generation instructions. Could someone help with general guidelines as far as using it in SQL Server 2005 Express?Thanks."(if C_Base_Data.PLDate is not null then (Floor(cast((DATEDIFF(day, c_Base_Data.PLDate , _Ref_Date.refdate)/365.25)as Decimal(5,1)))) else 0 endif) as Current_Age,""(if Current_Age = 0 then 'T.unpl' else 'Planted' endif) as T_Unpl" |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-05-31 : 16:53:52
|
have a look at the CASE statement in books online.
elsasoft.org |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-01 : 03:02:04
|
quote:
Originally posted by jlbfor
I created a query in Sybase SQL Anywhere and used the following column generation instructions. Could someone help with general guidelines as far as using it in SQL Server 2005 Express?Thanks."(if C_Base_Data.PLDate is not null then (Floor(cast((DATEDIFF(day, c_Base_Data.PLDate , _Ref_Date.refdate)/365.25)as Decimal(5,1)))) else 0 endif) as Current_Age,""(if Current_Age = 0 then 'T.unpl' else 'Planted' endif) as T_Unpl"
Replace if with case when like thiscase when C_Base_Data.PLDate is not null then (Floor(cast((DATEDIFF(day, c_Base_Data.PLDate , _Ref_Date.refdate)/365.25)as Decimal(5,1)))) else 0 end as Current_Ageandcase when Current_Age = 0 then 'T.unpl' else 'Planted' end as T_Unpl |
|
|
|
jlbfor
Starting Member
3 Posts |
Posted - 2008-06-01 : 14:07:29
|
| Thank you both for the help.As far as I can see the quoted statements work fine but as part of a create view command I get an error message referring to the second of the two clauses above even though the line above it is executed prior to it:Msg 207, Level 16, State 1, Procedure AgeClassAnalysis, Line 30Invalid column name 'Current_Age'Is there something different in sql server syntax that I must pick up here?Thanks once again.John. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-01 : 14:43:46
|
quote:
Originally posted by jlbfor
Thank you both for the help.As far as I can see the quoted statements work fine but as part of a create view command I get an error message referring to the second of the two clauses above even though the line above it is executed prior to it:Msg 207, Level 16, State 1, Procedure AgeClassAnalysis, Line 30Invalid column name 'Current_Age'Is there something different in sql server syntax that I must pick up here?Thanks once again.John.
that error is because you are using the alias name in the next case statement. You can use alias names within same scope. EWither you need to repeat the entore expression for Current_Age in second case statement or make a derived table out of main query where Current_age value is got and access the column by alias name outside. If you want complete soln, please post your full query. |
|
|
|
jlbfor
Starting Member
3 Posts |
Posted - 2008-06-03 : 01:38:42
|
| Thanks visakh.John |
|
|
|
Faisal Nasir
Starting Member
4 Posts |
Posted - 2008-06-03 : 05:24:24
|
| Can We use two insert statements in one stored procedures with respect to two different tables Example;REATE PROCEDURE INSERTS @ItemName as varchar(50)@WName as varchar(50)ASinsert into Items ( ItemName ) values(@ItemName)insert into WareHouse (WareName ) values (@WName )GO |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-03 : 05:36:59
|
quote:
Originally posted by Faisal Nasir
Can We use two insert statements in one stored procedures with respect to two different tables Example;REATE PROCEDURE INSERTS @ItemName as varchar(50)@WName as varchar(50)ASinsert into Items ( ItemName ) values(@ItemName)insert into WareHouse (WareName ) values (@WName )GO
you can. Suggest you to open new questions as a seperate thread for getting quick solutions. |
|
|
|
|
|
|
|
|
Create Query