Cursor questions/help - SQL Server Forums

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

SQL Server Development (2000)

Cursor questions/help

Author

Topic

becksinthecity
Starting Member

18 Posts

Posted - 2005-11-05 : 18:43:34

1. when using a cursor, can you have the variable be used as a column name?

See below:

each of the application names that i am pulling in from the vwtac... is a column name in the tblbsoxjuly. i am trying to update the tblbsoxjuly table from my temp table. i can run the insert part using the specific application names, but with over 100 applications, i did not want to code them directly as insert statements. i had the idea to use a cursor to pull in the application names and have that variable change with every application. however, this code is not quite correct as it is erroring as below. it does not seem to like the way i am attempting to use the variable as both the column name and as the renaming in the case statement. Any help would be appreciated.

Server: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near 'employeeid'.
Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near '@appname'.

declare @appname varchar(255)
declare ap cursor for
select distinct application from tacmssql..vwtac_users_sox2004_rpt
open ap

fetch next from ap into @appname
while @@fetch_status =0
begin

insert into tactrack..tblbsoxjuly (@appname, employeeid,productcode)
(SELECT case [Confirm/Reject] when 'confirm' then 'YES'
when 'reject' then 'NO' end as @appname,

[EmployeeID], [ProductCode] FROM [TACTRACK].[dbo].[tempsoxjuly]
where application=@appname
)
end

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-11-05 : 19:16:09

quote:
1. when using a cursor, can you have the variable be used as a column name?

No.

Even without seeing the full table structure (please post it), I'll still say you don't need to use a cursor for this at all. From what I'm seeing though, you may have a less-than-ideal table design, which is going to cause you problems no matter what you do.

becksinthecity
Starting Member

18 Posts

Posted - 2005-11-05 : 19:28:32

TABLE [tblbSOXjuly] (
[ProductCode] [nvarchar] (255) ,
[EmployeeID] [nvarchar] (255) ,
[ADP] [nvarchar] (255) ,
[AMM] [nvarchar] (255) ,
[ASAP] [nvarchar] (255) ,
[Cameo] [nvarchar] (255) ,
[CATS] [nvarchar] (255) ,
[CATS Europe] [nvarchar] (255) ,
[CDS] [nvarchar] (255) ,
[CIAS] [nvarchar] (255) ,
[CitiDirect (Web system)] [nvarchar] (255) ,
[Claims Tracking System] [nvarchar] (255) ,
[CMS Asia] [nvarchar] (255) ,
[Convertible Bond System EU] [nvarchar] (255) ,
[Convertible Bonds Tr Sys EU] [nvarchar] (255) ,
[CPI] [nvarchar] (255) ,
[Credit WorkStation
CTI] [nvarchar] (255) ,
[DBS] [nvarchar] (255) ,
[DELTA1] [nvarchar] (255) ,
[Domestic Program Trading] [nvarchar] (255) ,
[EFCASH] [nvarchar] (255) ,
[ELI] [nvarchar] (255) ,
[EM1] [nvarchar] (255) ,
[EUCLID SP] [nvarchar] (255) ,
[European Program Trading] [nvarchar] (255) ,
[FAS133] [nvarchar] (255) ,
[FPS] [nvarchar] (255) ,
[Fund Derivatives System] [nvarchar] (255) ,
[FX EMG RAT] [nvarchar] (255) ,
[FXMARIA] [nvarchar] (255) ,
[FXSPIRIT] [nvarchar] (255) ,
[GARM] [nvarchar] (255) ,
[GEDS (Asia)] [nvarchar] (255) ,
[GEDS America] [nvarchar] (255) ,
[GEDS Europe
GFS] [nvarchar] (255) ,
[Global One] [nvarchar] (255) ,
[GQUEST Asia Equities] [nvarchar] (255) ,
[GQUEST Asia FID] [nvarchar] (255) ,
[GQUEST LN Equities] [nvarchar] (255) ,
[GQUEST LN Fixed Income] [nvarchar] (255) ,
[GQUEST NY Equities] [nvarchar] (255) ,
[Athena Australia] [nvarchar] (255) ,
[Athena Hong Kong] [nvarchar] (255) ,
[Athena Korea] [nvarchar] (255) ,
[Athena Taiwan] [nvarchar] (255) ,
[Athena Tokyo] [nvarchar] (255) ,
[Automated Market Making Europe] [nvarchar] (255) ,
[Bank of Japan Net Terminal] [nvarchar] (255) ,
[Brass] [nvarchar] (255) ,
[CATS JGB] [nvarchar] (255) ,
[GQUEST NY Fixed Income
Green] [nvarchar] (255) ,
[Green] [nvarchar] (255) ,
[Green Asia] [nvarchar] (255) ,
[Green Europe] [nvarchar] (255) ,
[GSSR Global Cash And Stock] [nvarchar] (255) ,
[GSSR Global Trade] [nvarchar] (255) ,
[Hyperion Enterprise] [nvarchar] (255) ,
[INFAS] [nvarchar] (255) ,
[Infinity] [nvarchar] (255) ,
[Integrated Credit Environment] [nvarchar] (255) ,
[IRIS] [nvarchar] (255) ,
[ISTAR LC] [nvarchar] (255) ,
[ITS] [nvarchar] (255) ,
[ITS Asia - for new users] [nvarchar] (255) ,
[ITS Europe - for new users] [nvarchar] (255) ,
[Laura Americas] [nvarchar] (255) ,
[Laura Asia] [nvarchar] (255) ,
[LAURA Europe] [nvarchar] (255) ,
[Lehman Risk] [nvarchar] (255) ,
[LET Europe] [nvarchar] (255) ,
[LETNY] [nvarchar] (255) ,
[LoanIQ] [nvarchar] (255) ,
[MAGICS] [nvarchar] (255) ,
[Maximillion] [nvarchar] (255) ,
[MERVA] [nvarchar] (255) ,
[METS II] [nvarchar] (255) ,
[Mizuho EB] [nvarchar] (255) ,
[MTS] [nvarchar] (255) ,
[Murex MXG2000] [nvarchar] (255) ,
[OBI] [nvarchar] (255) ,
[OPTMODEL Europe] [nvarchar] (255) ,
[PALS] [nvarchar] (255) ,
[PALS Asia] [nvarchar] (255) ,
[PALS Europe] [nvarchar] (255) ,
[PeopleSoft (HR)] [nvarchar] (255) ,
[PeopleSoft - Expenses] [nvarchar] (255) ,
[PICASSO] [nvarchar] (255) ,
[RAMP] [nvarchar] (255) ,
[RISC] [nvarchar] (255) ,
[RISC HongKong] [nvarchar] (255) ,
[RISC Japan] [nvarchar] (255) ,
[RISC Singapore] [nvarchar] (255) ,
[RND] [nvarchar] (255) ,
[Royal Blue Fidessa Asia] [nvarchar] (255) ,
[Royal Blue Fidessa Europe] [nvarchar] (255) ,
[SalesComp Admin] [nvarchar] (255) ,
[SandM] [nvarchar] (255) ,
[SandM Europe] [nvarchar] (255) ,
[SBO 2000] [nvarchar] (255) ,
[Smart Flow] [nvarchar] (255) ,
[Smart Ticket] [nvarchar] (255) ,
[SMBC EB] [nvarchar] (255) ,
[SMBC Web21] [nvarchar] (255) ,
[Statements] [nvarchar] (255) ,
[Sub Ledger] [nvarchar] (255) ,
[SUMMIT Equity Europe] [nvarchar] (255) ,
[SUMMIT FID Europe] [nvarchar] (255) ,
[Summit FID Frankfurt] [nvarchar] (255) ,
[Summit FID New York BO] [nvarchar] (255) ,
[Summit FID New York MO] [nvarchar] (255) ,
[SYMFONI II] [nvarchar] (255) ,
[TMO] [nvarchar] (255) ,
[TMS] [nvarchar] (255) ,
[TPA Billing] [nvarchar] (255) ,
[TWS Pre Settlement Asia] [nvarchar] (255) ,
[TWS Pre Settlement Frankfurt] [nvarchar] (255) ,
[TWS Presettlement NY] [nvarchar] (255) ,
[TWS Summit Frankfurt] [nvarchar] (255) ,
[TWS Summit London] [nvarchar] (255) ,
[TWS Summit New York] [nvarchar] (255) ,
[TWS Summit Tokyo] [nvarchar] (255) ,
[UFJ] [nvarchar] (255) ,
[Vertigo] [nvarchar] (255) ,
[Walker] [nvarchar] (255) ,
[Whole Loan Tracking (WLT)] [nvarchar] (255)
)

TABLE [tempsoxjuly] (
[Confirm/Reject] [nvarchar] (255) ,
[Comments] [nvarchar] (255) ,
[Application] [nvarchar] (255) ,
[EmployeeID] [nvarchar] (255) ,
[ProductCode] [nvarchar] (255)
)

and the view, the only field i need from that is the application name, which matches the list in the first table

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-11-05 : 20:08:10

Can you provide the structure of the view (tacmssql..vwtac_users_sox2004_rpt)?

becksinthecity
Starting Member

18 Posts

Posted - 2005-11-05 : 20:34:23

ALTER VIEW dbo.vwTAC_USERS_SOX2004_RPT
AS
SELECT Application, EmployeeID, UserName, [XP USERID], [Lehman Live ID], LastDayWorked, [P&L], Department, Region, [Email Address], Division,
ProductCode, Title, City
FROM dbo.vwAPP_USERS_INFO
WHERE (obj_id IN
(SELECT DISTINCT obj_ID
FROM tactrack..tblbapp_info
WHERE (SoxRequired = 'yes')))
__________________

ALTER view vwAPP_USERS_INFO
as
-- THIS IS DATA THAT IS PULLED FROM THE APPLICATION
select obj_id, obj_name Application, u.*
from (select i.obj_id,o.obj_name, app_name, employeeid HRID from tblbAPP_user_info a, tactrack..tblbapp_info i, tactrack..tac_obj_all o
where i.short_name = a.short_name
and i.obj_id = o.obj_id
and status = 'A'
and a.del_ind != 1) o, -- pulling data from TAC
( select EmployeeID, l.cn UserName, l.NTID 'XP USERID' , l.LBLIVELOGIN 'Lehman Live ID',
convert(varchar(15),l.LastDayWorked,101) LastDayWorked, l.departmentNumber [P&L],
l.lbDepartment Department, l Region, l.email [Email Address],
l.Division, l.ProductCode, l.Title, l.City
from vwtac_users l
) u
where u.employeeid = o.HRID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources