The variable I select contains special character '&' - I pulled the data to my local schema and changed all "&" character into into "AND" then save the updated table on my user schema and try to read and select it again. I am getting the same error. any Idea how I can resolve this issue? so, when I run the scritp, I got a scream display which look like what is attached.
WHen I click on "OK" I get ORA-00904: "TYPE": invalid identifier error. can you help me resolving this error please?
Main Menu: Database -> Spool SQL -> Spool to screen.
Make the error happen.
Check the spool output for the error. If you aren't sure what to do about it, post the offending SQL here and let me know which Toad version you are on, and also which Oracle version you are using.
Hello John,
I am new to TOAD SQL, I flogged or Marked Spool to scree. Now, how do I view or check the spool out?
After you check "Spool to Screen", then do whatever you have to do to produce the ORA-00904.
SQL Output will appear at the bottom of Toad, and you can copy/paste it from there:
Hi John, I got the error. How do I send the error to you. On this media?
[Error] ORA-00904: "TYPE": invalid identifier
(165: 0): >> CREATE TABLE TG_PROD_PREDICT02 AS
SELECT
....
...
Error at line 2
ORA-00904: "TYPE": invalid identifier
Yes, you can just post it here. Or you can click my face above and then "Message" to send it to me directly.
If the error is coming from your "CREATE TABLE" command, it looks like that is your SQL, not Toad's.
-John
The original program is in SAS SQL, I converted it into TOAD SQL.
try again, I just increased your privileges
Thank you John, I just sent it to you.
I really apricate your help.
You're welcome. I replied to your message.
Hi John,
I changed the variable to Type_code, the problem is still there. I thought the cause is:
CASE
WHEN EP.POST18_BEN = 'CP' AND TY.nm LIKE '%PMC%' THEN 'P'
WHEN EP.POST18_BEN = 'CP' AND TY.cd IN ('020ASMDICP', '020RSCDTH') THEN 'P'
WHEN EP.POST18_BEN = 'CP' AND TY.nm NOT LIKE '%PMC%' THEN 'C'
ELSE EP.POST18_DEFBEN
END AS type_code
I checked those tables and variables if they exist and have values. I did some research on the same topic - I set define off and rerun the program, the error is still there.
You have two problems:
The prompt is because you have '&' characters in your SQL. If you are running your script with F5 or "run as script", you can eliminate that with SET DEFINE OFF at the top of your script. If you are running it with F9 or "Execute SQL" button in Toad, then right-click in the Editor, and set "Substitution variable prompting" to "None".
The ORA-00904 was due to the TYPE column reference. That should be fixed by renaming TYPE to TYPE_CODE as you have done.
John
