Home » SQL & PL/SQL » SQL & PL/SQL » sql error (oracle 10.1.2)
sql error [message #336019] Thu, 24 July 2008 07:27 Go to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Hi All
When i run the following the query i'm getting an error as :

ORA 009933 : SQL COMMAND NOT PROPERLY ENDED
at the 'FROM'
SELECT 'On-Call Unit Price' calc_type,

to_char(low,'DD-MON-YYYY') low, to_char(low,'Q') qtr, to_char(low,'YYYY') year,
to_char(high,'DD-MON-YYYY') high,to_char(high,'Q') qtr, to_char(high,'YYYY') year
FROM

(select add_months(trunc(sysdate),-3*rownum) low,
add_months(trunc(sysdate),-3*(rownum-1)) high
from all_objects where rownum <= 24
)


FROM
(SELECT DISTINCT 'On-Call Unit Price' calc_type




, TRUNC ( NTPACT - selection_date ) days
, contract_number
, ROW_NUMBER ( ) OVER ( PARTITION BY contract_number ORDER BY contract_number )
rn
FROM RFPCON.CONTRACT_VW2@RFPPROD.DOT
WHERE CONTRACT_TYPE='On Call'
AND CONTRACT_COMP_METHOD='Unit Price'
AND amend_status_code IN ( 4,33,34,35,36,37 )

WHERE rn = 1
Any help is greatly appreciated.

Thanks
Re: sql error [message #336020 is a reply to message #336019] Thu, 24 July 2008 07:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: sql error [message #336023 is a reply to message #336020] Thu, 24 July 2008 08:15 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
SELECT 'On-Call Unit Price' calc_type,
to_char(low,'DD-MON-YYYY') low, to_char(low,'Q') qtr, to_char(low,'YYYY') year,
to_char(high,'DD-MON-YYYY') high,to_char(high,'Q') qtr, to_char(high,'YYYY') year
FROM
(select add_months(trunc(sysdate),-3*rownum) low,
add_months(trunc(sysdate),-3*(rownum-1)) high
from all_objects where rownum <= 24),

FROM (SELECT DISTINCT 'On-Call Unit Price' calc_type

,TRUNC ( NTPACT - selection_date ) days
, contract_number
, ROW_NUMBER ( ) OVER ( PARTITION BY contract_number ORDER BY contract_number )
rn
FROM CONTRACT_VW2
WHERE CONTRACT_TYPE='On Call'
AND CONTRACT_COMP_METHOD='Unit Price'
AND amend_status_code IN ( 4,33,34,35,36,37 ) )

WHERE rn = 1
And i'm getting an error message at the FROM statement saying its an invalid table name
Re: sql error [message #336026 is a reply to message #336023] Thu, 24 July 2008 08:18 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The comma in front of the FROM is definitely wrong. Anything further might become visible once you have formatted the query to something readable.

Re: sql error [message #336027 is a reply to message #336019] Thu, 24 July 2008 08:31 Go to previous messageGo to next message
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
Member
Hi,

Again you have not formatted your posting.

Still, by looking at your query
Quote:
from all_objects where rownum <= 24),

FROM (SELECT DISTINCT 'On-Call Unit Price' calc_type


from is extra, and you have to include ) before
WHERE rn = 1


select ... from (...), (...) where rn = 1


Regards,

MSMallya

[Updated on: Thu, 24 July 2008 08:35]

Report message to a moderator

Re: sql error [message #336034 is a reply to message #336019] Thu, 24 July 2008 09:48 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
To all the sql guru's

please let me explain the problem. In the below query you have asked me to remove the EXTRA FROM, but if i remove the EXTRA FROM , the rest of the query is not executed for some reason.

SELECT 'On-Call Unit Price' calc_Type,
To_char(Low,'DD-MON-YYYY') Low,
To_char(Low,'Q') qtr,
To_char(Low,'YYYY') YEAR,
To_char(High,'DD-MON-YYYY') High,
To_char(High,'Q') qtr,
To_char(High,'YYYY') YEAR
FROM (SELECT Add_months(Trunc(SYSDATE),- 3 * ROWNUM) Low,
Add_months(Trunc(SYSDATE),- 3 * (ROWNUM - 1)) High
FROM Contract_vw2
WHERE ROWNUM <= 24),
(SELECT DISTINCT 'On-Call Unit Price' calc_Type,
Trunc(ntPact - Selection_Date) Days,
Contract_Number,
Row_number() OVER(PARTITION BY Contract_Number ORDER BY Contract_Number) rn
FROM Contract_vw2
WHERE Contract_Type = 'On Call'
AND Contract_comp_Method = 'Unit Price'
AND Amend_Status_Code IN (4,
33,
34,
35,
36,
37))
WHERE rn = 1


Thanks

[Edit MC: instead of posting in red and unreadable size 5, you should read and follow guidelines (just posting the same way to show you)]

[Updated on: Thu, 24 July 2008 10:03] by Moderator

Report message to a moderator

Re: sql error [message #336037 is a reply to message #336019] Thu, 24 July 2008 09:57 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Since you refuse post according to the guidelines,
You're On Your Own (YOYO)!
Re: sql error [message #336039 is a reply to message #336037] Thu, 24 July 2008 10:24 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
What did i do wrong can you please let meknow so that i will correct myself.
Re: sql error [message #336041 is a reply to message #336039] Thu, 24 July 2008 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 24 July 2008 14:39
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel


Re: sql error [message #336042 is a reply to message #336041] Thu, 24 July 2008 10:28 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
I'm Really sorry about that, but i did went into the formatter and got it formatted and then pasted it in the forum, sorry about the red color, i just wanted to differentiate between the queries.
Re: sql error [message #336046 is a reply to message #336042] Thu, 24 July 2008 10:31 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
SELECT 'On-Call Unit Price' calc_Type,
To_char(Low,'DD-MON-YYYY') Low,
To_char(Low,'Q') qtr,
To_char(Low,'YYYY') YEAR,
To_char(High,'DD-MON-YYYY') High,
To_char(High,'Q') qtr,
To_char(High,'YYYY') YEAR
FROM (SELECT Add_months(Trunc(SYSDATE),- 3 * ROWNUM) Low,
Add_months(Trunc(SYSDATE),- 3 * (ROWNUM - 1)) High
FROM Contract_vw2
WHERE ROWNUM <= 24),
(SELECT DISTINCT 'On-Call Unit Price' calc_Type,
Trunc(ntPact - Selection_Date) Days,
Contract_Number,
Row_number() OVER(PARTITION BY Contract_Number ORDER BY Contract_Number) rn
FROM Contract_vw2
WHERE Contract_Type = 'On Call'
AND Contract_comp_Method = 'Unit Price'
AND Amend_Status_Code IN (4,
33,
34,
35,
36,
37))
WHERE rn = 1
Re: sql error [message #336047 is a reply to message #336042] Thu, 24 July 2008 10:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to put your code between code tags as clearly explained in guidelines.
It is more the combination of red and size 5 that hurts.

Regards
Michel
Re: sql error [message #336048 is a reply to message #336046] Thu, 24 July 2008 10:31 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Hopefully i didnt offend any one , please need the advice thanks
aarti81 wrote on Thu, 24 July 2008 10:31
SELECT 'On-Call Unit Price' calc_Type,
To_char(Low,'DD-MON-YYYY') Low,
To_char(Low,'Q') qtr,
To_char(Low,'YYYY') YEAR,
To_char(High,'DD-MON-YYYY') High,
To_char(High,'Q') qtr,
To_char(High,'YYYY') YEAR
FROM (SELECT Add_months(Trunc(SYSDATE),- 3 * ROWNUM) Low,
Add_months(Trunc(SYSDATE),- 3 * (ROWNUM - 1)) High
FROM Contract_vw2
WHERE ROWNUM <= 24),
(SELECT DISTINCT 'On-Call Unit Price' calc_Type,
Trunc(ntPact - Selection_Date) Days,
Contract_Number,
Row_number() OVER(PARTITION BY Contract_Number ORDER BY Contract_Number) rn
FROM Contract_vw2
WHERE Contract_Type = 'On Call'
AND Contract_comp_Method = 'Unit Price'
AND Amend_Status_Code IN (4,
33,
34,
35,
36,
37))
WHERE rn = 1


Re: sql error [message #336050 is a reply to message #336019] Thu, 24 July 2008 10:35 Go to previous message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
If you format the code within your code tags it's easier to read

SELECT 'On-Call Unit Price' calc_Type,
       To_char(Low, 'DD-MON-YYYY') Low,
       To_char(Low, 'Q') qtr,
       To_char(Low, 'YYYY') YEAR,
       To_char(High, 'DD-MON-YYYY') High,
       To_char(High, 'Q') qtr,
       To_char(High, 'YYYY') YEAR
  FROM (SELECT Add_months(Trunc(SYSDATE), -3 * ROWNUM) Low,
               Add_months(Trunc(SYSDATE), -3 * (ROWNUM - 1)) High
          FROM Contract_vw2
         WHERE ROWNUM <= 24),
       (SELECT DISTINCT 'On-Call Unit Price' calc_Type,
                        Trunc(ntPact - Selection_Date) Days,
                        Contract_Number,
                        Row_number() OVER(PARTITION BY Contract_Number ORDER BY Contract_Number) rn
          FROM Contract_vw2
         WHERE Contract_Type = 'On Call'
           AND Contract_comp_Method = 'Unit Price'
           AND Amend_Status_Code IN (4, 33, 34, 35, 36, 37))
 WHERE rn = 1


I think what you need to go is give an alias to your select statements and reference your selected variables by the alias.


Previous Topic: Data Archiving n Purging
Next Topic: Column Alias
Goto Forum:
  


Current Time: Wed Dec 07 05:00:21 CST 2016

Total time taken to generate the page: 0.16610 seconds