Home » SQL & PL/SQL » SQL & PL/SQL » Select rows with non zero values
Select rows with non zero values [message #281265] Fri, 16 November 2007 04:48 Go to next message
Vackar
Messages: 81
Registered: October 2007
Member
Hi,

I've got a really simple question.If you have a query which returns the following results:

------------------------------------------------------
ROW_ID     Val_1       Val_2       Val_3
------------------------------------------------------
  1          22          0           5
  2          52          0           2
  3          65          0           5
  4           0          0           0
  5           0          0           0
  6           0          0           0
  7           0          0           0
  8          44          0           9
------------------------------------------------------


What clause do you add to the query to elimiate rows 4,5,6,7 i.e. the rows with no values.
So what I basically need is an sql clause which mimmicks this:
(Val_1 + Val_2 + Val_3)>0


So what i'm looking for is:
------------------------------------------------------
ROW_ID     Val_1       Val_2       Val_3
------------------------------------------------------
  1          22          0           5
  2          52          0           2
  3          65          0           5
  8          44          0           9
------------------------------------------------------


Also is it possible to order by the total number of results.
So I need the equivalant of
order by (Val_1 + Val_2 + Val_3)


so that you get
------------------------------------------------------
ROW_ID     Val_1       Val_2       Val_3
------------------------------------------------------
  3          65          0           5
  2          52          0           2
  8          44          0           9
  1          22          0           5
------------------------------------------------------




Thanks,
Vackar
Re: Select rows with non zero values [message #281267 is a reply to message #281265] Fri, 16 November 2007 04:52 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
I guess you didn't even try what you're asking.

Guess what? It could work Shocked
Re: Select rows with non zero values [message #281273 is a reply to message #281267] Fri, 16 November 2007 05:04 Go to previous messageGo to next message
Vackar
Messages: 81
Registered: October 2007
Member
I did try it but you can't do

select ....
from ....
where ...
and ...
and (V1+v2+v3)>0
order by (v1+v2+v3)


you get syntax errors. Razz

But thanks for the suggestion
Re: Select rows with non zero values [message #281275 is a reply to message #281273] Fri, 16 November 2007 05:21 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Show us..
Re: Select rows with non zero values [message #281280 is a reply to message #281265] Fri, 16 November 2007 05:53 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Are you sure You tried in Oracle ? Smile

Thumbs Up
Rajuvan
Re: Select rows with non zero values [message #281282 is a reply to message #281275] Fri, 16 November 2007 05:57 Go to previous messageGo to next message
Vackar
Messages: 81
Registered: October 2007
Member
Oops, my bad... I used the wrong var names in my query.
But I'm still not getting the results that i'm expecting.

When I run the query without the additional clause I get:
ENSG00000005156	0	0	1
ENSG00000177469	0	0	1
ENSG00000099812	0	0	1
ENSG00000170291	0	0	1
ENSG00000181885	0	0	1
ENSG00000137267	0	2	3
ENSG00000184363	0	0	1
ENSG00000105928	0	0	1
ENSG00000153071	0	0	1
ENSG00000167601	0	0	1


but when I add
and (X0_Count + X1_Count + X2_Count)>0


I get no results at all.
Re: Select rows with non zero values [message #281284 is a reply to message #281282] Fri, 16 November 2007 05:59 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Could you provide us with a describe of the table you're selecting from?
Re: Select rows with non zero values [message #281288 is a reply to message #281284] Fri, 16 November 2007 06:09 Go to previous messageGo to next message
Vackar
Messages: 81
Registered: October 2007
Member
Hi MarcS,

It's querying a table generated from a "with..." query

The structure of the table is as follows:

Column1: Gene_Name ,Varchar2
Column2: X0_Count, Number
Column3: X1_Count, Number
Column4: X2_Count, Number


[Updated on: Fri, 16 November 2007 06:10]

Report message to a moderator

Re: Select rows with non zero values [message #281289 is a reply to message #281288] Fri, 16 November 2007 06:13 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Let's stop beating about the bush. Can you post your full query rather than little bits of it.
Cheers
Re: Select rows with non zero values [message #281301 is a reply to message #281265] Fri, 16 November 2007 06:30 Go to previous messageGo to next message
Vackar
Messages: 81
Registered: October 2007
Member
Ok, the basic query is:

with X0Table as 
( 
select GENES.ID , count(Gene_Expression_results.ID) as X0_Count 
from GENES,GENE_EXPRESSION_RESULTS, RESULTS_METADATA  
where  GENES.ID = GENE_EXPRESSION_RESULTS.GENEID 
and GENE_EXPRESSION_RESULTS.RESULTSMETADATAID = RESULTS_METADATA.ID 
and RESULTS_METADATA.EXPERIMENTNAME='Cytoxan sensitivity' group by GENES.ID 
)
,
X1Table as 
( 
select GENES.ID , count(Gene_Expression_results.ID) as X1_Count 
from GENES,GENE_EXPRESSION_RESULTS, RESULTS_METADATA  
where  GENES.ID = GENE_EXPRESSION_RESULTS.GENEID 
and GENE_EXPRESSION_RESULTS.RESULTSMETADATAID = RESULTS_METADATA.ID 
and RESULTS_METADATA.EXPERIMENTNAME='5FU sensitivity classifier' group by GENES.ID 
)
,
X2Table as 
( 
select GENES.ID , count(Gene_Expression_results.ID) as X2_Count 
from GENES,GENE_EXPRESSION_RESULTS, RESULTS_METADATA  
where  GENES.ID = GENE_EXPRESSION_RESULTS.GENEID 
and GENE_EXPRESSION_RESULTS.RESULTSMETADATAID = RESULTS_METADATA.ID 
and RESULTS_METADATA.EXPERIMENTNAME='etoposide sensitivity' group by GENES.ID 
) 

select 
genes.NAME Gene_Name,
nvl(X0Table.X0_Count,0) X0_Count,
nvl(X1Table.X1_Count,0) X1_Count,
nvl(X2Table.X2_Count,0) X2_Count 
from 
(
  (
  Genes full outer join X0Table on (X0Table.ID=Genes.ID)
  )
  full outer join X1Table on (X1Table.ID=Genes.ID)
)
full outer join X2Table on (X2Table.ID=Genes.ID) 
where upper(genes.NAME) like upper('%') 


And here is a subset of results:

ENSG00000005156	0	0	1
ENSG00000177469	0	0	1
ENSG00000099812	0	0	1
ENSG00000170291	0	0	1
ENSG00000181885	0	0	1
ENSG00000137267	0	2	3
ENSG00000184363	0	0	1
ENSG00000105928	0	0	1
ENSG00000153071	0	0	1
ENSG00000167601	0	0	1
ENSG00000167642	0	0	1
ENSG00000178950	0	0	1
ENSG00000185187	0	0	1
ENSG00000157227	0	0	2
ENSG00000141738	0	0	1
ENSG00000173801	0	0	1
ENSG00000165215	0	0	1
ENSG00000160888	0	0	1
ENSG00000106683	0	0	1
ENSG00000067225	0	0	1
ENSG00000166145	0	0	1
ENSG00000118898	0	0	1
ENSG00000182107	0	0	1
ENSG00000119888	0	0	1
ENSG00000137274	0	2	3
ENSG00000073350	0	0	1
ENSG00000106078	0	0	1
ENSG00000169554	0	0	1
ENSG00000186470	0	0	2
ENSG00000170748	0	0	1
ENSG00000113140	0	0	1
ENSG00000103066	0	0	1
ENSG00000189143	0	0	1
ENSG00000131378	0	0	1
ENSG00000118900	0	0	1
ENSG00000100335	0	0	0
ENSG00000106261	0	0	0
ENSG00000166741	0	0	0
ENSG00000152234	0	0	0
ENSG00000118094	1	0	0
ENSG00000115486	1	0	0
ENSG00000114019	0	0	0
ENSG00000168502	0	0	0
ENSG00000173992	0	0	0
ENSG00000137285	0	2	0


when you add the clause

(Val_1 + Val_2 + Val_3)>0


I get no results.
Re: Select rows with non zero values [message #281303 is a reply to message #281301] Fri, 16 November 2007 06:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

when you add the clause
(Val_1 + Val_2 + Val_3)>0
I get no results.

Of course: when you add that you get an invalid syntax.

Regards
Michel
Re: Select rows with non zero values [message #281305 is a reply to message #281265] Fri, 16 November 2007 06:39 Go to previous messageGo to next message
Vackar
Messages: 81
Registered: October 2007
Member
Sorry, just copied from my last post. What I meant to say was that when I add

and (X0_Count+X1_Count+X2_Count)>0


I get no results.

It's not my day today Confused
Re: Select rows with non zero values [message #281307 is a reply to message #281305] Fri, 16 November 2007 06:41 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What happens if you modify it like
AND (NVL(X0_Count, 0) + NVL(X1_Count, 0) + NVL(X2_Count, 0)) > 0
Re: Select rows with non zero values [message #281308 is a reply to message #281301] Fri, 16 November 2007 06:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The problem you've got is that your reference to X0_Count, X1_Count etc in the where clause are refering directly to the values in X0Table, X1Table etc, where the values are null, and NULL + any number = NULL

Try replacing the Where clause with:
WHERE (nvl(X0Table.X0_Count,0) + nvl(X1Table.X1_Count,0) + nvl(X2Table.X2_Count,0)) > 0


{Drat - too slow}

[Updated on: Fri, 16 November 2007 06:43]

Report message to a moderator

Re: Select rows with non zero values [message #281309 is a reply to message #281265] Fri, 16 November 2007 06:45 Go to previous messageGo to next message
Vackar
Messages: 81
Registered: October 2007
Member
Ok it's a typical Friday mistake and I know why it's not working now. The additional clause relates to the query which is used to build the final table (and never holds true for the the individual tables which make up the final results table).

So if I make the final results table a temp table and query that with the clause:
(X0_Count+X1_Count+X2_Count)>0
order by (X0_Count+X1_Count+X2_Count) desc


Then it works.

Thanks for the help everyone! Cool
Re: Select rows with non zero values [message #281311 is a reply to message #281309] Fri, 16 November 2007 06:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You shouldn't need to make a Temp table.

Does NVLing the Xn_count values not work?
Re: Select rows with non zero values [message #281314 is a reply to message #281311] Fri, 16 November 2007 06:54 Go to previous messageGo to next message
Vackar
Messages: 81
Registered: October 2007
Member
Hi,

I don't think it's right for what want to do because I only want to remove rows where all column values are zero.
Re: Select rows with non zero values [message #281344 is a reply to message #281314] Fri, 16 November 2007 08:19 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
then do "where col1 = 0 and col2 = 0 and col3 = 0" instead of adding them.
Re: Select rows with non zero values [message #281355 is a reply to message #281344] Fri, 16 November 2007 08:48 Go to previous messageGo to next message
Vackar
Messages: 81
Registered: October 2007
Member
But that would give me all the rows with no values and filter out the rows with values. I wanted to do the opposite.
Re: Select rows with non zero values [message #281356 is a reply to message #281355] Fri, 16 November 2007 08:50 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Vackar wrote on Fri, 16 November 2007 15:48

But that would give me all the rows with no values and filter out the rows with values. I wanted to do the opposite.


What's wrong with a bit of creative thinking these days Shocked
Smile
Re: Select rows with non zero values [message #281405 is a reply to message #281356] Fri, 16 November 2007 13:24 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
MarcS wrote on Fri, 16 November 2007 09:50

Vackar wrote on Fri, 16 November 2007 15:48

But that would give me all the rows with no values and filter out the rows with values. I wanted to do the opposite.


What's wrong with a bit of creative thinking these days Shocked
Smile


How is this for creative thinking.

Get the rows that match Frank's answer, toss those and then what you have left is the answer.
Re: Select rows with non zero values [message #281421 is a reply to message #281265] Fri, 16 November 2007 16:07 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
think about it!!!



where nvl(col1,0) <> 0 or nvl(col2,0) <> 0 or nvl(col3,0) <> 0

[Updated on: Fri, 16 November 2007 16:08]

Report message to a moderator

Re: Select rows with non zero values [message #281723 is a reply to message #281421] Mon, 19 November 2007 01:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you can find me a functional difference between that and the code I posted then I'll be impressed (assuming that alt the values are null, 0 or positive, anyway):
WHERE (nvl(X0Table.X0_Count,0) + nvl(X1Table.X1_Count,0) + nvl(X2Table.X2_Count,0)) > 0


To the OP: Have you tried the solution I suggested above?
If it doesn't match your requirements, can you at least explain why not, and what it is doing wrong?
Re: Select rows with non zero values [message #281743 is a reply to message #281723] Mon, 19 November 2007 03:16 Go to previous message
Vackar
Messages: 81
Registered: October 2007
Member
OK, this solution does work. I just prefer the other solution because I'm generating the query dynamically based on a set of paramaters, so you can have X0, X1, X2, X3, X4, X4, ...Xn, and using the other solution just fits better into the strucure that I already have.

But thanks for the help, I'm sure it will come in handy in the future. Cool
Previous Topic: TRIGGER
Next Topic: round function in dates
Goto Forum:
  


Current Time: Fri Dec 09 13:39:00 CST 2016

Total time taken to generate the page: 0.10204 seconds