Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2012
    Posts
    37

    Unanswered: checking the value of columns in a row in PL SQL

    Hello Everyone

    I have a table which contain 6 columns

    C1<---->C2<---->C3<---->C4<---->C5<---->C6
    11<---->11<----><----><----><----><---->
    10<---->10<---->10<---->10<---->10<---->10
    11<---->11<---->11<---->11<---->11<---->11
    11<---->10<---->11<----><----><----><---->
    <----><----><----><----><----><---->

    What I want to do is for rows which has same data it should return value of any column else it should return value -1
    Null's are allowed but not for all the columns

    Like from above table the expected result is
    11
    10
    11
    -1
    -1

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    if row contains as below
    11<---->11<---->10<---->10<---->13<---->13<---->
    what should be returned & why?

    >Null's are allowed but not for all the columns
    if above is true, then how & why does row below exist in table?
    ><----><----><----><----><----><---->

    >What I want to do is for rows which has same data
    why is 11 not returned for row below, since 11 exists more than once (is same)?
    >11<---->10<---->11<----><----><----><---->
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2012
    Posts
    37
    Hi Thanks for taking interest

    for row
    11<---->11<---->10<---->10<---->13<---->13<---->
    its not about the maximum times the number exist its about the same data
    like in above row it should return -1

    >Null's are allowed but not for all the columns
    if above is true, then how & why does row below exist in table?
    ><----><----><----><----><----><---->


    I have a table which contain 200 column but I have to apply operation on this 6 column only, that's why I have written the above scenario.

    >What I want to do is for rows which has same data
    why is 11 not returned for row below, since 11 exists more than once (is same)?
    >11<---->10<---->11<----><----><----><---->


    The same case exist for above row the column has value but one column has different value that's why it return -1

    Hope I am able to clear you...

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If only 1 colum was NOT NULL, like
    ><---->11<----><----><----><----><---->
    what should be returned?
    11? or -1?

  5. #5
    Join Date
    Mar 2012
    Posts
    37
    It should return 11

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?

    Code:
         , CASE LEAST   (  COALESCE(c1 , c2 , c3 , c4 , c5 , c6)
                         , COALESCE(c2 , c3 , c4 , c5 , c6 , c1)
                         , COALESCE(c3 , c4 , c5 , c6 , c1 , c2)
                         , COALESCE(c4 , c5 , c6 , c1 , c2 , c3)
                         , COALESCE(c5 , c6 , c1 , c2 , c3 , c4)
                         , COALESCE(c6 , c1 , c2 , c3 , c4 , c5)
                        )
           WHEN GREATEST(  COALESCE(c1 , c2 , c3 , c4 , c5 , c6)
                         , COALESCE(c2 , c3 , c4 , c5 , c6 , c1)
                         , COALESCE(c3 , c4 , c5 , c6 , c1 , c2)
                         , COALESCE(c4 , c5 , c6 , c1 , c2 , c3)
                         , COALESCE(c5 , c6 , c1 , c2 , c3 , c4)
                         , COALESCE(c6 , c1 , c2 , c3 , c4 , c5)
                        )                                        THEN
                COALESCE(c1 , c2 , c3 , c4 , c5 , c6)
           ELSE -1
           END
    Note: I used COALESCEs, because I'm not sure how null values treated in GREATEST/LEAST functions.
    Last edited by tonkuma; 02-11-14 at 05:33. Reason: Replaced THEN value.

  7. #7
    Join Date
    Mar 2012
    Posts
    37
    thanks for the help..
    Last edited by goodman2253; 02-11-14 at 06:18.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •