3 lines of SQL clarification

Question

I am having an issue with a report that is running on a site where data is not pulling as it should. Specifically, there are drop downs setup with tlist, From and To dates, Students and Sports. The issue is related to the Sports dropdown, or output.

The Dropdown

<select id="lt" name="lt">
<option value="-1" selected="selected">All Sports</option>
~[tlist_sql;
SELECT DISTINCT log.logtypeid, 
CASE WHEN log.subtype is null THEN ' ' ELSE log.subtype END subID, 
CASE WHEN ','||'~[gpv:lt]'||',' LIKE '%,' || log.subtype || '.' || CASE WHEN 
log.subtype is null THEN ' ' ELSE log.subtype END || ',%' THEN 'selected' ELSE '' END  isselected,
lt.Name logtype, 
CASE WHEN to_char(st.ValueT) is null THEN ' - NONE' ELSE ' - ' || to_char(st.ValueT)    END subtype
FROM log
INNER JOIN gen lt ON log.logtypeid = lt.id
LEFT OUTER JOIN gen st ON st.Name = to_char(lt.ID)
AND st.value = log.subtype
AND st.Cat = 'subtype'
WHERE lt.Cat = 'logtype'
AND log.logtypeid = '3935'
AND to_char(log.schoolid) like CASE WHEN ~(curschoolid) = 0 THEN '%' ELSE to_char(~(curschoolid)) END
ORDER BY subtype
              ;]
                <option value="~(logID;l).~(subtypeID;t)" ~(isselected;t)>~(LogType;t)~ (SubType;t)</option>
              [/tlist_sql]

The Output

~[tlist_sql;
SELECT s.Student_Number, s.grade_level, s.lastfirst student, to_char(st.ValueT) subtype, log.discipline_weapontype,
CASE WHEN log.discipline_hearingofficerflag = '1' THEN 'Yes' ELSE ' ' END as pp,
CASE WHEN log.discipline_weaponrelatedflag = '1' THEN 'Yes' ELSE ' ' END  as ss,
DBMS_LOB.SUBSTR(log.entry, 4000,1) Notes
FROM log
INNER JOIN students s ON log.studentid = s.id
~[if#cursel.~[gpv:studentID]=cur]INNER JOIN ~[temp.table.current.selection:students] temp ON temp.dcid=s.dcid[/if#cursel]
INNER JOIN gen lt ON log.logtypeid = lt.id
LEFT OUTER JOIN gen st ON st.Name = to_char(lt.ID)
AND st.value = log.subtype
AND st.Cat = 'subtype'
AND lt.Cat = 'logtype'
WHERE studentid IN
(SELECT studentid
FROM log
WHERE subtype IN
(SELECT subtype
FROM log
WHERE logtypeid = '3935'))
AND subtype IN ('29','43','100','101','102','104','105','106','107','109','110','111','112','113','114','115','116','117','119','120','121','122','123','125','126','127','128','129','130','131','132','133')
AND log.entry_date >= to_date('~[gpv:dateselect]','MM/DD/YYYY')
AND log.entry_date <= to_date('~[gpv:dateselect2]','MM/DD/YYYY')
AND 
~[if#cursel.~[gpv:studentID]#cur]
to_char(s.ID) LIKE CASE WHEN '~[gpv:studentID]' = '-1' THEN '%' ELSE '~[gpv:studentID]' END
[/if#cursel]
AND (
(','||to_char('~[gpv:lt]')||',' LIKE '%,' || to_char(log.logtypeid) || '.' || CASE WHEN log.subtype is null THEN ' ' ELSE log.subtype END || ',%')
OR (','||to_char('~[gpv:lt]')||',' LIKE '%,' || to_char(log.logtypeid) || '.-1,%')
OR (to_char(log.logtypeid) LIKE CASE WHEN to_char('~[gpv:lt]') = '-1' THEN '%' ELSE ','||to_char('~[gpv:lt]')||',' END)
  ) 
ORDER BY s.lastfirst, log.entry_date;alternatecolor]

<tr class="oddRow">
<td>~(count;-)</td>
<td name="stNumCol">~(stNum;t)</td>
<td name="gradeCol">~(grade;t)</td>
<td name="studentCol">~(Student;t)</td>
<td name="subtypeCol">~(st;t)</td>
<td name="dopCol">~(date of physical;t)&nbsp;</td>
<td name="ppCol">~(parent perm.;t)&nbsp;</td>
<td name="ssCol">~(student signa.;t)</td>
<td name="notesCol">~(notes;t)</td>
</tr>
[/tlist_sql]

When the report is run with All Sports selected, it pulls all students who have Sports logs

log.logtypeid = '3935'

or they can also be

log.subtype IN ('100','101','102','104','105','106','107','109','110','111','112','113','114','115','116','117','119','120','121','122','123','125','126','127','128','129','130','131','132','133')

Another set of data needs to be pulled, subtype '29' and '43', which are health logs. A 29 code disables someone from being able to tryout for a sport, and a 43 enables them to.

The Issue

When running All Sports, it shows all students with a Sports logs, and those students 29 and 43 codes also get displayed.. but when selecting an individual sport from the drop down, it does not show those student's 29 and 43 health log subtypes.

Any advice or suggestions would be appreciated. Thank you

Answer 1

Here is how to read the symbols you are having trouble with:

• || denotes concatenation in Oracle (as well as languages such as Java and C#) so any where you see || between two statements means that the system is reading them as one.
• Case statements are essentially if/then statements for SQL. The Case defines what to examine, and then When and Else states what do with specific scenarios.
• The to_char('') statements list places specific character formatting on the surrounding string which is most likely used so that you can concatenate the metadata.
• % is a wildcard character that is required for LIKE statements

This code makes a little more sense when you change the formatting:

AND (
    (','||to_char('~[gpv:lt]')||',' LIKE '%,' || to_char(log.logtypeid) || '.' || 
        CASE WHEN log.subtype is null 
        THEN ' ' 
        ELSE log.subtype 
        END || ',%')
 OR (','||to_char('~[gpv:lt]')||',' LIKE '%,' || to_char(log.logtypeid) || '.-1,%')
 OR (to_char(log.logtypeid) LIKE 
        CASE 
        WHEN to_char('~[gpv:lt]') = '-1' 
        THEN '%' 
        ELSE ','||to_char('~[gpv:lt]')||',' 
        END)  
) 

So basically it is checking the values of gpv:lt LIKE the values of log.logtypeid and log.subtype (when there is a subtype - aka is not null) OR it checks gpv:lt LIKE log.logtypeid.-1 OR where log.logtypeid LIKE anything when gpv:lt = -1 or = to gpv:lt if not = 1.

Please note I have no idea what that means to your application/report since I don't understand the logic of what you are trying to accomplish... but hopefully that statement in English above helps you translate your problem.