ORA-00933: SQL command not properly ended 2

[jadams0173]

To say I'm a novice in Oracle 8i is an understatement.

I'm trying to run this code as one statement and I get the error ORA-00933: SQL command not properly ended.

If I run the insert first and then the selects it works. Why can't I run it all together. Also Table1 is a global temp table.

INSERT INTO TABLE1 
	   (ASSY_ID)
	   ( SELECT DISTINCT ASSEMBLY_ID FROM SNAPMAN.ASSY_PT)


SELECT SO.ITEM, 'HLA' TYPE 

FROM PROD.V_SHOPORDER SO, SNAPMAN.ASSY_PT AP 

WHERE SO.ITEM = AP.ASSEMBLY_ID AND SO.RELEASED_QTY > 0 

GROUP BY SO.ITEM, 'HLA' 

UNION ALL

SELECT SO.ITEM, 'OTHER' TYPE 

FROM PROD.V_SHOPORDER SO, SNAPMAN.ASSY_PT AP , TABLE1 TT

WHERE SO.ITEM = AP.COMPONENT_ID AND SO.RELEASED_QTY > 0 

AND SO.ITEM  = TT.ASSY_ID (+)--NOT IN(SELECT DISTINCT ASSEMBLY_ID FROM SNAPMAN.ASSY_PT) 

AND TT.ASSY_ID IS NULL

GROUP BY SO.ITEM, 'OTHER' ,TT.ASSY_ID

 

[SantaMufasa]

J,

Your running your SQL code as a single unit (script) is perfectly acceptable. You must, however, still tell Oracle where one statement ends and the next statement begins. If you are running this script from SQL*Plus (the default assumption in this and other Oracle forums), then you can terminate your INSERT and SELECT statements with either a semicolon (";") at the end of your last line or on a line of its own, immediately following the end of each statement; or you may use a forward slash ("/") as the first character on a line of its own:

INSERT INTO TABLE1 
       (ASSY_ID)
       ( SELECT DISTINCT ASSEMBLY_ID FROM SNAPMAN.ASSY_PT);

SELECT SO.ITEM, 'HLA' TYPE 
FROM PROD.V_SHOPORDER SO, SNAPMAN.ASSY_PT AP 
WHERE SO.ITEM = AP.ASSEMBLY_ID AND SO.RELEASED_QTY > 0 
GROUP BY SO.ITEM, 'HLA' 
UNION ALL
SELECT SO.ITEM, 'OTHER' TYPE 
FROM PROD.V_SHOPORDER SO, SNAPMAN.ASSY_PT AP , TABLE1 TT
WHERE SO.ITEM = AP.COMPONENT_ID AND SO.RELEASED_QTY > 0 
AND SO.ITEM  = TT.ASSY_ID (+)--NOT IN(SELECT DISTINCT ASSEMBLY_ID FROM SNAPMAN.ASSY_PT) 
AND TT.ASSY_ID IS NULL
GROUP BY SO.ITEM, 'OTHER' ,TT.ASSY_ID;

...or...

INSERT INTO TABLE1 
       (ASSY_ID)
       ( SELECT DISTINCT ASSEMBLY_ID FROM SNAPMAN.ASSY_PT)
/
SELECT SO.ITEM, 'HLA' TYPE 
FROM PROD.V_SHOPORDER SO, SNAPMAN.ASSY_PT AP 
WHERE SO.ITEM = AP.ASSEMBLY_ID AND SO.RELEASED_QTY > 0 
GROUP BY SO.ITEM, 'HLA' 
UNION ALL
SELECT SO.ITEM, 'OTHER' TYPE 
FROM PROD.V_SHOPORDER SO, SNAPMAN.ASSY_PT AP , TABLE1 TT
WHERE SO.ITEM = AP.COMPONENT_ID AND SO.RELEASED_QTY > 0 
AND SO.ITEM  = TT.ASSY_ID (+)--NOT IN(SELECT DISTINCT ASSEMBLY_ID FROM SNAPMAN.ASSY_PT) 
AND TT.ASSY_ID IS NULL
GROUP BY SO.ITEM, 'OTHER' ,TT.ASSY_ID
/

...or combinations of the above.

Let us know how it works out for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[jadams0173]

Thanks for the reply Dave!
I am using TOAD ver 6.3

I added the semicolon as you show above and now I have a NEW error.

The following error has occurred:

ORA-00911: invalid character

Details:
ORA-00911: invalid character

INSERT INTO TABLE1 
	  (ASSY_ID)
	  ( SELECT DISTINCT ASSEMBLY_ID FROM SNAPMAN.ASSY_PT);


SELECT SO.ITEM, 'HLA' TYPE 

FROM PROD.V_SHOPORDER SO, SNAPMAN.ASSY_PT AP 

WHERE SO.ITEM = AP.ASSEMBLY_ID AND SO.RELEASED_QTY > 0 

GROUP BY SO.ITEM, 'HLA' 

UNION ALL

SELECT SO.ITEM, 'OTHER' TYPE 

FROM PROD.V_SHOPORDER SO, SNAPMAN.ASSY_PT AP , TABLE1 TT

WHERE SO.ITEM = AP.COMPONENT_ID AND SO.RELEASED_QTY > 0 

AND SO.ITEM  = TT.ASSY_ID (+)--NOT IN(SELECT DISTINCT ASSEMBLY_ID FROM SNAPMAN.ASSY_PT) 

AND TT.ASSY_ID IS NULL

GROUP BY SO.ITEM, 'OTHER' ,TT.ASSY_ID;

 

[SantaMufasa]

J,

Something "TOADy Goofy" is going on then...Your code works fine in SQL*Plus. I'm not a TOAD user, so I cannot speak for/about the behavior of separating SQL statements with ";", which may be the source of your "invalid character" error.

Do you have access to SQL*Plus to at least do a "sanity check" on your code, and for validating the proof-of-concept?

What does TOAD expect as a statement-separating character withing scripts? (Anyone...anyone?)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[jadams0173]

I can't get to SQL plus. TOAD has an execute as script button but when I press it, I can see that it's running but I get no data back.

I'm lost.

 

[jadams0173]

I had SQL plus the entire time and didn't know it. The code does run from sql plus but not from FROGGY! Anyone have any idea why?

Thanks Dave for your help.

 

[jadams0173]

It does work in SQL plus, but still have no idea why in TOAD.

 

[SantaMufasa]

J,

My presumption regarding your problem is that punctuation for a TOAD-runnable script differs from SQL*Plus. Can you obtain, and successfully run, a TOAD-runnable script that contains multiple SQL commands so that we can compare punctuation?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[jadams0173]

Dave,

I tried running 2 selects from TOAD and could not make that run. I've had to stop messing with that for now but I'll come back to it.

 

[Thargy]

I use TOAD every day, and it's usually it's pretty good at doing sql plus type things. Were you running the sql as a script (clicking on the lightning bolt symbol) or running it as sql (clicking on the green arrow)?

Regards

T

 

[jadams0173]

T,

I tried to run it both ways. When I click the Execute all of current window as script button, it actually runs, but I get no data returned. If I use the green arrow and run it one piece at a time I get the data I expect.

 

[Thargy]

J,

just to be sure, can you alias 'HLA' and such to something other than TYPE, because it is a reserved work in oracle, and used to define a TYPE. How about

SELECT SO.ITEM, 'HLA' SHOP_ORDER_TYPE
FROM PROD.V_SHOPORDER SO, SNAPMAN.ASSY_PT AP
WHERE SO.ITEM = AP.ASSEMBLY_ID AND SO.RELEASED_QTY > 0
GROUP BY SO.ITEM, 'HLA'

instead of what you currently have.

Even if this isn't the answer, you should never use reserved and/or keywords in SQL unless its required. For example, I would never attempt to alias anything to be called 'WHERE' in a piece of SQL.

Regards

T

 

[jadams0173]

T,

Good advice. I made the changes that you showed me above in the script to not use the reserved word.

When execute window as script it runs but I still don't get and results back in the grid. If I run it with SQL *Plus I get results though.

Thanks again

 

[HarleyQuinn]

I might be way off base here (it's been a while since I've used TOAD) but on an INSERT query I'm pretty sure it doesn't return any values to the grid.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[jadams0173]

Hey HarleyQuinn,

The insert doesn't return data to the grid. But I would expect the SELECT UNION ALL statements to return data to the grid.

 

[Thargy]

to take this further, can you please post the create view statements for those in your query, and the create table statements for the tables which the views reference.

Regards

T

 

[HarleyQuinn]

Yes, it always helps to read the posted code properly

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[jadams0173]

CREATE OR REPLACE VIEW V_SHOPORDER ( SHOPORDER, 
ITEM, REV, ORDERED_QTY, RELEASED_QTY, 
DONE_QTY, SCRAPPED_QTY, RELDATE, STARTDATE
 ) AS SELECT	 
	SHOPORDER, 
	ITEM_ID ITEM, 
	EFF_REVISION REV, 
	ORDERED_QTY, 
	RELEASED_QTY, 
	DONE_QTY, 
	SCRAPPED_QTY, 
	CONV_TO_DATE(SCHEDSTARTDATE) RELDATE, 
	CONV_TO_DATE(ACTUALSTARTDATE) STARTDATE 
FROM 
	SHOPORDER 
WHERE 
	 STATUS_CD=20

 

[SantaMufasa]

J,

One issue right off the bat is that Oracle has no built-in function called "CONV_TO_DATE". Is that a user-defined function of your own? If so, why are "SCHEDSTARTDATE" and "ACTUALSTARTDATE" not already DATE data types?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[jadams0173]

Dave,

Good point. CONV_TO_DATE is a user-defined function. The way the DB was setup it records date and time stamps as seconds starting in like 1/1/1970 (i think). So each time we must convert it to a "usable" date and time to show the users. The columns "SCHEDSTARTDATE" and "ACTUALSTARTDATE" are defined as NUMBER(11).