CODEWITHSUNDEEP
sqlsql-servercorrelated-subquery
mreff555
mreff555

Reputation: 1111

SQL Server correlated queries

I currently have some SQL code which looks like this.

SELECT
    WORKORDER_BASE_ID As 'Work Order',
    SCHED_START_DATE AS 'Scheduled Start',
    SETUP_HRS AS 'Approx Setup Hrs',
    RUN_HRS AS 'Approx Run Hrs',
    (
        SELECT 
            PART_ID as "Components" 
        FROM 
            REQUIREMENT 
        WHERE 
            REQUIREMENT.WORKORDER_BASE_ID = OPERATION.WORKORDER_BASE_ID
            AND REQUIREMENT.WORKORDER_SUB_ID = OPERATION.WORKORDER_SUB_ID
            AND PART_ID LIKE '%PSU%'
    )   AS PSU
FROM 
    OPERATION
WHERE 
    OPERATION.STATUS = 'R' 
    AND RESOURCE_ID LIKE '%{Root Container.equipmentName}%'

I am receiving errors because the sub-query generates more than one field. What I need is a way to specify a condition to the sub-query to only display data pertaining to a the specific work order in that row, specifically the work order number. I'm guessing this is some sort of loop function.

Any suggestions?

BTW, the bottom line is correct. The platform I use interprets values in braces as local variables.

Upvotes: 4

Views: 132

Answers (1)

M.Ali
M.Ali

Reputation: 69524

A Sub-Query in select MUST return a scalar value. Use TOP 1 in your select and it should fix the issue. Or Alternately you can do the following ....

SELECT OPERATION.WORKORDER_BASE_ID   AS [Work Order]
      ,OPERATION.SCHED_START_DATE    AS [Scheduled Start]
      ,OPERATION.SETUP_HRS           AS [Approx Setup Hrs]
      ,OPERATION.RUN_HRS             AS [Approx Run Hrs]
      ,REQUIREMENT.PART_ID           AS [Components] 
FROM  OPERATION
INNER JOIN REQUIREMENT ON REQUIREMENT.WORKORDER_BASE_ID = OPERATION.WORKORDER_BASE_ID
                      AND REQUIREMENT.WORKORDER_SUB_ID = OPERATION.WORKORDER_SUB_ID
WHERE OPERATION.[STATUS] ='R' 
  AND OPERATION.RESOURCE_ID LIKE '%{Root Container.equipmentName}%'
  AND REQUIREMENT.PART_ID LIKE '%PSU%'

Upvotes: 1

Related Questions