Error when using Group By in suiteql

What am I doing wrong with this query:

SELECT Transaction.TranID AS PONumber, Transaction.TranDate AS DatePOCreated, nvl( vendor.companyName, 'null' ) AS VendorName, nvl( vendor.entityid, 'null' ) AS VendorId, nvl(to_char(POLine.expectedReceiptDate, 'MM/DD/YYYY'), 'null') as expectedReceiptDate, nvl(to_char(POLine.lineLastModifiedDate, 'MM/DD/YYYY"T"HH24:MM:SSTZH:TZM'), 'null' ) as TimeModified, nvl(concat(to_char(Transaction.createdDate, 'MM/DD/YYYY'), 'T00:00:00-05:00'), 'null') as TimeCreated, nvl(POLine.uniquekey, 0 ) AS ItemLineId, nvl(POLine.LineSequenceNumber, 0 ) AS POLineSequence, nvl( case when item.purchaseDescription is not null then item.purchaseDescription when item.description is not null then item.description when item.displayName is not null then item.displayName else item.itemId end, 'null' ) as item_description, nvl( BUILTIN.DF(POLine.units), 'null' ) as UOM, nvl(POLine.Quantity, 0 ) AS ItemQuantity, nvl( POLine.QuantityShipRecv, 0 ) AS ItemReceivedQty, case when nvl(POLine.QuantityShipRecv, 0) > 0 and nvl(POLine.Quantity, 0) > 0 and nvl(POLine.QuantityShipRecv, 0) >= nvl(POLine.Quantity, 0) then 'T' else 'F' end as isFullyReceived, nvl( POLine.isClosed, 'F' ) as LineClosed, nvl( item.itemId, 'null' ) as ItemPartNumber, nvl( POLine.QuantityBilled, 0 ) AS TotalBilled, BUILTIN.DF(Transaction.Status) as Status, nvl( POLine.itemType, 'null' ) as ItemType, nvl(sub.name, 'null' ) as SubsidiaryName, nvl(BUILTIN.DF(item.subtype), 'null') as subtype, nvl(to_char(Transaction.custbodypostartdate, 'MM/DD/YYYY'), 'null') as POStartDate, nvl(to_char(Transaction.custbodypoenddate, 'MM/DD/YYYY'), 'null') as POEndDate, nvl( to_char(sum(inv.quantityonhand)), 'null' ) as qtyOnHand FROM Transaction INNER JOIN TransactionLine as POLine ON ( POLine.Transaction = Transaction.ID ) INNER JOIN Item ON ( Item.ID = POLine.Item ) left outer join subsidiary as sub on sub.id = POLine.subsidiary left outer join vendor as vendor on vendor.id = Transaction.entity left outer join inventoryBalance as inv on inv.item = item.id WHERE ( Transaction.Type = 'PurchOrd' ) and item.itemtype in ('InvtPart', 'Assembly') and POLine.lineLastModifiedDate >= TO_DATE('2023-05-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS') group by Transaction.TranID, Transaction.TranDate, nvl( vendor.companyName, 'null' ), nvl( vendor.entityid, 'null' ), nvl(to_char(POLine.expectedReceiptDate, 'MM/DD/YYYY'), 'null'), nvl(to_char(POLine.lineLastModifiedDate, 'MM/DD/YYYY"T"HH24:MM:SSTZH:TZM'), 'null' ), nvl(concat(to_char(Transaction.createdDate, 'MM/DD/YYYY'), 'T00:00:00-05:00'), 'null'), nvl(POLine.uniquekey, 0 ), nvl(POLine.LineSequenceNumber, 0 ), nvl( case when item.purchaseDescription is not null then item.purchaseDescription when item.description is not null then item.description when item.displayName is not null then item.displayName else item.itemId end, 'null' ), nvl( BUILTIN.DF(POLine.units), 'null' ), nvl(POLine.Quantity, 0 ), nvl( POLine.QuantityShipRecv, 0 ), case when nvl(POLine.QuantityShipRecv, 0) > 0 and nvl(POLine.Quantity, 0) > 0 and nvl(POLine.QuantityShipRecv, 0) >= nvl(POLine.Quantity, 0) then 'T' else 'F' end, nvl( POLine.isClosed, 'F' ), nvl( item.itemId, 'null' ), nvl( POLine.QuantityBilled, 0 ), BUILTIN.DF(Transaction.Status), nvl( POLine.itemType, 'null' ), nvl(sub.name, 'null' ), nvl(BUILTIN.DF(item.subtype), 'null'), nvl(to_char(Transaction.custbodypostartdate, 'MM/DD/YYYY'), 'null'), nvl(to_char(Transaction.custbodypoenddate, 'MM/DD/YYYY'), 'null'), nvl( to_char(sum(inv.quantityonhand)), 'null' ) ORDER BY TimeCreated, TimeModified, VendorId, PONumber, ItemID

I get the following when I run it:

{ "type": "https://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html#sec10.4.1", "title": "Bad Request", "status": 400, "o:errorDetails": [ { "detail": "Invalid search query. Detailed unprocessed description follows. Search error occurred: Invalid or unsupported search.", "o:errorQueryParam": "q", "o:errorCode": "INVALID_PARAMETER" } ] }

If I get rid of “sum” and “group by”, it works fine. 

I also tried this:

 


SELECT Transaction.TranID AS PONumber,
Transaction.TranDate AS DatePOCreated,
nvl( vendor.companyName, 'null' ) AS VendorName,
nvl( vendor.entityid, 'null' ) AS VendorId,
nvl(to_char(POLine.expectedReceiptDate, 'MM/DD/YYYY'), 'null') as expectedReceiptDate,
nvl(to_char(POLine.lineLastModifiedDate, 'MM/DD/YYYY"T"HH24:MM:SSTZH:TZM'), 'null' ) as TimeModified,
nvl(concat(to_char(Transaction.createdDate, 'MM/DD/YYYY'), 'T00:00:00-05:00'), 'null') as TimeCreated,
nvl(POLine.uniquekey, 0 ) AS ItemLineId,
nvl(POLine.LineSequenceNumber, 0 ) AS POLineSequence,
nvl(
case
when item.purchaseDescription is not null then item.purchaseDescription
when item.description is not null then item.description
when item.displayName is not null then item.displayName
else item.itemId
end, 'null' ) as item_description,
nvl( BUILTIN.DF(POLine.units), 'null' ) as UOM,
nvl(POLine.Quantity, 0 ) AS ItemQuantity,
nvl( POLine.QuantityShipRecv, 0 ) AS ItemReceivedQty,
case
when nvl(POLine.QuantityShipRecv, 0) > 0 and
nvl(POLine.Quantity, 0) > 0 and
nvl(POLine.QuantityShipRecv, 0) >= nvl(POLine.Quantity, 0) then 'T'
else 'F'
end as isFullyReceived,
nvl( POLine.isClosed, 'F' ) as LineClosed,
nvl( item.itemId, 'null' ) as ItemPartNumber,
nvl( POLine.QuantityBilled, 0 ) AS TotalBilled,
BUILTIN.DF(Transaction.Status) as Status,
nvl( POLine.itemType, 'null' ) as ItemType,
nvl(sub.name, 'null' ) as SubsidiaryName,
nvl(BUILTIN.DF(item.subtype), 'null') as subtype,
nvl(to_char(Transaction.custbodypostartdate, 'MM/DD/YYYY'), 'null') as POStartDate,
nvl(to_char(Transaction.custbodypoenddate, 'MM/DD/YYYY'), 'null') as POEndDate,
nvl( to_char(sum(inv.quantityonhand)), 'null' ) as qtyOnHand
FROM Transaction
INNER JOIN TransactionLine as POLine ON
( POLine.Transaction = Transaction.ID )
INNER JOIN Item ON ( Item.ID = POLine.Item )
left outer join subsidiary as sub on sub.id = POLine.subsidiary
left outer join vendor as vendor on vendor.id = Transaction.entity
left outer join inventoryBalance as inv on inv.item = item.id
WHERE ( Transaction.Type = 'PurchOrd' )
and item.itemtype in ('InvtPart', 'Assembly')
and POLine.lineLastModifiedDate >= TO_DATE('2023-05-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
FROM Transaction
INNER JOIN TransactionLine as POLine ON
( POLine.Transaction = Transaction.ID )
INNER JOIN Item ON ( Item.ID = POLine.Item )
left outer join subsidiary as sub on sub.id = POLine.subsidiary
left outer join vendor as vendor on vendor.id = Transaction.entity
left outer join inventoryBalance as inv on inv.item = item.id
WHERE ( Transaction.Type = 'PurchOrd' )
and item.itemtype in ('InvtPart', 'Assembly')
group by PONumber,
DatePOCreated,
VendorName,
VendorId,
expectedReceiptDate,
TimeModified,
TimeCreated,
ItemLineId,
POLineSequence,
item_description,
UOM,
ItemQuantity,
ItemReceivedQty,
isFullyReceived,
LineClosed,
ItemPartNumber,
TotalBilled,
Status,
ItemType,
SubsidiaryName,
subtype,
POStartDate,
POEndDate
ORDER BY TimeCreated, TimeModified, VendorId, PONumber, ItemID


I get a different error for this one, so I suspect the error here is much more extreme.


Rookie Asked on May 2, 2023 in SuiteScript.
Add Comment
2 Answer(s)

Removed


Order By

and it worked.  Looks like you cannot use` order by` with` group by`.

Rookie Answered on May 2, 2023.
Add Comment

Hi,

Use Below Query

SELECT Transaction.TranID AS PONumber,
Transaction.TranDate AS DatePOCreated,
nvl( vendor.companyName, ‘null’ ) AS VendorName,
nvl( vendor.entityid, ‘null’ ) AS VendorId,
nvl(to_char(POLine.expectedReceiptDate, ‘MM/DD/YYYY’), ‘null’) as expectedReceiptDate,
nvl(to_char(POLine.lineLastModifiedDate, ‘MM/DD/YYYY”T”HH24:MM:SSTZH:TZM’), ‘null’ ) as TimeModified,
nvl(concat(to_char(Transaction.createdDate, ‘MM/DD/YYYY’), ‘T00:00:00-05:00’), ‘null’) as TimeCreated,
nvl(POLine.uniquekey, 0 ) AS ItemLineId,
nvl(POLine.LineSequenceNumber, 0 ) AS POLineSequence,
nvl(
case
when item.purchaseDescription is not null then item.purchaseDescription
when item.description is not null then item.description
when item.displayName is not null then item.displayName
else item.itemId
end, ‘null’ ) as item_description,
nvl( BUILTIN.DF(POLine.units), ‘null’ ) as UOM,
nvl(POLine.Quantity, 0 ) AS ItemQuantity,
nvl( POLine.QuantityShipRecv, 0 ) AS ItemReceivedQty,
case
when nvl(POLine.QuantityShipRecv, 0) > 0 and
nvl(POLine.Quantity, 0) > 0 and
nvl(POLine.QuantityShipRecv, 0) >= nvl(POLine.Quantity, 0) then ‘T’
else ‘F’
end as isFullyReceived,
nvl( POLine.isClosed, ‘F’ ) as LineClosed,
nvl( item.itemId, ‘null’ ) as ItemPartNumber,
nvl( POLine.QuantityBilled, 0 ) AS TotalBilled,
BUILTIN.DF(Transaction.Status) as Status,
nvl( POLine.itemType, ‘null’ ) as ItemType,
nvl(sub.name, ‘null’ ) as SubsidiaryName,
nvl(BUILTIN.DF(item.subtype), ‘null’) as subtype,
(SELECT nvl( to_char(sum(inventoryBalance .quantityonhand)), ‘null’ ) FROM inventoryBalance WHERE inventoryBalance .item = item.id) as qtyOnHand

FROM Transaction
INNER JOIN TransactionLine as POLine ON ( POLine.Transaction = Transaction.ID )
INNER JOIN Item ON ( Item.ID = POLine.Item )
left outer join subsidiary as sub on sub.id = POLine.subsidiary
left outer join vendor as vendor on vendor.id = Transaction.entity
left outer join inventoryBalance as inv on inv.item = item.id
WHERE ( Transaction.Type = ‘PurchOrd’ ) and item.itemtype in (‘InvtPart’, ‘Assembly’) AND Transaction.ID=13073

Thanks

BP Sharma

Rookie Answered on December 19, 2023.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.
  • This site made possible by our sponsors:   Tipalti   Celigo   Become a Sponsor   Become a Sponsor