I have this SQL code that runs product sales information, however I would like it to display a set range of numbers. The ALU is my product numbers, I have a large range of numbers and would only like to see ALU numbers between 4000000 and 4009999. Here is my current code. I have selected some results to be ignored.
Any help in the right direction would be great.
select
--r.sbs_name as "Subsidiary",
r.store_code as "Store",
r.ALU as "ALU",
--r.dcs_code as "DCS Code",
--r.vend_code as "Vendor Code",
r.description1 as "Description1",
--r.attribute as "Attribute",
--r.item_size as "Size",
r.qty_sold as "Qty Sold",
round(
case when r.ext_cost = 0
then 0
else (r.ext_cost / r.qty_sold)
end, 2) as "Item Cost",
r.ext_cost as "Total Cost",
r.ext_price as "Total Retail"
from
(
select s.sbs_name,
St.Store_code,
d.dcs_code,
v.vend_code,
i.description1,
i.attribute,
i.item_size,
i.ALU,
sum(decode(di.item_type, 2, di.qty * -1, di.qty)) as qty_sold,
sum(round((decode(di.item_type, 2, di.qty * -1, di.qty) * (di.cost)), 2)) as ext_cost,
sum(round((decode(di.item_type, 2, di.qty * -1, di.qty) * (di.price)), 2)) as ext_price
from rps.document do
inner join rps.document_item di
on do.sid = di.doc_sid
inner join rps.subsidiary s
on do.sbs_no = s.sbs_no
and di.sbs_no = s.sbs_no
inner join rps.dcs d
on s.sid = d.sbs_sid
and di.dcs_code = d.dcs_code
inner join rps.store st
on do.store_sid = st.sid
inner join rps.invn_sbs_item i
on di.invn_sbs_item_sid = i.sid
and s.sid = i.sbs_sid
inner join rps.vendor v
on i.vend_sid = v.sid
where do.receipt_type in (0, 1)
and di.item_type in (1, 2)
and do.status = 4
and =-=do.sbs_no=-=
and =-=st.store_no=-=
and =-=st.store_code=-=
and =-=trunc(do.created_datetime)=-=
group by s.sbs_name,
St.Store_code,
d.dcs_code,
v.vend_code,
i.description1,
i.attribute,
i.item_size,
i.ALU
) r
Tried adding between 4000000 and 4009999 but not sure of the correct command or location
New contributor
Paul Bieschke Jr is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Either of these should work:
select
<your select clause>
from
(
select <your select clause>
sum(..)
from <your from clause>
where <your where clause)
AND i.ALU between 4000000 and 4009999
group by <your group by clause>
) r
or
select
<your select clause>
from
(
select <your select clause>
sum(..)
from <your from clause>
where <your where clause)
group by <your group by clause>
) r
where r.ALU between 4000000 and 4009999
Because your ‘i’ table (rps.invn_sbs_item) is inner joined to everything else, you can also add this condition to the ON cluse of the JOIN, if you wish.
1