Home

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
	    

	    

	        
	        
	        
	        
	        
	        

	            

	                
	            

	            

	                

	                    
	                

	                

	                    Cancel
	                

	            

	        

	    


	                

            

            

        


        
                    

                

                    

                    
                        2 Answer(s)                    
                    

                    

                        
                    

                

            


            	
	

					

			    

	
    
    




	
    
    
                             

    
    

                    

    


    

        
Removed 

Order By




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

    


    

        

            
            

                
            

            

                

                    
                

                

                    
                        Cancel                    
                

            

        

    


    

    	
        

            
                
            
                Rookie                
                    Answered on May 2, 2023.                
        

		

        

        	
            
        

        
    

    

    

		

				


        		Add Comment
	    

	    

	        
	        
	        
	        
	        
	        

	            

	                
	            

	            

	                

	                    
	                

	                

	                    Cancel
	                

	            

	        

	    


	
    


			

						

			    

	
    
    




	
    
    
                             

    
    

                    

    


    

        
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

    


    

        

            
            

                
            

            

                

                    
                

                

                    
                        Cancel                    
                

            

        

    


    

    	
        

            
                
            
                Rookie                
                    Answered on December 19, 2023.                
        

		

        

        	
            
        

        
    

    

    

		

				


        		Add Comment
	    

	    

	        
	        
	        
	        
	        
	        

	            

	                
	            

	            

	                

	                    
	                

	                

	                    Cancel
	                

	            

	        

	    


	
    


			

				

	
	

	    

	        	    

	

	

            
            

                

                    
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  
SuiteMigration  
Become a Sponsor
    •