Skip to Content
-1

correlated subquery cannot have TOP or ORDER BY Even after Using Limit 1

Aug 01, 2017 at 10:23 AM

56

avatar image
Former Member

Hi guys i have this source code but am still getting the correlated subquery cannot have TOP or ORDER BY. even after using Limit 1

Any Help please urgent

SELECT DISTINCT t0."CreatedBy", t0."TransType", t0."Number" AS "Delete", T2."LicTradNum" AS "PIN Of Purchaser", T2."CardName" AS "Name Of Purchaser", '0911200500751G' AS "ETR Serial Number", CAST(CAST(T0."RefDate" AS date) AS varchar(10)) AS "Invoice Date", T0."Ref1" AS "Invoice Number", 'SALES ORDER' AS "Description", CASE WHEN T0."TransType" = '14' AND T1."Credit" < 0 THEN -1 * T1."BaseSum" WHEN T0."TransType" = '13' AND T1."Credit" < 0 THEN -1 * T1."BaseSum" ELSE T1."BaseSum" END AS "Taxable value", T1."Credit" AS "Amount of VAT", CASE WHEN T0."TransType" = '14' AND T1."Credit" < 0 THEN (SELECT TOP 1 "DocNum" FROM OINV a INNER JOIN INV1 b ON a."DocEntry" = b."DocEntry" WHERE b."TrgetEntry" = T0."CreatedBy" AND b."TargetType" = T0."TransType") WHEN T0."TransType" = '13' AND T1."Credit" < 0 THEN (SELECT "DocNum" FROM OINV a WHERE a."DocEntry" = T0."CreatedBy" AND T0."TransType" = a."ObjType") ELSE '' END AS "Relevant Invoice Number", CASE WHEN T0."TransType" = '14' THEN (SELECT TOP 1 a."DocDate" FROM OINV a INNER JOIN INV1 b ON a."DocEntry" = b."DocEntry" WHERE b."TrgetEntry" = T0."CreatedBy" AND b."TargetType" = T0."TransType") WHEN T0."TransType" = '13' THEN NULL ELSE 'FIND' END AS "Relevant Invoice Date", CASE WHEN T0."TransType" = '14' THEN 'CreditNote' WHEN T0."TransType" = '13' THEN 'Invoice' ELSE 'FIND' END AS "DocType" FROM OJDT T0 INNER JOIN JDT1 T1 ON T0."TransId" = T1."TransId" LEFT OUTER JOIN OCRD T2 ON T2."CardCode" = T1."ContraAct" LEFT OUTER JOIN ORIN T3 ON T0."Ref1" = T3."DocNum" LEFT OUTER JOIN RIN1 T4 ON T3."DocEntry" = T4."DocEntry" WHERE (T1."VatGroup" = 'SA') AND T0."RefDate" BETWEEN '[%0]' AND '[%1]' ORDER BY "Invoice Number";

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers