Page 1 of 3 123 LastLast
Results 1 to 15 of 35

Thread: Help with Query

  1. #1
    Join Date
    Oct 2005
    Posts
    92

    Unanswered: Help with Query

    Hello,

    I have a table such as,

    Seq Typ rate
    1 buy 5
    2 sell 4
    3 buy 5
    4 sell 0
    5 buy 4
    6 sell 5

    can someone please help me with a query, that fetch only those rows where type 'buy' and rate is not 0 and
    type 'sell' and rate is 0

    Thanks
    Pavan

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by pavan_test View Post
    . . . E t c . . .
    can someone please help me with a query, that fetch only those rows where type 'buy' and rate is not 0 and
    type 'sell' and rate is 0
    We do not do homework, but show us what you got and where you are stuck and maybe some angel here will help you out.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Oct 2005
    Posts
    92

    Help with Query

    I tried with this query, but it returns nothing

    select * from table cdrc, table cdrc1 where
    cdrc.seq_1 = cdrc1.seq_1
    and (cdrc1.TYP_CD = 'Buy' and cdrc1.RATE <> 0)
    and (cdrc.TYP_CD = 'Sell'and cdrc.RATE = 0)

    thanks
    Pavan

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    Quote Originally Posted by pavan_test View Post
    I tried with this query, but it returns nothing
    Code:
    SELECT *
      FROM TABLE cdrc, TABLE cdrc1
     WHERE cdrc.seq_1 = cdrc1.seq_1
       AND (cdrc1.typ_cd = 'Buy'
        AND cdrc1.rate <> 0)
       AND (cdrc.typ_cd = 'Sell'
        AND cdrc.rate = 0);
    thanks
    Pavan
    Hint: use the "code' tags to format your queries.

    You are in the right track...
    1) Are there two tables or only one? If only one, there is no need to self join the table.
    2) Remove the words "TABLE" after the "FROM" and before the table name(s).
    3) The conditions need to check for exact values (upper/lower case).
    Like this:
    Code:
    SELECT *
      FROM cdrc
     WHERE (cdrc.typ_cd = 'buy'
        AND cdrc.rate <> 0)
       OR (cdrc.typ_cd = 'sell'
        AND cdrc.rate = 0);
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Oct 2005
    Posts
    92

    Help with Query

    I apologize and I corrected the query.

    I have to check for 'and' only when both conditions satisfy then return the data. 'or' works but 'and' do not return any data.

    select * from table where
    ((TYP_CD = 'buy' and RATE <> 0) and (TYP_CD = 'sell'and RATE = 0))

    Thanks
    Pavan

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down

    Your query with "AND" will never return any data.

    If you expect data, post a sample of the expected results.
    .
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Oct 2005
    Posts
    92

    Help With Query

    I am trying to fetch all the consumer id's where, " typ is 'buy' and rate is <> 0 (and) typ is 'sell' and rate = 0 "

    Source Data:

    Seqid typ rate consumer_id
    1 buy 5 100
    2 misc 3 100
    3 search 4 100
    4 sell 0 100

    Expected Output Data:

    Seqid typ rate consumer_id
    1 buy 5 100
    4 sell 0 100

    (2)

    I am trying to fetch all the consumer id's where, " typ is 'buy' and rate is <> 0 (and) typ is 'sell' and rate <> 0 "

    Source Data:

    Seqid typ rate consumer_id
    1 buy 5 101
    2 misc 5 101
    3 search 4 101
    4 sell 3 101

    Expected Output Data:

    Seqid typ rate consumer_id
    1 buy 5 101
    4 sell 3 101

    So, I am trying with "and" condition.

    select * from table where (TYP_CD = 'buy' and RATE <> 0 and TYP_CD = 'sell' and RATE = 0)

    Thanks
    Pavan

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT * 
    FROM   table1 
    WHERE  ( typ_cd = 'buy' 
             AND rate <> 0 ) 
            OR ( typ_cd = 'sell' 
                 AND rate = 0 )
    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.

  9. #9
    Join Date
    Oct 2005
    Posts
    92

    Help With the Query

    Thank You.

    SELECT *
    FROM table1
    WHERE ( typ_cd = 'buy'
    AND rate <> 0 )
    OR ( typ_cd = 'sell'
    AND rate = 0 )

    This query works for customer '100'.

    However this query returns the following for customer_id '101'

    Seqid typ rate consumer_id
    1 buy 5 101

    But I am not expecting any output for the customer '101 since the query did not match the condition for this customer
    i.e. " typ is 'buy' and rate is <> 0 and typ is 'sell' and rate <> 0 "

    Thanks
    Pavan

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >However this query returns the following for customer_id '101'
    >Seqid typ rate consumer_id
    >1 buy 5 101
    >But I am not expecting any output for the customer '101 since the query did not match the condition for this customer
    >i.e. " typ is 'buy' and rate is <> 0 and typ is 'sell' and rate <> 0 "

    for CUSTOMER_ID=101, TYP='buy' & RATE != 0 (it is 5) so this record should be returned!
    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.

  11. #11
    Join Date
    Oct 2005
    Posts
    92

    Help With Query

    SELECT *
    FROM table1
    WHERE ( typ_cd = 'buy'
    AND rate <> 0 )
    and ( typ_cd = 'sell'
    AND rate = 0 )

    The above Query do not return anything. I am trying to return only when both conditions satisfy

    i.e." typ is 'buy' and rate is <> 0 (and) typ is 'sell' and rate = 0 " for customer 100

    When i change the query to

    SELECT *
    FROM table1
    WHERE ( typ_cd = 'buy'
    or rate <> 0 )
    or ( typ_cd = 'sell'
    AND rate = 0 )

    The expected output is good for customer 100.

    But it also returns 1 record for customer 101 where

    for CUSTOMER_ID=101, where TYP='buy' & RATE != 0 (it is 5).

    The expected output should be only when both the conditions satisfy. not either of them.

    " typ is 'buy' and rate is <> 0 (and) typ is 'sell' and rate <> 0 " for customer 101
    " typ is 'buy' and rate is <> 0 (and) typ is 'sell' and rate = 0 " for customer 100

    Thanks
    Pavan

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    So you want to return rows where rate equals zero and rate does not equal zero in the same row ??? I'm not sure how you are going to accomplish that!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #13
    Join Date
    Oct 2005
    Posts
    92

    Help with Query

    yes,

    " typ is 'buy' and rate is <> 0 and typ is 'sell' and rate <> 0 " for customer 101
    " typ is 'buy' and rate is <> 0 and typ is 'sell' and rate = 0 " for customer 100

    Thanks
    Pavan

  14. #14
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >" typ is 'buy' and rate is <> 0 and typ is 'sell' and rate <> 0 " for customer 101
    >" typ is 'buy' and rate is <> 0 and typ is 'sell' and rate = 0 " for customer 100

    how can TYP='buy' AND TYP='sell' for same record?
    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.

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are no rows in your sample data where type is both buy and sell in one row.

    There are no rows in your sample data where rate is zero and not zero in the same row.

    Neither of those conditions can exist in Oracle, the Oracle storage engine does not support multi-valued fields like Pick does.

    I understand that you have a clear understanding of what you want, but one of two problems is interfering. Either you aren't explaining what you want so that I can understand it, or what you want does not make sense.

    Please try to explain your intent differently or provide an example of what you want.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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