Hello! I'm newly back to writing SQL code, and I don't have a lot of experience writing queries using explicit join syntax. Here's my code:
The error I'm getting is ORA-00942: table or view does not exist. I'm entering this as a sql command in Crystal Reports XI against an Oracle 9i DB. I've also tried the depricated Oracle syntax, and while that would run, it wasn't executing the outer joins properly. I have my theories about what the issue might be related to, but I'd rather let some of you take a look first.
Thanks!!
SQL:
SELECT
(
select sum(y.ded_cur)
from ps_pay_check x
INNER JOIN ps_pay_deduction y ON (x.company = y.company AND x.paygroup = y.paygroup AND x.pay_end_dt = y.pay_end_dt
AND x.off_cycle = y.off_cycle AND x.page_nbr = y.page_nbr AND x.line_nbr = y.line_nbr AND x.sepchk = y.sepchk)
INNER JOIN ps_pay_calendar z ON (x.company = z.company AND x.paygroup = z.paygroup AND x.pay_end_dt = z.pay_end_dt)
LEFT JOIN gd ON (x.emplid = gd.EMPLID AND y.dedcd = gd.DEDCD)
WHERE z.calendar_year = to_char(sysdate,'YYYY') AND
x.pay_end_dt <= SYSDATE AND
x.paycheck_status = 'F'
) AS YTD_DED_AMT,
gd.DED_ADDL_AMT,gd.DEDCD, gd.DEDUCTION_END_DT, gd.EFFDT, gd.EMPLID, gd.GOAL_AMT, gd.GOAL_BAL,
gd.GOAL_LIMIT_NBR, gd.GOAL_START_DT, ec.COMPANY, ec.EMPL_STATUS, ec.NAME, ec.EMAIL_ADDRESS,
ec.COMPANY_NAME, ec.MAIL_DROP, ec.EMPL_CLASS, ec.EMPL_CLASS_DESC, ec.FULL_PART_TIME,
dp.DM_PROV_DESCR, ermc.EFF_STATUS as ERM_DED_STATUS, dp.FEDERAL_EIN, pv.STREET1, pv.STREET2, pv.CITY, pv.STATE, pv.ZIP,
ermc.DTTM_STAMP as ENTRY_DATE,
ermc.DM_ONE_TIME as ERM_ONE_TIME_DED
FROM
PS_DM_DED_PROV dp INNER JOIN PS_PROVIDR_TBL pv ON dp.dedcd = pv.provider
LEFT OUTER JOIN PS_GENL_DEDUCTION gd ON pv.provider = gd.dedcd
LEFT OUTER JOIN PS_ERM_CHARITY_EE ermc ON gd.emplid = ermc.emplid
LEFT OUTER JOIN PS_DM_EMPL_CURR_VW ec ON ermc.emplid = ec.emplid
WHERE
((
((gd.DEDCD LIKE 'C0%') OR
(gd.DEDCD LIKE 'C1%') OR
(gd.DEDCD LIKE 'C2%') OR
(gd.DEDCD LIKE 'C3%') OR
(gd.DEDCD LIKE 'C4%') OR
(gd.DEDCD LIKE 'C5%') OR
(gd.DEDCD LIKE 'C6%') OR
(gd.DEDCD LIKE 'C7%') OR
(gd.DEDCD LIKE 'C8%') OR
(gd.DEDCD LIKE 'C9%')) AND
((gd.EFFDT = (
SELECT MAX(INNERALIAS.EFFDT)
FROM PS_GENL_DEDUCTION INNERALIAS
WHERE INNERALIAS.DEDCD = gd.DEDCD
AND INNERALIAS.EMPLID = gd.EMPLID
AND INNERALIAS.EFFDT <= SYSDATE))) AND
((dp.EFFDT = (
SELECT MAX(INNERALIAS.EFFDT)
FROM PS_DM_DED_PROV INNERALIAS
WHERE INNERALIAS.DEDCD = dp.DEDCD
AND INNERALIAS.EFFDT <= SYSDATE))) AND
((ermc.EFFDT = (
SELECT MAX(INNERALIAS.EFFDT)
FROM PS_ERM_CHARITY_EE INNERALIAS
WHERE INNERALIAS.DEDCD = ermc.DEDCD
AND INNERALIAS.EMPLID = ermc.EMPLID
AND INNERALIAS.EFFDT <= SYSDATE))) AND
((pv.EFFDT = (
SELECT MAX(INNERALIAS.EFFDT)
FROM PS_PROVIDR_TBL INNERALIAS
WHERE INNERALIAS.PROVIDER = pv.PROVIDER
AND INNERALIAS.EFFDT <= SYSDATE)))
))
ORDER BY gd.DEDCD
The error I'm getting is ORA-00942: table or view does not exist. I'm entering this as a sql command in Crystal Reports XI against an Oracle 9i DB. I've also tried the depricated Oracle syntax, and while that would run, it wasn't executing the outer joins properly. I have my theories about what the issue might be related to, but I'd rather let some of you take a look first.
Thanks!!