How to avoid Temp Tables 1

[pandpp]

Hi there.

I come predominantly from a SQL Server background. What I am currently trying to do I would easily accomplish with a quick Temp Table & a Cursor.

I am on an Oracle 8i installation. As such, I understand that Temp Tables are taboo, a point I am happy to accept. Unfortunately, that causes me issues, in that I don't currently know how to resolve my issue without them.

So, here's the problem:

I return the latest Work_Orders from a number of tables. Each of the WO's is attached to a piece of Equipment (Equip_No).

On each returned record, I need to also include the last (MAX(Closed_Dt)) WO for this Equip_No.

I tried the above with an InLine View, but hit a snag when I tried to access one InLine View from within another.

My logic tells me it can't work, due to the fact that I need to return the MAX for each & every Equip_No. Any & all assistance at this point is exceedingly well received.

Regards,

Peter.

SELECT
    WO_Orig.*,
    WO_Sched.LastSchedDate

FROM
    (
        SELECT
            MSF600.equip_location,
            MSF600.item_name_1,
            MSF600.item_name_2,
            MSF620.closed_dt,
            MSF620.comp_code,
            MSF620.comp_mod_code,
            MSF620.dstrct_code,
            MSF620.equip_no,
            MSF620.plan_str_date,
            MSF620.raised_date,
            MSF620.wo_desc,
            MSF620.wo_status_m,
            MSF620.work_order

        FROM
            MSF600 MSF600,
            MSF620 MSF620

        WHERE
            msf620.equip_no = msf600.equip_no
            AND msf600.equip_classifx5 = 'Y'
    ) WO_Orig,
    (
        SELECT
            MAX(MSF620.Raised_Date) LastSchedDate

        FROM
            MSF620,
            WO_Orig

        WHERE
            MSF620.Equip_No = WO_Orig.Equip_No
            AND MSF620.Work_Order < WO_Orig.Work_Order
            AND MSF620.Maint_Sch_Task <> '    '
    ) WO_Sched

WHERE
    MSF620.Equip_No = WO_Orig.Equip_No AND
    MSF620.Work_Order < WO_Orig.Work_Order AND
    MSF620.Maint_Sch_Task <> '    '

 

[SantaMufasa]

Pandpp,

Was "hit a snag" the actual error message, or could you be a bit more specific about the problem? You shouldn't have any problem with a view, in a view, in a view, in a view.

Mufasa
(aka Dave of Sandy, Utah, USA @ 02:34 (29Jun04) UTC (aka "GMT" and "Zulu"), 19:34 (28Jun04) Mountain Time)

 

[pandpp]

Mufasa,

Oracle error - ORA-00942: table or view does not exist.

This is relating to WO_Orig in FROM clause in WO_Sched InLine view.

That's what I get from TOAD. If I try running the same through SQL*Plus I get the following:

SP2-0042: unknown command "FROM" - rest of line ignored.
SP2-0042: unknown command "FROM" - rest of line ignored.
SP2-0734: unknown command beginning "ellipse.MS..." - rest of line ignored.
SP2-0734: unknown command beginning "ellipse.MS..." - rest of line ignored.
SP2-0042: unknown command "WHERE" - rest of line ignored.
SP2-0044: For a list of known commands enter HELP and to leave enter EXIT.
SP2-0734: unknown command beginning "msf620.equ..." - rest of line ignored.
SP2-0734: unknown command beginning "AND msf600..." - rest of line ignored.
SP2-0042: unknown command ") WO_Orig," - rest of line ignored.
SP2-0042: unknown command "FROM" - rest of line ignored.
SP2-0042: unknown command "MSF620," - rest of line ignored.
SP2-0042: unknown command "WO_Orig" - rest of line ignored.
SP2-0042: unknown command "WHERE" - rest of line ignored.
SP2-0044: For a list of known commands enter HELP and to leave enter EXIT.
SP2-0734: unknown command beginning "MSF620.Equ..." - rest of line ignored.
SP2-0734: unknown command beginning "AND MSF620..." - rest of line ignored.
SP2-0734: unknown command beginning "AND MSF620..." - rest of line ignored.
SP2-0042: unknown command ") WO_Sched" - rest of line ignored.
SP2-0044: For a list of known commands enter HELP and to leave enter EXIT.
SP2-0042: unknown command "WHERE" - rest of line ignored.
SP2-0734: unknown command beginning "MSF620.Equ..." - rest of line ignored.
SP2-0734: unknown command beginning "MSF620.Wor..." - rest of line ignored.
SP2-0734: unknown command beginning "MSF620.Mai..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP and to leave enter EXIT.

Regards,

Peter.

 

[SantaMufasa]

Pandpp,

Try the code again, but don't leave any blank lines amongst the SQL. SQL*Plus interprets the blank lines as an end of current code. That is why you are receiving the "SP2-0042: unknown command "FROM" - rest of line ignored."

Let's get your code working first in SQL*Plus, then we'll migrate it over to TOAD.

Mufasa
(aka Dave of Sandy, Utah, USA @ 03:09 (29Jun04) UTC (aka "GMT" and "Zulu"), 20:09 (28Jun04) Mountain Time)

 

[pandpp]

Mufasa,

I know of the blank line issue in SQL*Plus. Unfortunately, there is no change in the errors. even when I manually remove the blanks.

I don't have access to SQL*Plus directly, only through TOAD, which I am required to access through Citrix.

Regards,

Peter.

 

[SantaMufasa]

Peter,

Here are some issues:
1) If you receive the error, "SP2-0042: unknown command..." it means that the word it encountered (appearing in the error message) is the beginning of what SQL*Plus considers to be a new command, but it does not recogise the word as a valid first word for a SQL or SQL*Plus command. Therefore, even though you say you got rid of blank lines, if you are still receiving the same errors, then SQL*Plus still thinks that "FROM" is the first word of a new command line, I promise.

2) Once you receive the above error, then the SQL*Plus interpreter trys to make every subsequent line the first line of a new SQL*Plus command line, and of course, until it encounters the word SELECT, or INSERT, or UPDATE, or DELETE, or COMMIT, or ROLLBACK, or ALTER, or DROP, or CREATE, or GRANT, or REVOKE, or TRUNCATE, et cetera, or a SQL*Plus command like SAVE, or COL, or BREAK, et cetera, it will continue to give you "SP2-0734: unknown command beginning..." errors

3) In your error listing is an error that flags the string "ellipse.MS...". But unless I'm losing my vision or my mind, I cannot see that string in your code. Are you certain that you are posting your actual code?

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA @ 05:50 (29Jun04) UTC (aka "GMT" and "Zulu"), 22:50 (28Jun04) Mountain Time)

 

[pandpp]

Dave,

Okay, I have managed to cheat TOAD & pump the code straight into SQL*Plus (which I should have previously), at which point I am presented with the following error:

            WO_Orig
            *
ERROR at line 32:
ORA-00942: table or view does not exist

As to your question regarding "ellipse.MS...", the MSF tables are synonyms for the elipse schema tables, ie. ellipse.MSF...

TOAD & SQL*Plus are now both returning the same error.

Any more thoughts? Appreciate your patience thus far.

Peter.

 

[SantaMufasa]

Peter,

Here is the most-recent problem in your code: You may have an "in-line view in an in-line view", but the definition of the inner in-line view must be within the referencing in-line view. You cannot refer to an in-line view that is "outside" the scope of the in-line view. In your case, notice that the in-line-view definition of "WO_Orig" is a sibling, and thus outside the definition of (and thus not referencable by) "WO_Sched".

If you wish to refer to "WO_Orig" in "WO_Sched", then you will need to either "re-engineer" the in-line definition of "WO_Orig's" inside of "WO_Sched" (which is totally feasible), or define "WO_Sched" as a static VIEW (with a CREATE VIEW... command).

Mufasa
(aka Dave of Sandy, Utah, USA @ 07:17 (29Jun04) UTC (aka "GMT" and "Zulu"), 00:17 (29Jun04) Mountain Time)

 

[aryeh1010]

Your select is getting error messages because of the final where clause.

the MSF620 alias is not recognized because it is inside an in-line view.

You are going to have to rework this query.

WHERE
MSF620.Equip_No = WO_Orig.Equip_No AND
MSF620.Work_Order < WO_Orig.Work_Order AND
MSF620.Maint_Sch_Task <> ' '


Aryeh Keefe

 

[carp]

I notice that your second view has the same WHERE clause as your outer query. Would it help to modify your second view to read

        SELECT
            equip_no, 
            work_order, 
            MAX(Raised_Date) LastSchedDate
        FROM
            msf620
        WHERE
            Maint_Sch_Task <> '    '
        GROUP BY equip_no, work_order

and then just do your joins/comparisons in the outer query?
I'm afraid I don't have time to set up a clone of your tables to test this, but it seems to me that

SELECT
    WO_Orig.*,
    WO_Sched.LastSchedDate
FROM
    (
        SELECT
            MSF600.equip_location,
            MSF600.item_name_1,
            MSF600.item_name_2,
            MSF620.closed_dt,
            MSF620.comp_code,
            MSF620.comp_mod_code,
            MSF620.dstrct_code,
            MSF620.equip_no,
            MSF620.plan_str_date,
            MSF620.raised_date,
            MSF620.wo_desc,
            MSF620.wo_status_m,
            MSF620.work_order
        FROM
            MSF600 MSF600,
            MSF620 MSF620
        WHERE
            msf620.equip_no = msf600.equip_no
            AND msf600.equip_classifx5 = 'Y'
    ) WO_Orig,
    (
        SELECT
            equip_no, 
            work_order, 
            MAX(Raised_Date) LastSchedDate
        FROM
            msf620
        GROUP BY equip_no, work_order
) wo_sched
WHERE
    wo_sched.Equip_No = WO_Orig.Equip_No AND
    wo_sched.Work_Order < WO_Orig.Work_Order;

Like I said, I can't test this so it might not be quite what you want - but it might get you closer.

 

[carp]

Nuts!
I HATE it when you realize you left something out just as the post gets submitted!
Forgot the WHERE clause in the second view!

SELECT
    WO_Orig.*,
    WO_Sched.LastSchedDate
FROM
    (
        SELECT
            MSF600.equip_location,
            MSF600.item_name_1,
            MSF600.item_name_2,
            MSF620.closed_dt,
            MSF620.comp_code,
            MSF620.comp_mod_code,
            MSF620.dstrct_code,
            MSF620.equip_no,
            MSF620.plan_str_date,
            MSF620.raised_date,
            MSF620.wo_desc,
            MSF620.wo_status_m,
            MSF620.work_order
        FROM
            MSF600 MSF600,
            MSF620 MSF620
        WHERE
            msf620.equip_no = msf600.equip_no
            AND msf600.equip_classifx5 = 'Y'
    ) WO_Orig,
    (
        SELECT
            equip_no, 
            work_order, 
            MAX(Raised_Date) LastSchedDate
        FROM
            msf620
        WHERE
            Maint_Sch_Task <> '    '
        GROUP BY equip_no, work_order
) wo_sched
WHERE
    wo_sched.Equip_No = WO_Orig.Equip_No AND
    wo_sched.Work_Order < WO_Orig.Work_Order;

 

[SantaMufasa]

Yes, Peter, Carp's rework which places references to WO_Orig outside of the second view is yet another (better) way to eliminate the "sibling"-reference problem I mentioned in my previous post.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:50 (29Jun04) UTC (aka "GMT" and "Zulu"), 09:50 (29Jun04) Mountain Time)

 

[pandpp]

Carp,

Perfect...

That gave me the tips required to get the whole thing working. Thanks for the assistance. Quite obvious when you pointed it out.

Mufasa, thanks also for your patience.

Regards,

Peter.