Does SQL permit use of a column alias directly in the CASE statement?

Our Case statements can get very long and complicated and I would like to maintain just a single "Case" and than depending on the type of set I'm processing,
use this generic "Case" statement to properly decode the value. My SQL code is listed below.

Select
'PCAR' as [vtyp]
,Case
When [vtyp]='PCAR' and substring(veh_vin,8,1)= 'A' Then 'Cyl = V-6 Fuel = Gasoline'
When [vtyp]='PTRK' and substring(veh_vin,8,1)= 'A' Then 'Cyl = V-8 Fuel = Diesel'
When [vtyp]='PCAR' and substring(veh_vin,8,1)= 'B' Then 'Cyl = V-6 Fuel = CNG'
Else '?' End as 'Engine_Decode'
From veh_owner
Where substring(veh_vin,10,1) = '3' --Selects on cars

Union All

Select
'PTRK' as [vtyp]
,Case
When [vtyp]='PCAR' and substring(veh_vin,8,1)= 'A' Then 'Cyl = V-6 Fuel = Gasoline'
When [vtyp]='PTRK' and substring(veh_vin,8,1)= 'A' Then 'Cyl = V-8 Fuel = Diesel'
When [vtyp]='PCAR' and substring(veh_vin,8,1)= 'B' Then 'Cyl = V-6 Fuel = CNG'
Else '?' End as 'Engine_Decode'
From veh_owner
Where substring(veh_vin,10,1) = '4' --Selects only Trucks

I would like to deploy the SQL code described above but get the following errors:

Msg 207, Level 16, State 1, Line 18 Invalid column name 'vtyp'

Any ideas, comments or help with this issue will be greatly appreciated.

sfmd:

SQL Server does not have this kind of alias. I am not sure that I understand what you are looking for, is this heading in the right direction?:

select veh_vin,
substring ('1 2 PCARPTRK', 4 * convert(integer, substring (veh_vin,10,1)) - 3, 4) as [vtyp],
case when substring (veh_vin,10,1) = '3' and substring (veh_vin,8,1) = 'A' then 'Cyl = V-6 Fuel = Gasoline'
when substring (veh_vin,10,1) = '4' and substring (veh_vin,8,1) = 'A' then 'Cyl = V-8 Fuel = Diesel'
when substring (veh_vin,10,1) = '3' and substring (veh_vin,8,1) = 'B' then 'Cyl = V-6 Fuel = CNG'
else '?'
end as Engine_Decode
from veh_owner
where substring (veh_vin,10,1) between '3' and '4'

-- - Sample Output: -

-- veh_vin vtyp Engine_Decode
-- -- -
-- 1233567A1373 PCAR Cyl = V-6 Fuel = Gasoline
-- 1243567A1373 PCAR Cyl = V-6 Fuel = Gasoline
-- 1253567B1373 PCAR Cyl = V-6 Fuel = CNG
-- 1263567A1373 PCAR Cyl = V-6 Fuel = Gasoline
-- 1334567A1473 PTRK Cyl = V-8 Fuel = Diesel
-- 1344467A1473 PTRK Cyl = V-8 Fuel = Diesel
-- 2344467B1473 PTRK ?

Dave

|||

One thing to consider is persisting these two characters of the VIN. I realize that a VIN is what it is and you cannot change these "smart coded" columns; nonetheless, these portions of the VIN have a specific meaning and it might be a good idea to give these meanings an existence of themselves rather than only existing as a "sub-column".

Dave

|||

>>One thing to consider is persisting these two characters of the VIN. I realize that a VIN is what it is and you cannot change these "smart coded" columns; nonetheless, these portions of the VIN have a specific meaning and it might be a good idea to give these meanings an existence of themselves rather than only existing as a "sub-column".<<

Absolutely. Anytime you find yourself querying on a substring, you probably ought to reconsider what you are doing :)

|||

Perhaps I was not clear enough with my example

My point with this thread is to find a way to use a generic or multi-purpose "Case" Statement to process similar "sets" of rows. The selection of each processing set, in this case either all cars or all trucks, but never a mixed group of vehicles can be handled a thousand different ways by configuring a "Where" clause.

However the vehicle group is selected, I would like to use a generic "Case" for decoding certain values. I will always know the composition of the set (vtyp=value) again either cars or trucks and need to convey/pass this informational profile to the generic "Case" statement.

Notice that a car "A" in VIN position 10 decodes differently than a truck "A" in VIN position 10, that's the crux of the problem. Sure it's easy to use and maintain 2 different "Case" Statements(1 for car and 1 for trucks), but that's not what I initially would prefer to do. It's sure to get out of hand very quickly.

I hope this helps clarify my issue

|||In answer to your question "no" you cannot do what you want without making it dynamic SQL.|||

How about having UDF on this..

Create Function dbo.VehData(@.Vtype as varchar(10), @.VinId Varchar(10))
returns Table As
Return (Select
@.Vtype as [vtyp]
,Case
When @.Vtype='PCAR' and substring(veh_vin,8,1)= 'A' Then 'Cyl = V-6 Fuel = Gasoline'
When @.Vtype='PTRK' and substring(veh_vin,8,1)= 'A' Then 'Cyl = V-8 Fuel = Diesel'
When @.Vtype='PCAR' and substring(veh_vin,8,1)= 'B' Then 'Cyl = V-6 Fuel = CNG'
Else '?' End as Engine_Decode
From veh_owner
Where substring(veh_vin,10,1) = @.VinId )

Go

Select * From VehData('PCAR',3)
Union All
Select * From VehData('PTRK',4)
Union ALL
..etc

Does Reporting Services allow you to select a dataset dynamically?

I have a remote report ...
I need to call one of two stored procedures depending on which parameters were passed to the report. (Both stored procedures return the same fields.)
Does SQL Reporting Services allow you to switch the dataset or stored procedure name dynamically? If so, where do I put the logic? Right now I have it working with one stored procedure.
I'm thinking that I should just make one stored procedure that takes all parameters and calls one of the other two stored procedures. Do I have any options besides this?

Thanks in advance!
Your query can be expression-based, e.g.; =Iif(Parameters!SomeParameterValue = 0, "EXEC sp1 1<parameter list>", EXEC sp2 1<parameter list>"). Alternatively, if the sp choice can be based on a configuration convention, you can use the Report Server web.config file to store the config value. The later case is demonstrated by the DynamicConnection report in this download. The report uses a dynamic connection string but the same approach can be applied to the query.|||That worked. Thanks!
sql

Does all of WHERE clause get executed?

Hi there.
I have an update statement that wishes to update certain fields depending on
a column that may or may not contain numeric values (the field is varchar bu
t
may contain '001', '002' etc. For the WHERE clause, I need to convert the
MaybeNumeric field into an integer BUT... I know the whole update statement
will fail if the value is non-numeric (due to the convert(integer,...)
criteria).
The solution I've chosen is to have IsNumeric(MaybeNumeric)=1 as the first
criteria in the WHERE clause - thinking that this will get executed first an
d
will exclude non-numeric rows.
This seems to work ok on my server (those famous words!) but I've seen cases
on another server where the update statement still fails due to non-numeric
values.
Q: Is there some server setting that would dictate whether all criteria in a
WHERE clause will get executed? Or in which order they get executed? I.e: Is
the following code reliable:
update
MyTable
set
MyTable.SomeField = OtherTable.SomeField
from
OtherTable
where
IsNumeric(MyTable.MaybeNumeric) = 1
and
OtherTable.DefinitelyNumeric = convert(integer, MyTable.MaybeNumeric)
*note: this is a simplified version of the statement - I do need the convert
!
I just want to know whether this is down to my code or is I should look for
some other issue.
Any help would be appreciated!IsNumeric will return 1 for some strings that can't be converted to an
integer, but can be converted to other numeric datatypes, like '1E10', which
can be converted to a float, or '200,000.00', which can be converted to
money.
http://www.aspfaq.com/show.asp?id=2390 gives you a workaround.
Jacco Schalkwijk
SQL Server MVP
"len" <len@.discussions.microsoft.com> wrote in message
news:01E35557-6ED2-4824-A721-F4F00A9B820F@.microsoft.com...
> Hi there.
> I have an update statement that wishes to update certain fields depending
> on
> a column that may or may not contain numeric values (the field is varchar
> but
> may contain '001', '002' etc. For the WHERE clause, I need to convert the
> MaybeNumeric field into an integer BUT... I know the whole update
> statement
> will fail if the value is non-numeric (due to the convert(integer,...)
> criteria).
> The solution I've chosen is to have IsNumeric(MaybeNumeric)=1 as the first
> criteria in the WHERE clause - thinking that this will get executed first
> and
> will exclude non-numeric rows.
> This seems to work ok on my server (those famous words!) but I've seen
> cases
> on another server where the update statement still fails due to
> non-numeric
> values.
> Q: Is there some server setting that would dictate whether all criteria in
> a
> WHERE clause will get executed? Or in which order they get executed? I.e:
> Is
> the following code reliable:
>
> update
> MyTable
> set
> MyTable.SomeField = OtherTable.SomeField
> from
> OtherTable
> where
> IsNumeric(MyTable.MaybeNumeric) = 1
> and
> OtherTable.DefinitelyNumeric = convert(integer, MyTable.MaybeNumeric)
> *note: this is a simplified version of the statement - I do need the
> convert!
> I just want to know whether this is down to my code or is I should look
> for
> some other issue.
> Any help would be appreciated!|||Hi
The query processor can choose to execute a query in any way it pleases.
Depending on statistics, indexes, processors and RAM, it might decide to
execute a different query plan.
Have you tried:
update
MyTable
set
MyTable.SomeField = OtherTable.SomeField
FROM
MyTable
INNER JOIN
OtherTable
ON
OtherTable.DefinitelyNumeric = convert(integer, MyTable.MaybeNumeric)
WHERE
IsNumeric(MyTable.MaybeNumeric) = 1
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"len" wrote:

> Hi there.
> I have an update statement that wishes to update certain fields depending
on
> a column that may or may not contain numeric values (the field is varchar
but
> may contain '001', '002' etc. For the WHERE clause, I need to convert the
> MaybeNumeric field into an integer BUT... I know the whole update statemen
t
> will fail if the value is non-numeric (due to the convert(integer,...)
> criteria).
> The solution I've chosen is to have IsNumeric(MaybeNumeric)=1 as the first
> criteria in the WHERE clause - thinking that this will get executed first
and
> will exclude non-numeric rows.
> This seems to work ok on my server (those famous words!) but I've seen cas
es
> on another server where the update statement still fails due to non-numeri
c
> values.
> Q: Is there some server setting that would dictate whether all criteria in
a
> WHERE clause will get executed? Or in which order they get executed? I.e:
Is
> the following code reliable:
>
> update
> MyTable
> set
> MyTable.SomeField = OtherTable.SomeField
> from
> OtherTable
> where
> IsNumeric(MyTable.MaybeNumeric) = 1
> and
> OtherTable.DefinitelyNumeric = convert(integer, MyTable.MaybeNumeric)
> *note: this is a simplified version of the statement - I do need the conve
rt!
> I just want to know whether this is down to my code or is I should look fo
r
> some other issue.
> Any help would be appreciated!|||Optimiser can choose whatever evaluation path it sees fit. Look for "short
circuit" in the page below and see if it helps:
http://msdn.microsoft.com/library/d...
heckitout.asp
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"len" <len@.discussions.microsoft.com> wrote in message
news:01E35557-6ED2-4824-A721-F4F00A9B820F@.microsoft.com...
Hi there.
I have an update statement that wishes to update certain fields depending on
a column that may or may not contain numeric values (the field is varchar
but
may contain '001', '002' etc. For the WHERE clause, I need to convert the
MaybeNumeric field into an integer BUT... I know the whole update statement
will fail if the value is non-numeric (due to the convert(integer,...)
criteria).
The solution I've chosen is to have IsNumeric(MaybeNumeric)=1 as the first
criteria in the WHERE clause - thinking that this will get executed first
and
will exclude non-numeric rows.
This seems to work ok on my server (those famous words!) but I've seen cases
on another server where the update statement still fails due to non-numeric
values.
Q: Is there some server setting that would dictate whether all criteria in a
WHERE clause will get executed? Or in which order they get executed? I.e: Is
the following code reliable:
update
MyTable
set
MyTable.SomeField = OtherTable.SomeField
from
OtherTable
where
IsNumeric(MyTable.MaybeNumeric) = 1
and
OtherTable.DefinitelyNumeric = convert(integer, MyTable.MaybeNumeric)
*note: this is a simplified version of the statement - I do need the
convert!
I just want to know whether this is down to my code or is I should look for
some other issue.
Any help would be appreciated!|||A more detailed analysis of the "short circuit" feature in SQL Server
is written in the following article (also by Itzik Ben-Gan):
http://www.windowsitpro.com/Article...?ArticleID=9148
Razvan|||>> I have an update statement that wishes to update certain fields [sic] depending on
a column that may or may not contain numeric values (the field [sic] is varchar b
ut may contain '001', '002' etc. <<
Exactly how did you get this kind of problem in the first place?
You have a huge design problem and need to change your schema, not go
hunting for kludges. One of the reasons that I beat on people about
not calling a "column" a "field" is that a field (a file processing
concept) can hold anything; it gets its meaning from being read by a
host program. A relational column has ONE AND ONLY ONE domain which
has ONE AND ONLY ONE data type. It has meaning in and of itself, it
enforces its own integrity, it does not depend on a host program.
If this data element is used for computations, then it needs to be a
numeric. If it is a tag number, then you can use character types. You
do not mix things like this in an RDBMS.
Also, you might want to stop using the unpredictable proprietary UPDATE
syntax.|||Len's requirement is common enough in an ELT data staging scenario. For
conforming external data I would typically load to an "untyped" table
(NVARCHAR throughout) first, perform the necessary validation, then
load the valid data to another staging table with the correct datatyes.
Any UPDATE against the actual data in the target database utilizes the
correctly typed table. This avoids type conversions and errors and
ensures you maximize the benefit of indexing on the two tables.
David Portas
SQL Server MVP
--|||On Wed, 25 May 2005 04:44:03 -0700, len wrote:
(snip)
>The solution I've chosen is to have IsNumeric(MaybeNumeric)=1 as the first
>criteria in the WHERE clause - thinking that this will get executed first a
nd
>will exclude non-numeric rows.
Hi len,
As others have said: there's no guarantee.
You might wish to try this one:
UPDATE MyTable
SET MyTable.SomeColumn = OtherTable.SomeColumn
FROM OtherTable
WHERE OtherTable.DefinitelyNumeric =
CASE WHEN IsNumeric(MyTable.MaybeNumeric) = 1
THEN CONVERT(integer, MyTable.MaybeNumeric)
ELSE OtherTable.DefinitelyNumeric + 1
END
But do beware the gotchas with IsNumeric (see Jacco's post).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)