Power BI SQL Server Direct Query

Question

Hi I'm trying to achieve the following:

1. Connect to an external database
2. Write a query to get data
3. Visualize

Looks like i've got the connection, and Power BI shows me the returned data as the preview.

However, when I try to "Load" the data, the following message appears:

"Microsoft SQL: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified."

How can I fix this?

The Query Script is as follows:

SELECT T1.WORKDATE, T1.CTKEY, T1.CARKEY, TC.CARNO, MAX(T1.CHANGEDATE) AS CHANGEDATE, T1.VACATION_START, T1.VACATION_END, T1.INSERTURKEY, MIN(T1.INSERTDATE) AS INSERTDATE
FROM (
    SELECT TS.WORKDATE -- 작업일자
        , TS.CTKEY -- 센터코드
        , TS.CARKEY
        , MAX(TS.CHANGEDATE) AS CHANGEDATE -- 익착일자
        , TC.VACATION_START -- 휴차 시작일
        , TC.VACATION_END -- 휴차 종료일
        , TP.INSERTURKEY AS INSERTURKEY -- 업무종료 여부(SP_PLANTOEXEC_2 또는 NULL 이면 업무종료 미처리)
        , MIN(TP.INSERTDATE) AS INSERTDATE -- 업무종료 시간
    FROM TT_CAR_SCENARIO_DT TS
    LEFT JOIN TADMIN_MST_CAR TC
        ON TC.CARKEY = TS.CARKEY
            AND TC.CTKEY = TS.CTKEY
            AND TS.WORKDATE BETWEEN CONVERT(VARCHAR, DATEADD(DAY, - 1, TC.VACATION_START), 112) AND CONVERT(VARCHAR, DATEADD(DAY, - 1, TC.VACATION_END), 112)
    LEFT JOIN TT_PLAN TP
        ON TP.CTKEY = TS.CTKEY
            AND TP.CARKEY = TS.CARKEY
            AND TP.ALCDATE = CONVERT(VARCHAR, DATEADD(DAY, 1, TS.WORKDATE), 112)
    WHERE TS.WORKDATE BETWEEN '20230201' AND '20230228'
    GROUP BY TS.WORKDATE
        , TS.CTKEY
        , TS.CARKEY
        , TC.VACATION_START
        , TC.VACATION_END
        , TP.INSERTURKEY
    ) T1
LEFT JOIN TADMIN_MST_CAR TC ON TC.CARKEY = T1.CARKEY AND TC.FIXYN = 'Y'
WHERE TC.CARNO IS NOT NULL
GROUP BY T1.WORKDATE, T1.CTKEY, T1.CARKEY, TC.CARNO, T1.VACATION_START, T1.VACATION_END, T1.INSERTURKEY
ORDER BY T1.WORKDATE;

Answer 1

Hi @Ethan Seo

You can follow the prompts of the error message, add 'TOP (100) PERCENT' between 'SELECT' and 'T1.WORKDATE'.

Just like this:

SELECT TOP (100) PERCENT T1.WORKDATE... FROM ...

Or add 'OFFSET 0 ROWS' after 'ORDER BY T1.WORKDATE'.

Just like this:

SELECT ... FROM ... ORDER BY T1.WORKDATE OFFSET 0 ROWS;

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".