SQL script for pulling add on cost expense

jon-athey

Need someone to analyse my script and tell me where I went wrong. I'm trying to create a script that will pull freight expense for the last 7 days but when we run it we get no output. I basically started with Crystal and took the "show SQL query" information and modified it based on other scripts I've seen. Any SQL experts in the community?

column dt new_val x
select to_char(sysdate,'yyyymmdd') from dual;
spool /lawson/lawprod/law/prod/work/optifreight/MIDMIC-COMP-&x.txt
select
‘MIDMIC’ -- "Billing Group Short Name"
ICLOCATION.PO_NAME, -- "Facility Name"
APINVOICE.PO_NUMBER, -- "Purchase Order Number"
APINVOICE.INVOICE, -- "Invoice Number"
APINVOICE.VENDOR, -- "Vendor Number"
coalesce (APVENLOC.VENDOR_VNAME, APVENMAST.VENDOR_VNAME), -- "Vendor Name"
APDISTRIB.ORIG_TRAN_AMT, -- "Freight Charge"
PURCHORDER.LOCATION, -- "Facility Code"
APDISTRIB.DIS_ACCT_UNIT, -- "Cost Center"
GLNAMES.DESCRIPTION, -- "Department Name"
ICLOCATION.PO_ADDR1, -- "Facility Address 1"
ICLOCATION.PO_ADDR2, -- "Facility Address 2"
ICLOCATION.PO_CITY_ADDR5, -- "Facility City"
ICLOCATION.PO_STATE_PROV, -- "Facility State"
ICLOCATION.PO_POSTAL_CD, -- "Facility Zip"
APINVOICE.BASE_TOT_PMT, -- "Total Invoice Amount"
APINVOICE.CREATE_DATE, -- "Invoice Date"
APVENLOC.VEND_ACCT, -- "Vendor Account Number"
from
PRODLAW.GLNAMES GLNAMES
inner join PRODLAW.APDISTRIB APDISTRIB
ON GLNAMES.COMPANY=APDISTRIB.COMPANY
AND GLNAMES.ACCT_UNIT=APDISTRIB.DIS_ACCT_UNIT
inner join PRODLAW.APVENMAST APVENMAST
ON APINVOICE.VENDOR = APVENMAST.VENDOR
inner join PRODLAW.APINVOICE APINVOICE
ON APDISTRIB.COMPANY=APINVOICE.COMPANY
AND APDISTRIB.VENDOR=APINVOICE.VENDOR
AND APDISTRIB.INVOICE=APINVOICE.INVOICE
AND APDISTRIB.SUFFIX=APINVOICE.SUFFIX
left outer join PRODLAW.APVENLOC APVENLOC
ON APINVOICE.VENDOR=APVENLOC.VENDOR
AND APINVOICE.PURCH_FR_LOC=APVENLOC.LOCATION_CODE
inner join PRODLAW.ICLOCATION ICLOCATION
ON PURCHORDER.COMPANY =ICLOCATION.COMPANY
AND PURCHORDER.LOCATION =ICLOCATION.LOCATION
inner join PRODLAW.PURCHORDER PURCHORDER
ON APINVOICE.COMPANY=PURCHORDER.COMPANY
AND APINVOICE.PO_CODE=PURCHORDER.PO_CODE
AND APINVOICE.PO_NUMBER=PURCHORDER.PO_NUMBER
where
APINVOICE.CREATE_DATE>=SYSDATE -7
AND APINVOICE.PO_NUMBER>' '
AND APDISTRIB.PO_AOC_CODE in ('FT','ON','2D','3D')

spool off;

0712131256598360.doc

unknown

there is a comma missing after 'MIDMIC' and I think a field name is needed here. also there is an extra comma after APVENLOC.VEND_ACCT in the select fields part of the query.

I don't use the inner join, outer join terminology when I write queries and we don't use add on charges so I commented that line out but I got data in an SQL query tool (TOAD) with this query:

select
'MIDMIC' Short_name, -- "Billing Group Short Name"
ICLOCATION.PO_NAME, -- "Facility Name"
APINVOICE.PO_NUMBER, -- "Purchase Order Number"
APINVOICE.INVOICE, -- "Invoice Number"
APINVOICE.VENDOR, -- "Vendor Number"
coalesce (APVENLOC.VENDOR_VNAME, APVENMAST.VENDOR_VNAME) vendor_name, -- "Vendor Name"
APDISTRIB.ORIG_TRAN_AMT, -- "Freight Charge"
PURCHORDER.LOCATION, -- "Facility Code"
APDISTRIB.DIS_ACCT_UNIT, -- "Cost Center"
GLNAMES.DESCRIPTION, -- "Department Name"
ICLOCATION.PO_ADDR1, -- "Facility Address 1"
ICLOCATION.PO_ADDR2, -- "Facility Address 2"
ICLOCATION.PO_CITY_ADDR5, -- "Facility City"
ICLOCATION.PO_STATE_PROV, -- "Facility State"
ICLOCATION.PO_POSTAL_CD, -- "Facility Zip"
APINVOICE.BASE_TOT_PMT, -- "Total Invoice Amount"
APINVOICE.CREATE_DATE, -- "Invoice Date"
APVENLOC.VEND_ACCT -- "Vendor Account Number"
from
PRODLAW.GLNAMES GLNAMES, PRODLAW.APDISTRIB APDISTRIB, PRODLAW.APVENMAST APVENMAST,
PRODLAW.APINVOICE APINVOICE, PRODLAW.APVENLOC APVENLOC, PRODLAW.ICLOCATION ICLOCATION,
PRODLAW.PURCHORDER PURCHORDER
--inner join PRODLAW.APDISTRIB APDISTRIB
Where GLNAMES.COMPANY=APDISTRIB.COMPANY
AND GLNAMES.ACCT_UNIT=APDISTRIB.DIS_ACCT_UNIT
--inner join PRODLAW.APVENMAST APVENMAST
and APINVOICE.VENDOR = APVENMAST.VENDOR
--inner join PRODLAW.APINVOICE APINVOICE
and APDISTRIB.COMPANY=APINVOICE.COMPANY
AND APDISTRIB.VENDOR=APINVOICE.VENDOR
AND APDISTRIB.INVOICE=APINVOICE.INVOICE
AND APDISTRIB.SUFFIX=APINVOICE.SUFFIX
--left outer join PRODLAW.APVENLOC APVENLOC
and APINVOICE.VENDOR=APVENLOC.VENDOR
AND APINVOICE.PURCH_FR_LOC=APVENLOC.LOCATION_CODE
--inner join PRODLAW.ICLOCATION ICLOCATION
and PURCHORDER.COMPANY =ICLOCATION.COMPANY
AND PURCHORDER.LOCATION =ICLOCATION.LOCATION
--inner join PRODLAW.PURCHORDER PURCHORDER
and APINVOICE.COMPANY=PURCHORDER.COMPANY
AND APINVOICE.PO_CODE=PURCHORDER.PO_CODE
AND APINVOICE.PO_NUMBER=PURCHORDER.PO_NUMBER
--where
and APINVOICE.CREATE_DATE>=SYSDATE -7
AND APINVOICE.PO_NUMBER>' '
--AND APDISTRIB.PO_AOC_CODE in ('FT','ON','2D','3D')