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
 SQL Server Development (2000)
 Solve my problem of table insertion ------------------

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-14 : 07:21:38
Santosh writes "Version SQL 8.00.194


----First Table:----With Zeroes

iss_quantity received_qty moved_qty alloc_qty
------------------------------ --------------------
14 0 0 0
0 0 0 0
0 0 13.7 0
0 0 0 13
14 0 0 0
14 0 0 0
0 40.9 0 0
14 0 0 0
0 0 13.8 0
0 27.6 0 0
0 0 13.8 0
0 0 13.7 0
0 0 13.8 0
0 41.4 0 0
0 0 13.9 0
0 0 0 13
14 0 0 0



----Resultant Table:----Without Zeroes

iss_quantity received_qty moved_qty alloc_qty
------------------------------ --------------------
14 40.9 13.7 13
14 27.6 13.8 13
14 41.4 13.8
14 13.7
14 13.8
13.9

"

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-06-14 : 07:29:23
INSERT INTO NewTable VALUES (iss_quantity,received_qty,moved_qty,alloc_qty)
SELECT iss_quantity,received_qty,moved_qty,alloc_qty FROM MyTable
WHERE iss_quantity <> 0 OR received_qty <> 0 OR moved_qty <> 0 OR alloc_qty <> 0
Go to Top of Page

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-06-14 : 09:12:05
Two things.

1. Santosh, Why are you trying to do this to your table? What is the purpose? If it is a report then handle it in the reporting tool.

2. Jason, the query you provided returns the exact same table minus one row. OR's are tricky. In this case, in all but one row, at least one field in the table satisfies the condition value <> 0 and therefore evaluates to "true" for the entire row to be returned.
set nocount on

select * into dbo.myTable
from
(select
14 'iss_quantity' ,0 'received_qty', 0 'moved_qty' ,0 'alloc_qty'
union all select
0 , 0 , 0 , 0
union all select
0 , 0 , 13.7 , 0
union all select
0 , 0 , 0 , 13
union all select
14 , 0 , 0 , 0
union all select
14 , 0 , 0 , 0
union all select
0 , 40.9 , 0 , 0
union all select
14 , 0 , 0 , 0
union all select
0 , 0 , 13.8 , 0
union all select
0 , 27.6 , 0 , 0
union all select
0 , 0 , 13.8 , 0
union all select
0 , 0 , 13.7 , 0
union all select
0 , 0 , 13.8 , 0
union all select
0 , 41.4 , 0 , 0
union all select
0 , 0 , 13.9 , 0
union all select
0 , 0 , 0 , 13
union all select
14 , 0 , 0 , 0) x
go
SELECT iss_quantity,received_qty,moved_qty,alloc_qty FROM MyTable
WHERE iss_quantity <> 0 OR received_qty <> 0 OR moved_qty <> 0 OR alloc_qty <> 0
go
drop table dbo.mytable

set nocount off
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-14 : 09:40:14
Preston,

When a re you going to start using [ code] [ /code] tags?

Did you go to DC last week?



Brett

8-)
Go to Top of Page

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-06-14 : 09:46:28
Drymchaser,

I see where you're coming from. I read the problem as wanting rows back unless all columns contained 0.
Re-reading through it, it seems as if he wants columns populated with values that aren't 0, but that it doesn't matter that the column values returned aren't from the original row the data was in, ie. he wants all columns sorted with non-zero values at the top, regardless, and then 'losing' the zeroes in the other rows. Bizarre - must be for a report.

Go to Top of Page

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-06-14 : 11:41:51
quote:
Originally posted by X002548

Preston,

When a re you going to start using [ code] [ /code] tags?

Did you go to DC last week?



I used the [lazy] cut & paste [/lazy] and didn't want to remove all that blank space.

No couldn't make it.
Go to Top of Page
   

- Advertisement -