concatenating fields using sql

Forums:

I have a custom page for teachers where I'm showing a specific log entry type. I'm having issues showing the time field correctly. For some reason, PowerSchool created separate date and time fields and the time field is actually being stored as the number of seconds past midnight. I was able to write a SQL statement to convert that integer into a time using Oracle SQL developer and it works great there:

SELECT
TO_CHAR(l.Entry_Date,'YYYY/MM/DD') as EntryDate,
TO_CHAR( TO_DATE( CONCAT(FLOOR(l.Entry_Time/60/60), MOD(l.Entry_Time/60,60)), 'HH24MI'), 'HH:MI AM') as EntryTime,
l.Entry as EntryText
FROM Log l
INNER JOIN Students s
ON l.StudentID = s.ID
WHERE s.ID = 20191 AND l.LogTypeID=4438
ORDER BY EntryDate DESC

I also used the || concatenate operator with success in the developer tool.

However, neither method works when I copy it over to my custom page [ I do change the 20191 to ~(ID) ]. I don't know if PowerSchool won't let us use the concatenate functions or if something else is going on. When I take out the concatenate part, the page displays the correct information. Here is what is currently working on my custom page:

SELECT
TO_CHAR(l.Entry_Date,'YYYY/MM/DD') as EntryDate,
FLOOR(l.Entry_Time/60/60) as EntryHour,
MOD(l.Entry_Time/60,60) as EntryMinute,
l.Entry as EntryText
FROM Log l
INNER JOIN Students s
ON l.StudentID = s.ID
WHERE s.ID = ~(ID) AND l.LogTypeID=4438
ORDER BY EntryDate DESC

Thanks for any help or insights you can provide.

Have you tried the classic " & " concatenate? I've noticed Powerschool pages tend to handle a variety of string conversion automagically, so forcibly attempting query modifications can cause havoc.

Try stripping the concat() function and using a string concat method instead.

J Bartee
Westside Union School District
Software and Web Specialist
j.bartee@westside.k12.ca.us

Subscribe to Comments for "concatenating fields using sql"