September 17, 2008 at 10:27 am
Hi,
I have a procedure that is searching through a record set sent from an XML file and mapping and assigning the values to variables for different stored procedure calls. At one point i am inserting specific records into a temp table so I can further manipulate the data prior to insert into the final database. The original record set is structured in such a way that I have to search for a name/value pair between two fields and insert those into my more standard temp table.
I chose to use a case statement to do this but I am now getting an error saying that the number of columns in the select don't match the number of insert columns. Is there a way in t-sql to create a 'header' record with the select I have populating the header record - this way the number of columns would match.
It may look something like this:
select @Var1, @Var2, @Var3 from
(select MAX(case
when field1 then field2
when field3 then field4
when field5 then field6
else null end)
from ##TmpTable)
Here is my current code for review.
Thanks! Maureen
insert into ##CreditCard (CCID, CardType, DiscRate, MidQual, NonQual)
select @CcardID, MAX(Case
when FieldName = 'VisaBusDiscRate' then 'VisaBiz'
when FieldName = 'VisaBusDiscRate' then FieldValue
when FieldName = 'MidQualRate' then FieldValue
when FieldName = 'NonQualRate' then FieldValue
else
case
when FieldName = 'VisaCreditDiscRate' then 'VisaCred'
when FieldName = 'VisaCreditDiscRate' then FieldValue
when FieldName = 'MidQualRate' then FieldValue
when FieldName = 'NonQualRate' then FieldValue
else
case
when FieldName = 'VisaCheckDiscRate' then 'VisaCheck'
when FieldName = 'VisaCheckDiscRate' then FieldValue
when FieldName = 'MidQualRate' then FieldValue
when FieldName = 'NonQualRate' then FieldValue
else
case
when FieldName = 'McBusCardDiscRate' then 'MCBiz'
when FieldName = 'McBusCardDiscRate' then FieldValue
when FieldName = 'MidQualRate' then FieldValue
when FieldName = 'NonQualRate' then FieldValue
End End End End)
from ##TempOmsa
where FieldName like '%Rate'
September 17, 2008 at 10:49 am
sweetdeal42, you are explicitly naming five columns in your insert statement,
insert into ##CreditCard (CCID, CardType, DiscRate, MidQual, NonQual)
but you are only selecting two. You need to either restrict the fields in the insert to only the two corresponding to the select, or add three NULLs to the end of your select. It seems too simple, but I think that is the problem.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 17, 2008 at 1:17 pm
Or maybe you need three more MAX statements. Currently you have MAX(--4 case statements--). If instead you had MAX(case), MAX(case), MAX(case), MAX(case), you would have the 5 columns you were expecting. I'm not sure if that is the logic you need though.
You also have some case statements set up twice:
Case when FieldName = 'VisaBusDiscRate' then 'VisaBiz'
when FieldName = 'VisaBusDiscRate' then FieldValue
The second "WHEN" will never be used - if the FieldName is 'VisaBusDiscRate', then it will return 'VisaBiz', it will never return the FieldValue
It almost looks like the first part of every case statement is what you want in the CardType column, which means that those need to be pulled out into their own field. The second part of each case statement looks like the specific rate for each card, so they should be grouped together too... I think you see where I'm going. I'm nervous though about how accurate this is all going to match up though - you are pulling the @CCardID from a variable and rate information from a temp table - does the temp table only contain rate information for that one particular cardID? If so, reorganizing your CASE might work for you.
Good Luck!
Chad
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply