9 Replies Latest reply: Feb 3, 2013 12:53 AM by jeneesh RSS

    Query output issue

    shabar
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        Anyway thax a lot for your responses


                        Cheers

                        Shabar
                        • 9. Re: Query output issue
                          jeneesh
                          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..