Here is my mysql_query i want to add this query into my main query but the problem is when im add it to query it show sub query returns more than one row
(
SELECT date(trn_orderheader_approvedby.dtApprovedDate) AS CREATE_DATE
FROM trn_orderheader_approvedby
WHERE trn_orderheader_approvedby.intApproveLevelNo IN('-10')
)
AS Order_COMPLETED_DATE
SELECT
TB1.*, (
SELECT
SUM(TRNS.dblQty *- 1)
FROM
ware_stocktransactions_bulk AS TRNS
WHERE
TRNS.intOrderNo = TB1.`NO`
AND TRNS.intOrderYear = TB1.`YEAR`
AND TRNS.intSalesOrderId = TB1.intSalesOrderId
AND TRNS.intItemId = TB1.ITEM_ID
AND TRNS.intLocationId = TB1.LOCATION_ID
AND TRNS.strType IN ('ISSUE', 'RETSTORES')
) AS QTY,
(
SELECT
SUM(
TRNS.dblQty * TRNS.dblGRNRate * mst_financeexchangerate.dblBuying *- 1
)
FROM
ware_stocktransactions_bulk AS TRNS
LEFT JOIN mst_financeexchangerate ON mst_financeexchangerate.dtmDate = TRNS.dtGRNDate
AND mst_financeexchangerate.intCompanyId = TRNS.intCompanyId
AND mst_financeexchangerate.intCurrencyId = TRNS.intCurrencyId
WHERE
TRNS.intOrderNo = TB1.`NO`
AND TRNS.intOrderYear = TB1.`YEAR`
AND TRNS.intSalesOrderId = TB1.intSalesOrderId
AND TRNS.intItemId = TB1.ITEM_ID
AND TRNS.intLocationId = TB1.LOCATION_ID
AND TRNS.strType IN ('ISSUE', 'RETSTORES')
) AS AMOUNT,
(
SELECT
SUM(
costing_sample_header.INK_COST
)
FROM
costing_sample_header
WHERE
TB1.intSampleNo = costing_sample_header.SAMPLE_NO
AND TB1.intSampleYear = costing_sample_header.SAMPLE_YEAR
AND TB1.intRevisionNo = costing_sample_header.REVISION
AND TB1.strCombo = costing_sample_header.COMBO
AND TB1.strPrintName = costing_sample_header.PRINT
) AS INK_COST,
(
SELECT
SUM(
costing_sample_header.SPECIAL_RM_COST
)
FROM
costing_sample_header
WHERE
TB1.intSampleNo = costing_sample_header.SAMPLE_NO
AND TB1.intSampleYear = costing_sample_header.SAMPLE_YEAR
AND TB1.intRevisionNo = costing_sample_header.REVISION
AND TB1.strCombo = costing_sample_header.COMBO
AND TB1.strPrintName = costing_sample_header.PRINT
) AS RM_COST
FROM
(
SELECT DISTINCT
tempory_rm_moved_orders_summery2.`NO`,
tempory_rm_moved_orders_summery2.`YEAR`,
trn_orderdetails.intSalesOrderId,
trn_orderdetails.strSalesOrderNo,
trn_orderdetails.strGraphicNo,
trn_orderdetails.intSampleNo,
trn_orderdetails.intSampleYear,
trn_orderdetails.intRevisionNo,
trn_orderdetails.strCombo,
trn_orderdetails.strPrintName,
mst_part.strName AS part,
mst_locations.intId AS LOCATION_ID,
mst_locations.strName AS LOCATION,
trn_orderdetails.intQty,
mst_maincategory.strName AS MAIN_CATEGORY,
mst_subcategory.strName AS SUB_CATEGORY,
mst_item.intId AS ITEM_ID,
mst_item.strCode AS ITEM_CODE,
mst_item.strName AS ITEM_NAME,
mst_units.strCode AS UOM,
trn_po_prn_details_sales_order.PRODUCTION_QTY,
/* trn_po_prn_details_sales_order.CONS_PC,
trn_po_prn_details_sales_order.PRODUCTION_QTY,
trn_po_prn_details_sales_order.REQUIRED, sum( ifnull(
ware_stocktransactions_bulk.dblQty *- 1, 0 ) ) AS qty,*/
/*sum( ifnull( ware_stocktransactions_bulk.dblQty * ware_stocktransactions_bulk.dblGRNRate * mst_financeexchangerate.dblBuying *- 1, 0 ) ) AS amount, */
trn_orderdetails.dblPrice,
mst_customer.strName AS CUSTOMER_NAME,
mst_brand.strName AS BRAND_NAME,
trn_orderheader.intStatus,
IF (
trn_orderheader.intStatus = 1,
'Approved',
IF (
trn_orderheader.intStatus = 0,
'Rejected',
IF (
trn_orderheader.intStatus =- 10,
'Completed',
IF (
trn_orderheader.intStatus =- 2,
'Cancel',
IF (
trn_orderheader.intStatus =- 1,
'Revised',
'Pending'
)
)
)
)
) AS STATUS,
trn_orderdetails.dtDeliveryDate,
mst_technique_groups.TECHNIQUE_GROUP_NAME
FROM
ware_stocktransactions_bulk
INNER JOIN tempory_rm_moved_orders_summery2 ON ware_stocktransactions_bulk.intOrderNo = tempory_rm_moved_orders_summery2.`NO`
AND ware_stocktransactions_bulk.intOrderYear = tempory_rm_moved_orders_summery2.`YEAR`
AND date(
ware_stocktransactions_bulk.dtDate
) >= '2017-06-01'
INNER JOIN trn_orderdetails ON ware_stocktransactions_bulk.intOrderNo = trn_orderdetails.intOrderNo
AND ware_stocktransactions_bulk.intOrderYear = trn_orderdetails.intOrderYear
AND ware_stocktransactions_bulk.intSalesOrderId = trn_orderdetails.intSalesOrderId
INNER JOIN mst_part ON trn_orderdetails.intPart = mst_part.intId
INNER JOIN trn_orderheader ON trn_orderdetails.intOrderNo = trn_orderheader.intOrderNo
AND trn_orderdetails.intOrderYear = trn_orderheader.intOrderYear
INNER JOIN mst_item ON ware_stocktransactions_bulk.intItemId = mst_item.intId
INNER JOIN mst_maincategory ON mst_item.intMainCategory = mst_maincategory.intId
INNER JOIN mst_subcategory ON mst_item.intSubCategory = mst_subcategory.intId
INNER JOIN mst_units ON mst_item.intUOM = mst_units.intId
INNER JOIN mst_locations ON ware_stocktransactions_bulk.intLocationId = mst_locations.intId
INNER JOIN mst_customer ON trn_orderheader.intCustomer = mst_customer.intId
INNER JOIN trn_sampleinfomations ON trn_orderdetails.intSampleNo = trn_sampleinfomations.intSampleNo
AND trn_orderdetails.intSampleYear = trn_sampleinfomations.intSampleYear
AND trn_orderdetails.intRevisionNo = trn_sampleinfomations.intRevisionNo
INNER JOIN mst_brand ON trn_sampleinfomations.intBrand = mst_brand.intId
INNER JOIN mst_technique_groups ON trn_orderdetails.TECHNIQUE_GROUP_ID = mst_technique_groups.TECHNIQUE_GROUP_ID
LEFT JOIN trn_po_prn_details_sales_order ON ware_stocktransactions_bulk.intOrderYear = trn_po_prn_details_sales_order.ORDER_NO
AND ware_stocktransactions_bulk.intOrderYear = trn_po_prn_details_sales_order.ORDER_YEAR
AND ware_stocktransactions_bulk.intSalesOrderId = trn_po_prn_details_sales_order.SALES_ORDER
AND ware_stocktransactions_bulk.intItemId = trn_po_prn_details_sales_order.ITEM
WHERE
ware_stocktransactions_bulk.strType IN ('ISSUE', 'RETSTORES')
AND ware_stocktransactions_bulk.intOrderNo > 0
AND trn_po_prn_details_sales_order.ITEM IS NULL
/* GROUP BY ware_stocktransactions_bulk.intLocationId, ware_stocktransactions_bulk.intOrderNo, ware_stocktransactions_bulk.intOrderYear, ware_stocktransactions_bulk.intSalesOrderId, ware_stocktransactions_bulk.intItemId */
) AS TB1here
