6 Replies Latest reply: Apr 27, 2013 12:26 PM by 1003231 RSS

    left join

    1003231
      left join
      Posted: Apr 27, 2013 8:08 AM
      11g [Edit]
           
           Edit                    Reply
      dear all,

      in the below query, when i use left join with PTRNPF i am getting 5 recirds only. for the sub query ''t'' am getting lots of records.sub query t is working fine. but left joining with ptrnpf gives 4 or 5 records only. is there any mistake in my query? im using 11g. i need to get all records from the sub query t.. please let me know if more info required.

      select t.CO_ID, t.POL_ID,t.AGNCY_ID,t.AGT_ID, npf.subdate
      from (select distinct com.m1co as CO_ID,
      fs.policy_number as POL_ID,
      com.agency_cd as AGNCY_ID,
      c.agent_code as AGT_ID,
      p.address,
      cas.date
      FROM comagtm1 com left JOIN channel c ON (com.agentno = c.agent_code),
      individual_agreement ia,
      benefit cas,
      CASCNTRM casc,
      casmwagt casm,
      person p ,
      financial_services fs

      WHERE fs.role_player_id = c.role_player_id
      AND com.m1co = fs.company_code
      and fs.policy_number = DECODE(ia.Business_Key_Contract_Number, NULL, ia.Business_Key_Policy_Number,
      ia.Business_Key_Contract_Number)
      and cas.fpolno = fs.policy_number
      and cas.fbrcd = fs.base_rider_code
      and casc.MCCNTR = fs.policy_number
      and casc.MCCO = fs.company_code
      and p.role_player_id = fs.role_player_id
      and fs.policy_number = casm.mwpoln
      and com.agency_cd = 'YAP') t
      left join .PTRNPF npf on t.POL_ID = npf.chdrnum
      where BATCDE = 'T600

      Edited by: 1000228 on Apr 27, 2013 12:10 PM
        • 1. Re: left join
          sb92075
          1000228 wrote:
          dear all,

          in the below query, when i use left join with PTRNPF i am getting 5 recirds only. for the sub query ''t'' am getting lots of records.sub query t is working fine. but left joining with ptrnpf gives 4 or 5 records only. is there any mistake in my query? im using 11g. i need to get all records from the sub query t.. please let me know if more info required.

          select t.CO_ID, t.POL_ID,t.AGNCY_ID,t.AGT_ID, npf.subdate
          from (select distinct com.m1co as CO_ID,
          fs.policy_number as POL_ID,
          com.agency_cd as AGNCY_ID,
          c.agent_code as AGT_ID,
          p.address,
          cas.date


          FROM comagtm1 com left JOIN channel c ON (com.agentno = c.agent_code),
          individual_agreement ia,
          benefit cas,
          CASCNTRM casc,
          casmwagt casm,
          person p ,
          financial_services fs

          WHERE fs.role_player_id = c.role_player_id
          AND com.m1co = fs.company_code
          and fs.policy_number = DECODE(ia.Business_Key_Contract_Number, NULL, ia.Business_Key_Policy_Number,
          ia.Business_Key_Contract_Number)
          and cas.fpolno = fs.policy_number
          and cas.fbrcd = fs.base_rider_code
          and casc.MCCNTR = fs.policy_number
          and casc.MCCO = fs.company_code
          and p.role_player_id = fs.role_player_id
          and fs.policy_number = casm.mwpoln

          and com.agency_cd = 'YAP') t
          left join .PTRNPF npf on t.POL_ID = npf.chdrnum

          where BATCDE = 'T600'

          Edited by: 1000228 on Apr 27, 2013 8:09 AM
          is COPY & PASTE broken for you?

          AFAIK, post SQL contains invalid syntax.
          left join .PTRNPF npf on t.POL_ID = npf.chdrnum
          what is the period character to the right of "left join" & to the left of "PTRNPF"?
          • 2. Re: left join
            Paulie
            >


            >


            Hi, and welcome to the group.


            Please read the two "fixed" posts at the top
            of this group and follow their instructions.
            (Marked with yellow asterisks)

            Please provide sample data in the form of
            working* (test before posting) statements.

            CREATE TABLE Blah(...

            INSERT INTO Blah VALUES(...

            Show desired results.


            HTH,


            Paul...
            • 3. Re: left join
              sb92075
              How do I ask a question on the forums?
              SQL and PL/SQL FAQ


              Handle:     1000228
              Status Level:     Newbie
              Registered:     Apr 15, 2013
              Total Posts:     27
              Total Questions:     8 (7 unresolved)


              I extend to you my condolences since you rarely get your questions answered.
              • 4. Re: left join
                ranit B
                Always post formatted code :
                SELECT 
                    t.co_id, 
                    t.pol_id, 
                    t.agncy_id, 
                    t.agt_id, 
                    npf.subdate
                  FROM (
                     SELECT DISTINCT com.m1co AS co_id, fs.policy_number AS pol_id,
                                        com.agency_cd AS agncy_id, c.agent_code AS agt_id,
                                        p.address, cas.DATE
                                   FROM comagtm1 com LEFT JOIN channel c
                                        ON (com.agentno = c.agent_code)
                                        ,
                                        individual_agreement ia,
                                        benefit cas,
                                        cascntrm casc,
                                        casmwagt casm,
                                        person p,
                                        financial_services fs
                                  WHERE fs.role_player_id = c.role_player_id
                                    AND com.m1co = fs.company_code
                                    AND fs.policy_number =
                                           DECODE (ia.business_key_contract_number,
                                                   NULL, ia.business_key_policy_number,
                                                   ia.business_key_contract_number
                                                  )
                                    AND cas.fpolno = fs.policy_number
                                    AND cas.fbrcd = fs.base_rider_code
                                    AND casc.mccntr = fs.policy_number
                                    AND casc.mcco = fs.company_code
                                    AND p.role_player_id = fs.role_player_id
                                    AND fs.policy_number = casm.mwpoln
                                    AND com.agency_cd = 'YAP'
                    ) t
                   LEFT JOIN
                      ptrnpf npf 
                   ON t.pol_id = npf.chdrnum
                 WHERE 
                    batcde = 'T600';
                May be your final WHERE clause (batcode = 'T600') is filtering all the records... just a guess
                Check w/o the WHERE clause how many records your query returns.

                Is there any specific reason you using both ANSI and Oracle style of SQL?
                Also, please tell us how did you decide upon this joining condition - <tt>"ON (t.pol_id = npf.chdrnum)"</tt>
                • 5. Re: left join
                  Etbin
                  Maybe (not mixing ANSI and Oracle join syntax)
                  select t.co_id,
                         t.pol_id,
                         t.agncy_id,
                         t.agt_id,
                         npf.subdate
                    from (select distinct 
                                 com.m1co as co_id,
                                 fs.policy_number as pol_id,
                                 com.agency_cd as agncy_id, 
                                 c.agent_code as agt_id,
                                 p.address,
                                 cas.date
                            from comagtm1 com,
                                 channel c,
                                 individual_agreement ia,
                                 benefit cas, 
                                 cascntrm casc,
                                 casmwagt casm,
                                 person p,
                                 financial_services fs 
                           where com.agentno = c.agent_code(+)
                             and fs.role_player_id = c.role_player_id
                             and com.m1co = fs.company_code 
                             and fs.policy_number = nvl(ia.business_key_contract_number,ia.business_key_policy_number) 
                             and cas.fpolno = fs.policy_number 
                             and cas.fbrcd = fs.base_rider_code 
                             and casc.mccntr = fs.policy_number
                             and casc.mcco = fs.company_code 
                             and p.role_player_id = fs.role_player_id 
                             and fs.policy_number = casm.mwpoln
                             and com.agency_cd = 'YAP'
                         ) t,
                         ptrnpf npf
                   where t.pol_id = npf.chdrnum(+)
                     and batcde = 'T600'
                  Regards

                  Etbin
                  • 6. Re: left join
                    1003231
                    WHERE clause (batcode = 'T600') is filtering all the records
                    now i got solution
                    Thank you for your suggestion ranjit...