INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Help formatting a statement for T.O.A.D.

Help formatting a statement for T.O.A.D.

(OP)
I'm new to T.O.A.D. and any PL/SQL.  I have a simple script that works only buy running it in pieces (all the drop tables, then all the create tables, then a select * from temp_stock).  I want the script formatted such that you can run it all at once.  Here is the script:

Drop Table temp_Pat;
Drop Table temp_SW;
Drop Table temp_AR;
Drop Table temp_RID;
Drop Table temp_CAT;
Drop Table temp_Stk;
Drop Table temp_Stock;

CREATE TABLE temp_Pat
AS
   SELECT   DISTINCT Inventory_Item_ID, Category_Concat_Segs AS Pattern
     FROM   MTL_ITEM_CATEGORIES_V
    WHERE   Category_Set_ID = 1100000044;
CREATE TABLE temp_SW
AS
   SELECT   DISTINCT Inventory_Item_ID, Category_Concat_Segs AS SW
     FROM   MTL_ITEM_CATEGORIES_V
    WHERE   Category_Set_ID = 1100000045;
CREATE TABLE temp_AR
AS
   SELECT   DISTINCT Inventory_Item_ID, Category_Concat_Segs AS AR
     FROM   MTL_ITEM_CATEGORIES_V
    WHERE   Category_Set_ID = 1100000046;
CREATE TABLE temp_RID
AS
   SELECT   DISTINCT Inventory_Item_ID, Category_Concat_Segs AS RID
     FROM   MTL_ITEM_CATEGORIES_V
    WHERE   Category_Set_ID = 1100000047;
CREATE TABLE temp_CAT
AS
   SELECT   DISTINCT Inventory_Item_ID, Category_Concat_Segs AS Cat
     FROM   MTL_ITEM_CATEGORIES_V
    WHERE   Category_Set_ID = 1;
CREATE TABLE temp_Stk
AS
     SELECT   Inventory_Item_ID, SUM (Transaction_Quantity) AS Stock
       FROM   Mtl_OnHand_Quantities_Detail
   GROUP BY   Inventory_Item_ID;
CREATE TABLE temp_Stock
As
SELECT   DISTINCT Item.Segment1 AS FTC_Part_Number,
                  SUBSTR (Item.Description, 1, 10) AS Size_Info,
                  Pattern,
                  SW,
                  AR,
                  RID,
                  Cat,
                  Stock,
                  '                         ' AS Blank
  FROM   MTL_SYSTEM_ITEMS_B Item,
        temp_Pat,
        temp_SW,
        temp_AR,
        temp_RID,
        temp_Cat,
        temp_Stk
 WHERE       Item.Inventory_Item_ID = temp_Pat.Inventory_Item_ID
         AND Item.Inventory_Item_ID = temp_SW.Inventory_Item_ID
         AND Item.Inventory_Item_ID = temp_AR.Inventory_Item_ID
         AND Item.Inventory_Item_ID = temp_RID.Inventory_Item_ID
         AND Item.Inventory_Item_ID = temp_Cat.Inventory_Item_ID
         AND Item.Inventory_Item_ID = temp_Stk.Inventory_Item_ID;

Select * from temp_Stock;

RE: Help formatting a statement for T.O.A.D.

What error are you getting?  Also, this may be more of an Oracle issue than a Toad issue.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close