Hi All,
Hoping you can help.
We have an app that displays a grid. We can add custom fields to the grid using subqueries with which I am struggling. The main grid query looks like this.
SELECT TOP 300000
'' AS alloc_status
,'' AS stock_status
,wo_description
,wo_quantity
,wo_number
,wo_pwos_id
,vad_variant_code
,oh_order_number
,oh_datetime
,ohd_dm_id
,ohd_customer_name
,vad_description AS vad_description_Condition
,vad_variant_code AS vad_variant_code_Condition
,wo_id AS key_id
FROM works_order
LEFT OUTER JOIN works_order_analysis
ON works_order_analysis.woa_wo_id = works_order.wo_id
LEFT OUTER JOIN works_order_process
ON works_order_process.wop_id = works_order.wo_current_wop_id
LEFT OUTER JOIN works_order_process_analysis
ON works_order_process_analysis.wopa_wop_id = works_order_process.wop_id
INNER JOIN variant_detail
ON variant_detail.vad_id = works_order.wo_vad_id
LEFT OUTER JOIN works_order_total
ON works_order.wo_id = works_order_total.wot_wo_id
LEFT JOIN order_line_item
ON oli_id = wo_oli_id
LEFT JOIN order_header
ON oh_id = oli_oh_id
LEFT JOIN job_number
ON jn_id = wo_jn_id
LEFT JOIN works_order_process_subcontract_analysis
ON wopsa_wop_id = wop_id
LEFT JOIN order_header_detail
ON ohd_oh_id = oh_id
LEFT JOIN customer_detail
ON cd_id = oh_cd_id
WHERE ((cd_ow_account = 'NOTHS')
AND (wo_pwos_id = 1)
OR (cd_ow_account = 'EBAY')
AND (wo_pwos_id = 1)
OR (cd_ow_account = '4008')
AND (wo_pwos_id = 1)
OR (cd_ow_account = 'TRUSCA')
AND (wo_pwos_id = 1))
AND ((wo_required_datetime <= '2016-12-24 23:59:59'
OR wo_required_datetime IS NULL)
AND (wo_wos_id <> 4)
AND (wo_kit = 0))
The subquery is to basically provide a column that looks at the sales order and returns a count of row within that order. When I do this I get the following:
There was a problem retrieving the data:
subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, < etc
or when the subquery is used as an expression
Below is the query complete with subquery. If anyone can help that would be amazing
SELECT TOP 300000
'' AS alloc_status
,'' AS stock_status
,wo_description
,wo_quantity
,wo_number
,wo_pwos_id
,vad_variant_code
,oh_order_number
,oh_datetime
,ohd_dm_id
,ohd_customer_name
,(SELECT
COUNT(order_line_item.oli_id) AS 'Count'
FROM dbo.order_line_item
INNER JOIN dbo.order_header
ON order_line_item.oli_oh_id = order_header.oh_id
INNER JOIN dbo.variant_detail
ON order_line_item.oli_vad_id = variant_detail.vad_id
INNER JOIN dbo.variant_setting
ON variant_setting.vas_vad_id = variant_detail.vad_id
WHERE variant_setting.vas_manufactured_variant = 1
GROUP BY order_header.oh_order_number)
AS CustomLineCount
,vad_description AS vad_description_Condition
,vad_variant_code AS vad_variant_code_Condition
,wo_id AS key_id
FROM works_order
LEFT OUTER JOIN works_order_analysis
ON works_order_analysis.woa_wo_id = works_order.wo_id
LEFT OUTER JOIN works_order_process
ON works_order_process.wop_id = works_order.wo_current_wop_id
LEFT OUTER JOIN works_order_process_analysis
ON works_order_process_analysis.wopa_wop_id = works_order_process.wop_id
INNER JOIN variant_detail
ON variant_detail.vad_id = works_order.wo_vad_id
LEFT OUTER JOIN works_order_total
ON works_order.wo_id = works_order_total.wot_wo_id
LEFT JOIN order_line_item
ON oli_id = wo_oli_id
LEFT JOIN order_header
ON oh_id = oli_oh_id
LEFT JOIN job_number
ON jn_id = wo_jn_id
LEFT JOIN works_order_process_subcontract_analysis
ON wopsa_wop_id = wop_id
LEFT JOIN order_header_detail
ON ohd_oh_id = oh_id
LEFT JOIN customer_detail
ON cd_id = oh_cd_id
WHERE ((cd_ow_account = 'NOTHS')
AND (wo_pwos_id = 1)
OR (cd_ow_account = 'EBAY')
AND (wo_pwos_id = 1)
OR (cd_ow_account = '4008')
AND (wo_pwos_id = 1)
OR (cd_ow_account = 'TRUSCA')
AND (wo_pwos_id = 1))
AND ((wo_required_datetime <= '2016-12-24 23:59:59'
OR wo_required_datetime IS NULL)
AND (wo_wos_id <> 4)
AND (wo_kit = 0))
