cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ArtieLadie Frequent Visitor
Frequent Visitor

with CTE statement giving error

I am pulling query from Oracle Database into Power BI that outputs this

 

 

but I want it to output this

 

 

 

I thought MIN(I.CREATIONDATE) would give date-time with earliest hour in my original query

 

 

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   MESSAGE M 
JOIN   INCIDENT I 
ON     M.MESSAGESOURCE = I.MESSAGESOURCE 
AND       M.MESSAGEID = I.MESSAGEID
AND    M.MESSAGEDATE    = I.MESSAGEDATE 
JOIN   MESSAGEORIGINATOR MO
ON     M.MESSAGEORIGINATORID = MO.MESSAGEORIGINATORID                  
GROUP BY TO_CHAR(I.CREATIONDATE,'MM-DD-YYYY'),
         TRIM(MO.DOMAINUSERNAME),
         TRIM(M.MESSAGESUBJECT)

 

Then I was advised to use CTE with row_number() but now I get 

 

Message=ORA-00933: SQL command not properly ended

 

 

I tried many variations. How to fix this?

 

with CTE as (
SELECT MyTable.*, 
       row_number() over (PARTITION BY MIN(I.CREATIONDATE) ORDER BY I.CREATIONDATE) AS rn   
FROM MyTable
)
SELECT *
FROM CTE
WHERE rn = 1

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   MESSAGE M 
JOIN   INCIDENT I 
ON     M.MESSAGESOURCE = I.MESSAGESOURCE 
AND       M.MESSAGEID = I.MESSAGEID
AND    M.MESSAGEDATE    = I.MESSAGEDATE 
JOIN   MESSAGEORIGINATOR MO
ON     M.MESSAGEORIGINATORID = MO.MESSAGEORIGINATORID                  
GROUP BY TO_CHAR(I.CREATIONDATE,'MM-DD-YYYY'),
         TRIM(MO.DOMAINUSERNAME),
         TRIM(M.MESSAGESUBJECT)

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 204 members 2,145 guests
Please welcome our newest community members: