April 19, 2007 at 2:24 pm
Below is the stored procedure i am creating.I created @Report_Parameter_1 report parameter and added two values Forclosure and Bancrupcy'.
I AM GETTING ERROR Line 16: Incorrect syntax near '=' IN BELOW PROCEDURE IN THIS LINE
then dbo.tblRAIL.lkpRailid = 1.Do you know why i am getting this error.
Store Procedure
declare @Attorneyrname as string
declare @Report_Parameter_1 as string
SELECT DISTINCT
dbo.tblVENDOR.VendorName, dbo.tblVENDOR.VendorShortName, dbo.tblRAIL.RailDescr, dbo.tblSPIKE.SpikeDescr, dbo.tblRAIL.ScreenValue,
dbo.tlkpSID.SId, dbo.tblConVendtoSuperClient.VendorID
FROM dbo.tlkpSID INNER JOIN
dbo.tblSPIKE ON dbo.tlkpSID.SidID = dbo.tblSPIKE.SidId INNER JOIN
dbo.tblRAIL INNER JOIN
dbo.tblVENDOR ON dbo.tblRAIL.SuperClientVendorId = dbo.tblVENDOR.VendorId ON dbo.tblSPIKE.RailId = dbo.tblRAIL.RailId INNER JOIN
dbo.tblConVendtoSuperClient ON dbo.tblVENDOR.VendorId = dbo.tblConVendtoSuperClient.SuperClientId
WHERE (dbo.tblConVendtoSuperClient.VendorID = 199) AND (dbo.tblvendor.vendorid <> 123)
and (dbo.tblVENDOR.VendorName=@Attorneyrname)and
case when @Report_Parameter_1='Forclosure'
then dbo.tblRAIL.lkpRailid = 1,
case when @Report_Parameter_1='Bankrupcy'
then dbo.tblRAIL.lkpRailid = 2
end
April 19, 2007 at 2:48 pm
I am very sure that you cannot use the CASE function in the where clause of a query
Why do you not work this out using conditional logic before the query and then test for dbo.tblRAIL.lkpRailid being equal to a variable depending on whether you want ForClosure or Bankruptcy.
David
April 19, 2007 at 3:11 pm
This should work:
WHERE (dbo.tblConVendtoSuperClient.VendorID = 199)
AND (dbo.tblvendor.vendorid <> 123)
and (dbo.tblVENDOR.VendorName=@Attorneyrname)
and ((@Report_Parameter_1='Forclosure' and dbo.tblRAIL.lkpRailid = 1)
or (@Report_Parameter_1='Bankrupcy' and dbo.tblRAIL.lkpRailid = 2))
April 19, 2007 at 4:10 pm
Shouldn't this:
dbo.tblVENDOR ON dbo.tblRAIL.SuperClientVendorId = dbo.tblVENDOR.VendorId ON dbo.tblSPIKE.RailId =
be this:
dbo.tblVENDOR ON dbo.tblRAIL.SuperClientVendorId = dbo.tblVENDOR.VendorId AND dbo.tblSPIKE.RailId =
April 20, 2007 at 11:26 am
Actually, you can, just not in the manner that the original poster did it in. For an example (using Northwind):
SELECT
*
FROM
Customers
WHERE
City = Case Country WHEN 'Germany' THEN 'Berlin' ELSE 'London' END
In other words, you can case a single expression, but not the entire comparison.
While the other methods might work as well, the following changes to the original code will work (although both foreclosure and bankruptcy are mispelled, so if they are spelled correctly in the parameter, it won't return what you want).
declare @Attorneyrname as string
declare @Report_Parameter_1 as string
SELECT DISTINCT
dbo.tblVENDOR.VendorName, dbo.tblVENDOR.VendorShortName, dbo.tblRAIL.RailDescr, dbo.tblSPIKE.SpikeDescr, dbo.tblRAIL.ScreenValue,
dbo.tlkpSID.SId, dbo.tblConVendtoSuperClient.VendorID
FROM dbo.tlkpSID INNER JOIN
dbo.tblSPIKE ON dbo.tlkpSID.SidID = dbo.tblSPIKE.SidId INNER JOIN
dbo.tblRAIL INNER JOIN
dbo.tblVENDOR ON dbo.tblRAIL.SuperClientVendorId = dbo.tblVENDOR.VendorId ON dbo.tblSPIKE.RailId = dbo.tblRAIL.RailId INNER JOIN
dbo.tblConVendtoSuperClient ON dbo.tblVENDOR.VendorId = dbo.tblConVendtoSuperClient.SuperClientId
WHERE (dbo.tblConVendtoSuperClient.VendorID = 199) AND (dbo.tblvendor.vendorid <> 123)
and (dbo.tblVENDOR.VendorName = @Attorneyrname) and
dbo.tblRAIL.lkpRailid = CASE @Report_Parameter_1
WHEN 'Forclosure' THEN 1
WHEN 'Bankrupcy' THEN 2
END
April 20, 2007 at 12:24 pm
Thats quite interesting David I had never though of using CASE like that in the where clause of a query.
David
April 20, 2007 at 12:57 pm
David, it also works great in the ORDER BY clause, where you can handle some offbeat requirements. For instance, using the same Northwind table from above, let's say you want to order Customers by city, but want customers in your home-city (I'll use London in this case) to show up first. Just do something like this:
SELECT
*
FROM
Customers
ORDER BY
CASE City WHEN 'London' THEN 0 ELSE 1 END
,City
One of the most common uses of this is to make Null behave differently when sorting on a column.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply