Jorgen V Jorgen V - 3 months ago 14
SQL Question

Getting a second query as a field in a first one

I'm fairly new to writing queries and I'm struggling with getting the next to queries combined:

SELECT od.ODCode
, c.Description as 'Line Position'
, ps.Description as 'Pallet Stacking'
, pt.Description as 'Pallet Type'
, odex.PalletRows
, odex.PalletTypeID
,CONCAT(( SELECT sp.SpecPropertyValue FROM SpecificationProperty sp
INNER JOIN specificationclass sc ON sc.specclassid = sp.specclassid
INNER JOIN specificationcode sco ON sco.specclassid = sc.specclassid and sco.speccodeid = pt.pallettypeid
INNER JOIN SpecificationClassProperty scp ON scp.specClassid = sp.specclassid and scp.specpropertyid = sp.specpropertyid and sco.speccodeid = sp.speccodeid
WHERE scp.specpropertyname = 'DocDir'and sc.specclassname = 'Pallettypedoc'
),
(SELECT sp.SpecPropertyValue FROM SpecificationProperty sp
INNER JOIN specificationclass sc ON sc.specclassid = sp.specclassid
INNER JOIN specificationcode sco ON sco.specclassid = sc.specclassid and sco.speccodeid = pt.pallettypeid
INNER JOIN SpecificationClassProperty scp ON scp.specClassid = sp.specclassid and scp.specpropertyid = sp.specpropertyid and sco.speccodeid = sp.speccodeid
WHERE scp.specpropertyname = 'Documentname'and sc.specclassname = 'Pallettypedoc' )) AS Spec
FROM ODExitPallet odex
INNER JOIN OperationDescr od
ON od.OperationDescrID=odex.OperationDescrID
INNER JOIN PalletStacking ps
ON ps.PalletStackingID=odex.PalletStackingID
INNER JOIN PalletType pt
ON pt.PalletTypeID=odex.PalletTypeID
INNER JOIN Code c
on c.CodeNumber=odex.LinePosition and c.CodeTypeID=72
INNER JOIN WorkOrder wo
on wo.OperationDescrID=odex.OperationDescrID
WHERE wo.WorkOrderID = @WorkOrderID


and

SELECT SUM(PlannedBatch) / SUM(NominalBlanks * Stacks) as NrOfPallets
FROM (SELECT WO.PlannedBatch, WO.NominalBlanks, ODEP.OperationDescrID, LinePosition,
Case when COUNT(*) = 0 then 1
else COUNT(*)
end as Stacks
FROM WorkOrder Wo
INNER JOIN ODExitPallet ODEX
ON WO.operationDescrID = ODEX.OperationDescrID
INNER JOIN Stacking ST
ON ST.PalletStackingID = ODEX.PalletStackingID
WHERE WO.WorkOrderID = @WorkOrderID
GROUP BY PlannedBatch, NominalBlanks, ODEX.OperationDescrID, LinePosition
) TOTAL


So I want to add the second query as a field in my first one. I've tried it with numerous thing like parentheses and stuff but I keep getting errors. Can someone enlighten me?

Answer

insert ( ) in whole second Query but make sure you second query return only 1 row

SELECT od.ODCode
    , c.Description as 'Line Position'
    , ps.Description as 'Pallet Stacking'
    , pt.Description as 'Pallet Type'
    , odex.PalletRows
    , odex.PalletTypeID
    ,CONCAT(
            (
                SELECT sp.SpecPropertyValue 
                FROM SpecificationProperty sp 
                INNER JOIN specificationclass sc ON sc.specclassid = sp.specclassid 
                INNER JOIN specificationcode sco ON sco.specclassid = sc.specclassid and sco.speccodeid = pt.pallettypeid
                INNER JOIN SpecificationClassProperty scp ON scp.specClassid = sp.specclassid and scp.specpropertyid = sp.specpropertyid and sco.speccodeid = sp.speccodeid 
                WHERE scp.specpropertyname  = 'DocDir'and sc.specclassname = 'Pallettypedoc' 
            )
            ,
            (
                SELECT sp.SpecPropertyValue FROM SpecificationProperty sp 
                INNER JOIN specificationclass sc ON sc.specclassid = sp.specclassid 
                INNER JOIN specificationcode sco ON sco.specclassid = sc.specclassid and sco.speccodeid = pt.pallettypeid
                INNER JOIN SpecificationClassProperty scp ON scp.specClassid = sp.specclassid and scp.specpropertyid = sp.specpropertyid and sco.speccodeid = sp.speccodeid 
                WHERE scp.specpropertyname  = 'Documentname'and sc.specclassname = 'Pallettypedoc' 
            )
    ) AS Spec
    ,(
        SELECT SUM(PlannedBatch) / SUM(NominalBlanks * Stacks)
        FROM (
            SELECT WO.PlannedBatch, WO.NominalBlanks, ODEP.OperationDescrID, LinePosition, 
                    Case when COUNT(*) = 0 then 1
                                            else COUNT(*)
                        end as Stacks
                FROM WorkOrder Wo
                    INNER JOIN ODExitPallet ODEX
                            ON WO.operationDescrID = ODEX.OperationDescrID
                    INNER JOIN Stacking ST
                            ON ST.PalletStackingID = ODEX.PalletStackingID
                WHERE WO.WorkOrderID = @WorkOrderID
            GROUP BY  PlannedBatch, NominalBlanks, ODEX.OperationDescrID, LinePosition
        ) TOTAL
    ) AS [NrOfPallets]
FROM ODExitPallet odex
INNER JOIN OperationDescr od ON od.OperationDescrID=odex.OperationDescrID
INNER JOIN PalletStacking ps ON ps.PalletStackingID=odex.PalletStackingID
INNER JOIN PalletType pt ON pt.PalletTypeID=odex.PalletTypeID
INNER JOIN Code c on c.CodeNumber=odex.LinePosition and c.CodeTypeID=72
INNER JOIN WorkOrder wo on wo.OperationDescrID=odex.OperationDescrID
WHERE wo.WorkOrderID = @WorkOrderID