Need another pair of eyes - 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 2000 Forums

Transact-SQL (2000)

Need another pair of eyes

Author

Topic

michpaust
Starting Member

21 Posts

Posted - 2005-04-21 : 10:09:30

My code is as follows

insert into invoices
(inv_num,inv_date,inv_amt,open_amt,status,ctl_num,do_num,isn_po_num,requisition
,cust_no)
select ar_temp.inv_num,ar_temp.[tran date], ar_temp.inv_amt,ar_temp.open_amt,
(case when(inv_amt-open_amt)>0 then 'partial due'else 'balance due'end)as status
,ar_temp.control_no, ar_temp.do_number,ar_temp.isn_po_number, ar_temp.requisition
,ar_temp.cust_num
from
ar_temp inner join m_customer on ar_temp.cust_num=m_customer.customer_no
group by
Ar_temp.inv_num, ar_temp.[tran date],ar_temp.inv_amt, ar_temp.open_amt,status
,ar_temp.control_no,ar_temp.do_number,ar_temp.isn_po_number, ar_temp.requisition
,ar_temp.cust_num

and its throwing the following error
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'status'.

I don't know why. Am I missing something?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-04-21 : 10:30:36

you can't group by an ALIAS....GROUP by the case statement.

michpaust
Starting Member

21 Posts

Posted - 2005-04-21 : 10:48:34

I tried to group by the case statement and now I'm getting the following error:

Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

Is there another way I should do this or should I just scrap it and startover in a new direction

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2005-04-21 : 10:59:44

quote:
Originally posted by michpaust

I tried to group by the case statement and now I'm getting the following error:

Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

Is there another way I should do this or should I just scrap it and startover in a new direction

Sounds like one of the columns you're trying to insert in to isn't big enough to handle the data. Revise your Create table statment.

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destruction

michpaust
Starting Member

21 Posts

Posted - 2005-04-21 : 11:07:15

Thanks Amethystium. I didn't have enough characters in my status field.

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-21 : 11:31:38

The DDL of the tables would be helpful as well so we can see what's going on.

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources