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

[Drate]

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;

不知道哪位可以帮帮我？

[Drate]

TO ：jiezhi(浪子)

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

[jiezhi]

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]

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]

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;

[2dian1G]

教你一个万能方法，装一个PB，那里提供生成SQL的工具，都是可视化的，可以满足你LEFT JOIN的要求

[jiezhi]

改一下：
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]

教你一个万能方法，装一个PB，那里提供生成SQL的工具，都是可视化的，可以满足你LEFT JOIN的要求

[jiezhi]

改一下：
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;

[Drate]

写的急了一点，还有在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子句中，那空值就取不到了。。

[Drate]

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) //同上
所以记录还是会被过滤掉

[jiezhi]

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;