0

Hello, everyone

I'm new into databases. I have an Oracle insert sequence that I need to convert to SQL server
if anyone could help me to get this to work. I'm not sure if nextval is compatible with SQL Server.

Here is the code:

// original
insert into invoice
select inv_no_seq.nextval, company_id, trunc(sysdate),
   trunc(sysdate) + 30, amount
from
(select co.company_id
       ,sum(a.amount_paid) amount
from company co, attendance a, student s
where s.student_id = a.student_id
and co.company_id = s.company_id
group by co.company_id);

// my changes
insert into invoice
select inv_no_seq.nextval, company_id, convert(datetime, convert(date,getdate())),
   convert(datetime, convert(date,getdate())) + 30, amount
from
(select co.company_id
       ,sum(a.amount_paid) amount
from company co, attendance a, student s
where s.student_id = a.student_id
and co.company_id = s.company_id
group by co.company_id);

Thank you in advance.

3
Contributors
9
Replies
11
Views
5 Years
Discussion Span
Last Post by toldav
0

I have been trying that syntax and it just the first 3 lines of code that is not working. I was able to insert to the tables from oracle to sql server and only this insert I have issues with.
Thank you for yor input

0

Thank you! that helps a lot, but when I execute the query it comes with an error:
Incorrect syntax near ';'.
Thanks again for your help

// my changes
insert into invoice
select next value inv_no_seq, company_id, convert(datetime, convert(date,getdate())),
convert(datetime, convert(date,getdate())) + 30, amount
from
(select co.company_id
,sum(a.amount_paid) amount
from company co, attendance a, student s
where s.student_id = a.student_id
and co.company_id = s.company_id
group by co.company_id);
go

0

According to the link, you need the word 'FOR' in your SELECT NEXT VALUE... I am not sure if that keyword may be excluded, though.

The syntax looks ok other than that. I do not have 2012 installed, so the Google references are the best I can offer. If someone has 2012 they might be able to format and check the syntax further.

0
// This is my update code
insert into invoice
select next value for inv_no_seq, company_id, convert(datetime, convert(date,getdate())),
convert(datetime, convert(date,getdate())) + 30, amount
from
(select co.company_id
,sum(a.amount_paid) amount
from company co, attendance a, student s
where s.student_id = a.student_id
and co.company_id = s.company_id
group by co.company_id);  // here is where the syntax show up red ";"
go

everything else clean.
Thank you

0

Try giving your inline view an alias...

...group by co.company_id) IV1;

and change your first select to use it:

select next value for inv_no_seq, IV1.company_id, convert(datetime, convert(date,getdate())),
convert(datetime, convert(date,getdate())) + 30, IV1.amount

0

In order to use a derived table in MS SQL Server, you have to give the derived table an alias like so:

-- // This is my update code
insert into invoice
select next value for inv_no_seq, company_id, convert(datetime, convert(date,getdate())),
convert(datetime, convert(date,getdate())) + 30, amount
from
(select co.company_id
,sum(a.amount_paid) amount
from company co, attendance a, student s
where s.student_id = a.student_id
and co.company_id = s.company_id
group by co.company_id) AS myTbl;  -- // here is where the syntax show up red ";"
go

Also, please note that the double-forward slash is not a valid comment... SQL Server uses a double-dash.

0

It Works! Thanks a lot for everyone that put a little bit of their knowledge.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.