PS Query & SQL

A coworker recently asked me for a way to have a query determine whether or not it was running on a Monday or not.  I managed to come up with a solution but it was a bit convoluted so I wondered if anyone had a better method.  Here's what I did (as SQL)

SELECT  CASE    WHEN TRUNC(SYSDATE, 'DDD') = NEXT_DATE(TRUNC(SYSDATE, 'D'), 'MONDAY') THEN 'Y'    ELSE 'N'  ENDFROM DUAL 

For context sake:

• TRUNC(SYSDATE, 'DDD') "rounds down" a date(time) to the nearest day
  • This is only needed in SQL, as SYSDATE is not a true date, in PS Query you could use %currentdatein and eliminate the need for this
• TRUNC(SYSDATE, 'D') "rounds down" a date(time) to the first day of the week
  • This is dependent on your system settings, but for APU this means Sunday
• NEXT_DATE(DATE, 'MONDAY') returns the date of the next Monday after the input date
  • If the input date is a Monday it will return a full week later

Hello Scott,

TO_CHAR(SYSDATE, 'DAY') will work.  That will give you all caps.  However, you can also use 'Day' and 'day' if you prefer.

A coworker recently asked me for a way to have a query determine whether or not it was running on a Monday or not.  I managed to come up with a solution but it was a bit convoluted so I wondered if anyone had a better method.  Here's what I did (as SQL)

SELECT  CASE    WHEN TRUNC(SYSDATE, 'DDD') = NEXT_DATE(TRUNC(SYSDATE, 'D'), 'MONDAY') THEN 'Y'    ELSE 'N'  ENDFROM DUAL 

For context sake:

• TRUNC(SYSDATE, 'DDD') "rounds down" a date(time) to the nearest day
  • This is only needed in SQL, as SYSDATE is not a true date, in PS Query you could use %currentdatein and eliminate the need for this
• TRUNC(SYSDATE, 'D') "rounds down" a date(time) to the first day of the week
  • This is dependent on your system settings, but for APU this means Sunday
• NEXT_DATE(DATE, 'MONDAY') returns the date of the next Monday after the input date
  • If the input date is a Monday it will return a full week later

Hello Scott,

TO_CHAR(SYSDATE, 'DAY') will work.  That will give you all caps.  However, you can also use 'Day' and 'day' if you prefer.

A coworker recently asked me for a way to have a query determine whether or not it was running on a Monday or not.  I managed to come up with a solution but it was a bit convoluted so I wondered if anyone had a better method.  Here's what I did (as SQL)

SELECT  CASE    WHEN TRUNC(SYSDATE, 'DDD') = NEXT_DATE(TRUNC(SYSDATE, 'D'), 'MONDAY') THEN 'Y'    ELSE 'N'  ENDFROM DUAL 

For context sake:

• TRUNC(SYSDATE, 'DDD') "rounds down" a date(time) to the nearest day
  • This is only needed in SQL, as SYSDATE is not a true date, in PS Query you could use %currentdatein and eliminate the need for this
• TRUNC(SYSDATE, 'D') "rounds down" a date(time) to the first day of the week
  • This is dependent on your system settings, but for APU this means Sunday
• NEXT_DATE(DATE, 'MONDAY') returns the date of the next Monday after the input date
  • If the input date is a Monday it will return a full week later

Could someone please help me with this? 

I want to get the day of the week from the Exam Date field on the EXAM_CODE_TBL. I am using the formula that Dan recommended in an earlier post, but some funkiness is occuring on the SQL generation side of things.

Here is my simple expression:

TO_CHAR(B.EXAM_DT, 'DAY')

When I run the query, I get back this Error, Code=1722, Message=ORA-01722: invalid number (50,380).

I noticed that the SQL is converting my expression the following:

 TO_CHAR( TO_CHAR(B.EXAM_DT,'YYYY-MM-DD'), 'DAY')

How might I get around this? My ultimate goal is to generate a new column with the day of the week based on the date in the Exam Date field, i.e. if exam date = 4/28/2025 then I want to see "Monday." 

Thanks!

Anna 

My entire SQL:

SELECT A.STRM, C.EXAM_TIME_CODE, TO_CHAR(C.EXAM_DT,'YYYY-MM-DD'), TO_CHAR(CAST((C.EXAM_START_TIME) AS TIMESTAMP),'HH24.MI.SS.FF'), TO_CHAR(CAST((C.EXAM_END_TIME) AS TIMESTAMP),'HH24.MI.SS.FF'), C.MON, C.TUES, C.WED, C.THURS, C.FRI, C.SAT, C.SUN, TO_CHAR(CAST((C.CLAS_STRT_TIME_FR) AS TIMESTAMP),'HH24.MI.SS.FF'), TO_CHAR(CAST((C.CLAS_STRT_TIME_TO) AS TIMESTAMP),'HH24.MI.SS.FF'), SUM( A.ENRL_TOT), TO_CHAR( TO_CHAR(B.EXAM_DT,'YYYY-MM-DD'), 'DAY')
  FROM PS_CLASS_TBL A, PS_CLASS_EXAM B, PS_EXAM_CODE_TBL C
  WHERE ( A.CRSE_ID = B.CRSE_ID
     AND A.CRSE_OFFER_NBR = B.CRSE_OFFER_NBR
     AND A.STRM = B.STRM
     AND A.SESSION_CODE = B.SESSION_CODE
     AND A.CLASS_SECTION = B.CLASS_SECTION
     AND B.STRM = C.STRM
     AND B.SESSION_CODE = C.SESSION_CODE
     AND C.EXAM_TIME_CODE = B.EXAM_TIME_CODE
     AND A.STRM = '1910'
     AND B.CLASS_EXAM_TYPE = 'FIN'
     AND C.CLASS_EXAM_TYPE = 'FIN'
     AND C.INSTITUTION = 'DUKEU')
  GROUP BY  A.STRM,  C.EXAM_TIME_CODE,  C.EXAM_DT,  C.EXAM_START_TIME,  C.EXAM_END_TIME,  C.MON,  C.TUES,  C.WED,  C.THURS,  C.FRI,  C.SAT,  C.SUN,  C.CLAS_STRT_TIME_FR,  C.CLAS_STRT_TIME_TO,  TO_CHAR( TO_CHAR(B.EXAM_DT,'YYYY-MM-DD'), 'DAY')
  ORDER BY 2

Hello Scott,

TO_CHAR(SYSDATE, 'DAY') will work.  That will give you all caps.  However, you can also use 'Day' and 'day' if you prefer.

A coworker recently asked me for a way to have a query determine whether or not it was running on a Monday or not.  I managed to come up with a solution but it was a bit convoluted so I wondered if anyone had a better method.  Here's what I did (as SQL)

SELECT  CASE    WHEN TRUNC(SYSDATE, 'DDD') = NEXT_DATE(TRUNC(SYSDATE, 'D'), 'MONDAY') THEN 'Y'    ELSE 'N'  ENDFROM DUAL 

For context sake:

• TRUNC(SYSDATE, 'DDD') "rounds down" a date(time) to the nearest day
  • This is only needed in SQL, as SYSDATE is not a true date, in PS Query you could use %currentdatein and eliminate the need for this
• TRUNC(SYSDATE, 'D') "rounds down" a date(time) to the first day of the week
  • This is dependent on your system settings, but for APU this means Sunday
• NEXT_DATE(DATE, 'MONDAY') returns the date of the next Monday after the input date
  • If the input date is a Monday it will return a full week later

Could someone please help me with this? 

I want to get the day of the week from the Exam Date field on the EXAM_CODE_TBL. I am using the formula that Dan recommended in an earlier post, but some funkiness is occuring on the SQL generation side of things.

Here is my simple expression:

TO_CHAR(B.EXAM_DT, 'DAY')

When I run the query, I get back this Error, Code=1722, Message=ORA-01722: invalid number (50,380).

I noticed that the SQL is converting my expression the following:

 TO_CHAR( TO_CHAR(B.EXAM_DT,'YYYY-MM-DD'), 'DAY')

How might I get around this? My ultimate goal is to generate a new column with the day of the week based on the date in the Exam Date field, i.e. if exam date = 4/28/2025 then I want to see "Monday." 

Thanks!

Anna 

My entire SQL:

SELECT A.STRM, C.EXAM_TIME_CODE, TO_CHAR(C.EXAM_DT,'YYYY-MM-DD'), TO_CHAR(CAST((C.EXAM_START_TIME) AS TIMESTAMP),'HH24.MI.SS.FF'), TO_CHAR(CAST((C.EXAM_END_TIME) AS TIMESTAMP),'HH24.MI.SS.FF'), C.MON, C.TUES, C.WED, C.THURS, C.FRI, C.SAT, C.SUN, TO_CHAR(CAST((C.CLAS_STRT_TIME_FR) AS TIMESTAMP),'HH24.MI.SS.FF'), TO_CHAR(CAST((C.CLAS_STRT_TIME_TO) AS TIMESTAMP),'HH24.MI.SS.FF'), SUM( A.ENRL_TOT), TO_CHAR( TO_CHAR(B.EXAM_DT,'YYYY-MM-DD'), 'DAY')
  FROM PS_CLASS_TBL A, PS_CLASS_EXAM B, PS_EXAM_CODE_TBL C
  WHERE ( A.CRSE_ID = B.CRSE_ID
     AND A.CRSE_OFFER_NBR = B.CRSE_OFFER_NBR
     AND A.STRM = B.STRM
     AND A.SESSION_CODE = B.SESSION_CODE
     AND A.CLASS_SECTION = B.CLASS_SECTION
     AND B.STRM = C.STRM
     AND B.SESSION_CODE = C.SESSION_CODE
     AND C.EXAM_TIME_CODE = B.EXAM_TIME_CODE
     AND A.STRM = '1910'
     AND B.CLASS_EXAM_TYPE = 'FIN'
     AND C.CLASS_EXAM_TYPE = 'FIN'
     AND C.INSTITUTION = 'DUKEU')
  GROUP BY  A.STRM,  C.EXAM_TIME_CODE,  C.EXAM_DT,  C.EXAM_START_TIME,  C.EXAM_END_TIME,  C.MON,  C.TUES,  C.WED,  C.THURS,  C.FRI,  C.SAT,  C.SUN,  C.CLAS_STRT_TIME_FR,  C.CLAS_STRT_TIME_TO,  TO_CHAR( TO_CHAR(B.EXAM_DT,'YYYY-MM-DD'), 'DAY')
  ORDER BY 2

Hello Scott,

TO_CHAR(SYSDATE, 'DAY') will work.  That will give you all caps.  However, you can also use 'Day' and 'day' if you prefer.

A coworker recently asked me for a way to have a query determine whether or not it was running on a Monday or not.  I managed to come up with a solution but it was a bit convoluted so I wondered if anyone had a better method.  Here's what I did (as SQL)

SELECT  CASE    WHEN TRUNC(SYSDATE, 'DDD') = NEXT_DATE(TRUNC(SYSDATE, 'D'), 'MONDAY') THEN 'Y'    ELSE 'N'  ENDFROM DUAL 

For context sake:

• TRUNC(SYSDATE, 'DDD') "rounds down" a date(time) to the nearest day
  • This is only needed in SQL, as SYSDATE is not a true date, in PS Query you could use %currentdatein and eliminate the need for this
• TRUNC(SYSDATE, 'D') "rounds down" a date(time) to the first day of the week
  • This is dependent on your system settings, but for APU this means Sunday
• NEXT_DATE(DATE, 'MONDAY') returns the date of the next Monday after the input date
  • If the input date is a Monday it will return a full week later

Hi Jeffrie, 

thanks for your quick tip. Unfortunately, I get "Error in running query because of SQL Error, Code=918, Message=ORA-00918: column ambiguously defined (50,380)."

Could someone please help me with this? 

I want to get the day of the week from the Exam Date field on the EXAM_CODE_TBL. I am using the formula that Dan recommended in an earlier post, but some funkiness is occuring on the SQL generation side of things.

Here is my simple expression:

TO_CHAR(B.EXAM_DT, 'DAY')

When I run the query, I get back this Error, Code=1722, Message=ORA-01722: invalid number (50,380).

I noticed that the SQL is converting my expression the following:

 TO_CHAR( TO_CHAR(B.EXAM_DT,'YYYY-MM-DD'), 'DAY')

How might I get around this? My ultimate goal is to generate a new column with the day of the week based on the date in the Exam Date field, i.e. if exam date = 4/28/2025 then I want to see "Monday." 

Thanks!

Anna 

My entire SQL:

SELECT A.STRM, C.EXAM_TIME_CODE, TO_CHAR(C.EXAM_DT,'YYYY-MM-DD'), TO_CHAR(CAST((C.EXAM_START_TIME) AS TIMESTAMP),'HH24.MI.SS.FF'), TO_CHAR(CAST((C.EXAM_END_TIME) AS TIMESTAMP),'HH24.MI.SS.FF'), C.MON, C.TUES, C.WED, C.THURS, C.FRI, C.SAT, C.SUN, TO_CHAR(CAST((C.CLAS_STRT_TIME_FR) AS TIMESTAMP),'HH24.MI.SS.FF'), TO_CHAR(CAST((C.CLAS_STRT_TIME_TO) AS TIMESTAMP),'HH24.MI.SS.FF'), SUM( A.ENRL_TOT), TO_CHAR( TO_CHAR(B.EXAM_DT,'YYYY-MM-DD'), 'DAY')
  FROM PS_CLASS_TBL A, PS_CLASS_EXAM B, PS_EXAM_CODE_TBL C
  WHERE ( A.CRSE_ID = B.CRSE_ID
     AND A.CRSE_OFFER_NBR = B.CRSE_OFFER_NBR
     AND A.STRM = B.STRM
     AND A.SESSION_CODE = B.SESSION_CODE
     AND A.CLASS_SECTION = B.CLASS_SECTION
     AND B.STRM = C.STRM
     AND B.SESSION_CODE = C.SESSION_CODE
     AND C.EXAM_TIME_CODE = B.EXAM_TIME_CODE
     AND A.STRM = '1910'
     AND B.CLASS_EXAM_TYPE = 'FIN'
     AND C.CLASS_EXAM_TYPE = 'FIN'
     AND C.INSTITUTION = 'DUKEU')
  GROUP BY  A.STRM,  C.EXAM_TIME_CODE,  C.EXAM_DT,  C.EXAM_START_TIME,  C.EXAM_END_TIME,  C.MON,  C.TUES,  C.WED,  C.THURS,  C.FRI,  C.SAT,  C.SUN,  C.CLAS_STRT_TIME_FR,  C.CLAS_STRT_TIME_TO,  TO_CHAR( TO_CHAR(B.EXAM_DT,'YYYY-MM-DD'), 'DAY')
  ORDER BY 2

Hello Scott,

TO_CHAR(SYSDATE, 'DAY') will work.  That will give you all caps.  However, you can also use 'Day' and 'day' if you prefer.

A coworker recently asked me for a way to have a query determine whether or not it was running on a Monday or not.  I managed to come up with a solution but it was a bit convoluted so I wondered if anyone had a better method.  Here's what I did (as SQL)

SELECT  CASE    WHEN TRUNC(SYSDATE, 'DDD') = NEXT_DATE(TRUNC(SYSDATE, 'D'), 'MONDAY') THEN 'Y'    ELSE 'N'  ENDFROM DUAL 

For context sake:

• TRUNC(SYSDATE, 'DDD') "rounds down" a date(time) to the nearest day
  • This is only needed in SQL, as SYSDATE is not a true date, in PS Query you could use %currentdatein and eliminate the need for this
• TRUNC(SYSDATE, 'D') "rounds down" a date(time) to the first day of the week
  • This is dependent on your system settings, but for APU this means Sunday
• NEXT_DATE(DATE, 'MONDAY') returns the date of the next Monday after the input date
  • If the input date is a Monday it will return a full week later

Hello Anna,

You can try TO_CHAR(TO_DATE(A.EXAM_DT), 'DAY').  That should work. PSQuery likes to try to convert dates to strings so you need to convert it back to a date and then back to TO_CHAR.  Silly, but that is PSQuery for you.

Hi Jeffrie, 

thanks for your quick tip. Unfortunately, I get "Error in running query because of SQL Error, Code=918, Message=ORA-00918: column ambiguously defined (50,380)."

Could someone please help me with this? 

I want to get the day of the week from the Exam Date field on the EXAM_CODE_TBL. I am using the formula that Dan recommended in an earlier post, but some funkiness is occuring on the SQL generation side of things.

Here is my simple expression:

TO_CHAR(B.EXAM_DT, 'DAY')

When I run the query, I get back this Error, Code=1722, Message=ORA-01722: invalid number (50,380).

I noticed that the SQL is converting my expression the following:

 TO_CHAR( TO_CHAR(B.EXAM_DT,'YYYY-MM-DD'), 'DAY')

How might I get around this? My ultimate goal is to generate a new column with the day of the week based on the date in the Exam Date field, i.e. if exam date = 4/28/2025 then I want to see "Monday." 

Thanks!

Anna 

My entire SQL:

SELECT A.STRM, C.EXAM_TIME_CODE, TO_CHAR(C.EXAM_DT,'YYYY-MM-DD'), TO_CHAR(CAST((C.EXAM_START_TIME) AS TIMESTAMP),'HH24.MI.SS.FF'), TO_CHAR(CAST((C.EXAM_END_TIME) AS TIMESTAMP),'HH24.MI.SS.FF'), C.MON, C.TUES, C.WED, C.THURS, C.FRI, C.SAT, C.SUN, TO_CHAR(CAST((C.CLAS_STRT_TIME_FR) AS TIMESTAMP),'HH24.MI.SS.FF'), TO_CHAR(CAST((C.CLAS_STRT_TIME_TO) AS TIMESTAMP),'HH24.MI.SS.FF'), SUM( A.ENRL_TOT), TO_CHAR( TO_CHAR(B.EXAM_DT,'YYYY-MM-DD'), 'DAY')
  FROM PS_CLASS_TBL A, PS_CLASS_EXAM B, PS_EXAM_CODE_TBL C
  WHERE ( A.CRSE_ID = B.CRSE_ID
     AND A.CRSE_OFFER_NBR = B.CRSE_OFFER_NBR
     AND A.STRM = B.STRM
     AND A.SESSION_CODE = B.SESSION_CODE
     AND A.CLASS_SECTION = B.CLASS_SECTION
     AND B.STRM = C.STRM
     AND B.SESSION_CODE = C.SESSION_CODE
     AND C.EXAM_TIME_CODE = B.EXAM_TIME_CODE
     AND A.STRM = '1910'
     AND B.CLASS_EXAM_TYPE = 'FIN'
     AND C.CLASS_EXAM_TYPE = 'FIN'
     AND C.INSTITUTION = 'DUKEU')
  GROUP BY  A.STRM,  C.EXAM_TIME_CODE,  C.EXAM_DT,  C.EXAM_START_TIME,  C.EXAM_END_TIME,  C.MON,  C.TUES,  C.WED,  C.THURS,  C.FRI,  C.SAT,  C.SUN,  C.CLAS_STRT_TIME_FR,  C.CLAS_STRT_TIME_TO,  TO_CHAR( TO_CHAR(B.EXAM_DT,'YYYY-MM-DD'), 'DAY')
  ORDER BY 2

Hello Scott,

TO_CHAR(SYSDATE, 'DAY') will work.  That will give you all caps.  However, you can also use 'Day' and 'day' if you prefer.

A coworker recently asked me for a way to have a query determine whether or not it was running on a Monday or not.  I managed to come up with a solution but it was a bit convoluted so I wondered if anyone had a better method.  Here's what I did (as SQL)

SELECT  CASE    WHEN TRUNC(SYSDATE, 'DDD') = NEXT_DATE(TRUNC(SYSDATE, 'D'), 'MONDAY') THEN 'Y'    ELSE 'N'  ENDFROM DUAL 

For context sake:

• TRUNC(SYSDATE, 'DDD') "rounds down" a date(time) to the nearest day
  • This is only needed in SQL, as SYSDATE is not a true date, in PS Query you could use %currentdatein and eliminate the need for this
• TRUNC(SYSDATE, 'D') "rounds down" a date(time) to the first day of the week
  • This is dependent on your system settings, but for APU this means Sunday
• NEXT_DATE(DATE, 'MONDAY') returns the date of the next Monday after the input date
  • If the input date is a Monday it will return a full week later