Showing posts with label alias. Show all posts
Showing posts with label alias. Show all posts

Tuesday, March 27, 2012

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

Wednesday, March 21, 2012

does not match with a table name or alias name used in the query

I am trying to create a trigger on a table. The idea is
that the trigger will stop duplicates being entered but
will allow null values. I am trying to use the inserted
table but am receiving the following error.
The column prefix 'T2' does not match with a table name
or alias name used in the query.
The table and trigger creation script follows.
Thanks in advance.
========= Table
=========
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Class]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[Class]
GO
CREATE TABLE [dbo].[Class] (
[ClassID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (60) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Symbol] [T_STD_SYMBOL] NULL ,
[ClassTypeID] [int] NOT NULL ,
[Description] [varchar] (40) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
========= Trigger
=========
if exists (select 1
from sysobjects
where id = object_id('uqSymbol_class')
and type = 'TR')
drop trigger uqSymbol_class
go
/* Trigger to stop duplicate Symbols being entered in
the Symbol column in the class table. */
create trigger uqSymbol_class on Class for insert, update
as
begin
declare
@.numrows int,
@.errno int,
@.errmsg varchar(255)
select @.numrows = @.@.rowcount
if @.numrows = 0
return
/* Check to see if the inserted updated symbol
already exists */
if update(symbol)
begin
-- Check weather the Symbol value is Null.
Ignore if so.
-- if ((select symbol from inserted) != null)
begin -- IF((SELECT COUNT(*) FROM INSERTED WHERE
Symbol IS NOT NULL) > 0)
if (select count(*)
from Class t1, inserted t2
where t1.symbol = t2.symbol) >1 AND
T2.Symbol IS NOT NULL
begin
select @.errno = 30003,
@.errmsg = 'The Symbol you have
entered or just updated already exists.'
goto error
end
end
-- end
return
/* Error handling */
error:
raiserror @.errno @.errmsg
rollback transaction
end
ThanX :-)You are not giving us any code which uses t2 as an alias... please repost
complete code...
also if there are ever multiple triggers, and one of the other triggers does
a select insert update or delete, then @.@.rowcount in this trigger will not
be correct. It is probably safer to count records in inserted and deleted...
"Jamie" <jamie.downs@.risk.sungard.com> wrote in message
news:0d4101c360b0$976a42e0$a101280a@.phx.gbl...
> I am trying to create a trigger on a table. The idea is
> that the trigger will stop duplicates being entered but
> will allow null values. I am trying to use the inserted
> table but am receiving the following error.
> The column prefix 'T2' does not match with a table name
> or alias name used in the query.
> The table and trigger creation script follows.
> Thanks in advance.
> =========> Table
> =========> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[Class]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[Class]
> GO
> CREATE TABLE [dbo].[Class] (
> [ClassID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (60) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Symbol] [T_STD_SYMBOL] NULL ,
> [ClassTypeID] [int] NOT NULL ,
> [Description] [varchar] (40) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
>
> =========> Trigger
> =========> if exists (select 1
> from sysobjects
> where id = object_id('uqSymbol_class')
> and type = 'TR')
> drop trigger uqSymbol_class
> go
> /* Trigger to stop duplicate Symbols being entered in
> the Symbol column in the class table. */
> create trigger uqSymbol_class on Class for insert, update
> as
> begin
> declare
> @.numrows int,
> @.errno int,
> @.errmsg varchar(255)
> select @.numrows = @.@.rowcount
> if @.numrows = 0
> return
>
> /* Check to see if the inserted updated symbol
> already exists */
> if update(symbol)
> begin
> -- Check weather the Symbol value is Null.
> Ignore if so.
> -- if ((select symbol from inserted) != null)
>
----
--
> ThanX :-)|||You missed out the relevant bit of code.
In fact you don't need a trigger to do this. You can use an indexed view to
enforce uniqueness only for non-NULL values:
CREATE VIEW Symbols
WITH SCHEMABINDING
AS
SELECT symbol
FROM dbo.class
WHERE symbol IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX uclsymbol ON Symbols (symbol)
--
David Portas
--
Please reply only to the newsgroup
--|||Hi Jamie,
Posting messages with attachments usually isn't very useful because most
people do not trust them, won't open them and so don't have enough
information to answer your question. Post everything in plain text instead.
If you are on SQL Server 2000 you don't have to use a trigger to achieve
this, but you can instead create an indexed view to check for duplicates:
CREATE VIEW Class_Symbol_check
AS
SELECT Symbol FROM Class
WHERE Symbol IS NOT NULL
GO
CREATE UNIQUE INDEX ON Class_Symbol_check (Symbol )
GO
Read the topic "Indexed views" in Books online for more information.
The fact that a UNIQUE index can only have one NULL (otherwise you could
just create a unique constraint on the Symbol column in the class table) is
a problem in SQL Server and one I sincerly hope will be addressed in the
next version.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Jamie" <jamie.downs@.risk.sungard.com> wrote in message
news:0d4101c360b0$976a42e0$a101280a@.phx.gbl...
> I am trying to create a trigger on a table. The idea is
> that the trigger will stop duplicates being entered but
> will allow null values. I am trying to use the inserted
> table but am receiving the following error.
> The column prefix 'T2' does not match with a table name
> or alias name used in the query.
> The table and trigger creation script follows.
> Thanks in advance.
> =========> Table
> =========> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[Class]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[Class]
> GO
> CREATE TABLE [dbo].[Class] (
> [ClassID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (60) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Symbol] [T_STD_SYMBOL] NULL ,
> [ClassTypeID] [int] NOT NULL ,
> [Description] [varchar] (40) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
>
> =========> Trigger
> =========> if exists (select 1
> from sysobjects
> where id = object_id('uqSymbol_class')
> and type = 'TR')
> drop trigger uqSymbol_class
> go
> /* Trigger to stop duplicate Symbols being entered in
> the Symbol column in the class table. */
> create trigger uqSymbol_class on Class for insert, update
> as
> begin
> declare
> @.numrows int,
> @.errno int,
> @.errmsg varchar(255)
> select @.numrows = @.@.rowcount
> if @.numrows = 0
> return
>
> /* Check to see if the inserted updated symbol
> already exists */
> if update(symbol)
> begin
> -- Check weather the Symbol value is Null.
> Ignore if so.
> -- if ((select symbol from inserted) != null)
>
----
--
> ThanX :-)|||> a problem in SQL Server and one I sincerly hope will be addressed in the
> next version.
Full SQL92 constraints would solve this problem and more. I hope Yukon will
support the ANSI-style constraints.
--
David Portas
--
Please reply only to the newsgroup
--|||Yeah,
In the all the info I have read and heard about Yukon there is a lot of talk
about CLR support, and only very general remarks about improvements in
T-SQL. I guess the marketing people at Microsoft think that (full ANSI
constraints etc) is "hard core SQL" and too difficult for 95% of the
developers, and they are probably right. Euan Garden (Program manager for
SQL Server) said during a presentation "that people think that Microsoft
will drop support for T-SQL now that there's CLR support, but that's not the
case", so that tells you the level they are targeting. </rant>
Of the new SQL features in Yukon that I know of I like the idea in Yukon
that you can write your own datatypes, I just don't like the idea that you
have to write them in a CLR language instead of being able to declare them
in SQL, although I expect that there will soon be a cottage industry in
datatypes for SQL Server (postcodes, telephone numbers, credit card numbers,
ISBN etc), just like there was with ActiveX controls for VB 6.
But there is little or no information on structural improvement to T-SQL and
further compliance with ANSI standards. Do we get ANSI style constraints?
row constructors? ...?
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:#ZqfnQLYDHA.1004@.TK2MSFTNGP12.phx.gbl...
> > a problem in SQL Server and one I sincerly hope will be addressed in the
> > next version.
> Full SQL92 constraints would solve this problem and more. I hope Yukon
will
> support the ANSI-style constraints.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>
>|||Thanks for all your help. I have implemented the view as
the trigger.
Jacco, what did you mean by CLR.
Thanks again.