March 1, 2016 at 12:58 am
Hello,
I want to combine the result sets from two multiple query using union all operator and send the final result as database email reminder but i getting error message when i parse the command "Only one expression can be specified in the select list when the sub query is not introduced...."
Please advise.
Thank you
CAST((
SELECT (
SELECT * FROM (
SELECT Customer.Name, SalesOrder.OrderNo, SalesOrder.CustomerPO, StockItem.StockCode + ' - ' + StockItem.Description1 AS StockItem, CONVERT(varchar(10), SalesOrderDet.RequestDate, 103) AS RequestDate, CONVERT(varchar(10), SalesOrderDet.ConfirmDate, 103) AS ConfirmDate, SalesOrderDet.DueQty AS DueQty,
sum(StockManagement.Fg) OnhandFg, sum(StockManagement.SFg) AS OnhandSfg, DATEDIFF(D, SalesOrderDet.RequestDate, GETDATE()) AS Delay, 1 AS ReferenceNo FROM SalesOrderDet INNER JOIN SalesOrder ON SalesOrderDet.OrderId = SalesOrder.Uniid INNER JOIN StockItem ON SalesOrderDet.StockCode = StockItem.Uniid INNER JOIN
Customer ON SalesOrder.CustomerId = Customer.Uniid LEFT OUTER JOIN
(SELECT STOCKCODE, CAST(SUM((CASE WHEN TransactionTypeId IN (1, 4, 5, 7, 8, 10, 11, 13, 14) AND StockTypeId = 2 THEN TransactionQty ELSE 0 END)
- (CASE WHEN TransactionTypeId IN (2, 3, 6, 9, 12) AND StockTypeId = 2 THEN TransactionQty ELSE 0 END)) AS numeric(10, 0)) AS Fg,
CAST(SUM((CASE WHEN TransactionTypeId IN (1, 4, 5, 7, 8, 10, 11, 13, 14) AND StockTypeId = 3 THEN TransactionQty ELSE 0 END)
- (CASE WHEN TransactionTypeId IN (2, 3, 6, 9, 12) AND StockTypeId = 3 THEN TransactionQty ELSE 0 END)) AS numeric(10, 0)) AS Sfg
FROM STOCKMANAGEMENT group by StockCode) AS StockManagement on StockItem.Uniid = StockManagement.StockCode
WHERE (DATEDIFF(D, SalesOrderDet.RequestDate, GETDATE()) > 0) AND (SalesOrderDet.DueQty > 0) AND (SalesOrder.StatusId = 3) AND (SalesOrderDet.Suppress <> 1)
GROUP BY SalesOrder.OrderNo, Customer.Name, SalesOrder.CustomerPO, StockItem.StockCode + ' - ' + StockItem.Description1, SalesOrderDet.RequestDate, SalesOrderDet.ConfirmDate, SalesOrderDet.DueQty, SalesOrder.StatusId, DATEDIFF(D, SalesOrderDet.RequestDate, GETDATE()), SalesOrder.Uniid
UNION ALL
SELECT Customer.Name, SalesOrder.OrderNo, SalesOrder.CustomerPO, StockItem.StockCode + ' - ' + StockItem.Description1 AS StockItem, CONVERT(varchar(10), SalesOrderDet.RequestDate, 103) AS RequestDate, CONVERT(varchar(10), SalesOrderDet.ConfirmDate, 103) AS ConfirmDate, SalesOrderDet.DueQty AS DueQty,
sum(StockManagement.Fg) OnhandFg, sum(StockManagement.SFg) AS OnhandSfg, DATEDIFF(D, SalesOrderDet.ConfirmDate, GETDATE()) AS Delay, 2 AS ReferenceNo FROM SalesOrderDet INNER JOIN SalesOrder ON SalesOrderDet.OrderId = SalesOrder.Uniid INNER JOIN StockItem ON SalesOrderDet.StockCode = StockItem.Uniid INNER JOIN
Customer ON SalesOrder.CustomerId = Customer.Uniid LEFT OUTER JOIN
(SELECT STOCKCODE, CAST(SUM((CASE WHEN TransactionTypeId IN (1, 4, 5, 7, 8, 10, 11, 13, 14) AND StockTypeId = 2 THEN TransactionQty ELSE 0 END)
- (CASE WHEN TransactionTypeId IN (2, 3, 6, 9, 12) AND StockTypeId = 2 THEN TransactionQty ELSE 0 END)) AS numeric(10, 0)) AS Fg,
CAST(SUM((CASE WHEN TransactionTypeId IN (1, 4, 5, 7, 8, 10, 11, 13, 14) AND StockTypeId = 3 THEN TransactionQty ELSE 0 END)
- (CASE WHEN TransactionTypeId IN (2, 3, 6, 9, 12) AND StockTypeId = 3 THEN TransactionQty ELSE 0 END)) AS numeric(10, 0)) AS Sfg
FROM STOCKMANAGEMENT group by StockCode) AS StockManagement on StockItem.Uniid = StockManagement.StockCode
WHERE (DATEDIFF(D, SalesOrderDet.ConfirmDate, GETDATE()) > 0) AND (SalesOrderDet.DueQty > 0) AND (SalesOrder.StatusId = 3) AND (SalesOrderDet.Suppress <> 1)
GROUP BY SalesOrder.OrderNo, Customer.Name, SalesOrder.CustomerPO, StockItem.StockCode + ' - ' + StockItem.Description1, SalesOrderDet.ConfirmDate, SalesOrderDet.RequestDate,SalesOrderDet.DueQty, SalesOrder.StatusId, DATEDIFF(D, SalesOrderDet.ConfirmDate, GETDATE()), SalesOrder.Uniid
--ORDER BY ReferenceNo, Customer.Name, StockItem.StockCode + ' - ' + StockItem.Description1
) Sub
)
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>'
March 1, 2016 at 2:13 am
You have one too many SELECTs in there:
IF OBJECT_ID('tempdb..#StockManagement') IS NOT NULL DROP TABLE #StockManagement;
SELECT
STOCKCODE,
CAST(SUM((CASE WHEN TransactionTypeId IN (1, 4, 5, 7, 8, 10, 11, 13, 14) AND StockTypeId = 2 THEN TransactionQty ELSE 0 END)
- (CASE WHEN TransactionTypeId IN (2, 3, 6, 9, 12) AND StockTypeId = 2 THEN TransactionQty ELSE 0 END)) AS numeric(10, 0)) AS Fg,
CAST(SUM((CASE WHEN TransactionTypeId IN (1, 4, 5, 7, 8, 10, 11, 13, 14) AND StockTypeId = 3 THEN TransactionQty ELSE 0 END)
- (CASE WHEN TransactionTypeId IN (2, 3, 6, 9, 12) AND StockTypeId = 3 THEN TransactionQty ELSE 0 END)) AS numeric(10, 0)) AS Sfg
INTO #StockManagement
FROM STOCKMANAGEMENT
group by StockCode;
WITH MyResultSet AS (
SELECT
c.Name,
so.OrderNo,
so.CustomerPO,
so.StockCode + ' - ' + so.Description1 AS StockItem,
CONVERT(varchar(10), sod.RequestDate, 103) AS RequestDate,
CONVERT(varchar(10), sod.ConfirmDate, 103) AS ConfirmDate,
sod.DueQty AS DueQty,
sum(sm.Fg) OnhandFg,
sum(sm.SFg) AS OnhandSfg,
DATEDIFF(D, sod.RequestDate, GETDATE()) AS Delay,
1 AS ReferenceNo
FROM SalesOrderDet sod
INNER JOIN SalesOrder so ON sod.OrderId = so.Uniid
INNER JOIN StockItem si ON sod.StockCode = so.Uniid
INNER JOIN Customer c ON so.CustomerId = c.Uniid
LEFT OUTER JOIN #StockManagement sm ON so.Uniid = sm.StockCode
WHERE (DATEDIFF(D, sod.RequestDate, GETDATE()) > 0)
AND (sod.DueQty > 0)
AND (so.StatusId = 3)
AND (sod.Suppress <> 1)
GROUP BY so.OrderNo, c.Name, so.CustomerPO, so.StockCode + ' - ' + so.Description1, sod.RequestDate, sod.ConfirmDate, sod.DueQty, so.StatusId, DATEDIFF(D, sod.RequestDate, GETDATE()), so.Uniid
UNION ALL
SELECT
c.Name,
so.OrderNo,
so.CustomerPO,
so.StockCode + ' - ' + so.Description1 AS StockItem,
CONVERT(varchar(10), sod.RequestDate, 103) AS RequestDate,
CONVERT(varchar(10), sod.ConfirmDate, 103) AS ConfirmDate,
sod.DueQty AS DueQty,
sum(sm.Fg) OnhandFg,
sum(sm.SFg) AS OnhandSfg,
DATEDIFF(D, sod.ConfirmDate, GETDATE()) AS Delay,
2 AS ReferenceNo
FROM SalesOrderDet
INNER JOIN SalesOrder ON sod.OrderId = so.Uniid
INNER JOIN StockItem ON sod.StockCode = so.Uniid
INNER JOIN Customer ON so.CustomerId = c.Uniid
LEFT OUTER JOIN #StockManagement sm ON so.Uniid = sm.StockCode
WHERE (DATEDIFF(D, sod.ConfirmDate, GETDATE()) > 0)
AND (sod.DueQty > 0)
AND (so.StatusId = 3)
AND (sod.Suppress <> 1)
GROUP BY so.OrderNo, c.Name, so.CustomerPO, so.StockCode + ' - ' + so.Description1, sod.ConfirmDate, sod.RequestDate,sod.DueQty, so.StatusId, DATEDIFF(D, sod.ConfirmDate, GETDATE()), so.Uniid
)
SELECT CAST(
(
--SELECT (
SELECT *
FROM MyResultSet Sub
--)
FOR XML PATH('tr'), TYPE
)
AS NVARCHAR(MAX)) + N'</table>'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 1, 2016 at 7:22 pm
Thank you very much for your valuable support.
I am getting parse command text error...unable to identify syntax error (Incorrect syntax near the keyword 'IF').
Please review the full command text below and do the need full.
DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
DECLARE @tableHTML nvarchar(max)
BEGIN
SET @subject = 'Delivery Pending Orders - Red Card'
SET @tableHTML =
N'<style type="text/css">
#box-table
{
font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
font-size: 12px;
text-align: center;
border-collapse: collapse;
border-top: 7px solid #9baff1;
border-bottom: 7px solid #9baff1;
}
#box-table th
{
font-size: 13px;
font-weight: normal;
background: #b9c9fe;
border-right: 2px solid #9baff1;
border-left: 2px solid #9baff1;
border-bottom: 2px solid #9baff1;
color: #039;
}
#box-table td
{
border-right: 1px solid #aabcfe;
border-left: 1px solid #aabcfe;
border-bottom: 1px solid #aabcfe;
color: #669;
}
tr:nth-child(odd){ background-color:#eee; }
tr:nth-child(even){ background-color:#fff; }
</style>'+
N'<H2><font color="Red">Delivery Pending Orders - Red Card</H2>' +
N'<table id="box-table" >' +
N'<tr><font color="Green">
<th>Customer</th>
<th>Order No</th>
<th>Customer PO</th>
<th>Stock Item</th>
<th>Confirm Date</th>
<th>Balance Qty</th>
<th>Onhand Fg</th>
<th>Onhand Sfg</th>
<th>Delay (Days)</th>
</tr>' +
CAST((
IF OBJECT_ID('tempdb..#StockManagement') IS NOT NULL DROP TABLE #StockManagement;
SELECT
STOCKCODE,
CAST(SUM((CASE WHEN TransactionTypeId IN (1, 4, 5, 7, 8, 10, 11, 13, 14) AND StockTypeId = 2 THEN TransactionQty ELSE 0 END)
- (CASE WHEN TransactionTypeId IN (2, 3, 6, 9, 12) AND StockTypeId = 2 THEN TransactionQty ELSE 0 END)) AS numeric(10, 0)) AS Fg,
CAST(SUM((CASE WHEN TransactionTypeId IN (1, 4, 5, 7, 8, 10, 11, 13, 14) AND StockTypeId = 3 THEN TransactionQty ELSE 0 END)
- (CASE WHEN TransactionTypeId IN (2, 3, 6, 9, 12) AND StockTypeId = 3 THEN TransactionQty ELSE 0 END)) AS numeric(10, 0)) AS Sfg
INTO #StockManagement
FROM STOCKMANAGEMENT
group by StockCode;
WITH MyResultSet AS (
SELECT
c.Name,
so.OrderNo,
so.CustomerPO,
so.StockCode + ' - ' + so.Description1 AS StockItem,
CONVERT(varchar(10), sod.RequestDate, 103) AS RequestDate,
CONVERT(varchar(10), sod.ConfirmDate, 103) AS ConfirmDate,
sod.DueQty AS DueQty,
sum(sm.Fg) OnhandFg,
sum(sm.SFg) AS OnhandSfg,
DATEDIFF(D, sod.RequestDate, GETDATE()) AS Delay,
1 AS ReferenceNo
FROM SalesOrderDet sod
INNER JOIN SalesOrder so ON sod.OrderId = so.Uniid
INNER JOIN StockItem si ON sod.StockCode = so.Uniid
INNER JOIN Customer c ON so.CustomerId = c.Uniid
LEFT OUTER JOIN #StockManagement sm ON so.Uniid = sm.StockCode
WHERE (DATEDIFF(D, sod.RequestDate, GETDATE()) > 0)
AND (sod.DueQty > 0)
AND (so.StatusId = 3)
AND (sod.Suppress <> 1)
GROUP BY so.OrderNo, c.Name, so.CustomerPO, so.StockCode + ' - ' + so.Description1, sod.RequestDate, sod.ConfirmDate, sod.DueQty, so.StatusId, DATEDIFF(D, sod.RequestDate, GETDATE()), so.Uniid
UNION ALL
SELECT
c.Name,
so.OrderNo,
so.CustomerPO,
so.StockCode + ' - ' + so.Description1 AS StockItem,
CONVERT(varchar(10), sod.RequestDate, 103) AS RequestDate,
CONVERT(varchar(10), sod.ConfirmDate, 103) AS ConfirmDate,
sod.DueQty AS DueQty,
sum(sm.Fg) OnhandFg,
sum(sm.SFg) AS OnhandSfg,
DATEDIFF(D, sod.ConfirmDate, GETDATE()) AS Delay,
2 AS ReferenceNo
FROM SalesOrderDet
INNER JOIN SalesOrder ON sod.OrderId = so.Uniid
INNER JOIN StockItem ON sod.StockCode = so.Uniid
INNER JOIN Customer ON so.CustomerId = c.Uniid
LEFT OUTER JOIN #StockManagement sm ON so.Uniid = sm.StockCode
WHERE (DATEDIFF(D, sod.ConfirmDate, GETDATE()) > 0)
AND (sod.DueQty > 0)
AND (so.StatusId = 3)
AND (sod.Suppress <> 1)
GROUP BY so.OrderNo, c.Name, so.CustomerPO, so.StockCode + ' - ' + so.Description1, sod.ConfirmDate, sod.RequestDate,sod.DueQty, so.StatusId, DATEDIFF(D, sod.ConfirmDate, GETDATE()), so.Uniid
)
SELECT CAST(
(
--SELECT (
SELECT *
FROM MyResultSet Sub
--)
FOR XML PATH('tr'), TYPE
)
AS NVARCHAR(MAX)) + N'</table>'
EXEC msdb.dbo.sp_send_dbmail
March 2, 2016 at 1:39 am
Run the result of the query into a #temp table and reference the temp table in your email body.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 3, 2016 at 3:17 pm
You have misplaced pieces of your code.
These don't belong together:
...
'</tr>' +
CAST((
IF OBJECT_ID('tempdb..#StockManagement') IS NOT NULL DROP TABLE #StockManagement;
SELECT
STOCKCODE,
CAST(SUM((CASE WHEN TransactionTypeId IN (1, 4, 5, 7, 8, 10, 11, 13, 14) AND StockTypeId = 2 THEN TransactionQty ELSE 0 END)
...
_____________
Code for TallyGenerator
March 3, 2016 at 6:01 pm
...and thing like this...
WHERE (DATEDIFF(D, sod.ConfirmDate, GETDATE()) > 0)
... make it impossible to do index seeks and generally slow code way down because it has to calculate for every row before applying the criteria. Anywhere that you've done such a thing anywhere from the FROM clause down is going to be a problem. You need to rewrite it so that soc.ConfirmDate (in this case) is "stand alone" on one side of the relational operator.
We can show you if you get stuck but give it a try on your own, first.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 3, 2016 at 6:38 pm
i am unable to understand your suggestion clearly.
Please show me where to do amendments in the query.
Thank you.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply