4 Replies Latest reply: Nov 13, 2012 10:19 PM by 894936 RSS

    EXPECTED OUTPUT:   here i need to get the count of 932 + 134 records = 1064

    894936
      Hi Team,
      could you pls suggest y iam getting wrong count.

      select * from i_invoice_info_t_log_v invlog,invoice_header_t invhead
      WHERE substr(invlog.tot_cust_no,1,7)=substr(invhead.acct_no,3,7)--------comparing tot_cust_no and acct_no for the other countries
      and invhead.comp_code NOT in (2300) ----here when iam running this query i am getting the count of 932 records.
      
      select * from i_invoice_info_t_log_v invlog,invoice_header_t invhead
      where substr(invlog.tot_cust_no,3,7)=to_char(substr(invhead.acct_no,3,7))--------------comparing tot_cust_no and acct_no FOR THE COUNTRY 2300
      and invhead.comp_code in (2300) ----Here when i am running this query i am getting the count of 134 records.
      
      EXPECTED OUTPUT:   here i need to get the count of 932 + 134 records = 1064 records.
      
      I am  using this query like below:
      
      select * from  i_invoice_info_t_log_v invlog,invoice_header_t invhead 
      where
      (
      (substr(invlog.tot_cust_no,1,7)=substr(invhead.acct_no,3,7)
                 and invhead.comp_code NOT in (2300)
                 )
                OR
                (substr(invlog.tot_cust_no,3,7)=to_char(substr(invhead.acct_no,3,7))
                 and invhead.comp_code in (2300)
                )
      )---------------------------------------------------here when i am running this query i am getting the count of  18381 records which is incorrect.
      
      
      Expected output: i need to get the data for the comp_code which are not in 2300 and which are in 2300... both