July 27, 2006 at 1:00 pm
The following script attempts to create a dataset table coming from a user table which I will use as selection list to a parameter. The payrollUsers.usrDept has string value depending on how many departments are assigned to the user. For example, User1 may have 100200300 in usrDept field meaning, the User1 may select depts 100, 200, or 300 as parameter.
The script is giving me "Invalid column name" error in tLength, Dept, and usrKey. All variables were correctly DECLAREd. Thanks for any assistance.
jodjim
SELECT payrollUsers.usrKey AS usrKey,payrollUsers.usrDept As Dept, payrollUsers.usrType,
Len(payrollUsers.usrDept) As tLength
FROM payrollUsers
WHERE payrollUsers.usrType = 'ADM'
SET @Len= tLength
SET @usrDept=Dept
SET @usrKey=usrKey
SET @TotDept = @Len/3
SET @i=1
WHILE @i <= @TotDept
BEGIN
IF @i = 1
SET @strDept=Left(@usrDept,3)
IF @i= 2
SET @strDept=SUBSTRING(@usrDept,4,3)
IF @i= 3
SET @strDept=SUBSTRING(@usrDept,7,3)
IF @i= 4
SET @strDept=SUBSTRING(@usrDept,10,3)
IF @i= 5
SET @strDept=SUBSTRING(@usrDept,13,3)
IF @i= 6
SET @strDept=SUBSTRING(@usrDept,16,3)
IF @i= 7
SET @strDept=SUBSTRING(@usrDept,19,3)
INSERT INTO @table
VALUES (@usrKey, @strDept)
END
SELECT usrKey, Dept
FROM @table
July 27, 2006 at 5:02 pm
I found the answer to my dilemna. Instead of using Set to assign values from Users table, I used SELECT. Example, instead of SET @Len = tLength,
SELECT @Len=Len(payrollUsers.usrDept) FROM payrollUsers
WHERE payrollUsers.usrType = 'ADM'
July 31, 2006 at 12:08 am
Hi,
Thanks & Regards,
9989069383
Katakam.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply