Help : Nested Sql Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help : Nested Sql Statement

Author

Topic

vini
Starting Member

11 Posts

Posted - 2007-01-08 : 08:03:30

I am trying to run this simple nested sql statement, just wondering what am i doing wrong.

Appreciate any help

SELECT dtl_doccode, dtl_prodid, sum(TotalItems)
FROM
(
SELECT
dtl_doccode,
dtl_prodid,
dtl_prodquantity,
uomd_packing,
TotalItems = dtl_prodquantity * uomd_packing
FROM tblDocumentDetails
Inner Join tblProducts ON tblDocumentDetails.DTL_ProdId = tblProducts.PROD_Id
Inner Join tblDocuments ON tblDocumentDetails.DTL_DocId = tblDocuments.DOC_Id
Inner Join tbluomdetails On tblDocumentDetails.dtl_produomdid = uomd_id
WHERE
tblDocumentDetails.DeletedFlag = 0
AND DOC_TypeId = 20
AND DTL_DocId = 451
AND PROD_Id = '17'
)
GROUP BY dtl_doccode, dtl_prodid

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-08 : 08:10:36

Give an alias to the derived table


SELECT dtl_doccode, dtl_prodid, sum(TotalItems)
FROM
(
SELECT
dtl_doccode,
dtl_prodid,
dtl_prodquantity,
uomd_packing,
TotalItems = dtl_prodquantity * uomd_packing
FROM tblDocumentDetails
Inner Join tblProducts ON tblDocumentDetails.DTL_ProdId = tblProducts.PROD_Id
Inner Join tblDocuments ON tblDocumentDetails.DTL_DocId = tblDocuments.DOC_Id
Inner Join tbluomdetails On tblDocumentDetails.dtl_produomdid = uomd_id
WHERE
tblDocumentDetails.DeletedFlag = 0
AND DOC_TypeId = 20
AND DTL_DocId = 451
AND PROD_Id = '17'
) a
GROUP BY dtl_doccode, dtl_prodid

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-08 : 08:10:49

Specify Alias name for the table.

Select col1, col2
from 
(select * from sometable) as Tbl
Where ...

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

vini
Starting Member

11 Posts

Posted - 2007-01-08 : 08:13:33

Thanks guys, that worked!
You rock!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources