FAQ
Chaps,

Been stuck on a little bit of sql.

Need to add up some columns with text values in them e.g 'One', 'Two'
etc etc and see if the total is > 0.

Thought I had it working but its not

I am doing

select CASE WHEN (CASE WHEN READERSHIP_OFTEN_SATURDAY='One'
THEN 0.25 END)+
(CASE WHEN READERSHIP_OFTEN_SATURDAY='Two'
THEN 0.5 END)+
(CASE WHEN READERSHIP_OFTEN_SATURDAY='Three'
THEN 0.75 END)+
(CASE WHEN READERSHIP_OFTEN_SATURDAY='Four'
THEN 1 END) > 0 THEN 'Y' ELSE 'N' END AS Reader
FROM lee;

Data is as follows
Two values in table for READERSHIP_OFTEN_SATURDAY
One
Four

Both values are coming back as N when I would expect an Y.

Any help appreciated, its going to be staring me in the face but its
late and Im tired.

TIA
Lee

***************************************************************************
The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be legally
privileged.

If the reader of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.

If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.

Thank You.
****************************************************************************

Search Discussions

  • Jack van Zanen at Nov 7, 2011 at 10:23 pm
    WHEN SUM (
    CASE
    WHEN readership_often_saturday = 'One' THEN 0.25
    WHEN readership_often_saturday = 'Two' THEN 0.5
    WHEN readership_often_saturday = 'Three' THEN 0.75
    WHEN readership_often_saturday = 'Four' THEN 1
    END) > 0 THEN 'Y'
    ELSE 'N'
    END AS reader
    FROM lee;
    Jack van Zanen

    -------------------------
    This e-mail and any attachments may contain confidential material for the
    sole use of the intended recipient. If you are not the intended recipient,
    please be aware that any disclosure, copying, distribution or use of this
    e-mail or any attachment is prohibited. If you have received this e-mail in
    error, please contact the sender and delete all copies.
    Thank you for your cooperation

    On Tue, Nov 8, 2011 at 8:36 AM, Robertson Lee - lerobe wrote:

    Chaps,

    Been stuck on a little bit of sql.

    Need to add up some columns with text values in them e.g 'One', 'Two'
    etc etc and see if the total is > 0.

    Thought I had it working but its not

    I am doing

    select CASE WHEN (CASE WHEN READERSHIP_OFTEN_SATURDAY='One'
    THEN 0.25 END)+
    (CASE WHEN READERSHIP_OFTEN_SATURDAY='Two'
    THEN 0.5 END)+
    (CASE WHEN READERSHIP_OFTEN_SATURDAY='Three'
    THEN 0.75 END)+
    (CASE WHEN READERSHIP_OFTEN_SATURDAY='Four'
    THEN 1 END) > 0 THEN 'Y' ELSE 'N' END AS Reader
    FROM lee;

    Data is as follows
    Two values in table for READERSHIP_OFTEN_SATURDAY
    One
    Four

    Both values are coming back as N when I would expect an Y.

    Any help appreciated, its going to be staring me in the face but its
    late and Im tired.

    TIA
    Lee

    ***************************************************************************
    The information contained in this communication is confidential, is
    intended only for the use of the recipient named above, and may be legally
    privileged.

    If the reader of this message is not the intended recipient, you are
    hereby notified that any dissemination, distribution or copying of this
    communication is strictly prohibited.

    If you have received this communication in error, please resend this
    communication to the sender and delete the original message or any copy
    of it from your computer system.

    Thank You.

    ****************************************************************************

    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • Robertson Lee - lerobe at Nov 7, 2011 at 10:46 pm
    Hi Jack,


    Thanks for the suggestion. I maybe didn't explain my problem clearly
    enough.



    I cannot use sum as each row in the table would need to return a value
    for the indicator flag.



    Lee



    From: jack.van.zanen@gmail.com On
    Behalf Of Jack van Zanen
    Sent: 07 November 2011 22:19
    To: Robertson Lee - lerobe
    Cc: oracle-l@freelists.org
    Subject: Re: Irritating problem



    SELECT

    CASE

    WHEN SUM (

    CASE

    WHEN readership_often_saturday = 'One' THEN 0.25

    WHEN readership_often_saturday = 'Two' THEN 0.5

    WHEN readership_often_saturday = 'Three' THEN 0.75

    WHEN readership_often_saturday = 'Four' THEN 1

    END) > 0 THEN 'Y'

    ELSE 'N'

    END AS reader

    FROM lee;


    Jack van Zanen

    -------------------------
    This e-mail and any attachments may contain confidential material for
    the sole use of the intended recipient. If you are not the intended
    recipient, please be aware that any disclosure, copying, distribution or
    use of this e-mail or any attachment is prohibited. If you have received
    this e-mail in error, please contact the sender and delete all copies.
    Thank you for your cooperation



    On Tue, Nov 8, 2011 at 8:36 AM, Robertson Lee - lerobe
    wrote:

    Chaps,

    Been stuck on a little bit of sql.

    Need to add up some columns with text values in them e.g 'One', 'Two'
    etc etc and see if the total is > 0.

    Thought I had it working but its not

    I am doing

    select CASE WHEN (CASE WHEN READERSHIP_OFTEN_SATURDAY='One'
    THEN 0.25 END)+
    (CASE WHEN READERSHIP_OFTEN_SATURDAY='Two'
    THEN 0.5 END)+
    (CASE WHEN READERSHIP_OFTEN_SATURDAY='Three'
    THEN 0.75 END)+
    (CASE WHEN READERSHIP_OFTEN_SATURDAY='Four'
    THEN 1 END) > 0 THEN 'Y' ELSE 'N' END AS Reader
    FROM lee;

    Data is as follows
    Two values in table for READERSHIP_OFTEN_SATURDAY
    One
    Four

    Both values are coming back as N when I would expect an Y.

    Any help appreciated, its going to be staring me in the face but its
    late and Im tired.

    TIA
    Lee

    ************************************************************************
    ***
    The information contained in this communication is confidential, is
    intended only for the use of the recipient named above, and may be
    legally
    privileged.

    If the reader of this message is not the intended recipient, you are
    hereby notified that any dissemination, distribution or copying of this
    communication is strictly prohibited.

    If you have received this communication in error, please resend this
    communication to the sender and delete the original message or any copy
    of it from your computer system.

    Thank You.
    ************************************************************************
    ****
  • Stephane Faroult at Nov 7, 2011 at 10:50 pm
    Lee,
    Jack provided you with a correct query but your problem is that you
    have CASEs with only one WHEN - and when the condition isn't satisfied
    (within one CASE) you don't know what the whole CASE expression should
    be, therefore it's NULL. You are each time adding four values, three of
    which are unknown, result unknown, which you cannot say is > 0, and it
    ends up as 'N'.

    I hope it clarifies somehow.
    --
    Stephane Faroult
    RoughSea Ltd <http://www.roughsea.com>
    Konagora <http://www.konagora.com>
    RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>


    On 11/07/2011 10:36 PM, Robertson Lee - lerobe wrote:
    Chaps,

    Been stuck on a little bit of sql.

    Need to add up some columns with text values in them e.g 'One', 'Two'
    etc etc and see if the total is> 0.

    Thought I had it working but its not

    I am doing

    select CASE WHEN (CASE WHEN READERSHIP_OFTEN_SATURDAY='One'
    THEN 0.25 END)+
    (CASE WHEN READERSHIP_OFTEN_SATURDAY='Two'
    THEN 0.5 END)+
    (CASE WHEN READERSHIP_OFTEN_SATURDAY='Three'
    THEN 0.75 END)+
    (CASE WHEN READERSHIP_OFTEN_SATURDAY='Four'
    THEN 1 END)> 0 THEN 'Y' ELSE 'N' END AS Reader
    FROM lee;

    Data is as follows
    Two values in table for READERSHIP_OFTEN_SATURDAY
    One
    Four

    Both values are coming back as N when I would expect an Y.

    Any help appreciated, its going to be staring me in the face but its
    late and Im tired.

    TIA
    Lee

    ***************************************************************************
    The information contained in this communication is confidential, is
    intended only for the use of the recipient named above, and may be legally
    privileged.

    If the reader of this message is not the intended recipient, you are
    hereby notified that any dissemination, distribution or copying of this
    communication is strictly prohibited.

    If you have received this communication in error, please resend this
    communication to the sender and delete the original message or any copy
    of it from your computer system.

    Thank You.
    ****************************************************************************

    --
    http://www.freelists.org/webpage/oracle-l



    --
    http://www.freelists.org/webpage/oracle-l
  • Michael Moore at Nov 7, 2011 at 11:51 pm
    Why not just ...
    SELECT CASE
    WHEN readership_often_saturday IN ('One', 'Two', 'Three', 'Four')
    THEN 'Y'
    ELSE 'N'
    END
    AS reader
    FROM lee;
  • Robertson Lee - lerobe at Nov 8, 2011 at 12:06 am
    Oh dear,


    Fantastic and I knew it would be something simple.



    That works a treat and also for the other bit that needs to be done as
    part of that as well.



    Thanks to everyone who contributed to curing my brain freeze J





    From: Michael Moore
    Sent: 07 November 2011 23:50
    To: sfaroult@roughsea.com
    Cc: Robertson Lee - lerobe; oracle-l@freelists.org
    Subject: Re: Irritating problem



    Why not just ...



    SELECT CASE

    WHEN readership_often_saturday IN ('One', 'Two', 'Three',
    'Four') THEN 'Y'

    ELSE 'N'

    END

    AS reader

    FROM lee;





    ***************************************************************************
    The information contained in this communication is confidential, is
    intended only for the use of the recipient named above, and may be legally
    privileged.

    If the reader of this message is not the intended recipient, you are
    hereby notified that any dissemination, distribution or copying of this
    communication is strictly prohibited.

    If you have received this communication in error, please resend this
    communication to the sender and delete the original message or any copy
    of it from your computer system.

    Thank You.
    ****************************************************************************
  • Mark W. Farnham at Nov 8, 2011 at 3:37 am
    Add up? I'm unclear about what you mean. You're going to get exactly one
    value per column per row. From your statement it appears that you mean you
    want 'N' for a return value when the columns contains 'One', 'Two', or
    'Three' and you want 'Y' for 'Four'. Unless you're trying to mask your
    meaning I don't know why you're trying to transfer that meaning via numeric
    amounts.

    If you mean something different, please explain. Otherwise

    select decode(READERSHIP_OFTEN_SATURDAY,
    'One','N','Two','N','Three','N','Four','Y','N') "Reader" from lee;

    might be what you're trying to do.

    Regards,

    mwf

    -----Original Message-----
    From: oracle-l-bounce@freelists.org
    On Behalf Of Robertson Lee - lerobe
    Sent: Monday, November 07, 2011 4:36 PM
    To: oracle-l@freelists.org
    Subject: Irritating problem

    Chaps,

    Been stuck on a little bit of sql.

    Need to add up some columns with text values in them e.g 'One', 'Two'
    etc etc and see if the total is > 0.

    Thought I had it working but its not

    I am doing

    select CASE WHEN (CASE WHEN READERSHIP_OFTEN_SATURDAY='One'
    THEN 0.25 END)+
    (CASE WHEN READERSHIP_OFTEN_SATURDAY='Two'
    THEN 0.5 END)+
    (CASE WHEN READERSHIP_OFTEN_SATURDAY='Three'
    THEN 0.75 END)+
    (CASE WHEN READERSHIP_OFTEN_SATURDAY='Four'
    THEN 1 END) > 0 THEN 'Y' ELSE 'N' END AS Reader
    FROM lee;

    Data is as follows
    Two values in table for READERSHIP_OFTEN_SATURDAY One Four

    Both values are coming back as N when I would expect an Y.

    Any help appreciated, its going to be staring me in the face but its late
    and Im tired.

    TIA
    Lee

    ***************************************************************************
    The information contained in this communication is confidential, is intended
    only for the use of the recipient named above, and may be legally
    privileged.

    If the reader of this message is not the intended recipient, you are hereby
    notified that any dissemination, distribution or copying of this
    communication is strictly prohibited.

    If you have received this communication in error, please resend this
    communication to the sender and delete the original message or any copy of
    it from your computer system.

    Thank You.
    ****************************************************************************

    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 7, '11 at 10:02p
activeNov 8, '11 at 3:37a
posts7
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase