0

I have table mkitem for items and mknewitem where it will store all of the updated value. Each updated row for items will update in mkitem and mknewitem. While mkolditems will store the value before the updated. I hope this is clears.

I am trying to do and sql code to get the item_code, item_name, item_barcode, old_price and new_price. I have tried a few code but I dont think, I get the output like how I imagined. Please help me.

this is what I tried using nested.

select distinct g.trn_no, g.item_code, g.item_barcode,g.item_name, s.sprice old, g.sprice new
from mkmknewitem g, mkolditems s
where g.trn_no=s.trn_no
and g.item_code = s.item_code
and trunc(to_date(g.crt_date,'dd//mm/yyyy')) between trunc(to_date('01/01/2020','dd//mm/yyyy')) AND trunc(to_date('23/01/2020','dd//mm/yyyy'))
and g.date_b3 is not null
and s.store_no in ('RW','RQ','RD')
order by g.item_code, g.trn_no;
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
newbeginnner
  • 45
  • 7
  • 1
    Can you provide sample data and output ? that will be helpful for people to provide solution – Avi Jan 23 '20 at 03:41
  • you need to do an `inner join`. See [this question](https://stackoverflow.com/q/3022713/4256677) – varontron Jan 23 '20 at 03:43

1 Answers1

0

We unable to give a proper solution without any provided data and expected output. I modify your SQL to use the INNER JOIN to see whether is your expected data.

SELECT g.trn_no, g.item_code, g.item_barcode,g.item_name, s.sprice old, g.sprice new
from mkmknewitem g
INNER JOIN mkolditems s
ON g.trn_no=s.trn_no

WHERE trunc(to_date(g.crt_date,'dd//mm/yyyy')) between trunc(to_date('01/01/2020','dd//mm/yyyy')) AND trunc(to_date('23/01/2020','dd//mm/yyyy'))
and g.date_b3 is not null
and s.store_no in ('RW','RQ','RD')
--AND g.item_code = s.item_code
order by g.item_code, g.trn_no;

Here is a gentle reminder, unless you really know what you want for the result, DISTINCT is not a good idea and it might cause a certain troublesome problem in the future.

If you can provide more information, I will update the answer for you.

RogerSK
  • 393
  • 1
  • 18