Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that c-CodePudding

SELECT
    local_upc,
    shopify_id,
    sku,
    description1,
    description2,
    SIZ,
    ATTR,
    base_price,
    retail_price,
    SUM(oh_qty) oh_qty,
    SUM(available_qty) available_qty,
    SUM(so_qty) so_qty,
    MAX(qty_update) qty_update,
    MAX(price_update) price_update
FROM
    (
    SELECT
        i.local_upc,
        c.s_varrient_id AS shopify_id,
        c.sku,
        i.description1,
        i.description2,
        i.SIZ,
        i.ATTR,
        cp.pl_1 AS base_price,
        cp.pl_2 AS retail_price,
        SUM(cq.oh_qty) oh_qty,
        SUM(cq.available_qty) available_qty,
        SUM(cq.so_qty) so_qty,
        (
            TO_CHAR(
                cq.modified_date,
                'MM/DD/YYYY HH12:MI:SS AM'
            )
        ) AS qty_update,
        (
            TO_CHAR(
                cp.modified_date,
                'MM/DD/YYYY HH12:MI:SS AM'
            )
        ) AS price_update
    FROM
        inventory_c c,
        invn_sbs i,
        invn_price_c cp,
        invn_qty_c cq
    WHERE
        i.item_sid = c.item_sid AND i.item_Sid = cp.item_sid AND s_varrient_id IS NOT NULL AND i.item_Sid = cq.item_sid
    GROUP BY
        i.local_upc,
        c.s_varrient_id,
        c.sku,
        i.description1,
        i.description2,
        i.SIZ,
        i.ATTR,
        cp.pl_1,
        cp.pl_2,
        (
            TO_CHAR(
                cq.modified_date,
                'MM/DD/YYYY HH12:MI:SS AM'
            )
        ),
        (
            TO_CHAR(
                cp.modified_date,
                'MM/DD/YYYY HH12:MI:SS AM'
            )
        )
)
GROUP BY
    local_upc,
    shopify_id,
    sku,
    description1,
    description2,
    SIZ,
    ATTR,
    base_price,
    retail_price

I am using sql database with laravel 5.7 and don't know why it shows error apparently query looks good and is working in oracle database but not in sql, i have created all tables and all keywords are clearly defined. This query is used for oracle database(plsql) and is working fine with oracle. what i want to do is to create sql database and run this query using laravel.

CodePudding user response：

If you have fixed the to_char issue you should have a Error Code: 1248. Every derived table must have its own alias

so give it one

SELECT
    local_upc,
    shopify_id,
    sku,
    description1,
    description2,
    SIZ,
    ATTR,
    base_price,
    retail_price,
    SUM(oh_qty) oh_qty,
    SUM(available_qty) available_qty,
    SUM(so_qty) so_qty,
    MAX(qty_update) qty_update,
    MAX(price_update) price_update
FROM
    (
    SELECT
        i.local_upc,
        c.s_varrient_id AS shopify_id,
        c.sku,
        i.description1,
        i.description2,
        i.SIZ,
        i.ATTR,
        cp.pl_1 AS base_price,
        cp.pl_2 AS retail_price,
        SUM(cq.oh_qty) oh_qty,
        SUM(cq.available_qty) available_qty,
        SUM(cq.so_qty) so_qty#,
        #(TO_CHAR(cq.modified_date,'MM/DD/YYYY HH12:MI:SS AM')) AS qty_update,
        #(TO_CHAR(cp.modified_date,'MM/DD/YYYY HH12:MI:SS AM')) AS price_update
    FROM
        inventory_c c,invn_sbs i,invn_price_c cp,invn_qty_c cq
    WHERE
        i.item_sid = c.item_sid AND i.item_Sid = cp.item_sid AND s_varrient_id IS NOT NULL AND i.item_Sid = cq.item_sid
    GROUP BY
        i.local_upc,
        c.s_varrient_id,
        c.sku,
        i.description1,
        i.description2,
        i.SIZ,
        i.ATTR,
        cp.pl_1,
        cp.pl_2#,
        #(TO_CHAR(cq.modified_date,'MM/DD/YYYY HH12:MI:SS AM')),
        #(TO_CHAR(cp.modified_date,'MM/DD/YYYY HH12:MI:SS AM'))
 ) aliasedsubqueryname #to whatever you want
GROUP BY
    local_upc,
    shopify_id,
    sku,
    description1,
    description2,
    SIZ,
    ATTR,
    base_price,
    retail_price