Home » SQL & PL/SQL » SQL & PL/SQL » Select the row if atleast 1 column = 0 (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit)
Select the row if atleast 1 column = 0 [message #434101] Mon, 07 December 2009 03:37 Go to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Hi,

I have a table with the following columns:


NAME                            Null?     Type
------------------------------- --------- -----
Name                               NOT NULL  VARCHAR2(10)
Project                            NOT NULL  VARCHAR2(16)
Activity                           NOT NULL  VARCHAR2(12)
WEEK                               NOT NULL  DATE
MON                                       NUMBER(6,2)
MON1                                      NUMBER(6,2)
MON2                                      NUMBER(6,2)
TUE                                       NUMBER(6,2)
TUE1                                      NUMBER(6,2)
TUE2                                      NUMBER(6,2)
WED                                       NUMBER(6,2)
WED1                                      NUMBER(6,2)
WED2                                      NUMBER(6,2)
THU                                       NUMBER(6,2)
THU1                                      NUMBER(6,2)
THU2                                      NUMBER(6,2)
FRI                                       NUMBER(6,2)
FRI1                                      NUMBER(6,2)
FRI2                                      NUMBER(6,2)
SAT                                       NUMBER(6,2)
SAT1                                      NUMBER(6,2)
SAT2                                      NUMBER(6,2)
SUN                                       NUMBER(6,2)
SUN1                                      NUMBER(6,2)
SUN2                                      NUMBER(6,2)



I want a query to select a row IF ANY ONE of the following columns = 0


MON,MON1,MON2,TUE,TUE1,TUE2,WED,WED1,WED2,THU,THU1,THU2,FRI,FRI1,FRI2,SAT,SAT1,SAT2,SUN,SUN1,SUN2



Any suggestions are welcome.

Thanks in Advance Smile

Re: Select the row if atleast 1 column = 0 [message #434103 is a reply to message #434101] Mon, 07 December 2009 03:45 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
where MON  = 0,
   or MON1 = 0,
   or MON2 = 0,
   or TUE  = 0,
   or TUE1 = 0,
...........


If the can't have any negative values you could also do

where least(MON,MON1,MON2,TUE......) = 0


but that is most likely worse performance-wise.


Re: Select the row if atleast 1 column = 0 [message #434106 is a reply to message #434103] Mon, 07 December 2009 03:49 Go to previous message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Cool.

Thanks Thomas Smile
Previous Topic: Offset Value of a image
Next Topic: running total
Goto Forum:
  


Current Time: Wed Sep 28 10:49:46 CDT 2016

Total time taken to generate the page: 0.09124 seconds