i have this query to run in database table to export some data,but its throwing an error like this:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LIMIT 0, 25' at line 45
below is the query:
SELECT DISTINCT ITS_MATTER_NUMBER, REQUEST_DATE, REQUEST_TYPE, REQUEST_STATUS, CUSTODIAN_NAME, CUSTODIAN_WWID, JNJ_MATTER_NAME, JNJ_MATTER_NUMBER, COLLECTION_VENDOR, RECEIVER FROM ( SELECT DISTINCT ITS_MATTER_NO AS ITS_MATTER_NUMBER, TO_CHAR(CREATE_DATE,'MM/DD/YYYY') AS REQUEST_DATE, REQUEST_TYPE, CFA_DM_ED_REQUEST_HDR.STATUS AS REQUEST_STATUS, CUSTODIAN_NAME, CUSTODIAN_WWID, JNJ_MATTER_NAME, JNJ_MATTER_NO AS JNJ_MATTER_NUMBER, CFA_DM_ED_COLL_VENDOR.COMPANY AS COLLECTION_VENDOR, CFA_DM_ED_PROD_RECEIVER.COMPANY AS RECEIVER FROM CFA_DM_ED_REQUEST_HDR LEFT OUTER JOIN CFA_DM_ED_PROD_RECEIVER ON CFA_DM_ED_REQUEST_HDR.REQUEST_ID = CFA_DM_ED_PROD_RECEIVER.REQUEST_ID_FK LEFT OUTER JOIN CFA_DM_ED_COLL_VENDOR ON CFA_DM_ED_REQUEST_HDR.REQUEST_ID = CFA_DM_ED_COLL_VENDOR.REQUEST_ID_FK JOIN ( SELECT CFA_DM_ED_LIVE_CUSTODIAN.FIRST_NAME || ' ' || CFA_DM_ED_LIVE_CUSTODIAN.LAST_NAME AS CUSTODIAN_NAME, CFA_DM_ED_LIVE_CUSTODIAN.WWID AS CUSTODIAN_WWID, REQUEST_ID_FK FROM CFA_DM_ED_LIVE_CUSTODIAN UNION ALL SELECT CFA_DM_ED_HISTORIC_CUSTODIAN.FIRST_NAME || ' ' || CFA_DM_ED_HISTORIC_CUSTODIAN.LAST_NAME AS CUSTODIAN_NAME, CFA_DM_ED_HISTORIC_CUSTODIAN.WWID AS CUSTODIAN_WWID, REQUEST_ID_FK FROM CFA_DM_ED_HISTORIC_CUSTODIAN ORDER BY REQUEST_ID_FK ) T1 ON CFA_DM_ED_REQUEST_HDR.REQUEST_ID = T1.REQUEST_ID_FK ORDER BY REQUEST_ID,ITS_MATTER_NO )
Discussion posts and replies are publicly visible
Hi salinis Giving an alias to the table which is returned by subquery should resolve this error. Try the below:
SELECT DISTINCT A.ITS_MATTER_NUMBER, A.REQUEST_DATE, A.REQUEST_TYPE, A.REQUEST_STATUS, A.CUSTODIAN_NAME, A.CUSTODIAN_WWID, A.JNJ_MATTER_NAME, A.JNJ_MATTER_NUMBER, A.COLLECTION_VENDOR, A.RECEIVER FROM ( SELECT DISTINCT ITS_MATTER_NO AS ITS_MATTER_NUMBER, TO_CHAR(CREATE_DATE,'MM/DD/YYYY') AS REQUEST_DATE, REQUEST_TYPE, CFA_DM_ED_REQUEST_HDR.STATUS AS REQUEST_STATUS, CUSTODIAN_NAME, CUSTODIAN_WWID, JNJ_MATTER_NAME, JNJ_MATTER_NO AS JNJ_MATTER_NUMBER, CFA_DM_ED_COLL_VENDOR.COMPANY AS COLLECTION_VENDOR, CFA_DM_ED_PROD_RECEIVER.COMPANY AS RECEIVER FROM CFA_DM_ED_REQUEST_HDR LEFT OUTER JOIN CFA_DM_ED_PROD_RECEIVER ON CFA_DM_ED_REQUEST_HDR.REQUEST_ID = CFA_DM_ED_PROD_RECEIVER.REQUEST_ID_FK LEFT OUTER JOIN CFA_DM_ED_COLL_VENDOR ON CFA_DM_ED_REQUEST_HDR.REQUEST_ID = CFA_DM_ED_COLL_VENDOR.REQUEST_ID_FK JOIN ( SELECT CFA_DM_ED_LIVE_CUSTODIAN.FIRST_NAME || ' ' || CFA_DM_ED_LIVE_CUSTODIAN.LAST_NAME AS CUSTODIAN_NAME, CFA_DM_ED_LIVE_CUSTODIAN.WWID AS CUSTODIAN_WWID, REQUEST_ID_FK FROM CFA_DM_ED_LIVE_CUSTODIAN UNION ALL SELECT CFA_DM_ED_HISTORIC_CUSTODIAN.FIRST_NAME || ' ' || CFA_DM_ED_HISTORIC_CUSTODIAN.LAST_NAME AS CUSTODIAN_NAME, CFA_DM_ED_HISTORIC_CUSTODIAN.WWID AS CUSTODIAN_WWID, REQUEST_ID_FK FROM CFA_DM_ED_HISTORIC_CUSTODIAN ORDER BY REQUEST_ID_FK ) T1 ON CFA_DM_ED_REQUEST_HDR.REQUEST_ID = T1.REQUEST_ID_FK ORDER BY REQUEST_ID,ITS_MATTER_NO) A