Incorrect Syntax near ")"

Hello Ramco,

Your orderBr clause is wrong, Please do correction as below in you query to ger desire out put,

WITH RankedData AS (
    SELECT
        T0."DocNum" AS "Invoice No",
        T1."Quantity",
        T0."DocTotal",
        ROW_NUMBER() OVER (PARTITION BY T0."DocNum" ORDER BY T1."DocEntry") AS RowNum
    FROM OINV T0
    INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry"
)
SELECT * 
FROM RankedData;

In your Query : ORDER BY (SELECT NULL) throwing error.

WITH RankedData AS (
    SELECT
        T0."DocNum" AS "Invoice No",
        T1."Quantity",
        T0."DocTotal",
        ROW_NUMBER() OVER (PARTITION BY T0."DocNum" ORDER BY T1."Quantity") AS RowNum
    FROM OINV T0
    INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry"
)
SELECT * FROM RankedData;

The issue is with the ORDER BY (SELECT NULL), which is not allowed in the ROW_NUMBER() function. The ORDER BY clause must have a valid column for ordering the rows.

Please try this

Hello Ramco,

The error occurs because ORDER BY (SELECT NULL) is not a valid syntax in ROW_NUMBER(). The ORDER BY clause in ROW_NUMBER() requires a deterministic column to define the order in which rows are assigned row numbers.

Modify the query to use a valid column for ordering. If there is no meaningful column to order by, you can use T1."DocEntry" or any other unique column. Here is an updated query:

WITH RankedData AS (
    SELECT
        T0."DocNum" AS "Invoice No",
        T1."Quantity",
        T0."DocTotal",
        ROW_NUMBER() OVER (PARTITION BY T0."DocNum" ORDER BY T1."DocEntry") AS RowNum
    FROM OINV T0
    INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry"
)
SELECT * FROM RankedData;

There is an alternative approach, if you truly don't have a column for ordering and just need an arbitrary order, you can use:

ORDER BY T0."DocNum"

OR

ORDER BY (SELECT 1)

Good Luck!

Hi

  It is giving error - Constants are not allowed on order by clause of windows functions.

Thanks

The syntax error near ")" in your SQL query is due to the usage of (SELECT NULL) within the ORDER BY clause of the ROW_NUMBER() function. The ORDER BY clause in the ROW_NUMBER() function requires an actual column or expression based on which the ordering will be performed. Using (SELECT NULL) doesn't provide a valid ordering criterion.

To correct this error, you should replace (SELECT NULL) with a valid column or expression that determines the order in which ROW_NUMBER() should assign the row numbers. If you want an arbitrary row number without any specific order, you can use a constant value like 1.

Here's the corrected version of your SQL query:

WITH RankedData AS (
    SELECT
        T0."DocNum" As "Invoice No",
        T1."Quantity",
        T0."DocTotal",
        ROW_NUMBER() OVER (PARTITION BY T0."DocNum" ORDER BY 1) AS RowNum
    FROM OINV T0 
    INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry"
)

SELECT * FROM RankedData;

In this corrected query, I replaced (SELECT NULL) with 1 in the ORDER BY clause after ROW_NUMBER(), which will assign row numbers in an arbitrary order.

Feel free to test this corrected query in your environment, and it should resolve the syntax error near ")". If you have any more questions or need further clarification, feel free to ask!