Results 1 to 5 of 5

Thread: Query

  1. #1
    Join Date
    Jan 2015
    Posts
    2

    Unanswered: Query

    HI the below statement selects the rows in which all criteria are met as below.
    But how can i also pull all the entry's from PPOS which don't have INv_quan?

    SELECT HNR.HNR_ID, PPOS.KNR, SUPL.SUPL_NO, SUPL.NAME1, PPOS.PUR_NO, PUR.NAME, PPOS.POS_NO, PPOS.QUAN, PPOS.UNIT, PPOS.PRICE_QUAN, PUR.CURRENCY, PPOS.PRICE, PPOS.NET, PPOS.DESCR, PPOS.POS_TEXT, INV_QUAN.INV_NO, INV_QUAN.QUAN, INV_QUAN.AMOUNT, BUDGET.ACT_COST, PPOS.TOT_INV, PPOS.STATUS
    FROM HI.BUDGET BUDGET, HI.HNR HNR, HI.INV_QUAN INV_QUAN, HI.INVOICE INVOICE, HI.PPOS PPOS, HI.PUR PUR, HI.SUPL SUPL
    WHERE INV_QUAN.INV_NO = INVOICE.INV_NO
    AND PPOS.PUR_NO = INV_QUAN.PUR_NO
    AND PPOS.POS_NO = INV_QUAN.POS_NO
    AND PUR.PUR_NO = PPOS.PUR_NO
    AND HNR.HNR = PUR.HNR
    AND PUR.SUP_NO = SUPL.SUPL_NO
    AND PPOS.PUR_NO = BUDGET.PUR_NO
    AND PPOS.POS_NO = BUDGET.POS_NO
    AND ((HNR.HNR_ID=?))

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    depends
    ..if your column is allowed to be null then use the appropriate function tro add to your where clause
    http://www.techonthenet.com/oracle/isnull.php

    if you don't allow nulls, then presumably you want to return any row which has an invalid INv_quan, which presumably anything which is 0 or less.

    ...of course the smart alek answer would be to set a check constraint, or whatever they are called within Oracle that forces the INv_quan to be valid before storing it.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2015
    Posts
    2
    Perhaps should be clearer
    The INV_QUAN is table linked via this
    AND PPOS.PUR_NO = INV_QUAN.PUR_NO
    AND PPOS.POS_NO = INV_QUAN.POS_NO

    But I also need the rest of the values from the given criteria.
    Basically all lines from PPOS that meet the input criteria along with any data from the INV_QUAN table
    if i just use these 2 tables a left outer join does the job, but struggling to use when adding the extra tables.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    we don't have your tables or data.
    How will you, I, or any recognize when correct SQL is posted here?

    what exactly is correct result set?
    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.

  5. #5
    Join Date
    May 2014
    Location
    World Wide On The Web
    Posts
    16
    Quote Originally Posted by trebor21 View Post
    if i just use these 2 tables a left outer join does the job, but struggling to use when adding the extra tables.
    Then do the same while joining other tables. Your posted query doesn't look like you have tried OUTER join. Post a working test case, copy paste what you have done so far.
    Regards,
    Lalit

Posting Permissions

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