Link to home
Start Free TrialLog in
Avatar of cmdown
cmdown

asked on

SQL Query Problem - Liberum Helpdesk

We use the opensource liberum helpdesk - a classic asp application.  I have been modifying the code to allow us to have more than 1 close status code.  However changing a <> operator to < in the select statement  is not returning the expected results.  

The select query is built up piece by piece in the asp code (which is attached) but in essence does the following (I have replaced the database lookups with fixed values):

SELECT
      TOP 100 p.id, p.title, p.start_date, p.uid, p.uemail, r.uid AS ruid, pri.pname, s.sname
FROM
      ((problems AS p INNER JOIN tblUsers AS r ON p.rep = r.sid) INNER JOIN priority AS pri ON p.priority = pri.priority_id)
      INNER JOIN status AS s ON p.status = s.status_id
WHERE
      p.status<>100 AND p.rep="user1"
ORDER BY
      p.id DESC

The above code returns any record that doesn't have a p.status of 100 , i.e. a legacy 'closed' job.  We have updated the database so that any value > 90 indicates a 'closed' job which works elsewhere in the application.

However, amending the p.status code line to read:

     p.status<90 AND p.rep="user1"

results in ALL jobs being returned - including those with a p.status of 100.  

I've spent a couple of hours bashing by head over this and I am probably missing something very simple.  I would be very grateful if someone could have  a look at the attached code and give me some sort of idea as to what is going on with this.

For reference I have been testing this using a 2003 web server running IIS 6.5 and a SQL server running SQL Server 2008 (not express).

Full code for liberum is at: http://www.liberum.org/
view-asp.txt
ASKER CERTIFIED SOLUTION
Avatar of Saurabh Bhadauria
Saurabh Bhadauria
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cmdown
cmdown

ASKER

Thanks Saurv.  I'm out of work tomorrow but will give this a try on Wednesday.
Avatar of cmdown

ASKER

Hi Saurv.

That worked, thanks.