Link to home
Avatar of frogman22

asked on

Getting Bad Data Back for SQL Query

In running the below listed query I will get bad data back about 2% of the time when I am running 250 accounts numbers and 250 sub numbers at the same time.  When I run a single account number and a single phone number the data is always correct. I am wondering if I need a second left join to eliminate the problem. If I do I can not seem to get it to work right.  I am geting false chg_ent_seq_no info associated with the account number and sub no.

select *
from openquery(db1,'
select c.*, a.acv_amt, a.acv_cde, a.act_dt
    (select acount_nbr, order_number, sub_no, chg_ent_seq_no
    from db1.charge_it
    where acount_nbr in (123456789)
    and sub_no in (11111111111)
) c
left join db1.adjust_it a
on c.acount = a.acount
and c.chg_ent_seq_no = a.charge_seq_no
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

just for the record, you use:
>select acount_nbr
but the join is:
on c.acount = a.acount

now, cna you clarify why you use LEFT JOIN?

also, can you try to find out which account is giving the problem?
Avatar of frogman22


you are correct. The join should be c.acount_nbr = a.acount_nbr.  I modfied the real code field names for security purposes.  The reason the left join is used becasue I need all charges to show if no adjustments have been done at all.

In regard to the accounts I have reviewed multiple accounts where the error is occurring.  The sub_id is recycled in the database and historically can be on multiple accounts.  It is believed the chg_ent_seq_no is recycled too but is unique to acount_nbr and sub_id combination.
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
i agree with your solution.  However,it is not practical for what i am doing. I will post another question seeking a different solution. Thanks for yoiur help.