CODEWITHSUNDEEP
sqloracle-databaseddl
user3740154
user3740154

Reputation: 89

Error - Keyword not found where expected

I am trying to create a table using following code in oracle 10g and each time I am getting the following error:

ORA-00923: FROM keyword not found where expected

The query is as follows:

Create table Tab2 nologging as
select /*+parallel(a,6)*/  Tab1col1, Tab1col2,
MAX(case when tab1Col5 = '21-aug-2015' then Tab1Col3 end) 21AUGBALANCE,
MAX(case when tab1Col5 = '22-aug-2015' then Tab1Col3 end) 22AUGBALANCE,
MAX(case when tab1Col5 = '23-aug-2015' then Tab1Col3 end) 23AUGBALANCE
from Tab1 a
GROUP BY msisdn, sdp_node
order by msisdn, sdp_node

Table 1 has 5 columns namely tab1Col1, tab1Col2, Tab1Col3, Tab1Col4 and Tab1Col5. I need to create Tab2 from Tab1 which also has 5 columns 1,2,3,4,5. But what is the error in this code?

Upvotes: 2

Views: 126

Answers (4)

Matt
Matt

Reputation: 15061

Use quotes on your Aliases.

Create table Tab2 nologging AS
SELECT Tab1col1, Tab1col2,
MAX(CASE WHEN tab1Col5 = '21-aug-2015' THEN Tab1Col3 END) AS "21AUGBALANCE",
MAX(CASE WHEN tab1Col5 = '22-aug-2015' THEN Tab1Col3 END) AS "22AUGBALANCE",
MAX(CASE WHEN tab1Col5 = '23-aug-2015' THEN Tab1Col3 END) AS "23AUGBALANCE"
FROM Tab1 a
GROUP BY msisdn, sdp_node
ORDER BY msisdn, sdp_node

Upvotes: 1

Tharunkumar Reddy
Tharunkumar Reddy

Reputation: 2813

Problem is invalid alias name so use double quotes to overcome the problem 

Create table Tab2 nologging as
    select /*+parallel(a,6)*/  Tab1col1, Tab1col2,
    MAX(case when tab1Col5 = '21-aug-2015' then Tab1Col3 end) as "21AUGBALANCE",
    MAX(case when tab1Col5 = '22-aug-2015' then Tab1Col3 end) as "22AUGBALANCE",
    MAX(case when tab1Col5 = '23-aug-2015' then Tab1Col3 end) as "23AUGBALANCE"
    from Tab1 a
    GROUP BY msisdn, sdp_node
    order by msisdn, sdp_node

Upvotes: 1

Sawalhah
Sawalhah

Reputation: 156

you have problem with column aliases try use double quotes "" like this

 Create table Tab2 nologging as
 select /*+parallel(a,6)*/  Tab1col1, Tab1col2,
 MAX(case when tab1Col5 = '21-aug-2015' then Tab1Col3 end) "21AUGBALANCE",
 MAX(case when tab1Col5 = '22-aug-2015' then Tab1Col3 end) "22AUGBALANCE",
 MAX(case when tab1Col5 = '23-aug-2015' then Tab1Col3 end) "23AUGBALANCE"
 from Tab1 a
 GROUP BY msisdn, sdp_node
 order by msisdn, sdp_node

Upvotes: 1

realbart
realbart

Reputation: 3974

try

Create table Tab2 nologging as
select /*+parallel(a,6)*/  Tab1col1, Tab1col2,
MAX(case when tab1Col5 = '21-aug-2015' then Tab1Col3 end) "21AUGBALANCE",
MAX(case when tab1Col5 = '22-aug-2015' then Tab1Col3 end) "22AUGBALANCE",
MAX(case when tab1Col5 = '23-aug-2015' then Tab1Col3 end) "23AUGBALANCE"  
from Tab1 a
GROUP BY msisdn, sdp_node
order by msisdn, sdp_node

oracle supports column names starting with numbers, but you have to quote them if you want a column name STARTING with a number.

alternatively, pick different names (e.g. BALANCE21AUG)

Upvotes: 1

Related Questions