-1

I try to follow the solutions offered here but it doesn't work in my case.

1) Here is the 1st solution I tried but raised the following error:

SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.SKU, maxdate) WHERE inv.outbound_date IS NOT NULL AND co.system='Magent' at line 26

    SELECT
        inv.inventory_id, 
        pu.purchase_order_id, 
        inv.inbound_date, 
        inv.outbound_date,
        siz.SKU,
        cat.name,
        pu.cogs,
        pu.cogs_currency,
        co.system,
        co.order_number
    FROM ERP_INVENTORY_ITEM inv
        INNER JOIN ERP_PROD_PRODUCT_SIZES siz ON siz.ID = inv.prod_index
        INNER JOIN ERP_PURCHASE_ITEM pu ON pu.`Index` = inv.ERP_PURCHASE_ITEM_Index
        INNER JOIN ERP_PROD_CATALOG cat ON cat.`index` = siz.`SKUf ID`
        INNER JOIN ERP_CUSTOMER_ORDER_ITEMS_2 coi ON coi.`index` = inv.ERP_CUSTOMER_ORDER_ITEMS_2_index_outbound
        INNER JOIN ERP_CUSTOMER_ORDER co ON co.`index` = coi.ERP_CUSTOMER_ORDER_index

        INNER JOIN (
          SELECT siz2.SKU, MAX(inv2.inbound_date) AS maxdate
          FROM ERP_INVENTORY_ITEM inv2
          INNER JOIN ERP_PROD_PRODUCT_SIZES siz2 ON siz2.ID = inv2.prod_index
          GROUP BY siz2.SKU
        ) AS max USING (siz2.SKU, maxdate)

    WHERE inv.outbound_date IS NOT NULL
       AND co.system='Magento'
       AND inv.item_warehouse='KL'
       AND pu.cogs IS NOT NULL
       AND LOCATE('-',co.order_number)=FALSE
       AND siz.SKU IN
            (
            'SPRA0148002R0303-54',
            'SRYB2658004P0303-50',
            'SOAK0632006R0105-55',
            'SRYB0372080R0707-50',
            'SRYB0226048P1406-58',
            'SRBJ0030048R1406-50',
            'SOAK0281046R0203-70',
            'SRYB2671020R1406-58',
            'SVAL0170005R0101-51',
            'SOAK0091035R0506-56',
            'SMAU0038046P1205-71',
            'GVER054500501-53',
            'GRYB009302401-52',
            'AC05-88N0275Z00000Z',
            'BF02-87N0400N12518Z',
            'BF02-87N1000N07502Z',
            'GCAR019705306-54',
            'PF01-86Z0000Z00000Z',
            'BBT05-86N0150N20018Z',
            'AC11-87N0425Z00000Z',
            'BF01-86N0350Z00000Z',
            'PUV03-87N0700N07516Z',
            'BF02-87N0100N12518Z',
            'BF02-87N0750N22517Z',
            'BF02-87N0550N12509Z',
            'AC02-85N0300Z00000Z',
            'PC02-86N0650Z00000Z',
            'AC06-86N0550N22518Z',
            'BM03-87N0250N17512Z',
            'CV53-86N0300Z00000Z',
            'PUV03-87N0550N17509Z',
            'BBT04-86N0250N25002Z',
            'BI20-86Z0000Z00000Z',
            'SGMV0612005R0101-00',
            'VE04-86N0400Z00000Z',
            'GSFD020111-59',
            'FL17-86N0225Z00000Z',
            'FL18-86N0325Z00000Z',
            'GMC02-86N0375Z00000Z',
            'BE06-86N0175Z00000Z',
            'PM01-86Z0000Z00000Z',
            'PM02-86N0450Z00000Z',
            'GMC55-87N0650Z00000Z',
            'GCN05-87N0350Z00000Z',
            'GMC01-86N0300Z00000Z'
            )
    ORDER BY siz.SKU;

   

2) Here is the 2nd solution I tried but the number of results doesn't match the number of results if I do a simple GROUP BY

SELECT
  inv.inventory_id, 
  pu.purchase_order_id, 
  inv.inbound_date, 
  inv.outbound_date,
  siz.SKU,
  cat.name,
  pu.cogs,
  pu.cogs_currency,
  co.system,
  co.order_number
FROM ERP_INVENTORY_ITEM inv
    INNER JOIN ERP_PROD_PRODUCT_SIZES siz ON siz.ID = inv.prod_index
    INNER JOIN ERP_PURCHASE_ITEM pu ON pu.`Index` = inv.ERP_PURCHASE_ITEM_Index
    INNER JOIN ERP_PROD_CATALOG cat ON cat.`index` = siz.`SKUf ID`
    INNER JOIN ERP_CUSTOMER_ORDER_ITEMS_2 coi ON coi.`index` = inv.ERP_CUSTOMER_ORDER_ITEMS_2_index_outbound
    INNER JOIN ERP_CUSTOMER_ORDER co ON co.`index` = coi.ERP_CUSTOMER_ORDER_index
WHERE inv.outbound_date IS NOT NULL
    AND inv.inbound_date =
        (
        SELECT MAX(inv2.inbound_date)
        FROM ERP_INVENTORY_ITEM inv2
        INNER JOIN ERP_PROD_PRODUCT_SIZES siz2 ON siz2.ID=inv2.prod_index
        WHERE siz2.SKU=siz.SKU
        )
    AND co.system='Magento'
    AND inv.item_warehouse='KL'
    AND pu.cogs IS NOT NULL
    AND LOCATE('-',co.order_number)=FALSE
    AND siz.SKU IN
       (
    'SPRA0148002R0303-54',
    'SRYB2658004P0303-50',
    'SOAK0632006R0105-55',
    'SRYB0372080R0707-50',
    'SRYB0226048P1406-58',
    'SRBJ0030048R1406-50',
    'SOAK0281046R0203-70',
    'SRYB2671020R1406-58',
    'SVAL0170005R0101-51',
    'SOAK0091035R0506-56',
    'SMAU0038046P1205-71',
    'GVER054500501-53',
    'GRYB009302401-52',
    'AC05-88N0275Z00000Z',
    'BF02-87N0400N12518Z',
    'BF02-87N1000N07502Z',
    'GCAR019705306-54',
    'PF01-86Z0000Z00000Z',
    'BBT05-86N0150N20018Z',
    'AC11-87N0425Z00000Z',
    'BF01-86N0350Z00000Z',
    'PUV03-87N0700N07516Z',
    'BF02-87N0100N12518Z',
    'BF02-87N0750N22517Z',
    'BF02-87N0550N12509Z',
    'AC02-85N0300Z00000Z',
    'PC02-86N0650Z00000Z',
    'AC06-86N0550N22518Z',
    'BM03-87N0250N17512Z',
    'CV53-86N0300Z00000Z',
    'PUV03-87N0550N17509Z',
    'BBT04-86N0250N25002Z',
    'BI20-86Z0000Z00000Z',
    'SGMV0612005R0101-00',
    'VE04-86N0400Z00000Z',
    'GSFD020111-59',
    'FL17-86N0225Z00000Z',
    'FL18-86N0325Z00000Z',
    'GMC02-86N0375Z00000Z',
    'BE06-86N0175Z00000Z',
    'PM01-86Z0000Z00000Z',
    'PM02-86N0450Z00000Z',
    'GMC55-87N0650Z00000Z',
    'GCN05-87N0350Z00000Z',
    'GMC01-86N0300Z00000Z'
    )
ORDER BY siz.SKU;

Any help would be appreciate.

Thx.

Community
  • 1
  • 1
seeusoon
  • 45
  • 6
  • 2
    Change the alias name `max` to something else since `max` is a reserved word. `) AS max USING` may be to `) AS max_val USING` – Abhik Chakraborty Jan 19 '16 at 09:46
  • As the error message says, the problem is on `.SKU`. Remove the table name (`siz2`), make it `USING (SKU, maxdate)`. – axiac Jan 19 '16 at 09:54
  • @Abhik Chakraborty I changed it but I'm facing the same error. – seeusoon Jan 19 '16 at 10:58
  • @axiac I remove the alias and now a new error is raised: SQL Error (1054): Unknown column 'maxdate' in 'from clause' – seeusoon Jan 19 '16 at 11:01
  • Note that `siz2.SKU` is a part of the derived query hence you need to use the alias name used for the derived query something as `GROUP siz2.SKU ) AS max_val USING (max_val.SKU, max_val.maxdate)` – Abhik Chakraborty Jan 19 '16 at 11:04
  • @AbhikChakraborty I face the same error as the beginning INNER JOIN ( SELECT siz2.SKU, MAX(inv2.inbound_date) AS maxdate FROM ERP_INVENTORY_ITEM inv2 INNER JOIN ERP_PROD_PRODUCT_SIZES siz2 ON siz2.ID = inv2.prod_index GROUP BY siz2.SKU ) AS max_val USING (max_val.SKU, max_val.maxdate) – seeusoon Jan 19 '16 at 11:10
  • now when you do inner join you need to specify with which table you are doing the join you have `USING (max_val.SKU, max_val.maxdate)` but with which table ? so better to have it as `inner join (select....) as max_val on sometable.SKU = max_val.SKU and sometable.date_val = max_val.maxdate` – Abhik Chakraborty Jan 19 '16 at 11:16
  • Hi @AbhikChakraborty, I followed your solution and it's almost working but I'm still facing the problem that I defined below. Do you have any idea about that issue? Thx. – seeusoon Jan 26 '16 at 03:38

1 Answers1

0

Here is the "almost" working solution I have so far :
No error is raised but I get 13 results instead of 17.
I get 17 results if I remove the last INNER JOIN and add just a GROUP BY siz.SKU

Thanks.

    SELECT
        inv.inventory_id, 
        pu.purchase_order_id, 
        inv.inbound_date, 
        inv.outbound_date,
        siz.SKU,
        cat.name,
        pu.cogs,
        pu.cogs_currency,
        co.system,
        co.order_number
    FROM ERP_INVENTORY_ITEM inv
        INNER JOIN ERP_PROD_PRODUCT_SIZES siz ON siz.ID = inv.prod_index
        INNER JOIN ERP_PURCHASE_ITEM pu ON pu.`Index` = inv.ERP_PURCHASE_ITEM_Index
        INNER JOIN ERP_PROD_CATALOG cat ON cat.`index` = siz.`SKUf ID`
        INNER JOIN ERP_CUSTOMER_ORDER_ITEMS_2 coi ON coi.`index` = inv.ERP_CUSTOMER_ORDER_ITEMS_2_index_outbound
        INNER JOIN ERP_CUSTOMER_ORDER co ON co.`index` = coi.ERP_CUSTOMER_ORDER_index

        INNER JOIN (
          SELECT siz2.SKU AS SKU_2, MAX(inv2.inbound_date) AS maxdate
          FROM ERP_INVENTORY_ITEM inv2
          INNER JOIN ERP_PROD_PRODUCT_SIZES siz2 ON siz2.ID = inv2.prod_index
          GROUP BY siz2.SKU
        ) AS max_val ON siz.SKU = max_val.SKU_2 AND inv.inbound_date = max_val.maxdate

    WHERE inv.outbound_date IS NOT NULL
       AND co.system='Magento'
       AND inv.item_warehouse='KL'
       AND pu.cogs IS NOT NULL
       AND LOCATE('-',co.order_number)=FALSE
       AND siz.SKU IN
            (
            'SPRA0148002R0303-54',
            'SRYB2658004P0303-50',
            'SOAK0632006R0105-55',
            'SRYB0372080R0707-50',
            'SRYB0226048P1406-58',
            'SRBJ0030048R1406-50',
            'SOAK0281046R0203-70',
            'SRYB2671020R1406-58',
            'SVAL0170005R0101-51',
            'SOAK0091035R0506-56',
            'SMAU0038046P1205-71',
            'GVER054500501-53',
            'GRYB009302401-52',
            'AC05-88N0275Z00000Z',
            'BF02-87N0400N12518Z',
            'BF02-87N1000N07502Z',
            'GCAR019705306-54',
            'PF01-86Z0000Z00000Z',
            'BBT05-86N0150N20018Z',
            'AC11-87N0425Z00000Z',
            'BF01-86N0350Z00000Z',
            'PUV03-87N0700N07516Z',
            'BF02-87N0100N12518Z',
            'BF02-87N0750N22517Z',
            'BF02-87N0550N12509Z',
            'AC02-85N0300Z00000Z',
            'PC02-86N0650Z00000Z',
            'AC06-86N0550N22518Z',
            'BM03-87N0250N17512Z',
            'CV53-86N0300Z00000Z',
            'PUV03-87N0550N17509Z',
            'BBT04-86N0250N25002Z',
            'BI20-86Z0000Z00000Z',
            'SGMV0612005R0101-00',
            'VE04-86N0400Z00000Z',
            'GSFD020111-59',
            'FL17-86N0225Z00000Z',
            'FL18-86N0325Z00000Z',
            'GMC02-86N0375Z00000Z',
            'BE06-86N0175Z00000Z',
            'PM01-86Z0000Z00000Z',
            'PM02-86N0450Z00000Z',
            'GMC55-87N0650Z00000Z',
            'GCN05-87N0350Z00000Z',
            'GMC01-86N0300Z00000Z'
            )
    ORDER BY siz.SKU
    ;
seeusoon
  • 45
  • 6