This discussion is archived
4 Replies Latest reply: Nov 13, 2012 8:19 PM by 894936 RSS

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

894936 Newbie
Currently Being Moderated
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

Legend

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