Help with IF and variables - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Help with IF and variables

Author

Topic

blackX
Posting Yak Master

102 Posts

Posted - 2008-01-10 : 14:57:30

Hello, I need help with the following query. Basically I need to set the variable @Comm=30 where OrgnMemDesc like 'UDI%'. Here is is

declare @Comm as int
set @Comm=15
SELECT Member.PrestnDate AS 'TourDate',
Member.ClosingCost AS 'Tax Paid',
Member.NewContractID AS 'Member Number',
Member.Lastname,
Location.Name AS 'Location',
CASE
WHEN orgnmemdesc < 'UDI' THEN ''
WHEN orgnmemdesc > 'UDI+ZZ' THEN ''
ELSE 'UDI'
END AS UDI,
Member.ProdType,
Member.GrossSalePrice AS 'Sales_Price',
Member.ProcessingFee,
Member.DownPayment,
Member.DownPaymentType,
Member.DownPayment2,
Member.DownPaymentType2,
(member.grosssaleprice * @Comm/100) + member.processingfee as 'PMG Commission'
from member
inner join location on
Location.Location_Id = Member.PrimaryMarkLocation_Id
WHERE Member.ProjectID In ('ODW','New Trails') AND
member.SalesRep<>'8999' and
((Member.PrestnDate>='2008-1-1' And Member.PrestnDate<='2008-1-7') AND
(Member.PrestnStatus='S')) OR
((Member.PrestnStatus='OH') and
(Member.OutOfHoldDate>='2008-1-1' And Member.OutOfHoldDate<='2008-1-7') and
Member.ProjectID In ('ODW','New Trails') AND
member.SalesRep<>'8999')
order by member.lastname

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 23:23:18

quote:
Originally posted by blackX

Hello, I need help with the following query. Basically I need to set the variable @Comm=30 where OrgnMemDesc like 'UDI%'. Here is is

declare @Comm as int
set @Comm=CASE WHEN LEFT(OrgnMemDesc,3)='UDI' THEN 30
ELSE 15
END
SELECT Member.PrestnDate AS 'TourDate',
Member.ClosingCost AS 'Tax Paid',
Member.NewContractID AS 'Member Number',
Member.Lastname,
Location.Name AS 'Location',
CASE
WHEN orgnmemdesc < 'UDI' THEN ''
WHEN orgnmemdesc > 'UDI+ZZ' THEN ''
ELSE 'UDI'
END AS UDI,
Member.ProdType,
Member.GrossSalePrice AS 'Sales_Price',
Member.ProcessingFee,
Member.DownPayment,
Member.DownPaymentType,
Member.DownPayment2,
Member.DownPaymentType2,
(member.grosssaleprice * @Comm/100) + member.processingfee as 'PMG Commission'
from member
inner join location on
Location.Location_Id = Member.PrimaryMarkLocation_Id
WHERE Member.ProjectID In ('ODW','New Trails') AND
member.SalesRep<>'8999' and
((Member.PrestnDate>='2008-1-1' And Member.PrestnDate<='2008-1-7') AND
(Member.PrestnStatus='S')) OR
((Member.PrestnStatus='OH') and
(Member.OutOfHoldDate>='2008-1-1' And Member.OutOfHoldDate<='2008-1-7') and
Member.ProjectID In ('ODW','New Trails') AND
member.SalesRep<>'8999')
order by member.lastname

blackX
Posting Yak Master

102 Posts

Posted - 2008-01-11 : 08:24:58

Thanks but that dont work. I get this error:
Invalid column name 'OrgnMemDesc'.

However I know that is a valid column name so it must be the syntax

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-11 : 08:29:51

Which table this column belongs too?

blackX
Posting Yak Master

102 Posts

Posted - 2008-01-11 : 08:33:43

member

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-11 : 09:11:32

try this then:-

declare @Comm as int
select @Comm=CASE WHEN LEFT(OrgnMemDesc,3)='UDI' THEN 30
ELSE 15
END
FROM Member

SELECT Member.PrestnDate AS 'TourDate',
Member.ClosingCost AS 'Tax Paid',
Member.NewContractID AS 'Member Number',
Member.Lastname,
Location.Name AS 'Location',
CASE
WHEN orgnmemdesc < 'UDI' THEN ''
WHEN orgnmemdesc > 'UDI+ZZ' THEN ''
ELSE 'UDI'
END AS UDI,
Member.ProdType,
Member.GrossSalePrice AS 'Sales_Price',
Member.ProcessingFee,
Member.DownPayment,
Member.DownPaymentType,
Member.DownPayment2,
Member.DownPaymentType2,
(member.grosssaleprice * @Comm/100) + member.processingfee as 'PMG Commission'
from member
inner join location on
Location.Location_Id = Member.PrimaryMarkLocation_Id
WHERE Member.ProjectID In ('ODW','New Trails') AND
member.SalesRep<>'8999' and
((Member.PrestnDate>='2008-1-1' And Member.PrestnDate<='2008-1-7') AND
(Member.PrestnStatus='S')) OR
((Member.PrestnStatus='OH') and
(Member.OutOfHoldDate>='2008-1-1' And Member.OutOfHoldDate<='2008-1-7') and
Member.ProjectID In ('ODW','New Trails') AND
member.SalesRep<>'8999')
order by member.lastname

blackX
Posting Yak Master

102 Posts

Posted - 2008-01-11 : 09:21:33

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources