第一次提问:这个ORACLE的SQL语句如写改成标准的SQL语句呢?主要是(+)联接改为LEFT JOIN的操作!

Drate
博客专家认证
2003-09-04 10:15:32
ORACLE源语句:
SELECT /*+INDEX(D PK_STOCKDAY)*/ G.GDSNAME, G.GDSID, G.GDSINID, G.SPECIFY,
G.UOMID,G.SalePrice,0 as QTY,SUM(D.ADDQTY) AS PROFQTY
FROM GOODS G,STOCKDAY D
WHERE ( G.ACCOUNTID = :LS_ACCOUNTID )
AND ( G.GDSID like :LS_GDSID )
AND ( D.GDSINID(+) = G.GDSINID)
AND ( D.STOCKLOCID(+) =:LS_STOCKLOCID )
AND ( D.ACCOUNTID(+) = :LS_ACCOUNTID1 )
AND ( D.CUSTOMERID(+) = :LS_CUSTOMERID)
AND ( D.DAY(+) <= :LS_BILLDT)
GROUP BY G.GDSNAME,G.GDSID,G.GDSINID,G.SPECIFY,G.UOMID,G.SalePrice;

我试过很多遍,在ACCESS中,有试成:
SELECT /*+INDEX(D PK_STOCKDAY)*/ G.GDSNAME, G.GDSID, G.GDSINID, G.SPECIFY,G.UOMID,G.SalePrice,0 as QTY,SUM(D.ADDQTY) AS PROFQTY
FROM GOODS G
LEFT JOIN STOCKDAY D ON ( D.GDSINID = G.GDSINID)
AND ( D.STOCKLOCID =:LS_STOCKLOCID )
//但这里不允许联接一个常量,非要是一个字段名才行
AND ( D.ACCOUNTID = :LS_ACCOUNTID1 )
AND ( D.CUSTOMERID = :LS_CUSTOMERID)
AND ( D.DAY <= :LS_BILLDT)
WHERE ( G.ACCOUNTID = :LS_ACCOUNTID )
AND ( G.GDSID like :LS_GDSID )
GROUP BY G.GDSNAME,G.GDSID,G.GDSINID,G.SPECIFY,G.UOMID,G.SalePrice;

不知道哪位可以帮帮我?
...全文
98 11 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
jiezhi 2003-09-04
  • 打赏
  • 举报
回复
SELECT G.GDSNAME, G.GDSID, G.GDSINID,
G.SPECIFY,G.UOMID,G.SalePrice,0 as QTY,SUM(D.ADDQTY) AS PROFQTY
FROM GOODS G
LEFT JOIN
(select * from STOCKDAY where
AND ( STOCKLOCID =:LS_STOCKLOCID )
AND ( ACCOUNTID = :LS_ACCOUNTID1 )
AND ( CUSTOMERID = :LS_CUSTOMERID)
AND ( DAY <= :LS_BILLDT)
) D ON ( D.GDSINID = G.GDSINID)

WHERE ( G.ACCOUNTID = :LS_ACCOUNTID )
AND ( G.GDSID like :LS_GDSID )
GROUP BY G.GDSNAME,G.GDSID,G.GDSINID,G.SPECIFY,G.UOMID,G.SalePrice;
Drate 2003-09-04
  • 打赏
  • 举报
回复
TO: 2dian1G(小硬盘)
其实不只只是PB有SQL 生成器,像SQL SERVER, ACCESS都有这样的工具
问题是,并不是所有的SQL语句都可以用生成器来做的了

TO jiezhi(浪子)
你上面写的语句我也执行过,可惜,它会把我需要的记录都过滤掉的
如:我想查的记录在GOODS表中有的,但在STOCKDAY表中可能是没有的
如果按你的语句查的话,因为在STOCKDAY 中并不满足以下条件:
AND ( D.STOCKLOCID =:LS_STOCKLOCID )
AND ( D.ACCOUNTID = :LS_ACCOUNTID1 ) //同上
AND ( D.CUSTOMERID = :LS_CUSTOMERID) //同上
AND ( D.DAY <= :LS_BILLDT) //同上
所以记录还是会被过滤掉
Drate 2003-09-04
  • 打赏
  • 举报
回复
写的急了一点,还有在ACCESS中,应该把索引的语句去掉的
我试过很多遍,在ACCESS中,有试成:
SELECT G.GDSNAME, G.GDSID, G.GDSINID,
G.SPECIFY,G.UOMID,G.SalePrice,0 as QTY,SUM(D.ADDQTY) AS PROFQTY
FROM GOODS G
LEFT JOIN STOCKDAY D ON ( D.GDSINID = G.GDSINID)
AND ( D.STOCKLOCID =:LS_STOCKLOCID ) //但这里不允许联接一个常量,非要是一个字段名才行

AND ( D.ACCOUNTID = :LS_ACCOUNTID1 ) //同上
AND ( D.CUSTOMERID = :LS_CUSTOMERID) //同上
AND ( D.DAY <= :LS_BILLDT) //同上
WHERE ( G.ACCOUNTID = :LS_ACCOUNTID )
AND ( G.GDSID like :LS_GDSID )
GROUP BY G.GDSNAME,G.GDSID,G.GDSINID,G.SPECIFY,G.UOMID,G.SalePrice;

如果按楼上的办法,把 AND ( D.STOCKLOCID =:LS_STOCKLOCID ) 语句写到
WHERE子句中,那么就达不到我的要求了,因为这个字段值是允许取空值的,
放到WHERE子句中,那空值就取不到了。。
jiezhi 2003-09-04
  • 打赏
  • 举报
回复
改一下:
SELECT /*+INDEX(D PK_STOCKDAY)*/ G.GDSNAME, G.GDSID, G.GDSINID, G.SPECIFY,G.UOMID,G.SalePrice,0 as QTY,SUM(D.ADDQTY) AS PROFQTY
FROM GOODS G
LEFT JOIN STOCKDAY D ON ( D.GDSINID = G.GDSINID)
WHERE ( G.ACCOUNTID = :LS_ACCOUNTID )
AND ( G.GDSID like :LS_GDSID )
AND ( D.STOCKLOCID =:LS_STOCKLOCID )

AND ( D.ACCOUNTID = :LS_ACCOUNTID1 )
AND ( D.CUSTOMERID = :LS_CUSTOMERID)
AND ( D.DAY <= :LS_BILLDT)

GROUP BY G.GDSNAME,G.GDSID,G.GDSINID,G.SPECIFY,G.UOMID,G.SalePrice;
2dian1G 2003-09-04
  • 打赏
  • 举报
回复
教你一个万能方法,装一个PB,那里提供生成SQL的工具,都是可视化的,可以满足你LEFT JOIN的要求
jiezhi 2003-09-04
  • 打赏
  • 举报
回复
改一下:
SELECT /*+INDEX(D PK_STOCKDAY)*/ G.GDSNAME, G.GDSID, G.GDSINID, G.SPECIFY,G.UOMID,G.SalePrice,0 as QTY,SUM(D.ADDQTY) AS PROFQTY
FROM GOODS G
LEFT JOIN STOCKDAY D ON ( D.GDSINID = G.GDSINID)
WHERE ( G.ACCOUNTID = :LS_ACCOUNTID )
AND ( G.GDSID like :LS_GDSID )
AND ( D.STOCKLOCID =:LS_STOCKLOCID )

AND ( D.ACCOUNTID = :LS_ACCOUNTID1 )
AND ( D.CUSTOMERID = :LS_CUSTOMERID)
AND ( D.DAY <= :LS_BILLDT)

GROUP BY G.GDSNAME,G.GDSID,G.GDSINID,G.SPECIFY,G.UOMID,G.SalePrice;
2dian1G 2003-09-04
  • 打赏
  • 举报
回复
教你一个万能方法,装一个PB,那里提供生成SQL的工具,都是可视化的,可以满足你LEFT JOIN的要求
jiezhi 2003-09-04
  • 打赏
  • 举报
回复
SELECT /*+INDEX(D PK_STOCKDAY)*/ G.GDSNAME, G.GDSID, G.GDSINID, G.SPECIFY,G.UOMID,G.SalePrice,0 as QTY,SUM(D.ADDQTY) AS PROFQTY
FROM GOODS G
LEFT JOIN STOCKDAY D ON ( D.GDSINID = G.GDSINID)

//但这里不允许联接一个常量,非要是一个字段名才行
AND ( D.ACCOUNTID = :LS_ACCOUNTID1 )
AND ( D.CUSTOMERID = :LS_CUSTOMERID)
AND ( D.DAY <= :LS_BILLDT)
WHERE ( G.ACCOUNTID = :LS_ACCOUNTID )
AND ( G.GDSID like :LS_GDSID )
AND ( D.STOCKLOCID =:LS_STOCKLOCID )
GROUP BY G.GDSNAME,G.GDSID,G.GDSINID,G.SPECIFY,G.UOMID,G.SalePrice;
jiezhi 2003-09-04
  • 打赏
  • 举报
回复
SELECT /*+INDEX(D PK_STOCKDAY)*/ G.GDSNAME, G.GDSID, G.GDSINID, G.SPECIFY,G.UOMID,G.SalePrice,0 as QTY,SUM(D.ADDQTY) AS PROFQTY
FROM GOODS G
LEFT JOIN STOCKDAY D ON ( D.GDSINID = G.GDSINID)

//但这里不允许联接一个常量,非要是一个字段名才行
AND ( D.ACCOUNTID = :LS_ACCOUNTID1 )
AND ( D.CUSTOMERID = :LS_CUSTOMERID)
AND ( D.DAY <= :LS_BILLDT)
WHERE ( G.ACCOUNTID = :LS_ACCOUNTID )
AND ( G.GDSID like :LS_GDSID )
AND ( D.STOCKLOCID =:LS_STOCKLOCID )
GROUP BY G.GDSNAME,G.GDSID,G.GDSINID,G.SPECIFY,G.UOMID,G.SalePrice;
jiezhi 2003-09-04
  • 打赏
  • 举报
回复
SELECT /*+INDEX(D PK_STOCKDAY)*/ G.GDSNAME, G.GDSID, G.GDSINID, G.SPECIFY,G.UOMID,G.SalePrice,0 as QTY,SUM(D.ADDQTY) AS PROFQTY
FROM GOODS G
LEFT JOIN STOCKDAY D ON ( D.GDSINID = G.GDSINID)

//但这里不允许联接一个常量,非要是一个字段名才行
AND ( D.ACCOUNTID = :LS_ACCOUNTID1 )
AND ( D.CUSTOMERID = :LS_CUSTOMERID)
AND ( D.DAY <= :LS_BILLDT)
WHERE ( G.ACCOUNTID = :LS_ACCOUNTID )
AND ( G.GDSID like :LS_GDSID )
AND ( D.STOCKLOCID =:LS_STOCKLOCID )
GROUP BY G.GDSNAME,G.GDSID,G.GDSINID,G.SPECIFY,G.UOMID,G.SalePrice;
Drate 2003-09-04
  • 打赏
  • 举报
回复
TO :jiezhi(浪子)

你的这句好象有点像了,呵呵,我去试试

17,140

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧