Rosti Zacharias · Sep 22, 2018

Trouble with comparison


Somewhat weird behaviour in testing values in a resulting query.

I have a table where I need to read through a list of data, and apply a charge for matching occurrences. However, only ONE charge for one set of data. I have applied a DISTINCT function to the query, but as I cannot get a completely unique set, the DISTINCT function does not de-duplicate all of the sets.

I have therefore attempted to look for a value that if it repeats for the one data set, I can choose to skip the function that applies the charge.

The problem, is that a comparison in CSP is failing to produce the required result.

I have a CSP page with the code in a script block .. a bit complicated, bet these are the crucial segments..

<script language="Cache" method="OnPreHTTP" arguments="" returntype="%Boolean">

1. A query loop

set lQuery = "SELECT DISTINCT studentclasses.ClassName,studentclasses.FamilyID,studentclasses.StudentID,studentclasses.SchoolName,"
set lQuery = lQuery_"TimeTable.ClassID,TimeTable.AddlItem1,TimeTable.AddlCost1,TimeTable.AddlItem2,TimeTable.AddlCost2 "
set lQuery = lQuery_"FROM SQLUser.LCDA.studentclasses "
set lQuery = lQuery_"inner join SQLUser.LCDA.TimeTable on StudentClasses.ClassID=TimeTable.ID "
set lQuery = lQuery_"where TimeTable.addlcost1>' ' and TimeTable.addlcost1<>'5' "
set lQuery = lQuery_"order by studentclasses.familyid, studentclasses.studentid,TimeTable.ClassID"
set rs=##class(%ResultSet).%New() // Set up a new result set object 
set lStatus = rs.Prepare(lQuery) // Check the syntax of the Query

set lClassID = "" // prime the global

do rs.Execute()

for  {

2. I get a result

ClassName FamilyID StudentID SchoolName ClassID AddlItem1 AddlCost1 AddlItem2 AddlCost2
2018|Grade 2 Ballet|\19|Year 4 119 135 Roselea 88 Costume Fees 38
2018|Grade 2 Ballet|\19|Year 4 119 135 Roselea 88 Costume Fees 38
2018|Primary Ballet|\13|Year 2 119 136 Mobbs Lane 120 Costume Fees 30

3. After processing the first row, I store the values of the row in a globals, before reading the next row

set lFamilyID = rs.GetData(2) 
set lSudentID = rs.GetData(3) 
set lClassID = rs.GetData(5) 

4. The iteration of processing the next row, if FamilyID, StudentID and ClassID are the same as values stored in globals, then a duplicate entry exists and I need to skip this row.

5. I test for the values

if lFamilyID = rs.GetData(2) {..
if lSudentID = rs.GetData(3) {...
// so far so good..
if lClassID '= rs.GetData(5) {
// only process if ClassIDs are different
// apply charges, etc..

6. Problem is that the comparison of "if lClassID '= rs.GetData(5)" return FALSE, every time, regardless of the values of lClassID or rs.GetData(5). A trace through the process, storing the values, shows that according to the comparison, 
first row values are  "" '= "88" - result is FALSE (should be TRUE and process the next code block)
second row values are  "88" '= "88" - result is FALSE - correct
first values are  "88" '= "120" - result is FALSE (should be TRUE and process the next code block)

7. I have tried all sorts of alternatives, like placing rs.GetData(5) into a global before doing the comparison, but same result. 

At a loss WHY the comparison is failing to correctly evaluate the contents and produce the correct result, while the values of the FamilyID and Student ID produce the correct results.

Any clues would be appreciated.


0 188
Discussion (2)1
Log in or sign up to continue

As IClassID seems to be numeric, did you try to force a pure numeric comparison ?

eg.  if +lClassID '= +rs.GetData(5) {

or if (lClassID - rs.GetData(5)) {

Thanks Robert.. will give that a shot..