Query Syntax error

Question

Post reply

Query Syntax error

dva2007

SSCertifiable

Points: 7707
More actions
June 24, 2008 at 7:12 am

#183965

hi friends,
i m just writing simple query which insert record into table. But before selecting record it should have some condition to match and it gives error.
declare @startdate datetime
declare @enddate datetime
declare @ward varchar(20)
set @startdate = '20071201'
set @enddate = '20071231'
set @ward = 'AAA'
insert into NPI (total, monthname, type, ward)
if (select count(ward) from NPI_Table where monthname = getdate() and ward = @ward and type = 'CDiff') = '0'
begin
select '0' as total,
getdate() as date,
'CD' as type, @ward as ward
end
else
begin
select isnull(total, 0) as total, monthname, type, ward from NPI_Table
where monthname = getdate()
and ward = @ward
and type = 'CD'
end
Here if i take out insert statement, it works fine. But it gives problem with insert. The table is having same 4 fields which is selected.
Any Suggestion?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Hari.Sharma SSCertifiable Points: 6904 More actions · Answer 1

/*

Dear Friend,

Try below mentioned code....

----------------------------------

*/

declare @startdate datetime

declare @enddate datetime

declare @ward varchar(20)

set @startdate = '20071201'

set @enddate = '20071231'

set @ward = 'AAA'

if (select count(ward) from NPI_Table where monthname = getdate() and ward = @ward and type = 'CDiff') = '0'

begin

insert into NPI (total, monthname, type, ward)

select '0' as total,

getdate() as date,

'CD' as type, @ward as ward

end

else

begin

insert into NPI (total, monthname, type, ward)

select isnull(total, 0) as total, monthname, type, ward from NPI_Table

where monthname = getdate()

and ward = @ward

and type = 'CD'

end

Cheers,
Hari
Tips & Tricks for SQL BI Developers

Jeff Moden SSC Guru Points: 1004353 More actions · Answer 2

Yep... suggestion is that you post the code that has the problem insert so we can see what you're actually doing and post the text from the actual error so we can see that, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dva2007 SSCertifiable Points: 7707 More actions · Answer 3

Hi Hari,

Bluddy hell.....why i didnt think about it...it was so easy....thanks very much....

thankssss