Oracle SQL CTE not working - any workarounds???

[ArtieLadie]

I've been working on a query for nearly a month

I import Oracle SQL into PowerBI, and I wish for distinct columns

SELECT TO_CHAR(MIN(I.INCIDENTID))                  AS "Incident ID",
          MIN(I.CREATIONDATE)                      AS "Creation Date",
          TO_CHAR(I.CREATIONDATE,'MM-DD-YYYY')     AS "Date",
          TRIM(MO.DOMAINUSERNAME)                  AS "Login ID",  
          TRIM(M.MESSAGESUBJECT)                   AS "Email Subject"
   FROM  INCIDENT I 
   JOIN  MESSAGE M
   ON M.MESSAGEID = I.MESSAGEID
   JOIN   MESSAGEORIGINATOR MO
   ON     M.MESSAGEORIGINATORID = MO.MESSAGEORIGINATORID           
   GROUP BY TO_CHAR(I.CREATIONDATE,'MM-DD-YYYY'),
            TRIM(MO.DOMAINUSERNAME),
            TRIM(M.MESSAGESUBJECT)

This query gives bad result

But I am looking for good result, i.e.

I have been advised to use INNER JOIN and HAVING COUNT(*)=1, which partially fixes the problem, i.e. instead of 3 or 4 duplicates, I get 2 duplicates.

Now I have been advised to use CTE ---- unfortunately this gives error in PowerBI. How to fix this?

with cte as (SELECT TO_CHAR(I.INCIDENTID))                  AS "Incident ID",
              (I.CREATIONDATE)                      AS "Creation Date",
              TO_CHAR(I.CREATIONDATE,'MM-DD-YYYY')     AS "Date",
              TRIM(MO.DOMAINUSERNAME)                  AS "Login ID",  
              TRIM(M.MESSAGESUBJECT)                   AS "Email Subject",
              row_number() over(partition by TO_CHAR(I.CREATIONDATE,'MM-DD-YYYY'),
            TRIM(MO.DOMAINUSERNAME), TRIM(M.MESSAGESUBJECT) order by TO_CHAR(I.INCIDENTID)),I.CREATIONDATE) rn 
       FROM  INCIDENT I 
       JOIN  MESSAGE M
       ON M.MESSAGEID = I.MESSAGEID
       JOIN   MESSAGEORIGINATOR MO
       ON     M.MESSAGEORIGINATORID = MO.MESSAGEORIGINATORID 
) select * from cte where rn=1

PowerBI Error

[ArtieLadie]

Thank you for your response --- however I still need assistance

I already made sure

1. Syntax is correct.

2. FROM is in correct place, and no spelling errors have occurred.

3. No reserved words were used as an alias

[v-xicai]

Hi @ArtieLadie ,

Maybe you need to check it again , for example the bracket ")" in a wrong place, or you may ask someone great who is good at it.

Best Regards,

Amy

[v-xicai]

Hi @ArtieLadie ,

To work around the ORA-00923 error, you can check the following points:

1.Correct the syntax. Make sure you have placed the keyword FROM in its correct place, and that no spelling errors have occurred

2.If you used quotation marks in an alias, make sure that they have properly enclosed the alias and that they are double quotation marks.

3.Make sure no reserved words were used as an alias. See the Oracle appendix for reserved words to view a complete list. 

You can learn more: http://www.dba-oracle.com/t_ora_00923_from_keyword_not_found_where_expected.htm

                                     https://www.tekstream.com/oracle-error-messages/ora-00923-from-keyword-not-found-where-expected/

Please understand that this link is provided “AS IS” with no warranties or guarantees of content changes, and confers no rights.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.