Sonny Sonny - 4 months ago 7
SQL Question

How to add a sysdate column to a query containing left outer joins

I need a little help here in adding a column to my query that will be used as a 'date extracted' column.
My originaly query looks like:

SELECT
PR.PARENT_ID,
PR.ID as Record_ID,
PROJECT.NAME as Project_Name,
PR_STATUS_TYPE.NAME as RECORD_State,
PERSON_RELATION.NAME as ORIGINATOR,
PR.DATE_OPENED,
TW_V_AUDIT.S_VALUE as aud,
PR.NAME TITLE
FROM (PR PR
LEFT OUTER JOIN PROJECT PROJECT ON PR.PROJECT_ID=PROJECT.ID)
LEFT OUTER JOIN PERSON_RELATION ON PERSON_RELATION.ID = PR.ORIGINATOR_REL_ID
LEFT OUTER JOIN PR_STATUS_TYPE PR_STATUS_TYPE ON PR.STATUS_TYPE=PR_STATUS_TYPE.ID
LEFT OUTER JOIN TW_V_AUDIT ON TW_V_AUDIT.PR_ID = PR.ID
WHERE
PROJECT.NAME='Audit'
GROUP BY
PERSON_RELATION.NAME,
PR.ID,
PR.PARENT_ID,
PROJECT.NAME,
PR_STATUS_TYPE.NAME,
PR.DATE_OPENED,
TW_V_AUDIT.S_VALUE,
PR.NAME
ORDER BY
RECORD_ID;


I am looking to add a column that shows the system date which is the following query:

Select TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') from dual


How would I do so? And I am using sql developer.
Please let me know if anymore information is needed from my end.
Thank you all again in advance.

Sonny

Answer

Just add the date column to your Select Statement:

SELECT 
PR.PARENT_ID,
PR.ID as Record_ID,   
PROJECT.NAME as Project_Name,
PR_STATUS_TYPE.NAME  as RECORD_State, 
PERSON_RELATION.NAME as ORIGINATOR,
PR.DATE_OPENED, 
TW_V_AUDIT.S_VALUE as aud,
PR.NAME TITLE,
TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') as date
FROM (PR PR 
LEFT OUTER JOIN PROJECT PROJECT ON PR.PROJECT_ID=PROJECT.ID) 
LEFT OUTER JOIN PERSON_RELATION ON PERSON_RELATION.ID = PR.ORIGINATOR_REL_ID
LEFT OUTER JOIN PR_STATUS_TYPE PR_STATUS_TYPE ON PR.STATUS_TYPE=PR_STATUS_TYPE.ID
LEFT OUTER JOIN TW_V_AUDIT ON TW_V_AUDIT.PR_ID = PR.ID
WHERE  
PROJECT.NAME='Audit' 
GROUP BY
PERSON_RELATION.NAME,
PR.ID, 
PR.PARENT_ID,
PROJECT.NAME,
PR_STATUS_TYPE.NAME,
PR.DATE_OPENED,
TW_V_AUDIT.S_VALUE,
PR.NAME
ORDER BY
RECORD_ID;
Comments