Topic: editing someone elses SQL Posted: 05 Oct 2015 at 5:04am
I am attempting to add a field to a Command table. The SQL was written by someone else. I have minimal SQL knowledge. IDK if someone has the knowledge to see an easy way to accomplish this.
When I look at this report in crystal the Command table has a field called PRODUCT_CODE.
It appears to be created in SQL with the following code.
What I would like to be able to do is to see the ci.comp_product_code field listed in the COMMAND table.
If there is an easy way to do this and someone has the knowledge to tell me how that would be greatly appreciated.
Thanks,
Chuck
The SQL is below:
select
'HEADER' header,
case
when substr(to_char(r.shipment_id),1,4)='9999' then to_number(substr(to_char(r.shipment_id),5)) else r.shipment_id end final_shipment_id,
r.*
cpc.literal,
s.freight_cd,
bd.actual_price,
sum(case when ci.abo_cd||ci.rh_cd='AP' then 1 else 0 end) apos,
sum(case when ci.abo_cd||ci.rh_cd='AN' then 1 else 0 end) aneg,
sum(case when ci.abo_cd||ci.rh_cd='BP' then 1 else 0 end) bpos,
sum(case when ci.abo_cd||ci.rh_cd='BN' then 1 else 0 end) bneg,
sum(case when ci.abo_cd||ci.rh_cd='ABP' then 1 else 0 end) abpos,
sum(case when ci.abo_cd||ci.rh_cd='ABN' then 1 else 0 end) abneg,
sum(case when ci.abo_cd||ci.rh_cd='OP' then 1 else 0 end) opos,
sum(case when ci.abo_cd||ci.rh_cd='ON' then 1 else 0 end) oneg,
sum(1) total
from shipment s
inner join shipment_item si
on s.shipment_id=si.shipment_id
inner join shipment_item_component sic
on s.shipment_id=sic.shipment_id
and si.shipment_item_no=sic.shipment_item_no
inner join component_inventory ci
on sic.compinv_id=ci.compinv_id
inner join component_product_code cpc
on ci.comp_product_code=cpc.comp_product_code
inner join facility fs
on s.ship_to_facility_id=fs.facility_id
left outer join facility fb
on s.bill_to_facility_id=fb.facility_id
left outer join cd_freight cf
on s.freight_cd=cf.freight_cd
left outer join billing_transaction bt
on s.shipment_id=bt.shipment_id
and bt.item_type_cd='F'
left outer join billing_detail bd
on bt.transaction_id=bd.transaction_id
and bd.billing_service_cd=cf.billing_service_cd
from shipment s
inner join shipment_item si
on s.shipment_id=si.shipment_id
inner join shipment_item_service sis
on s.shipment_id=sis.shipment_id
and si.shipment_item_no=sis.shipment_item_no
inner join service se
on sis.service_id=se.service_id
inner join facility fs
on s.ship_to_facility_id=fs.facility_id
left outer join facility fb
on s.bill_to_facility_id=fb.facility_id
left outer join cd_freight cf
on s.freight_cd=cf.freight_cd
left outer join billing_transaction bt
on s.shipment_id=bt.shipment_id
and bt.item_type_cd='F'
left outer join billing_detail bd
on bt.transaction_id=bd.transaction_id
and bd.billing_service_cd=cf.billing_service_cd
cpc.literal,
null as freight_cd,
null as actual_price,
sum(case when ci.abo_cd||ci.rh_cd='AP' then 1 else 0 end) apos,
sum(case when ci.abo_cd||ci.rh_cd='AN' then 1 else 0 end) aneg,
sum(case when ci.abo_cd||ci.rh_cd='BP' then 1 else 0 end) bpos,
sum(case when ci.abo_cd||ci.rh_cd='BN' then 1 else 0 end) bneg,
sum(case when ci.abo_cd||ci.rh_cd='ABP' then 1 else 0 end) abpos,
sum(case when ci.abo_cd||ci.rh_cd='ABN' then 1 else 0 end) abneg,
sum(case when ci.abo_cd||ci.rh_cd='OP' then 1 else 0 end) opos,
sum(case when ci.abo_cd||ci.rh_cd='ON' then 1 else 0 end) oneg,
sum(1) total
from rp_shipment rs
inner join rp_shipment_carton rsc
on rs.rp_shipment_id=rsc.rp_shipment_id
inner join rp_shipment_carton_comp rscc
on rs.rp_shipment_id=rscc.rp_shipment_id
and rsc.carton_id=rscc.carton_id
inner join component_inventory ci
on rscc.compinv_id=ci.compinv_id
inner join component_product_code cpc
on ci.comp_product_code=cpc.comp_product_code
inner join facility fs
on rs.ship_to_facility_id=fs.facility_id
left outer join facility fb
on rs.bill_to_facility_id=fb.facility_id
where rs.rp_shipment_id is not null
group by
to_number(9999 || regexp_replace(rs.rp_shipment_id,'[^[:digit:]]')),
rs.ship_to_facility_id,
rs.bill_to_facility_id,
fs.facility_name,
rs.courier_id,
rs.shipment_datetime,
rs.created_by,
ci.comp_product_code || ci.donation_type_cd || ci.division,
cpc.literal) r
where r.shipment_id is not null
{?DATE_SEL}
{?SHIPMENT_ID_SEL}
{?RP_SHIPMENT_ID_SEL}
{?ORDER_ID_SEL}
{?SHIP_TO_FACILITY_SEL}
{?BILL_TO_FACILITY_SEL}
{?COURIER_ID_SEL}
{?CREATED_BY_SEL}
{?REQUEST_PRIORITY_SEL}
order by to_number(regexp_replace(r.product_cd,'[^[:digit:]]')), regexp_replace(r.product_cd,'[[:digit:]]')
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Posted: 07 Oct 2015 at 12:41pm
This is not too difficult, but I suspect you're running into issues because this is a "Union" query with multiple select statements. Every query in a Union has to return the same data types in the same column locations. So, your query would look like this (my changes are in bold:
select
'HEADER' header,
case
when substr(to_char(r.shipment_id),1,4)='9999' then to_number(substr(to_char(r.shipment_id),5)) else r.shipment_id end final_shipment_id,
r.*
from (
select
'S' shipment_type,
to_number(regexp_replace(s.shipment_id,'[^[:digit:]]')) shipment_id,
s.inventory_order_id,
s.ship_request_priority_cd,
s.ship_to_facility_id,
s.bill_to_facility_id,
fs.facility_name,
s.courier_id,
s.shipment_datetime,
s.created_by,
ci.comp_product_code || ci.donation_type_cd || ci.division product_cd,
ci.comp_product_code, cpc.literal,
s.freight_cd,
bd.actual_price,
sum(case when ci.abo_cd||ci.rh_cd='AP' then 1 else 0 end) apos,
sum(case when ci.abo_cd||ci.rh_cd='AN' then 1 else 0 end) aneg,
sum(case when ci.abo_cd||ci.rh_cd='BP' then 1 else 0 end) bpos,
sum(case when ci.abo_cd||ci.rh_cd='BN' then 1 else 0 end) bneg,
sum(case when ci.abo_cd||ci.rh_cd='ABP' then 1 else 0 end) abpos,
sum(case when ci.abo_cd||ci.rh_cd='ABN' then 1 else 0 end) abneg,
sum(case when ci.abo_cd||ci.rh_cd='OP' then 1 else 0 end) opos,
sum(case when ci.abo_cd||ci.rh_cd='ON' then 1 else 0 end) oneg,
sum(1) total
from shipment s
inner join shipment_item si
on s.shipment_id=si.shipment_id
inner join shipment_item_component sic
on s.shipment_id=sic.shipment_id
and si.shipment_item_no=sic.shipment_item_no
inner join component_inventory ci
on sic.compinv_id=ci.compinv_id
inner join component_product_code cpc
on ci.comp_product_code=cpc.comp_product_code
inner join facility fs
on s.ship_to_facility_id=fs.facility_id
left outer join facility fb
on s.bill_to_facility_id=fb.facility_id
left outer join cd_freight cf
on s.freight_cd=cf.freight_cd
left outer join billing_transaction bt
on s.shipment_id=bt.shipment_id
and bt.item_type_cd='F'
left outer join billing_detail bd
on bt.transaction_id=bd.transaction_id
and bd.billing_service_cd=cf.billing_service_cd
where s.shipment_id is not null
group by
to_number(regexp_replace(s.shipment_id,'[^[:digit:]]')) ,
s.inventory_order_id,
s.ship_request_priority_cd,
s.ship_to_facility_id,
s.bill_to_facility_id,
fs.facility_name,
s.courier_id,
s.shipment_datetime,
s.created_by,
ci.comp_product_code,
ci.donation_type_cd,
ci.division, cpc.literal,
s.freight_cd,
bd.actual_price
from shipment s
inner join shipment_item si
on s.shipment_id=si.shipment_id
inner join shipment_item_service sis
on s.shipment_id=sis.shipment_id
and si.shipment_item_no=sis.shipment_item_no
inner join service se
on sis.service_id=se.service_id
inner join facility fs
on s.ship_to_facility_id=fs.facility_id
left outer join facility fb
on s.bill_to_facility_id=fb.facility_id
left outer join cd_freight cf
on s.freight_cd=cf.freight_cd
left outer join billing_transaction bt
on s.shipment_id=bt.shipment_id
and bt.item_type_cd='F'
left outer join billing_detail bd
on bt.transaction_id=bd.transaction_id
and bd.billing_service_cd=cf.billing_service_cd
where s.shipment_id is not null
union all
select
'R' shipment_type,
to_number(9999 || regexp_replace(rs.rp_shipment_id,'[^[:digit:]]')) rp_shipment_id,
null as inventory_order_id,
null as ship_request_priority_cd,
rs.ship_to_facility_id,
rs.bill_to_facility_id,
fs.facility_name,
rs.courier_id,
rs.shipment_datetime,
rs.created_by,
ci.comp_product_code || ci.donation_type_cd || ci.division product_cd,
ci.comp_product_code, cpc.literal,
null as freight_cd,
null as actual_price,
sum(case when ci.abo_cd||ci.rh_cd='AP' then 1 else 0 end) apos,
sum(case when ci.abo_cd||ci.rh_cd='AN' then 1 else 0 end) aneg,
sum(case when ci.abo_cd||ci.rh_cd='BP' then 1 else 0 end) bpos,
sum(case when ci.abo_cd||ci.rh_cd='BN' then 1 else 0 end) bneg,
sum(case when ci.abo_cd||ci.rh_cd='ABP' then 1 else 0 end) abpos,
sum(case when ci.abo_cd||ci.rh_cd='ABN' then 1 else 0 end) abneg,
sum(case when ci.abo_cd||ci.rh_cd='OP' then 1 else 0 end) opos,
sum(case when ci.abo_cd||ci.rh_cd='ON' then 1 else 0 end) oneg,
sum(1) total
from rp_shipment rs
inner join rp_shipment_carton rsc
on rs.rp_shipment_id=rsc.rp_shipment_id
inner join rp_shipment_carton_comp rscc
on rs.rp_shipment_id=rscc.rp_shipment_id
and rsc.carton_id=rscc.carton_id
inner join component_inventory ci
on rscc.compinv_id=ci.compinv_id
inner join component_product_code cpc
on ci.comp_product_code=cpc.comp_product_code
inner join facility fs
on rs.ship_to_facility_id=fs.facility_id
left outer join facility fb
on rs.bill_to_facility_id=fb.facility_id
where rs.rp_shipment_id is not null
group by
to_number(9999 || regexp_replace(rs.rp_shipment_id,'[^[:digit:]]')),
rs.ship_to_facility_id,
rs.bill_to_facility_id,
fs.facility_name,
rs.courier_id,
rs.shipment_datetime,
rs.created_by,
ci.comp_product_code,
ci.donation_type_cd,
ci.division, cpc.literal) r
order by to_number(regexp_replace(r.product_cd,'[^[:digit:]]')), regexp_replace(r.product_cd,'[[:digit:]]')
Note: I just assumed that you would want a null in the middle query there. You can change that to be the same as the field above it if you need to - just make sure that you have it in there twice so there are the same number of fields in all of the queries.
Thank you sir. Your assumption is correct i was running into errors based on number of columns or something like that. Thank you very much for you help.
I do pretty good with crystal reports that i have initiated myself, but unraveling someone else's SQL can be a steep learning curve for me.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
