create from select(Query error)

Question

create table1 as SELECT from c.Name, Value, Qtr, Value as SUM(Value) 
from User.Pofile a,pet_dsa_qtr_table b,User.Count c,User.Service d 
WHERE Category = 'PROFESSIONAL' and Item_Name = 'PROF_PKT_RECVD' and 
convert(char(32),d.User_Id) = c.Name and Service_Name_Cd = 2 
and Status = 2 and d.User_Id *= a.User_Id and c.Period = b.Period 
and Module = 'ACCT_TRADES' and Type in ('EQ','OPTN')
GROUP BY Name, Item_Value,
Qtr HAVING SUM(Value) >= 10000 and Item_Value in ('0', NULL);

The above was a query that was created by me as an equivalent for sybase.Can anyone help me in correcting this query

• reader_1000: Did you try putting paranthesis to select statement create table1 as (select ... ))
• diagonalbatman: One problem i do see is you have two FROM words in your SQL, you are using aliases in the select, but not in the GROUP BY.

Answer 1

You have some quite serious syntax inconsistencies in your query which I have tried to correct (with guesswork as you haven't provided a table structure). e.g. In the select list you select "Value" but in the GROUP BY you group on "Item_Value" etc.

You also need to fully qualify your columns, some you qualify, some (like "Module" and "Type" etc) you don't.

You might also want to add a storage clause too.

Here is my best educated guess at what you are trying to achieve:

CREATE TABLE table1 
AS 
SELECT c.Name, 
 Item_Value, 
 Qtr, 
 SUM(Value) as "SUM_Value"
 FROM User.Pofile a
 pet_dsa_qtr_table b,
 User.Count c,
 User.Service d 
 WHERE Category = 'PROFESSIONAL' 
 AND Item_Name = 'PROF_PKT_RECVD' 
 AND TO_CHAR(d.User_Id) = c.Name 
 AND Service_Name_Cd = 2 
 AND Status = 2 
 AND d.User_Id = a.User_Id(+) 
 AND c.Period = b.Period 
 AND Module = 'ACCT_TRADES' 
 AND Type in ('EQ','OPTN') 
 GROUP BY c.Name, 
 Item_Value, 
 Qtr 
HAVING (SUM(Value) >= 10000 
 AND NVL(Item_Value, '0') = '0');