This discussion is archived
9 Replies Latest reply: Feb 2, 2013 10:53 PM by jeneesh RSS

Query output issue

shabar Newbie
Currently Being Moderated
Hi All

In the following query my intention is to get matching records when Both "1" and "3" values are there (in SMPHT_Med.StateCode) against "A1" ( in SMPHT_LKP_ProOpt.StateCode)

Here SMPHT_LKP_ProOpt is the lookup table to match records between SMPHQ_Active_Item and SMPHT_Med

Basically I need un matched records when there are no two records in SMPHT_Med for "1" and "3" in SMPHT_Med.StateCode column when the column value of SMPHT_LKP_ProOpt.State is "A1"

SELECT SMPHQ_Active_Item.SMPHT_Mod.ItemID, SMPHT_LKP_ProOpt.*, SMPHT_Med.*
FROM (SMPHQ_Active_Item INNER JOIN SMPHT_LKP_ProOpt ON SMPHQ_Active_Item.Comp = SMPHT_LKP_ProOpt.Comp) LEFT JOIN SMPHT_Med ON 
(
   (
        (SMPHT_LKP_ProOpt.State = SMPHT_Med.StateCode) 
          OR 
         (
             (SMPHT_LKP_ProOpt.State = "A1") AND  (SMPHT_Med.StateCode ="1") AND (SMPHT_Med.StateCode = "3") 
          )
    )
    AND (SMPHT_LKP_ProOpt.OptCode = SMPHT_Med.OptCode)
)
But with the above query even I have both "1" and "3" values in SMPHT_Med it shows as missing



What would be the issue


Cheers

Shabar
  • 1. Re: Query output issue
    sb92075 Guru
    Currently Being Moderated
    shabar wrote:
    Hi All

    In the following query my intention is to get matching records when Both "1" and "3" values are there (in SMPHT_Med.StateCode) against "A1" ( in SMPHT_LKP_ProOpt.StateCode)

    Here SMPHT_LKP_ProOpt is the lookup table to match records between SMPHQ_Active_Item and SMPHT_Med

    Basically I need un matched records when there are no two records in SMPHT_Med for "1" and "3" in SMPHT_Med.StateCode column when the column value of SMPHT_LKP_ProOpt.State is "A1"

    SELECT SMPHQ_Active_Item.SMPHT_Mod.ItemID, SMPHT_LKP_ProOpt.*, SMPHT_Med.*
    FROM (SMPHQ_Active_Item INNER JOIN SMPHT_LKP_ProOpt ON SMPHQ_Active_Item.Comp = SMPHT_LKP_ProOpt.Comp) LEFT JOIN SMPHT_Med ON 
    (
    (
    (SMPHT_LKP_ProOpt.State = SMPHT_Med.StateCode) 
    OR 
    (
    (SMPHT_LKP_ProOpt.State = "A1") AND  (SMPHT_Med.StateCode ="1") AND (SMPHT_Med.StateCode = "3") 
    )
    )
    AND (SMPHT_LKP_ProOpt.OptCode = SMPHT_Med.OptCode)
    )
    But with the above query even I have both "1" and "3" values in SMPHT_Med it shows as missing



    What would be the issue


    Cheers

    Shabar
    SELECT smphq_active_item.smpht_mod.itemid, 
           smpht_lkp_proopt.*, 
           smpht_med.* 
    FROM   (smphq_active_item 
            inner join smpht_lkp_proopt 
                    ON smphq_active_item.comp = smpht_lkp_proopt.comp) 
           left join smpht_med 
                  ON ( ( ( smpht_lkp_proopt.state = smpht_med.statecode ) 
                          OR ( ( smpht_lkp_proopt.state = "a1" ) 
                               AND ( smpht_med.statecode = "1" ) 
                               AND ( smpht_med.statecode = "3" ) ) ) 
                       AND ( smpht_lkp_proopt.optcode = smpht_med.optcode ) ) 
    how can STATECODE be both 1 AND 3 in the same record?

    You should ALWAYS post CREATE TABLE & INSERT statements so we have same tables & data as you.


    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: Query output issue
    shabar Newbie
    Currently Being Moderated
    Thax sb92075 for your reply

    how can STATECODE be both 1 AND 3 in the same record?
    There should be two records in SMPHT_Med table once is for "1" the others is for "3". Otherwise it should give the missing record as mis match

    In following scenario one record should give as mismatch
    ID     TrfCode     SubCatCode     ProdCode     OptCode     StateCode
    9     11TRC                              11OPT     5
    Table data as follows
    SMPHT_Med
    
    ID     TrfCode     SubCatCode     ProdCode     OptCode              StateCode
    1     11TRC                              11OPT     1
    2     12TRC                              12OPT     2
    3     13TRC                              13OPT     2
    4     15TRC                              15OPT     4
    5     16TRC                              16OPT     4
    6                                        
    7     XXTRC                              19OPT     1
    8     18TRC                              XXOPT     4
    9     11TRC                              11OPT     5
    SMPHT_LKP_ProOpt
    
    ID     Comp      State     OptCode     Desc
    1     abc     A1             11OPT     
    2     xyz     2             12OPT     
    3     mno     3             13OPT     
    4     pqr     3          
    5     ghi     4              18OPT     
    6     kky     1              19OPT     
    Cheers

    Shabar
  • 3. Re: Query output issue
    sb92075 Guru
    Currently Being Moderated
    You are free to think what you want (regardless of whether it is valid or not)
    I won't waste more of my time on this thread.
  • 4. Re: Query output issue
    shabar Newbie
    Currently Being Moderated
    Hi sb92075

    Sorry for bothering you

    You reckon this is invalid and impossible scenario. Because I have got this type of condition when table data matching


    Cheers


    Shabar
  • 5. Re: Query output issue
    sb92075 Guru
    Currently Being Moderated
    shabar wrote:
    Hi sb92075

    Sorry for bothering you

    You reckon this is invalid and impossible scenario.
    I am saying that the posted SQL is working correctly.

    If you require different results, then you'll need different SQL.
  • 6. Re: Query output issue
    shabar Newbie
    Currently Being Moderated
    Hi sb92075

    I tried to do following way

    SELECT SMPHQ_Active_Item.SMPHT_Mod.ItemID, SMPHT_LKP_ProOpt.*, SMPHT_Med.*
    FROM (SMPHQ_Active_Item INNER JOIN SMPHT_LKP_ProOpt ON SMPHQ_Active_Item.Comp = SMPHT_LKP_ProOpt.Comp) LEFT JOIN SMPHT_Med ON 
    (
       (
            (SMPHT_LKP_ProOpt.State = SMPHT_Med.StateCode) 
              OR 
             (
                 (SMPHT_LKP_ProOpt.State = "A1") AND  (SMPHT_Med.StateCode IN ( "1") )
              )
        )
        AND (SMPHT_LKP_ProOpt.OptCode = SMPHT_Med.OptCode)
    )
    
    UNION
    
    SELECT SMPHQ_Active_Item.SMPHT_Mod.ItemID, SMPHT_LKP_ProOpt.*, SMPHT_Med.*
    FROM (SMPHQ_Active_Item INNER JOIN SMPHT_LKP_ProOpt ON SMPHQ_Active_Item.Comp = SMPHT_LKP_ProOpt.Comp) LEFT JOIN SMPHT_Med ON 
    (
       (
            (SMPHT_LKP_ProOpt.State = SMPHT_Med.StateCode) 
              OR 
             (
                 (SMPHT_LKP_ProOpt.State = "A1") AND  (SMPHT_Med.StateCode IN ( "3") )
              )
        )
        AND (SMPHT_LKP_ProOpt.OptCode = SMPHT_Med.OptCode)
    )
    That case I got all the matching records. But in case if there is more than one not matching situations its giving only ONE record for un-matching record.

    For example if do some more UNIONs to the above query with other values for SMPHT_Med.StateCode.

    Is it possible to get unmatch records for each value if there are,


    One more clarification In case if we get same results for two queries even if they UNION we don't get same records twice. Is that the expected behavior


    Appreciate your response

    Cheers

    Shabar
  • 7. Re: Query output issue
    sb92075 Guru
    Currently Being Moderated
    We don't have your tables & we don't have your data, so what you claim is 100% meaningless to all but you.
  • 8. Re: Query output issue
    shabar Newbie
    Currently Being Moderated
    Anyway thax a lot for your responses


    Cheers

    Shabar
  • 9. Re: Query output issue
    jeneesh Guru
    Currently Being Moderated
    shabar wrote:
    Anyway thax a lot for your responses


    Cheers

    Shabar
    Help us to help you better..

    Please provide your DB version..

    And Sample data (CREATE TABLE and INSERT statements)

    And expected output - with the explanation of the logic to arrive at the output..

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points