Update SQL Help Needed - SQL 2005 TSQL

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 2005 Forums

Transact-SQL (2005)

Update SQL Help Needed - SQL 2005 TSQL

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-04-16 : 11:12:38

Per my client, when it creates the qualifier for 'HCPS-DEN"' it needs to create the qualifier as 'AD' not 'HC'. Currently, with the present update script its inserting 'HC' on all. I need it to say when the 'HCPS-DEN' is used, insert the 'AD' not 'HC'. Everything else should be the HC.

I tried using a CASE statement:
CASE WHEN Description = 'HCPC - DEN' THEN 'AD' ELSE 'HC' END

however I get this back:

Msg 207, Level 16, State 1, Line 10
Invalid column name 'Description'.

I was trying to plug this part in where HC is being entered. I think its missing an obvious Join or maybe there is a better way to write this. Any help is greatly appreciated!!

[CODE]
declare @qualid int
if 1=1
BEGIN
if not exists (select * from medlists where tablename='ProcedureCodeQualifier' and Description = 'HCPC - DEN')
begin
declare @listorder int
select @listorder=max(listorder)+1 from medlists where tablename='ProcedureCodeQualifier'

insert into medlists
select 'ProcedureCodeQualifier',NULL,'HC','HCPC - DEN',@listorder,NULL,NULL,NULL,NULL,NULL,NULL,NULL,@listorder,getdate(),'CYSScript',getdate(),dbo.GetLogonId()

update medlists set dotid=medlistsid where medlistsid=scope_identity()
end

select @qualid=medlistsid from medlists where tablename='ProcedureCodeQualifier' and Description = 'HCPC - DEN'

update procedures set CPTProcedureCodeQualifierMId=@qualid where proceduresid in (8634)
END
ELSE
BEGIN
select @qualid =(select top 1 medlistsid from medlists where tablename='ProcedureCodeQualifier' and Description like 'Health Care Financing Administration Common Procedural Coding System (HCPCS) Codes%')
update procedures set CPTProcedureCodeQualifierMId=@qualid where proceduresid in (8634)
END
[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-16 : 11:17:09

Do you have Decription column in table which your are using in query?

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-04-16 : 11:22:42

its referenced right after the first BEGIN:

if not exists (select * from medlists where tablename='ProcedureCodeQualifier' and Description = 'HCPC - DEN')

I need to structure this to read if Description = or like 'HCPC-DEN' then 'AD' not 'HC'. I know its not structured right presently and I'm trying to wrap my brain around how to get it there.

This is how the Syntax looks in my 3rd Party Application:

declare @qualid int
if ?REVERT.VALUE.U?=1
BEGIN
if not exists (select * from medlists where tablename='ProcedureCodeQualifier' and Description = '?QUALIFIER.TEXT.U?')
begin
declare @listorder int
select @listorder=max(listorder)+1 from medlists where tablename='ProcedureCodeQualifier'

insert into medlists
select 'ProcedureCodeQualifier',NULL,'HC','?QUALIFIER.TEXT.U?',@listorder,NULL,NULL,NULL,NULL,NULL,NULL,NULL,@listorder,getdate(),'CYSScript',getdate(),dbo.GetLogonId()

update medlists set dotid=medlistsid where medlistsid=scope_identity()
end

select @qualid=medlistsid from medlists where tablename='ProcedureCodeQualifier' and Description = '?QUALIFIER.TEXT.U?'

update procedures set CPTProcedureCodeQualifierMId=@qualid where proceduresid in (?PROCEDURE.ITEMDATA.U?)
END
ELSE
BEGIN
select @qualid =(select top 1 medlistsid from medlists where tablename='ProcedureCodeQualifier' and Description like 'Health Care Financing Administration Common Procedural Coding System (HCPCS) Codes%')
update procedures set CPTProcedureCodeQualifierMId=@qualid where proceduresid in (?PROCEDURE.ITEMDATA.U?)
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources