Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Table or View does not exist

Status
Not open for further replies.
Davest11

Davest11

Programmer
Oct 26, 2004
82
US
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:

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!!
 
Sort by date Sort by votes
The most obvious one is to check that the user in question has select privilege on all the tables involved in the query.

Regards

T
 
Upvote 0 Downvote
Thanks T, but yes...I do know that it's actually a problem with the code and not something related to the infrastructure, permissions, or anything else along those lines. The following query works fine:

SQL: 
SELECT
(select sum(y.ded_cur)   
from    ps_pay_check x,
           ps_pay_deduction y,
           ps_pay_calendar z
where 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
and     x.company     = z.company
and     x.paygroup     = z.paygroup
and     x.pay_end_dt = z.pay_end_dt
and     x.emplid         = gd.EMPLID
and     y.dedcd         = gd.DEDCD 
and     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_GENL_DEDUCTION gd, PS_DM_EMPL_CURR_VW ec,  
PS_DM_DED_PROV dp, PS_ERM_CHARITY_EE ermc, PS_PROVIDR_TBL pv

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))) 
)) 
AND
(dp.dedcd = pv.provider) AND
(pv.provider = gd.dedcd) AND
(gd.emplid = ermc.emplid) AND
(gd.dedcd = ermc.dedcd) AND
(ermc.emplid = ec.emplid)
ORDER BY
gd.DEDCD

As you'll notice, the only difference is that this version uses inner joins. The client wants the report to include entities that have no records in some of the tables, hence my effort to convert it to outer joins where necessary.
 
Upvote 0 Downvote
Dave,

Can you post create table statements and insert statements so I can try to reproduce your issue?

If not, then I suggest a divide and conquer approach. The second query has no join conditions in the from clause, so I'm guessing you're getting lots of unexpected records returned.
I suggest that you restrict the select clause to only use values from one table and comment the others out.
Run the query and make sure it returns what you expect. Then join to a second table in the from clause, and uncomment the values from that table.
Check to see that it returns what you expect.
Rinse and repeat until you find the offending table.

Is that making sense?

Regards

T
 
Upvote 0 Downvote
No...the second query is returning exactly what I'd expect - the joins are handled implicitly in the WHERE clause. The problem is that the second query is not returning rows where there are non-matching rows in some tables, hence the change to outer joins.

I found the cause of the initial error, but I'm still having issues. The bad table name error was caused by this table reference from the first sub-query:

SQL: 
	LEFT JOIN gd ON (x.emplid = gd.EMPLID AND y.dedcd = gd.DEDCD)

I didn't realize that alias references like this wouldn't work properly in explicit join syntax. I put in the table name, and that rid me of the original error. I was also getting error in the numbers that that sub-query was meant to pull, so I went back to the original code for that bit, leaving me with this:

SQL: 
SELECT
(select sum(y.ded_cur)   
from    ps_pay_check x,
           ps_pay_deduction y,
           ps_pay_calendar z
where 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
and     x.company     = z.company
and     x.paygroup     = z.paygroup
and     x.pay_end_dt = z.pay_end_dt
and     x.emplid         = gd.EMPLID
and     y.dedcd         = gd.DEDCD 
and     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 AND gd.dedcd = ermc.dedcd)
	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

Now, I'm getting the proper numbers, but the joins are being executed as inner joins. Here are my theories as to what could be causing this:

1) The links to the gd.emplid and gd.dedcd fields in the top sub-query
2) Two of the four sub-queries at the bottom link to tables that are part of the outer joins.

Could either of these cause the joins to revert to inner joins? Any thoughts on how I'd revise the code to avoid that?
 
Upvote 0 Downvote
Your WHERE clause clearly defeats the OUTER JOIN purpose for gd and ermc.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Well, yes...thanks. That's what I thought. The problem I'm having is that I've been unable to find a way to rework the code to avoid that issue. When I tried to move the subqueries up into the joins, I get an error telling me that I can't use a subquery in an outer join. I've seen posts elsewhere that mention using a UNION, but none give examples that work with a complex query like mine.

Do you have any thoughts on how I would fix the problem?
 
Upvote 0 Downvote
In your WHERE clause, why testing gd.DEDCD insread of dp.dedcd or pv.provider ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
You're right, the first bit (looking for the dedcd's that start with 'Cx' could be replaced with another table. The rest, though - the ones that are looking for the max dates - cannot. Those are my real concern - I'm looking for a way to include those in the joins somehow, or perhaps use a UNIION.
 
Upvote 0 Downvote
A starting point:
...
LEFT JOIN (SELECT DEDCD,EMPLID,MAX(EFFDT) AS MaxEFFDT
FROM PS_GENL_DEDUCTION WHERE EFFDT<=SYSDATE GROUP BY DEDCD,EMPLID
) mgd ON gd.DEDCD=mgd.DEDCD AND gd.EMPLID=mgd.EMPLID AND gd.EFFDT=mgd.MaxEFFDT
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Thanks...I see where you're going, and I'll give it a try.

Also...do you think the top sub-query would have the same effect of knocking the whole thing back to inner joins?
 
Upvote 0 Downvote
I see where you're going, and I'll give it a try
So, where did YOU go ?
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

harfri
  • Locked
  • Question
Insert into a new table
Replies
0
Views
103
harfri
harfri
mikrom
  • Locked
  • Question
from a table with symmetric relation select every pair only once
Replies
0
Views
102
mikrom
mikrom
szeiss
  • Locked
  • Question
Need to select the one value if it exists otherwise another value
Replies
4
Views
159
szeiss
szeiss
Junior6202
  • Locked
  • Question
String or binary data would be truncated.
Replies
1
Views
115
fredericofonseca
fredericofonseca
NickyJay
  • Locked
  • Question
SQL 2008 exclude parent records in results where multiple child records exist in 2nd table
Replies
4
Views
146
PHV
PHV

Part and Inventory Search

Sponsor

Back
Top