Hello,
I am trying to understand a SQL statement to develop a report. Please find the below code that I have a question about.
set underline
*break on fund_acct skip 1 on report
compute sum of excess committed obligated unoblig funds_avail on fund_acct
compute sum of excess committed obligated unoblig funds_avail on report
col pl for 9999
col funds_avail for 999,999,999.99
col committed for 999,999,999.99
col wi_code for a6
col obligated for 999,999,999.99
col unoblig for 999,999,999.99
col prac_no for a8
col type for a12
select substr(h.wi_name,1+instr(h.wi_name,'['), 6) p2, substr(h.wi_name,1,4) wi_name, substr(a.prac_no,7,16) prac_no, moa_code, resource_code, nvl(certified_us_amt,0)-nvl(deob_amt,0)+nvl(obli_excess_commit_amt,0) + nvl(pr_interest_amt,0) committed, nvl(certified_us_amt,0)-nvl(deob_amt,0)+nvl(obli_excess_commit_amt,0)-nvl(unoblig_us_bal_amt,0)+ nvl(pr_interest_amt,0) obligated, unoblig_us_bal_amt unoblig, max(receive_org_code) receive, substr(org_name,1,13) org_name, a.fund_acct_no fund_acct, decode(f.fund_acct_no,'J177777','SA','J177778','SA','J177779','SA','J177768','OTHER CWE', 'J176716','OTHER CWE','J176717','OTHER CWE','J177780','CONST','DESIGN') type from pr_line_item a, work_item h, pr_amend d, wm_organization e, fund_acct_reg_view f
where ((substr(h.wi_name,1 + instr(h.wi_name,'['), 6) = '&p2'))
and a.wi_code = h.wi_code and a.prac_no = d.prac_no
and a.fund_acct_no not like 'OA%' and d.receive_org_code = e.org_code(+)
and a.fund_acct_no = f.fund_acct_no and (ABS(nvl(certified_us_amt,0)-nvl(deob_amt,0)+nvl(obli_excess_commit_amt,0)) > 0 or nvl(obli_excess_commit_amt,0) > 0)
and f.fund_acct_no
not like 'J169479'
group by substr(h.wi_name,1+instr(h.wi_name,'['), 6), substr(a.prac_no,7,16), nvl(certified_us_amt,0)-nvl(deob_amt,0)+nvl(obli_excess_commit_amt,0) + nvl(pr_interest_amt,0), nvl(certified_us_amt,0)-nvl(deob_amt,0)+nvl(obli_excess_commit_amt,0)-nvl(unoblig_us_bal_amt,0) + nvl(pr_interest_amt,0), unoblig_us_bal_amt, resource_code, a.fund_acct_no, decode(f.fund_acct_no,'J177777','SA','J177778','SA','J177779','SA','J177768','OTHER CWE', 'J176716','OTHER CWE','J176717','OTHER CWE','J177780','CONST','DESIGN'), substr(org_name,1,13), substr(h.wi_name,1,4), moa_code order by type, fund_acct, moa_code, resource_code, receive /
This is the question I have for now… the part where it is pulling from the tables **where…a.wi_code=h.wi_code, etc. I believe it is pulling records from different tables where those fields are equal. I am trying to recreate this query in Access to learn more. Does anyone foresee any issues recreating this is Access? I recreated it so far using this code, which returns zero results. If I remove the last DISTINCT field and ‘AND’ criteria, the table returns results.
SELECT DISTINCT Mid([S0CIA123_WORK_ITEM].[WI_NAME],InStr(1, [S0CIA123_WORK_ITEM].[WI_NAME], '[')+1,6) , Mid( [S0CIA123_WORK_ITEM].[WI_NAME], 1, 4), Mid([S0CIA123_PR_LINE_ITEM].[PRAC_NO], 7, 16)
FROM S0CIA123_WORK_ITEM, S0CIA123_PR_LINE_ITEM, S0CIA123_PR_AMEND
WHERE Mid([S0CIA123_WORK_ITEM].[WI_NAME],InStr(1, [S0CIA123_WORK_ITEM].[WI_NAME], '[')+1,6)="153719"
AND [S0CIA123_PR_LINE_ITEM].[WI_CODE]=[S0CIA123_WORK_ITEM].[WI_CODE]
AND [S0CIA123_PR_LINE_ITEM].[PRAC_NO]=[S0CIA123_PR_AMEND].[PRAC_NO] ;