How can I put a condition in where clause such that if item NetSuite Professionals #suiteql

How can I put a condition in where clause such tha...

raghav

10/20/2023, 11:19 AM

How can I put a condition in where clause such that, if item fullfillment exist for a sales order then only make itemfullfillment maineline = ''t'

Copy code

SELECT
    soTrans.id AS so_internal_id,
    soTrans.tranid as so_id,
    ifTrans.id AS if_internal_id,
    ifTrans.tranid AS if_id,
    BUILTIN.DF (ifTrans.status)
FROM
    transaction AS soTrans
    INNER JOIN customer AS c ON soTrans.entity = c.id
    INNER JOIN TransactionLine AS soLine ON soLine.Transaction = soTrans.id
    INNER JOIN location AS l ON soLine.location = l.id
    LEFT OUTER JOIN  TransactionLine AS ifLine ON ifLine.createdfrom = soTrans.id
    LEFT OUTER JOIN  transaction AS ifTrans ON ifTrans.id = ifLine.Transaction
WHERE
    soTrans.type = 'SalesOrd'
    AND BUILTIN.DF (soTrans.status) IN ('Sales Order : Pending Fulfillment')
    AND soLine.mainline = 'T'
    AND ifLine.mainline = 'T'
    AND soTrans.entity = '20011'
    AND l.id = '80'
    AND soTrans.shipdate = '10/12/2023'

Have 6 sales order in the system out of which for 3 IF is created. While running the query its giving only 3 result for which there is IF instead of 6. Essentially want all 6 SOs even if there is no IFs.

raghav

10/20/2023, 11:41 AM

This is one potential way I could do without putting the ifLine.mainline = 'T' But I would like to avoid grouping, j Would like to use if.mainline = 'T' only when there is an IF created from that sales order.

Copy code

SELECT
    soTrans.id AS so_internal_id,
    soTrans.tranid as so_id,
    ifTrans.id AS if_internal_id,
    ifTrans.tranid AS if_id,
    ifTrans.custbody_kdl_id AS if_kdl_order_num,
FROM
    transaction AS soTrans
    INNER JOIN customer AS c ON soTrans.entity = c.id
    INNER JOIN TransactionLine AS soLine ON soLine.Transaction = soTrans.id
    INNER JOIN location AS l ON soLine.location = l.id
    LEFT OUTER JOIN TransactionLine AS ifLine ON ifLine.createdfrom = soTrans.id
    LEFT OUTER JOIN transaction AS ifTrans ON ifTrans.id = ifLine.Transaction
WHERE
    soTrans.type = 'SalesOrd'
    AND BUILTIN.DF (soTrans.status) IN ('Sales Order : Pending Fulfillment')
    AND soLine.mainline = 'T'
    AND soTrans.entity = '20011'
    AND l.id = '80'
    AND soTrans.shipdate = '10/12/2023'
GROUP BY
    soTrans.id,
    soTrans.tranid,
    ifTrans.id,
    ifTrans.tranid,
    ifTrans.custbody_kdl_id

raghav

10/20/2023, 11:50 AM

I guess this worked for me if there is a better more performant way to write this please do let me know. Its a small part of original query but any insight will be helpful

Copy code

SELECT
    soTrans.id AS so_internal_id,
    soTrans.tranid as so_id,
    ifTrans.id AS if_internal_id,
    ifTrans.tranid AS if_id,
    ifTrans.custbody_kdl_id AS if_kdl_order_num,
FROM
    transaction AS soTrans
    INNER JOIN customer AS c ON soTrans.entity = c.id
    INNER JOIN TransactionLine AS soLine ON soLine.Transaction = soTrans.id
    INNER JOIN location AS l ON soLine.location = l.id
    LEFT OUTER JOIN TransactionLine AS ifLine ON ifLine.createdfrom = soTrans.id
    LEFT OUTER JOIN transaction AS ifTrans ON ifTrans.id = ifLine.Transaction
WHERE
    soTrans.type = 'SalesOrd'
    AND BUILTIN.DF (soTrans.status) IN ('Sales Order : Pending Fulfillment')
    AND soLine.mainline = 'T'
    AND soTrans.entity = '20011'
    AND l.id = '80'
    AND soTrans.shipdate = '10/12/2023'
   AND (ifTrans.id IS NULL OR ifLine.mainline = 'T')

raghav

10/20/2023, 5:39 PM

There is still a problem with this query, ifLine.id could be for a customer deposit or any other transaction record created from that SO. I only want SOs details for the given filters along with If details if there is any created from that So. Could go with the UNION route, but is there any other way, could some1 help with the query?

4 Views

Open in Slack

Previous Next