Understanding SQL Statement

sql

#1

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] ;

#2

Could you format your code a bit more readable? Like this:

 ```
 Your code
 ```

#3

Yes, what you think is correct for most of the where-statement.
But I have absolutely no experience with Access, so I can't say anything about recreating it there.