Insert with multiple Values

[Steve95]

Hi All

I have complied a SP in sql 2005, iam using a table variable to manipulate the data, in my final insert which is directing the data to inserted into a db table, it is coming back with the following error msg :-

Msg 512, Level 16, State 1, Line 51
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Iam basically putting a insert and then a select = '200604' but get the above error message.

Anyone any ideas?

Many Thanks

 

[gmmastros]

What does the insert statment look like?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

Steve,
Can you post your query.
Your subselect should only be returning one value and it is returning more than one.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[Steve95]

This is my final select statement into my a db table

insert into testtable
select
case when PurposeCode = 'current'
then 'CODA' else 'FIRE' end,
case when PurposeCode = 'current'
then 'CODA' else 'FIRE' end,
null,
(select (processyear * 100) + processqtr from Report_Rates
where (processyear * 100) + processqtr = '200604'),

(SELECT max((processyear * 100) + (processqtr + 2) ) from ReportRates),
'GBP', originalcurrency, finalrate, NULL
from @test

 

[gmmastros]

I suspect your problem is here:

(select (processyear * 100) + processqtr from Report_Rates
where (processyear * 100) + processqtr = '200604'),

If you run the following query, it is probably returning multiple records.

select (processyear * 100) + processqtr
from Report_Rates
where (processyear * 100) + processqtr = '200604'

Of course, they are probably all the same value, but that doesn't matter to the query. You can probably solve this problem by using Top 1, like this...

[COLOR=blue]insert[/color] [COLOR=blue]into[/color] testtable
[COLOR=blue]select[/color] 
[COLOR=blue]case[/color] [COLOR=blue]when[/color] PurposeCode = [COLOR=red]'current'[/color] 
[COLOR=blue]then[/color] [COLOR=red]'CODA'[/color] [COLOR=blue]else[/color] [COLOR=red]'FIRE'[/color] [COLOR=blue]end[/color],
[COLOR=blue]case[/color] [COLOR=blue]when[/color] PurposeCode = [COLOR=red]'current'[/color] 
[COLOR=blue]then[/color] [COLOR=red]'CODA'[/color] [COLOR=blue]else[/color] [COLOR=red]'FIRE'[/color] [COLOR=blue]end[/color],
null, 
([COLOR=blue]select[/color] [!]Top 1[/!] (processyear * 100) + processqtr from Report_Rates
[COLOR=blue]where[/color] (processyear * 100) + processqtr = [COLOR=red]'200604'[/color]),

([COLOR=blue]SELECT[/color] [COLOR=#FF00FF]max[/color]((processyear * 100) + (processqtr + 2) ) [COLOR=blue]from[/color] ReportRates),
[COLOR=red]'GBP'[/color], originalcurrency, finalrate, NULL
[COLOR=blue]from[/color] @test

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

Hmmmm,
I don't see any reason to use SELECT when you know the value beforehand. Only of course if there is a possibility that value not to exist in Report_Rates AND the field where you put that value is NULLable.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP