I have been asked to come up with a report around Purchase Orders that shows varied information from Macola. I have created reports in the past using MS Visual Studio. Typically, I get the SQL I want from one or two existing reports, I put the SQL together, run it in our SQL Server environment until I am happy with what I am seeing, then I run it through Report Wizard on the MS Visual Studio and create my report. All good..

My issue is for this task is finding the right SQL, I have a couple reports, but they are both using the same tables, and neither are offering me the GL Account code that my Finance TM is asking for. Is it possible to get the combined information in one report and save the Finance Team from having to run the PO Number and drill down, to get varied information?

Here is the SQL I have plagiarized thus far:
SELECT
SUBSTRING(ph.ord_no,1,6) AS Ord_No,
SUBSTRING(ph.ord_no,7,2) AS Release,
ph.vend_no,
pl.item_no,
pl.item_desc_1,
pl.item_desc_2,
pl.uom,
pl.qty_ordered,
pl.request_dt,
pl.byr_plnr,
ap.vend_name,
pl.line_no,
pl.qty_remaining,
ph.ord_status AS HdrStatus
FROM (poordhdr_sql ph
LEFT OUTER JOIN poordlin_sql pl ON (ph.ord_no = pl.ord_no)
AND (ph.vend_no = pl.vend_no))
LEFT OUTER JOIN apvenfil_sql ap ON ph.vend_no = ap.vend_no

WHERE NOT (ph.ord_status = 'C' OR ph.ord_status = 'H')
and ord_dt >= '10/20/2020' and ord_dt <= '10/22/2020'
---and ph.ord_no = '25449'

The selected fields are flexible obviously, to start I had done Select * from the tables and did not see the GL Account Code, and when I check in Macola to see where that field exists I only see the prglfil_sql table but when I query that table I get nothing.
SQL I use to get tables: (also plagiarized)
select sys.columns.name as ColumnName, tables.name as TableName
from sys.columns join sys.tables on sys.columns.object_id = tables.object_id
where sys.columns.name like 'gl%'
order by columnName asc

This gives me a bunch of tables but none seem to be regular tables that might have the data I want?

J Green
DeltaTRAK
www.deltatrak.com