Dear all
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
AhmedAhmend
May be you need
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT B.Code, Sum(Amount) AS Total FROM B WHERE Month(B.Date
) =A.MonthDate
AND A.Code=C.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:eJcP$W1lFHA.32
56@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
Ahmed|||Sorry,correction
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT * FROM B WHERE Month(B.Date) =A.MonthDate
AND A.Code=C.Code)
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eHl4Ra1lFHA.3568@.tk2ms
ftngp13.phx.gbl...
Ahmend
May be you need
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT B.Code, Sum(Amount) AS Total FROM B WHERE Month(B.Date
) =A.MonthDate
AND A.Code=C.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:eJcP$W1lFHA.32
56@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
Ahmed|||Ahmed,
Try:
select code, name, monthdate, code,
(select sum(amount)
from b
where b.code = a.code
and month(b.date) = a.monthdate) as total
from a;
If you're after applying a table expression querying B and returning a
rowset, to each row from A, there's no set-based way to achieve this in SQL
Server 2000.
You'll have to use itterative logic. SQL Server 2005 solves this by
introducing the APPLY operator, e.g.,
-- CROSS APPLY Query Returning the Two Most Recent Sales Rows for each Store
USE pubs;
SELECT ST.stor_id, CA.*
FROM dbo.Stores AS ST
CROSS APPLY
(SELECT TOP(2) ord_num, title_id, ord_date, qty
FROM dbo.Sales AS SL
WHERE SL.stor_id = ST.stor_id AND qty >= 10
ORDER BY ord_date DESC, ord_num DESC, title_id DESC) AS CA;
BG, SQL Server MVP
www.SolidQualityLearning.com
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message
news:eJcP$W1lFHA.3256@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on
a value in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT
B.Code, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON
A.Code=C.Code
thanks in advance
Ahmed|||I think there is some error in your sql, What C reference for?
Ahmed
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eHl4Ra1lFHA.3568@.tk2ms
ftngp13.phx.gbl...
Ahmend
May be you need
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT B.Code, Sum(Amount) AS Total FROM B WHERE Month(B.Date
) =A.MonthDate
AND A.Code=C.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:eJcP$W1lFHA.32
56@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
Ahmed|||Ahemd
Yes , you are right , please take a look at Itzik's solution
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT * FROM B WHERE Month(B.Date) =A.MonthDate
AND A.Code=B.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:u5bldm1lFHA.32
88@.TK2MSFTNGP09.phx.gbl...
I think there is some error in your sql, What C reference for?
Ahmed
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eHl4Ra1lFHA.3568@.tk2ms
ftngp13.phx.gbl...
Ahmend
May be you need
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A
WHERE EXISTS (SELECT B.Code, Sum(Amount) AS Total FROM B WHERE Month(B.Date
) =A.MonthDate
AND A.Code=C.Code)
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message news:eJcP$W1lFHA.32
56@.TK2MSFTNGP12.phx.gbl...
Dear all
I need to make a correlated subquery that has the where clause depend on a v
alue in the master query, ex
Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
I want to perform SQL like this
SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT B.Code
, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C ON A.Cod
e=C.Code
thanks in advance
Ahmed|||Thanks BG
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:OLrJph1lFHA.3544@.TK2MSFTNGP15.phx.gbl...
> Ahmed,
> Try:
> select code, name, monthdate, code,
> (select sum(amount)
> from b
> where b.code = a.code
> and month(b.date) = a.monthdate) as total
> from a;
> If you're after applying a table expression querying B and returning a
> rowset, to each row from A, there's no set-based way to achieve this in
> SQL Server 2000.
> You'll have to use itterative logic. SQL Server 2005 solves this by
> introducing the APPLY operator, e.g.,
> -- CROSS APPLY Query Returning the Two Most Recent Sales Rows for each
> Store
> USE pubs;
> SELECT ST.stor_id, CA.*
> FROM dbo.Stores AS ST
> CROSS APPLY
> (SELECT TOP(2) ord_num, title_id, ord_date, qty
> FROM dbo.Sales AS SL
> WHERE SL.stor_id = ST.stor_id AND qty >= 10
> ORDER BY ord_date DESC, ord_num DESC, title_id DESC) AS CA;
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message
> news:eJcP$W1lFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Dear all
> I need to make a correlated subquery that has the where clause depend
> on a value in the master query, ex
> Suppose Table A(Code,Name,MonthDate),B(Code,Amount,Dat
e)
> I want to perform SQL like this
> SELECT A.Code, A.Name, A.MonthDate, C.Total FROM A INNER JOIN (SELECT
> B.Code, Sum(Amount) AS Total FROM B WHERE (Month(B.Date) =A.MonthDate)) C
> ON A.Code=C.Code
>
> thanks in advance
> Ahmed
>
Showing posts with label clause. Show all posts
Showing posts with label clause. Show all posts
Sunday, March 25, 2012
Monday, March 19, 2012
Can I build WHERE clause dynamically in a Stored Procedure in SQL Server 2005?
Hello,
I am using SQL Server 2005 and I am just wondering if I can build the
WHERE clause dynamically. I only want to build WHERE class if the my
@.VIN count is of exactly 17 charecters. Here is my code
****************************************
*********
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[SP_SearchGroundedVehicles]
@.vin varchar(17)
AS
BEGIN
SET NOCOUNT ON;
SELECT V.vin
,C.FirstName
,C.LastName
,C.AccountNum
,D.DealerShipName
,Contrt.MaturityDate
FROM ContractInfo Contrt
Inner Join VehicleInformation V ON Contrt.VehicleID = V.VehicleID
Inner Join DealerShips D ON Contrt.DealerShipID = D.DealerShipID
Inner Join Customer C ON Contrt.CustomerID = C.CustomerID
if count(@.vin) = 17
begin
Where VIN = @.VIn
end
END
****************************************
***************
But when I parse the above stored proc its throwing this message
********
Msg 156, Level 15, State 1, Procedure SP_SearchGroundedVehicles, Line
33
Incorrect syntax near the keyword 'Where'.
********
If it is possible to dynamically build WHERE clause please correct the
way I am doing it or if it is not possible what is the best way of
writing it?
Thanks
-LYou could do something like this:
SELECT V.vin
,C.FirstName
,C.LastName
,C.AccountNum
,D.DealerShipName
,Contrt.MaturityDate
FROM ContractInfo Contrt
Inner Join VehicleInformation V ON Contrt.VehicleID = V.VehicleID
Inner Join DealerShips D ON Contrt.DealerShipID = D.DealerShipID
Inner Join Customer C ON Contrt.CustomerID = C.CustomerID
where case when len(@.Vin) = 17 then @.Vin else VIN end = VIN|||Wow this is popular today.
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
"Learner" <pradev@.gmail.com> wrote in message
news:1142368232.024389.309720@.z34g2000cwc.googlegroups.com...
> Hello,
> I am using SQL Server 2005 and I am just wondering if I can build the
> WHERE clause dynamically. I only want to build WHERE class if the my
> @.VIN count is of exactly 17 charecters. Here is my code
> ****************************************
*********
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
> ALTER PROCEDURE [dbo].[SP_SearchGroundedVehicles]
> @.vin varchar(17)
> AS
> BEGIN
> SET NOCOUNT ON;
> SELECT V.vin
> ,C.FirstName
> ,C.LastName
> ,C.AccountNum
> ,D.DealerShipName
> ,Contrt.MaturityDate
> FROM ContractInfo Contrt
> Inner Join VehicleInformation V ON Contrt.VehicleID = V.VehicleID
> Inner Join DealerShips D ON Contrt.DealerShipID = D.DealerShipID
> Inner Join Customer C ON Contrt.CustomerID = C.CustomerID
> if count(@.vin) = 17
> begin
> Where VIN = @.VIn
> end
> END
> ****************************************
***************
> But when I parse the above stored proc its throwing this message
>
> ********
> Msg 156, Level 15, State 1, Procedure SP_SearchGroundedVehicles, Line
> 33
> Incorrect syntax near the keyword 'Where'.
> ********
>
> If it is possible to dynamically build WHERE clause please correct the
> way I am doing it or if it is not possible what is the best way of
> writing it?
> Thanks
> -L
>|||> where case when len(@.Vin) = 17 then @.Vin else VIN end = VIN
At first this struck me as incorrect, but technically it's not, just not in
the format I'd typically expect, e.g. compare the readability to:
WHERE VIN = CASE LEN(@.vin) WHEN 17 THEN @.Vin ELSE VIN END|||Hello,
Thank you all for the above suggestions and WHERE VIN = CASE LEN(@.vin)
WHEN 17 THEN @.Vin ELSE VIN END works.
I am not sure if we build this in SQL Server 2000 but SQL Server 2005
rocks!
Thanks
-L|||Question "du jour". :-)
Would you like a nice bottle of Merlot with that?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ueXCca6RGHA.1236@.TK2MSFTNGP11.phx.gbl...
> Wow this is popular today.
> http://www.sommarskog.se/dyn-search.html
> http://www.sommarskog.se/dynamic_sql.html
>
>
> "Learner" <pradev@.gmail.com> wrote in message
> news:1142368232.024389.309720@.z34g2000cwc.googlegroups.com...
>|||Well why not :) By the way what do you mean by "du jour". ?
Thanks
-L|||Well why not :) By the way what do you mean by "du jour". ?
Thanks
-L|||"Learner" <pradev@.gmail.com> wrote in message
news:1142376483.643521.89440@.v46g2000cwv.googlegroups.com...
> Well why not :) By the way what do you mean by "du jour". ?
> Thanks
> -L
It's French.
http://en.wikipedia.org/wiki/Soup_du_jour|||>> I only want to build WHERE class if the my @.VIN count is of exactly 17 ch
arecters.<<
This is crazy. A VIN is CHAR(17) by definition; read your ISO
standard. Furthermroe, it has a very fixed format for each position in
the string. What you want is a CHECK() constraint or procedure that
will validate your input
NOTES ON VIN:
In North America, a system is used that is far more stringent than the
ISO Standards but is "backward compatible." Here, the VIN is divided
into four sections:
The first three characters shall uniquely identify the manufacturer,
make and type of vehicle (with the same exception of manufacturers that
produce less than 500 vehicles). Effectively, this is the WMI. There
are indeed examples of manufacturers who have more than one WMI that
use the third character as a code for a vehicle category (for instance
bus or truck). Just as often however this is not the case;
The second section consists of five characters (VIN positions 4-8) and
identifies the attributes of the vehicle. For each type of vehicle
(passenger cars, MPV's, trucks, buses, trailers, motorcycles,
incomplete vehicles other than trailers), different information is
required. For cars, MPV's and light trucks it is required that the
first two characters of this section are alphabetic, the third and
fourth shall be numeric and the fifth alphanumeric. This section is the
VDS in ISO 3779 but there it comprises another position of the VIN;
The third section consists of one character which is the check digit,
calculated over the other 16 characters of the VIN. This character can
be numeric or the letter X;
The fourth section consists of eight characters on positions 10-17 of
the VIN. The last five shall be numeric for cars, MPV's and light
trucks and the last four shall be numeric for all other vehicles. The
first character represents the vehicle model year, the second character
represents the plant of manufacture. The third through eighth
characters are a sequential production number (for manufacturers
producing more than 500 vehicles per year). For other manufacturers,
the sixth, seventh and eight positions represent the sequential
production number.
This section confirms to the VIS in ISO 3779.
A portion of the VIN is the WMI (World Manufacturer Identifier) Code.
SAE assigns this code to U.S. vehicle manufacturers. If you are a U.S.
manufacturer, please contact:
Cathy Douds
WMI Coordinator
SAE International
400 Commonwealth Drive
Warrendale, PA 15096-0001
724.772.8511
724.776.4026 - fax
douds@.sae.org
Related Standards:
There are several standards available on VINs and WMIs:
SAE - J187 - Truck Vehicle Identification Numbers
SAE - J218 - Passenger Car Identification Terminology
SAE - J272 - Vehicle Identification Number Systems
SAE - J273 - Passenger Car Vehicle Identification Number System
SAE - J853 - Vehicle Identification Numbers
SAE - J1108 - Truck and Truck Tractor Vehicle Identification Number
Systems
SAE - J1044 - World Manufacturer Identifier
SAE - J1229 - Truck Identification Terminology
SAE - J1877 - Recommended Practice for Bar-Coded Vehicle Identification
Number Label
SAE J129 - Engine and Transmission Identification Numbers
ISO 3779 - Road vehicles - Vehicle identification number (VIN) Content
and structure
ISO 3780 - Road vehicles - World manufacturer identifier (WMI) code
I am using SQL Server 2005 and I am just wondering if I can build the
WHERE clause dynamically. I only want to build WHERE class if the my
@.VIN count is of exactly 17 charecters. Here is my code
****************************************
*********
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[SP_SearchGroundedVehicles]
@.vin varchar(17)
AS
BEGIN
SET NOCOUNT ON;
SELECT V.vin
,C.FirstName
,C.LastName
,C.AccountNum
,D.DealerShipName
,Contrt.MaturityDate
FROM ContractInfo Contrt
Inner Join VehicleInformation V ON Contrt.VehicleID = V.VehicleID
Inner Join DealerShips D ON Contrt.DealerShipID = D.DealerShipID
Inner Join Customer C ON Contrt.CustomerID = C.CustomerID
if count(@.vin) = 17
begin
Where VIN = @.VIn
end
END
****************************************
***************
But when I parse the above stored proc its throwing this message
********
Msg 156, Level 15, State 1, Procedure SP_SearchGroundedVehicles, Line
33
Incorrect syntax near the keyword 'Where'.
********
If it is possible to dynamically build WHERE clause please correct the
way I am doing it or if it is not possible what is the best way of
writing it?
Thanks
-LYou could do something like this:
SELECT V.vin
,C.FirstName
,C.LastName
,C.AccountNum
,D.DealerShipName
,Contrt.MaturityDate
FROM ContractInfo Contrt
Inner Join VehicleInformation V ON Contrt.VehicleID = V.VehicleID
Inner Join DealerShips D ON Contrt.DealerShipID = D.DealerShipID
Inner Join Customer C ON Contrt.CustomerID = C.CustomerID
where case when len(@.Vin) = 17 then @.Vin else VIN end = VIN|||Wow this is popular today.
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
"Learner" <pradev@.gmail.com> wrote in message
news:1142368232.024389.309720@.z34g2000cwc.googlegroups.com...
> Hello,
> I am using SQL Server 2005 and I am just wondering if I can build the
> WHERE clause dynamically. I only want to build WHERE class if the my
> @.VIN count is of exactly 17 charecters. Here is my code
> ****************************************
*********
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
> ALTER PROCEDURE [dbo].[SP_SearchGroundedVehicles]
> @.vin varchar(17)
> AS
> BEGIN
> SET NOCOUNT ON;
> SELECT V.vin
> ,C.FirstName
> ,C.LastName
> ,C.AccountNum
> ,D.DealerShipName
> ,Contrt.MaturityDate
> FROM ContractInfo Contrt
> Inner Join VehicleInformation V ON Contrt.VehicleID = V.VehicleID
> Inner Join DealerShips D ON Contrt.DealerShipID = D.DealerShipID
> Inner Join Customer C ON Contrt.CustomerID = C.CustomerID
> if count(@.vin) = 17
> begin
> Where VIN = @.VIn
> end
> END
> ****************************************
***************
> But when I parse the above stored proc its throwing this message
>
> ********
> Msg 156, Level 15, State 1, Procedure SP_SearchGroundedVehicles, Line
> 33
> Incorrect syntax near the keyword 'Where'.
> ********
>
> If it is possible to dynamically build WHERE clause please correct the
> way I am doing it or if it is not possible what is the best way of
> writing it?
> Thanks
> -L
>|||> where case when len(@.Vin) = 17 then @.Vin else VIN end = VIN
At first this struck me as incorrect, but technically it's not, just not in
the format I'd typically expect, e.g. compare the readability to:
WHERE VIN = CASE LEN(@.vin) WHEN 17 THEN @.Vin ELSE VIN END|||Hello,
Thank you all for the above suggestions and WHERE VIN = CASE LEN(@.vin)
WHEN 17 THEN @.Vin ELSE VIN END works.
I am not sure if we build this in SQL Server 2000 but SQL Server 2005
rocks!
Thanks
-L|||Question "du jour". :-)
Would you like a nice bottle of Merlot with that?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ueXCca6RGHA.1236@.TK2MSFTNGP11.phx.gbl...
> Wow this is popular today.
> http://www.sommarskog.se/dyn-search.html
> http://www.sommarskog.se/dynamic_sql.html
>
>
> "Learner" <pradev@.gmail.com> wrote in message
> news:1142368232.024389.309720@.z34g2000cwc.googlegroups.com...
>|||Well why not :) By the way what do you mean by "du jour". ?
Thanks
-L|||Well why not :) By the way what do you mean by "du jour". ?
Thanks
-L|||"Learner" <pradev@.gmail.com> wrote in message
news:1142376483.643521.89440@.v46g2000cwv.googlegroups.com...
> Well why not :) By the way what do you mean by "du jour". ?
> Thanks
> -L
It's French.
http://en.wikipedia.org/wiki/Soup_du_jour|||>> I only want to build WHERE class if the my @.VIN count is of exactly 17 ch
arecters.<<
This is crazy. A VIN is CHAR(17) by definition; read your ISO
standard. Furthermroe, it has a very fixed format for each position in
the string. What you want is a CHECK() constraint or procedure that
will validate your input
NOTES ON VIN:
In North America, a system is used that is far more stringent than the
ISO Standards but is "backward compatible." Here, the VIN is divided
into four sections:
The first three characters shall uniquely identify the manufacturer,
make and type of vehicle (with the same exception of manufacturers that
produce less than 500 vehicles). Effectively, this is the WMI. There
are indeed examples of manufacturers who have more than one WMI that
use the third character as a code for a vehicle category (for instance
bus or truck). Just as often however this is not the case;
The second section consists of five characters (VIN positions 4-8) and
identifies the attributes of the vehicle. For each type of vehicle
(passenger cars, MPV's, trucks, buses, trailers, motorcycles,
incomplete vehicles other than trailers), different information is
required. For cars, MPV's and light trucks it is required that the
first two characters of this section are alphabetic, the third and
fourth shall be numeric and the fifth alphanumeric. This section is the
VDS in ISO 3779 but there it comprises another position of the VIN;
The third section consists of one character which is the check digit,
calculated over the other 16 characters of the VIN. This character can
be numeric or the letter X;
The fourth section consists of eight characters on positions 10-17 of
the VIN. The last five shall be numeric for cars, MPV's and light
trucks and the last four shall be numeric for all other vehicles. The
first character represents the vehicle model year, the second character
represents the plant of manufacture. The third through eighth
characters are a sequential production number (for manufacturers
producing more than 500 vehicles per year). For other manufacturers,
the sixth, seventh and eight positions represent the sequential
production number.
This section confirms to the VIS in ISO 3779.
A portion of the VIN is the WMI (World Manufacturer Identifier) Code.
SAE assigns this code to U.S. vehicle manufacturers. If you are a U.S.
manufacturer, please contact:
Cathy Douds
WMI Coordinator
SAE International
400 Commonwealth Drive
Warrendale, PA 15096-0001
724.772.8511
724.776.4026 - fax
douds@.sae.org
Related Standards:
There are several standards available on VINs and WMIs:
SAE - J187 - Truck Vehicle Identification Numbers
SAE - J218 - Passenger Car Identification Terminology
SAE - J272 - Vehicle Identification Number Systems
SAE - J273 - Passenger Car Vehicle Identification Number System
SAE - J853 - Vehicle Identification Numbers
SAE - J1108 - Truck and Truck Tractor Vehicle Identification Number
Systems
SAE - J1044 - World Manufacturer Identifier
SAE - J1229 - Truck Identification Terminology
SAE - J1877 - Recommended Practice for Bar-Coded Vehicle Identification
Number Label
SAE J129 - Engine and Transmission Identification Numbers
ISO 3779 - Road vehicles - Vehicle identification number (VIN) Content
and structure
ISO 3780 - Road vehicles - World manufacturer identifier (WMI) code
Friday, February 24, 2012
Can a WHERE clause refer to a column based on a subquery ?
I've got a stored procedure that contains a query similar to the following which works.
(The real query contains lots more columns and the subquery is quite long)
(The real query contains lots more columns and the subquery is quite long)
SELECT table1.ColA, table1.ColB, (SELECT TOP 1 colX from table2 WHERE etc. ) FROM table1
Sometimes the sub-query returns nulls which is fine.
I now want to modify the stored procedure so that it will optionally only return the rows where the sub-query returns non-null values. I've added an int parameter @.filter to the procedure. If @.filter = 0 I will return all rows; if @.filter = 1 I will filter the results.
The query now looks like this:
SELECT table1.ColA, table1.ColB, subquery = (SELECT TOP 1 colX from table2 WHERE etc. ) FROM table1
WHERE (@.filter = 0 or not subquery is null).
However, I get an invalid column name 'subquery' error.
Have I simply got my syntax wrong or is it not possible to do this. I've played with a few variations such as '(SELECT ....) as subquery' but nothing seems to work.
I can imagine a solution where the sub-query is used to generate a table that table1 can be inner joined with, but the query is quite big (and has been tested) and I didn't want to start hacking it about if there was a trivial fix.
Any help appreciated.I have found the answer to my own question.
The SELECT parameters (including the sub-query) are not evaluated until after the WHERE so what I wanted to do is not possible.
I found the link below useful.
http://databases.aspfaq.com/database/how-do-i-use-a-select-list-alias-in-the-where-or-group-by-clause.html|||i din get ur exact requirement , but yes the syntax is not correct....
read a bit abt 'corelated subqueries' from books online..u'll hopefuly get a solution...
if u dont post back , i'll try to put some example...|||
The SELECT list expressions can be evaluated before the WHERE clause in SQL Server. But that has nothing to do with the problem though. Column aliases are only part of the result set metadata that the client uses. As such it can be used only in the ORDER BY clause (in ANSI SQL, the ORDER BY belongs on the client-side & hence it is allowed in ORDER BY). You can use derived table to simplify complex SELECT statements and avoid repeating expressions like:
SELECT ColA, ColB, ColN
FROM (
SELECT table1.ColA, table1.ColB
, (SELECT TOP 1 colX from table2 WHERE etc. ) as ColN
FROM table1
) as t
WHERE (@.filter = 0 or ColN is not null)
It works now.
Can a value be assigned to an Output parameter and be used in a where clause?
I need to update a table using a stored procedure that "UPSERTS". In this case, @.OfficeId is passed with ParameterDirection.Output
So when I update I assign the value to OfficeId like this (the office id value is being correctly assigned):
SqlParameter OfficeIdParam = new SqlParameter("@.OfficeId", SqlDbType.Int, 4);
if (_officeId > 0) { OfficeIdParam.Value = _officeId; }
OfficeIdParam.Direction = ParameterDirection.Output;
The following segment of the stored procedure is throwing an error that the OfficeId does not exist:
Update -- @.OfficeId int output
if not exists (select 1 from ORG_Office where OfficeId=@.OfficeId)
begin
RAISERROR ('OfficeId does not exist in ORG_Office: E002', 16, 1) -- OfficeId does not exist.
return -1
end
May this approach work, and if the operation turns out to be an Insert I may do this:
select @.error_code = @.@.ERROR, @.OfficeId= scope_identity()
Thank you,
jspurlin
I am thinking you need to add the parameter conditionally, instead of adding it and then setting the direction conditionally. (you might need to fix syntax errors as I dont do .NET code and definetely not in C#
If (_officeId > 0) {
SqlParameter OfficeIdParam = new SqlParameter("@.OfficeId", SqlDbType.Int, 4);
OfficeIdParam.Direction = ParameterDirection.Output;
}
else {
SqlParameter OfficeIdParam = new SqlParameter("@.OfficeId", SqlDbType.Int, 4);
}
Tuesday, February 14, 2012
Can a CASE Staement be used in the WHERE clause
I have 
myself and need some help clarifing how to weite the
following statement so that 3 difierent results can come from the value of
the passed Parameter. The parameter can have 0,1 or 2 as the values
What I'm trying to get back is the records that are automated if the value
is 1 or the records if the value is manual 0 other wise return both types of
records
SELECT Info.ID,
Info.Automated
FROM Info
WHERE (Info.Automated LIKE @.Auto))
Can a case statement be used in the where clause?
I want to change the Like to = if possible.
Thanks for the helpJim Abel (JimAbel@.discussions.microsoft.com) writes:
> I have myself and need some help clarifing how to weite the
> following statement so that 3 difierent results can come from the value
> of the passed Parameter. The parameter can have 0,1 or 2 as the values
> What I'm trying to get back is the records that are automated if the
> value is 1 or the records if the value is manual 0 other wise return
> both types of records
> SELECT Info.ID,
> Info.Automated
> FROM Info
> WHERE (Info.Automated LIKE @.Auto))
> Can a case statement be used in the where clause?
> I want to change the Like to = if possible.
There is no CASE statement in T-SQL, but there is a CASE expression. And,
yes, it can be used in WHERE clauses. I'm not sure that I understood
exactly what you wanted to do, but here is an example:
SELECT ..
FROM Info
WHERE Automated LIKE CASE WHEN @.param = 1 THEN @.Auto
WHEN @.param = 2 THEN @.Bil
WHEN @.param = 3 THEN @.Macchina
END
You find more information in Books Online.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks this is helping. Let me see know if I can get you to understand what
I'm trying to achive with the query because I'm not sure if a CASE expressio
n
is the right thing to do.
SELECT Info.ID, Info.Automated
FROM Info
WHERE (info.Automated = CASE
WHEN @.Auto = 0 THEN
audPolicyDetail.Auyomated = 0
WHEN @.Auto = 1 THEN
audPolicyDetail.Auyomated = 1
WHEN @.Auto = 2 THEN
audPolicyDetail.Auyomated in(0,1)))
END
"Erland Sommarskog" wrote:
> Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> There is no CASE statement in T-SQL, but there is a CASE expression. And,
> yes, it can be used in WHERE clauses. I'm not sure that I understood
> exactly what you wanted to do, but here is an example:
> SELECT ..
> FROM Info
> WHERE Automated LIKE CASE WHEN @.param = 1 THEN @.Auto
> WHEN @.param = 2 THEN @.Bil
> WHEN @.param = 3 THEN @.Macchina
> END
> You find more information in Books Online.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> Thanks this is helping. Let me see know if I can get you to understand
> what I'm trying to achive with the query because I'm not sure if a CASE
> expression is the right thing to do.
> SELECT Info.ID, Info.Automated
> FROM Info
> WHERE (info.Automated = CASE
> WHEN @.Auto = 0 THEN
> audPolicyDetail.Auyomated = 0
> WHEN @.Auto = 1 THEN
> audPolicyDetail.Auyomated = 1
> WHEN @.Auto = 2 THEN
> audPolicyDetail.Auyomated in(0,1)))
> END
I don't know what you are trying to achieve, but the above is not
correct. A CASE expression must return one of the data types in SQL Server,
and boolean is not a data type in SQL Server.
If also looks funny with audPolicyDetail, as there is no such table in
the query. Is this a CLR UDT column?
The general recommendation for query problems is that you post:
o CREATE TABLE statement(s) for your table(s).
o INSERT statements with sample data.
o The desired result given the sample.
Including this sort of information often helps to clarify what you are
looking for, and reduces the amount of guessing needed.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You missed one of the fundamental principels of programming, not just
SQL.
SQL has a CASE **expression** and not a CASE **statement**; expressions
return a single value of a known data type. It does not change the
control flow and in fact since SQL is a declarative language, there is
no control flow concept.
Did you mean something like this? A bit weird, but valid:
SELECT I.vague_id, automated_status
FROM Info AS I. AudPolicyDetail AS A
WHERE A.automated_status
= CASE @.auto
WHEN 0 THEN 0
WHEN 1 THEN 1
ELSE I.automated_status END;|||Ok, Ill try once more then we can drop it. I have a page that passed the
@.Auto parameter. On the page the parameter can have one of three options
(All, True or False). In the SQL Statement I think of these options as 3
different possible WHERE conditions as shown below.
1) WHERE Automated = 0 -- Returns records that are not automated
2) WHERE Automated = 1 -- Returns records that are automated
3) -- does not use the Automated field -- returns all the records
It is in the 3rd option that I get stuck with the syntax of the statement
how do I write a WHERE clause that can both use the Automated field and then
not use it if all records are to be returned?
I appreciate your patients with me on this.
Thanks for all the help
"Erland Sommarskog" wrote:
> Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> I don't know what you are trying to achieve, but the above is not
> correct. A CASE expression must return one of the data types in SQL Server
,
> and boolean is not a data type in SQL Server.
> If also looks funny with audPolicyDetail, as there is no such table in
> the query. Is this a CLR UDT column?
> The general recommendation for query problems is that you post:
> o CREATE TABLE statement(s) for your table(s).
> o INSERT statements with sample data.
> o The desired result given the sample.
> Including this sort of information often helps to clarify what you are
> looking for, and reduces the amount of guessing needed.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> Ok, Ill try once more then we can drop it. I have a page that passed the
> @.Auto parameter. On the page the parameter can have one of three options
> (All, True or False). In the SQL Statement I think of these options as 3
> different possible WHERE conditions as shown below.
> 1) WHERE Automated = 0 -- Returns records that are not automated
> 2) WHERE Automated = 1 -- Returns records that are automated
> 3) -- does not use the Automated field -- returns all the records
> It is in the 3rd option that I get stuck with the syntax of the
> statement how do I write a WHERE clause that can both use the Automated
> field and then not use it if all records are to be returned?
The most common way to write it would be
WHERE (Automated = @.Auto OR @.Auto IS NULL)
But if you prefer to pass 2 meaning "don't matter, this works too:
WHERE (Automated = @.Auto OR @.Auto = 2)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
myself and need some help clarifing how to weite thefollowing statement so that 3 difierent results can come from the value of
the passed Parameter. The parameter can have 0,1 or 2 as the values
What I'm trying to get back is the records that are automated if the value
is 1 or the records if the value is manual 0 other wise return both types of
records
SELECT Info.ID,
Info.Automated
FROM Info
WHERE (Info.Automated LIKE @.Auto))
Can a case statement be used in the where clause?
I want to change the Like to = if possible.
Thanks for the helpJim Abel (JimAbel@.discussions.microsoft.com) writes:
> I have
myself and need some help clarifing how to weite the> following statement so that 3 difierent results can come from the value
> of the passed Parameter. The parameter can have 0,1 or 2 as the values
> What I'm trying to get back is the records that are automated if the
> value is 1 or the records if the value is manual 0 other wise return
> both types of records
> SELECT Info.ID,
> Info.Automated
> FROM Info
> WHERE (Info.Automated LIKE @.Auto))
> Can a case statement be used in the where clause?
> I want to change the Like to = if possible.
There is no CASE statement in T-SQL, but there is a CASE expression. And,
yes, it can be used in WHERE clauses. I'm not sure that I understood
exactly what you wanted to do, but here is an example:
SELECT ..
FROM Info
WHERE Automated LIKE CASE WHEN @.param = 1 THEN @.Auto
WHEN @.param = 2 THEN @.Bil
WHEN @.param = 3 THEN @.Macchina
END
You find more information in Books Online.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks this is helping. Let me see know if I can get you to understand what
I'm trying to achive with the query because I'm not sure if a CASE expressio
n
is the right thing to do.
SELECT Info.ID, Info.Automated
FROM Info
WHERE (info.Automated = CASE
WHEN @.Auto = 0 THEN
audPolicyDetail.Auyomated = 0
WHEN @.Auto = 1 THEN
audPolicyDetail.Auyomated = 1
WHEN @.Auto = 2 THEN
audPolicyDetail.Auyomated in(0,1)))
END
"Erland Sommarskog" wrote:
> Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> There is no CASE statement in T-SQL, but there is a CASE expression. And,
> yes, it can be used in WHERE clauses. I'm not sure that I understood
> exactly what you wanted to do, but here is an example:
> SELECT ..
> FROM Info
> WHERE Automated LIKE CASE WHEN @.param = 1 THEN @.Auto
> WHEN @.param = 2 THEN @.Bil
> WHEN @.param = 3 THEN @.Macchina
> END
> You find more information in Books Online.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> Thanks this is helping. Let me see know if I can get you to understand
> what I'm trying to achive with the query because I'm not sure if a CASE
> expression is the right thing to do.
> SELECT Info.ID, Info.Automated
> FROM Info
> WHERE (info.Automated = CASE
> WHEN @.Auto = 0 THEN
> audPolicyDetail.Auyomated = 0
> WHEN @.Auto = 1 THEN
> audPolicyDetail.Auyomated = 1
> WHEN @.Auto = 2 THEN
> audPolicyDetail.Auyomated in(0,1)))
> END
I don't know what you are trying to achieve, but the above is not
correct. A CASE expression must return one of the data types in SQL Server,
and boolean is not a data type in SQL Server.
If also looks funny with audPolicyDetail, as there is no such table in
the query. Is this a CLR UDT column?
The general recommendation for query problems is that you post:
o CREATE TABLE statement(s) for your table(s).
o INSERT statements with sample data.
o The desired result given the sample.
Including this sort of information often helps to clarify what you are
looking for, and reduces the amount of guessing needed.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You missed one of the fundamental principels of programming, not just
SQL.
SQL has a CASE **expression** and not a CASE **statement**; expressions
return a single value of a known data type. It does not change the
control flow and in fact since SQL is a declarative language, there is
no control flow concept.
Did you mean something like this? A bit weird, but valid:
SELECT I.vague_id, automated_status
FROM Info AS I. AudPolicyDetail AS A
WHERE A.automated_status
= CASE @.auto
WHEN 0 THEN 0
WHEN 1 THEN 1
ELSE I.automated_status END;|||Ok, Ill try once more then we can drop it. I have a page that passed the
@.Auto parameter. On the page the parameter can have one of three options
(All, True or False). In the SQL Statement I think of these options as 3
different possible WHERE conditions as shown below.
1) WHERE Automated = 0 -- Returns records that are not automated
2) WHERE Automated = 1 -- Returns records that are automated
3) -- does not use the Automated field -- returns all the records
It is in the 3rd option that I get stuck with the syntax of the statement
how do I write a WHERE clause that can both use the Automated field and then
not use it if all records are to be returned?
I appreciate your patients with me on this.
Thanks for all the help
"Erland Sommarskog" wrote:
> Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> I don't know what you are trying to achieve, but the above is not
> correct. A CASE expression must return one of the data types in SQL Server
,
> and boolean is not a data type in SQL Server.
> If also looks funny with audPolicyDetail, as there is no such table in
> the query. Is this a CLR UDT column?
> The general recommendation for query problems is that you post:
> o CREATE TABLE statement(s) for your table(s).
> o INSERT statements with sample data.
> o The desired result given the sample.
> Including this sort of information often helps to clarify what you are
> looking for, and reduces the amount of guessing needed.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> Ok, Ill try once more then we can drop it. I have a page that passed the
> @.Auto parameter. On the page the parameter can have one of three options
> (All, True or False). In the SQL Statement I think of these options as 3
> different possible WHERE conditions as shown below.
> 1) WHERE Automated = 0 -- Returns records that are not automated
> 2) WHERE Automated = 1 -- Returns records that are automated
> 3) -- does not use the Automated field -- returns all the records
> It is in the 3rd option that I get stuck with the syntax of the
> statement how do I write a WHERE clause that can both use the Automated
> field and then not use it if all records are to be returned?
The most common way to write it would be
WHERE (Automated = @.Auto OR @.Auto IS NULL)
But if you prefer to pass 2 meaning "don't matter, this works too:
WHERE (Automated = @.Auto OR @.Auto = 2)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Friday, February 10, 2012
Calling user-defined function without dbo. -- possible?
Is it possible to call a user-defined function without prefixing it
with 'dbo.' within a SELECT clause somehow? Just curious; it's not a
big issue but just a stylistic one for me.
with 'dbo.' within a SELECT clause somehow? Just curious; it's not a
big issue but just a stylistic one for me.
Thanks!
Joel Thornton ~ <groups@.joelpt.eml.cc>You can do so with table function. However, you will have to specify an owner if
you're calling a scalar function. This is to allow sqlserver to distinguish an
udf as opposed to system function.
See if this helps:
create function dbo.scalar()
returns int
as
begin
return(select 123)
end
go
create function dbo.tb()
returns table
as
return(select top 5 * from Northwind..Orders)
go
select 'bad:'+cast(scalar() as varchar)
go
select 'good:'+cast(dbo.scalar() as varchar)
go
select * from tb()
go
select * from dbo.tb()
go
drop function dbo.tb,dbo.scalar
go
--
-oj
http://www.rac4sql.net
"Joel Thornton" <joelpt@.eml.cc> wrote in message
news:c190a45a.0401091144.40d9f8de@.posting.google.c om...
> Is it possible to call a user-defined function without prefixing it
> with 'dbo.' within a SELECT clause somehow? Just curious; it's not a
> big issue but just a stylistic one for me.
> Thanks!
> Joel Thornton ~ <groups@.joelpt.eml.cc
Calling UDF in where clause
I am new to user-defined functions in SQL Server. Can someone please shed
some light on my question.
I am trying to call a user-defined function from within a where clause but I
am getting errors. If I move the same UDF call to the 'select' part, it
works. It appears to me that it is not possible to call a UDF from within a
where clause but I am not really sure. Can someone please let me know if i
t
is possible or not.David wrote:
> I am new to user-defined functions in SQL Server. Can someone please shed
> some light on my question.
> I am trying to call a user-defined function from within a where clause but
I
> am getting errors. If I move the same UDF call to the 'select' part, it
> works. It appears to me that it is not possible to call a UDF from within
a
> where clause but I am not really sure. Can someone please let me know if
it
> is possible or not.
Works perfectly... Post your query and your error message...|||That's good news.
If I do it this way, it works great:
SELECT activity_id, md_group_id, dbo.udfTest() AS dave
FROM dbo.ImpactedMD
WHERE (md_group_id = 2)
If I try this, it doesn't work.
SELECT activity_id, md_group_id, dbo.udfTest() AS dave
FROM dbo.ImpactedMD
WHERE dbo.udfTest()
I get this error and I have already tried everything I could think of.
"Line 1: Incorrect syntax near ')'. "
and
"Error in list of function arguments: 'dbo' not recognized. Unable to
parse query text."
Here is my UDF function:
CREATE FUNCTION dbo.udfTest()
RETURNS varchar(255) AS
BEGIN
return '(id = 2)'
END
"Tracy McKibben" wrote:
> David wrote:
> Works perfectly... Post your query and your error message...
>|||Where clause expects some form of evaluation. You are just providing a value
(the results of the udf).
WHERE dbo.udfTest()
is like
WHERE 2
So, add the evaluation criteria, compare the results of the udf to
something. For example,
WHERE dbo.udfTest() <> 0
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"David" <dilworth@.newsgroups.nospam> wrote in message
news:CECD5327-DC62-4C47-B998-B9C691CF2A06@.microsoft.com...
> That's good news.
> If I do it this way, it works great:
> SELECT activity_id, md_group_id, dbo.udfTest() AS dave
> FROM dbo.ImpactedMD
> WHERE (md_group_id = 2)
> If I try this, it doesn't work.
> SELECT activity_id, md_group_id, dbo.udfTest() AS dave
> FROM dbo.ImpactedMD
> WHERE dbo.udfTest()
> I get this error and I have already tried everything I could think of.
> "Line 1: Incorrect syntax near ')'. "
> and
> "Error in list of function arguments: 'dbo' not recognized. Unable to
> parse query text."
> Here is my UDF function:
> CREATE FUNCTION dbo.udfTest()
> RETURNS varchar(255) AS
> BEGIN
> return '(id = 2)'
> END
>
> "Tracy McKibben" wrote:
>|||That's not the way UDFs work in SQL. The function returns a value that can b
e
returned (if called from the SELECT statement) or that can be compared to
another value (if called from within the WHERE/HAVING clauses).
Like this:
SELECT activity_id, md_group_id, dbo.udfTest() AS dave
FROM dbo.ImpactedMD
WHERE (dbo.udfTest() = <some value or column name> )
Maybe:
SELECT activity_id, md_group_id, dbo.udfTest() AS dave
FROM dbo.ImpactedMD
WHERE (dbo.udfTest() = id)
Change your function to return a value, rather than what looks like part of
some dynamic SQL statement:
CREATE FUNCTION dbo.udfTest()
RETURNS varchar(255) AS
BEGIN
return '2'
END
ML
http://milambda.blogspot.com/|||So, I am not able to return a string from my UDF that has the value and the
column name?
something like: (column_name=2)
"ML" wrote:
> That's not the way UDFs work in SQL. The function returns a value that can
be
> returned (if called from the SELECT statement) or that can be compared to
> another value (if called from within the WHERE/HAVING clauses).
> Like this:
> SELECT activity_id, md_group_id, dbo.udfTest() AS dave
> FROM dbo.ImpactedMD
> WHERE (dbo.udfTest() = <some value or column name> )
> Maybe:
> SELECT activity_id, md_group_id, dbo.udfTest() AS dave
> FROM dbo.ImpactedMD
> WHERE (dbo.udfTest() = id)
>
> Change your function to return a value, rather than what looks like part o
f
> some dynamic SQL statement:
> CREATE FUNCTION dbo.udfTest()
> RETURNS varchar(255) AS
> BEGIN
> return '2'
> END
>
> ML
> --
> http://milambda.blogspot.com/|||You're trying to use a VARCHAR as a boolean comparison in your WHERE clause.
That doesn't work unless you're using dynamic SQL, which you're not.
"David" <dilworth@.newsgroups.nospam> wrote in message
news:CECD5327-DC62-4C47-B998-B9C691CF2A06@.microsoft.com...
> That's good news.
> If I do it this way, it works great:
> SELECT activity_id, md_group_id, dbo.udfTest() AS dave
> FROM dbo.ImpactedMD
> WHERE (md_group_id = 2)
> If I try this, it doesn't work.
> SELECT activity_id, md_group_id, dbo.udfTest() AS dave
> FROM dbo.ImpactedMD
> WHERE dbo.udfTest()
> I get this error and I have already tried everything I could think of.
> "Line 1: Incorrect syntax near ')'. "
> and
> "Error in list of function arguments: 'dbo' not recognized. Unable to
> parse query text."
> Here is my UDF function:
> CREATE FUNCTION dbo.udfTest()
> RETURNS varchar(255) AS
> BEGIN
> return '(id = 2)'
> END
>
> "Tracy McKibben" wrote:
>|||You could, but why? It's best to avoid dynamic SQL. Here's a very nice
article on dynamic SQL by Erland Sommarskog (a must-read):
http://www.sommarskog.se/dynamic_sql.html
ML
http://milambda.blogspot.com/|||Thanks for everyone help. I guess I can't use UDF in the way that I thought
I could. Let me explain what I'm trying to do and let me know the best way
I
should do it.
I am trying to construct my where clause in a way where the comparison (like
'=', '>=', or '<=') would change depending on a value from another column.
I
thought that I could use UDF to return a string but I guess I can't. Does
anyone else have anymore ideas. What about a case statement? Would that
work?
"Mike C#" wrote:
> You're trying to use a VARCHAR as a boolean comparison in your WHERE claus
e.
> That doesn't work unless you're using dynamic SQL, which you're not.
> "David" <dilworth@.newsgroups.nospam> wrote in message
> news:CECD5327-DC62-4C47-B998-B9C691CF2A06@.microsoft.com...
>
>|||DECLARE @.which_operator VARCHAR(2)
SELECT @.which_operator = '='
SELECT activity_id, md_group_id
FROM dbo.ImpactedMD
WHERE (@.which_operator = '=' AND md_group_id = 2)
OR (@.which_operator = '<=' AND md_group_id <= 2)
OR (@.which_operator = '>=' AND md_group_id >= 2)
I don't know if the OR's will have a seriously adverse affect on your query
time or not... depends on if SQL Server is smart enough to short-circuit the
WHERE clause.
"David" <dilworth@.newsgroups.nospam> wrote in message
news:99AACBDE-6EFA-4863-84D6-404759E3C4A3@.microsoft.com...
> Thanks for everyone help. I guess I can't use UDF in the way that I
> thought
> I could. Let me explain what I'm trying to do and let me know the best
> way I
> should do it.
> I am trying to construct my where clause in a way where the comparison
> (like
> '=', '>=', or '<=') would change depending on a value from another column.
> I
> thought that I could use UDF to return a string but I guess I can't.
> Does
> anyone else have anymore ideas. What about a case statement? Would that
> work?
> "Mike C#" wrote:
>
some light on my question.
I am trying to call a user-defined function from within a where clause but I
am getting errors. If I move the same UDF call to the 'select' part, it
works. It appears to me that it is not possible to call a UDF from within a
where clause but I am not really sure. Can someone please let me know if i
t
is possible or not.David wrote:
> I am new to user-defined functions in SQL Server. Can someone please shed
> some light on my question.
> I am trying to call a user-defined function from within a where clause but
I
> am getting errors. If I move the same UDF call to the 'select' part, it
> works. It appears to me that it is not possible to call a UDF from within
a
> where clause but I am not really sure. Can someone please let me know if
it
> is possible or not.
Works perfectly... Post your query and your error message...|||That's good news.
If I do it this way, it works great:
SELECT activity_id, md_group_id, dbo.udfTest() AS dave
FROM dbo.ImpactedMD
WHERE (md_group_id = 2)
If I try this, it doesn't work.
SELECT activity_id, md_group_id, dbo.udfTest() AS dave
FROM dbo.ImpactedMD
WHERE dbo.udfTest()
I get this error and I have already tried everything I could think of.
"Line 1: Incorrect syntax near ')'. "
and
"Error in list of function arguments: 'dbo' not recognized. Unable to
parse query text."
Here is my UDF function:
CREATE FUNCTION dbo.udfTest()
RETURNS varchar(255) AS
BEGIN
return '(id = 2)'
END
"Tracy McKibben" wrote:
> David wrote:
> Works perfectly... Post your query and your error message...
>|||Where clause expects some form of evaluation. You are just providing a value
(the results of the udf).
WHERE dbo.udfTest()
is like
WHERE 2
So, add the evaluation criteria, compare the results of the udf to
something. For example,
WHERE dbo.udfTest() <> 0
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"David" <dilworth@.newsgroups.nospam> wrote in message
news:CECD5327-DC62-4C47-B998-B9C691CF2A06@.microsoft.com...
> That's good news.
> If I do it this way, it works great:
> SELECT activity_id, md_group_id, dbo.udfTest() AS dave
> FROM dbo.ImpactedMD
> WHERE (md_group_id = 2)
> If I try this, it doesn't work.
> SELECT activity_id, md_group_id, dbo.udfTest() AS dave
> FROM dbo.ImpactedMD
> WHERE dbo.udfTest()
> I get this error and I have already tried everything I could think of.
> "Line 1: Incorrect syntax near ')'. "
> and
> "Error in list of function arguments: 'dbo' not recognized. Unable to
> parse query text."
> Here is my UDF function:
> CREATE FUNCTION dbo.udfTest()
> RETURNS varchar(255) AS
> BEGIN
> return '(id = 2)'
> END
>
> "Tracy McKibben" wrote:
>|||That's not the way UDFs work in SQL. The function returns a value that can b
e
returned (if called from the SELECT statement) or that can be compared to
another value (if called from within the WHERE/HAVING clauses).
Like this:
SELECT activity_id, md_group_id, dbo.udfTest() AS dave
FROM dbo.ImpactedMD
WHERE (dbo.udfTest() = <some value or column name> )
Maybe:
SELECT activity_id, md_group_id, dbo.udfTest() AS dave
FROM dbo.ImpactedMD
WHERE (dbo.udfTest() = id)
Change your function to return a value, rather than what looks like part of
some dynamic SQL statement:
CREATE FUNCTION dbo.udfTest()
RETURNS varchar(255) AS
BEGIN
return '2'
END
ML
http://milambda.blogspot.com/|||So, I am not able to return a string from my UDF that has the value and the
column name?
something like: (column_name=2)
"ML" wrote:
> That's not the way UDFs work in SQL. The function returns a value that can
be
> returned (if called from the SELECT statement) or that can be compared to
> another value (if called from within the WHERE/HAVING clauses).
> Like this:
> SELECT activity_id, md_group_id, dbo.udfTest() AS dave
> FROM dbo.ImpactedMD
> WHERE (dbo.udfTest() = <some value or column name> )
> Maybe:
> SELECT activity_id, md_group_id, dbo.udfTest() AS dave
> FROM dbo.ImpactedMD
> WHERE (dbo.udfTest() = id)
>
> Change your function to return a value, rather than what looks like part o
f
> some dynamic SQL statement:
> CREATE FUNCTION dbo.udfTest()
> RETURNS varchar(255) AS
> BEGIN
> return '2'
> END
>
> ML
> --
> http://milambda.blogspot.com/|||You're trying to use a VARCHAR as a boolean comparison in your WHERE clause.
That doesn't work unless you're using dynamic SQL, which you're not.
"David" <dilworth@.newsgroups.nospam> wrote in message
news:CECD5327-DC62-4C47-B998-B9C691CF2A06@.microsoft.com...
> That's good news.
> If I do it this way, it works great:
> SELECT activity_id, md_group_id, dbo.udfTest() AS dave
> FROM dbo.ImpactedMD
> WHERE (md_group_id = 2)
> If I try this, it doesn't work.
> SELECT activity_id, md_group_id, dbo.udfTest() AS dave
> FROM dbo.ImpactedMD
> WHERE dbo.udfTest()
> I get this error and I have already tried everything I could think of.
> "Line 1: Incorrect syntax near ')'. "
> and
> "Error in list of function arguments: 'dbo' not recognized. Unable to
> parse query text."
> Here is my UDF function:
> CREATE FUNCTION dbo.udfTest()
> RETURNS varchar(255) AS
> BEGIN
> return '(id = 2)'
> END
>
> "Tracy McKibben" wrote:
>|||You could, but why? It's best to avoid dynamic SQL. Here's a very nice
article on dynamic SQL by Erland Sommarskog (a must-read):
http://www.sommarskog.se/dynamic_sql.html
ML
http://milambda.blogspot.com/|||Thanks for everyone help. I guess I can't use UDF in the way that I thought
I could. Let me explain what I'm trying to do and let me know the best way
I
should do it.
I am trying to construct my where clause in a way where the comparison (like
'=', '>=', or '<=') would change depending on a value from another column.
I
thought that I could use UDF to return a string but I guess I can't. Does
anyone else have anymore ideas. What about a case statement? Would that
work?
"Mike C#" wrote:
> You're trying to use a VARCHAR as a boolean comparison in your WHERE claus
e.
> That doesn't work unless you're using dynamic SQL, which you're not.
> "David" <dilworth@.newsgroups.nospam> wrote in message
> news:CECD5327-DC62-4C47-B998-B9C691CF2A06@.microsoft.com...
>
>|||DECLARE @.which_operator VARCHAR(2)
SELECT @.which_operator = '='
SELECT activity_id, md_group_id
FROM dbo.ImpactedMD
WHERE (@.which_operator = '=' AND md_group_id = 2)
OR (@.which_operator = '<=' AND md_group_id <= 2)
OR (@.which_operator = '>=' AND md_group_id >= 2)
I don't know if the OR's will have a seriously adverse affect on your query
time or not... depends on if SQL Server is smart enough to short-circuit the
WHERE clause.
"David" <dilworth@.newsgroups.nospam> wrote in message
news:99AACBDE-6EFA-4863-84D6-404759E3C4A3@.microsoft.com...
> Thanks for everyone help. I guess I can't use UDF in the way that I
> thought
> I could. Let me explain what I'm trying to do and let me know the best
> way I
> should do it.
> I am trying to construct my where clause in a way where the comparison
> (like
> '=', '>=', or '<=') would change depending on a value from another column.
> I
> thought that I could use UDF to return a string but I guess I can't.
> Does
> anyone else have anymore ideas. What about a case statement? Would that
> work?
> "Mike C#" wrote:
>
Subscribe to:
Posts (Atom)