Home » SQL & PL/SQL » SQL & PL/SQL » Diffrence in number records while using subquery
Diffrence in number records while using subquery [message #273873] Fri, 12 October 2007 01:24 Go to next message
sonalshastry
Messages: 52
Registered: September 2007
Member
Hi all,

i need some help problem is related to PLSQL

my subquery (A1) gives me 10403 records but my main query gives me only 7562 records i have not kept any condition except join between these two queries in my WHERE statement .

select C1.clientid,
       b.ent_name "Dealer Name",
       c.ent_name " Branch name ",
       a.ent_address_line_1,
       a.ent_address_line_2,
       a.ent_address_line_3,
       a.ent_address_line_4,
       a.ent_address_line_5,
       a.ent_address_line_7,
       a.ent_phone_no_1 ,
       a.ent_phone_no_2,
       a.ent_category,
       a.ent_client_type,
       a.ent_status,
       email_address,
       cis.cis_empl_name
  from ENTITY_MASTER A, ENTITY_MASTER B, ENTITY_MASTER C,
       client_info_stage cis, 
       (
       select distinct eam.eam_ent_id client,
       case when eam.eam_seg_id = 'E' then eam.eam_brk_sch_id end "BRK_ID",
       ebs.brs_scheme_details "SCH_DETAIL" 
       from entity_attribute_master eam,brokerage_schemes ebs
       where  eam.eam_brk_sch_id = ebs.brs_id
       and ebs.brs_to_date is null
       and eam.eam_seg_id = 'E'
       and eam.eam_brk_sch_id <> '15'
       )A1,
       (     
      SELECT End_Id Clientid,
              Trim(Max(Decode(Email_num,1 ,End_Email_Id)))||
              Trim(Max(Decode(Email_num,2 ,','||End_Email_Id))) email_address
              FROM (
              SELECT End_Id, END_EMAIL_ID, 
                     row_number() 
                       over (PARTITION BY End_Id ORDER BY end_email_id) email_Num
              FROM   ENTITY_DETAILS   
              WHERE  END_STATUS  = 'A' 
              ORDER BY END_id
              )
              GROUP BY End_Id
        ) C1   
 where a.ent_ctrl_id = b.ent_id 
       and b.ent_ctrl_id = c.ent_id
       and cis_ram_acc_no = a.ent_id 
       and C1.clientid = a.ent_id
       and A1.client = C1.Clientid
       and A1.client = C1.Clientid

can someone help
please

Sonal

[Updated on: Fri, 12 October 2007 01:45] by Moderator

Report message to a moderator

Re: PLSQL Query [message #273874 is a reply to message #273873] Fri, 12 October 2007 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Fix the query.

Regards
Michel
Mysterious query? [message #273875 is a reply to message #273873] Fri, 12 October 2007 01:27 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Congratulations on your useful title! Change it.

MHE
Re: Mysterious query? [message #273884 is a reply to message #273875] Fri, 12 October 2007 01:45 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Thank you for your quick response! Now we're getting somewhere. why don't you work step by step? See where the results differentiate from what you expected. We don't know your data model, nor what you are looking for ...

MHE
Re: Diffrence in number records while using subquery [message #273885 is a reply to message #273873] Fri, 12 October 2007 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is better now you give the query. Next time remove useless empty lines and take of line width.

Now do as Maarten said.

Regards
Michel
Re: Diffrence in number records while using subquery [message #273886 is a reply to message #273885] Fri, 12 October 2007 01:49 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I would suspect, from looking at your query, that there are records returned by A1 that have no matches in C1, and/or records in C1 that have no matches in A.
Previous Topic: ORA-01790
Next Topic: tricky grouping
Goto Forum:
  


Current Time: Sun Dec 04 18:47:16 CST 2016

Total time taken to generate the page: 0.04759 seconds