SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Solve my problem of table insertion ------------------
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 06/14/2004 :  07:21:38  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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

United Kingdom
158 Posts

Posted - 06/14/2004 :  07:29:23  Show Profile  Reply with Quote
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

USA
552 Posts

Posted - 06/14/2004 :  09:12:05  Show Profile  Reply with Quote
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 - 06/14/2004 :  09:40:14  Show Profile  Reply with Quote
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

United Kingdom
158 Posts

Posted - 06/14/2004 :  09:46:28  Show Profile  Reply with Quote
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

USA
552 Posts

Posted - 06/14/2004 :  11:41:51  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000