Snowflake query issue for Page Size and Page Number

0
Hello Everyone, I have one application which is interface between Supplier and Employee. Recently we have migrated Data import for this application from SAP to Snowflake, We have scheduler for updating suppliers daily which comprises of Task queue for updating Purchase order , Part Number and Project data for each supplier. Whenever I am running the query it in Databse, I can see the required Purchase orders but they are not reflecting in Master data -front end and the data is huge (in thousands).I am suspecting my old query was reflecting all data as new one has offset and fetch instead of page number and page size , both queries attached below. Old query in SAP-SELECTSAP.EKKO_SSIS.EBELN AS PurchaseOrderNumber,SAP.EKKO_SSIS.LIFNR AS VendorNumber,SAP.EKPO_SSIS.MATNR AS PartNumber,SAP.EKPO_SSIS.TXZ01 AS PartDescription,SAP.EKKN_SSIS.PROJ_POST1 ProjectDescription,SAP.EKKN_SSIS.WBS AS ProjectNumberFROM SAP.EKKO_SSISINNER JOIN SAP.EKPO_SSIS ON SAP.EKKO_SSIS.EBELN = SAP.EKPO_SSIS.EBELNLEFT JOIN SAP.EKKN_SSIS ON SAP.EKPO_SSIS.EBELN = SAP.EKKN_SSIS.EBELN AND SAP.EKPO_SSIS.EBELP = SAP.EKKN_SSIS.EBELPWHERE SAP.EKKO_SSIS.LIFNR = {vendorNumber}AND SAP.EKKO_SSIS.EBELN IS NOT NULLAND SAP.EKKO_SSIS.EBELN != ''AND SAP.EKPO_SSIS.MATNR IS NOT NULLAND SAP.EKPO_SSIS.MATNR != ''ORDER BY SAP.EKKO_SSIS.EBELN DESCOFFSET {pageSize} * ({pageNumber} - 1) ROWSFETCH NEXT {pageSize} ROWS ONLYNew query from Snowflake-SELECT QUA_DISTRIBUTE.AMERICAS_SMO.RPARS_POHEADER.EBELN AS PurchaseOrderNumber,QUA_DISTRIBUTE.AMERICAS_SMO.RPARS_POHEADER.LIFNR AS VendorNumber,QUA_DISTRIBUTE.AMERICAS_SMO.RPARS_POLINE.MATNR AS PartNumber,QUA_DISTRIBUTE.AMERICAS_SMO.RPARS_POLINE.MATNR AS PartDescription,QUA_DISTRIBUTE.AMERICAS_SMO.RPARS_ACCTASSIGN.PROJ_POST1 AS ProjectDescription,QUA_DISTRIBUTE.AMERICAS_SMO.RPARS_ACCTASSIGN.WBS AS ProjectNumberFROMQUA_DISTRIBUTE.AMERICAS_SMO.RPARS_POHEADERINNER JOIN QUA_DISTRIBUTE.AMERICAS_SMO.RPARS_POLINE ON QUA_DISTRIBUTE.AMERICAS_SMO.RPARS_POHEADER.EBELN = QUA_DISTRIBUTE.AMERICAS_SMO.RPARS_POLINE.EBELNLEFT JOIN QUA_DISTRIBUTE.AMERICAS_SMO.RPARS_ACCTASSIGN ON QUA_DISTRIBUTE.AMERICAS_SMO.RPARS_POLINE.EBELN = QUA_DISTRIBUTE.AMERICAS_SMO.RPARS_ACCTASSIGN.EBELN AND QUA_DISTRIBUTE.AMERICAS_SMO.RPARS_POLINE.EBELP = QUA_DISTRIBUTE.AMERICAS_SMO.RPARS_ACCTASSIGN.EBELPWHERE QUA_DISTRIBUTE.AMERICAS_SMO.RPARS_POHEADER.LIFNR = VendorNumberAND QUA_DISTRIBUTE.AMERICAS_SMO.RPARS_POHEADER.EBELN IS NOT NULLAND QUA_DISTRIBUTE.AMERICAS_SMO.RPARS_POHEADER.EBELN != ''AND QUA_DISTRIBUTE.AMERICAS_SMO.RPARS_POLINE.MATNR IS NOT NULLAND QUA_DISTRIBUTE.AMERICAS_SMO.RPARS_POLINE.MATNR != ''ORDER BY QUA_DISTRIBUTE.AMERICAS_SMO.RPARS_POHEADER.EBELN DESCOFFSET 20 ROWSFETCH 10 ROWS ONLY
asked
2 answers
0

Hi Rutuja,


This seems to be related to Snowflake itself and differences in SAP SQL and Snowflake SQL. I would advice you to test the SQL in a Snowfloke SQL Worksheet in Snowsight.

Best regards,

Emiel Paasschens - Team lead Alliance Dev Team.

answered
0

Hi Rutuja,


Are you using the External Dabase Connector or the Snowflake AI Data Connector?

And when it's working in a 'normal situation', so without a task queue, I suspect it has to do with transactions and I would adivise you to contact support for help.

answered