从选择创建（查询错误）

发布于 2024-12-04 16:15:53 字数 568 浏览 3 评论 0原文

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);

上面是我创建的一个查询，作为 sybase 的等效查询。任何人都可以帮助我纠正这个查询

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

若水微香 2024-12-11 16:15:53

您的查询中存在一些非常严重的语法不一致问题，我已尝试更正这些不一致的地方（由于您没有提供表结构，因此进行了猜测）。例如，在选择列表中，您选择“Value”，但在 GROUP BY 中，您对“Item_Value”等进行分组。

您还需要完全限定您的列，有些您符合条件，有些（如“模块”和“类型”等）您不需要't。

您可能还想添加一个存储子句。

这是我对您想要实现的目标的最有根据的猜测：

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');

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');

回复收藏 0 原文

~没有更多了~