[Resolved] SQL Syntax Error - 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 2005 Forums

Transact-SQL (2005)

[Resolved] SQL Syntax Error

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-05-14 : 13:06:02

Have following and getting error:

insert into #DisbursementTable(productcode, 
                               productname,
                               grossqtytoday,
                               netqtytoday)
select     l.branded as productcode,
           p.name as productname,
           sum(case when l.date = @DateToday 
                  then cast(l.gross) as decimal(15, 2) 
                  else 0
                end) as grossqtytoday,
           sum(case when l.date = @DateToday 
                  then cast(l.net) as decimal(15, 2)
                  else 0
                end) as netqtytoday
from       [SFM-TP6000-1].TP6000.dbo.product as p
           inner join [SFM-TP6000-1].TP6000.dbo.loadcomp as l on l.branded = p.product
where      l.date >= @DateFromPrevMonth and
           l.date <= @DateToCurrMonth
group by   l.branded, 
           p.name

Getting error:

The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-14 : 13:19:20

quote:

Originally posted by snufse

Have following and getting error:

insert into #DisbursementTable(productcode, 
                               productname,
                               grossqtytoday,
                               netqtytoday)
select     l.branded as productcode,
           p.name as productname,
           sum(case when l.date = @DateToday 
                  then cast(l.gross) as decimal(15, 2)) 
                  else 0
                end) as grossqtytoday,
           sum(case when l.date = @DateToday 
                  then cast(l.net) as decimal(15, 2)) 
                  else 0
                end) as netqtytoday
from       [SFM-TP6000-1].TP6000.dbo.product as p
           inner join [SFM-TP6000-1].TP6000.dbo.loadcomp as l on l.branded = p.product
where      l.date >= @DateFromPrevMonth and
           l.date <= @DateToCurrMonth
group by   l.branded, 
           p.name

Getting error:

The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

Thank you.

try modifying like above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-05-14 : 13:26:40

Did like this:

select     l.branded as productcode,
           p.name as productname,
           sum(case when l.date = @DateToday 
                  then cast(l.gross as decimal(15, 2) 
                  else 0
                end) as grossqtytoday,
           sum(case when l.date = @DateToday 
                  then cast(l.net as decimal(15, 2)
                  else 0
                end) as netqtytoday

Now getting error:

'sum' is not a recognized built-in function name.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-14 : 13:32:36

you're still missing closing braces for cast statements. also i doubt whether you're using SQL server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-05-14 : 13:35:23

Yes you are right, got it working like this:

select     l.branded as productcode,
           p.name as productname,
           sum(case when l.date = @DateToday 
                  then cast(l.gross as decimal(15, 2)) 
                  else 0
                end) as grossqtytoday,
           sum(case when l.date = @DateToday 
                  then cast(l.net as decimal(15, 2))
                  else 0
                end) as netqtytoday

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-14 : 13:38:03

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources