SQL and Selecting Multiple Values 1

[nicks60610]

I have a SQL statement I am running on an ASP page, and basically all it does is take 3 variables and display the information based on these 3 in a table. The problem I have is I want to display the results for 2 separate departments in one table. So I did this:

If Request.QueryString("Department&quot = 06211-06212 Then
sql = "SELECT....AND WO_WORK_ORDER.DEPARTMENT = '06211' AND WO_WORK_ORDER.DEPARTMENT = '06212'...
Else
sql = "SELECT....AND WO_WORK_ORDER.DEPARTMENT = '" & Request.QueryString("Department&quot & "'
End If

The problem I see is that when I pass the value for "06211-06212" from the previous page, it does not take this value and execute the above statement, but instead tries to run the SQL statement with that value as "Department" which of course does not work.

Any ideas as to what I am doing wrong here???

Thanks for all your help!

 

[henslecd]

I think the string is most like delimited by a comma and not a dash. 06211, 06212...

 

[nicks60610]

Let me try changing that, thanks!!

 

[nicks60610]

I got it to respond properly, had to change the variable name from department # to a text variable, but now the issue becomes when I do the statement using department = .... or department = .... I do not get any results at all even though I know there should be 9 records displaying. I assume that my OR statement is what is causing this to fail.

 

[henslecd]

No, you need to make variables named arrDept (to make an array), k and t.

k is your counter variable and t is the number of departments that you need to count from the previous page.

So just use a hidden variable on the previous page that counts the number of department numbers being passed.

arrDept = Split(department, "," )

for k = 0 to t - 1

department = arrDept(k)
department = LTrim(department)

sql = "SELECT....AND WO_WORK_ORDER.DEPARTMENT = '" & department & "'

do whatever with sql statement...

next

 

[henslecd]

Did my solution work?

 

[nicks60610]

No : - ( , and I think the issue may be with the SQL statement, I tried the sql statement in Access and it works fine, but when I select tried using the array statement you gave me, it still would not show me the data. I even tried setting the variable 06211 = MOLDING, then on the second page, I set the IF then Statement to run a separate SQL statement if Department variable = MOLDING, i.e. it would then set Department = 06211 OR Department = 06212 which is how I have it working Access, but that too displays blank tables.

 

[henslecd]

Could you please write out the sql statement entirely?

 

[nicks60610]

Here it is:

sql = "SELECT EQ_LOCATIONS.DESCRIPTION as LOCDESC, EQ_LOCATIONS.EQUIPMENT, EQ_EVENTS.EMPLOYEE_ID as EVEMP, SY_WO_STATUS.DESCRIPTION as SYDESC, EQ_EVENTS.START_TIME, EQ_EVENTS.WO_BASE, EM_EMPLOYEE.EMPLOYEE_ID, EM_EMPLOYEE.SHIFT, WO_WORK_ORDER.ENTERED_BY, WO_WORK_ORDER.DEPARTMENT, WO_WORK_ORDER.REQUESTOR, WO_WORK_ORDER.STATUS, WO_WORK_ORDER.ENTERED_DATE, WO_WORK_ORDER.WO_BASE, WO_WORK_ORDER.EQUIPMENT,WO_WORK_ORDER.DESCRIPTION as WODESC, WO_COMMENTS.LONG_DESCRIPTION

FROM WO_WORK_ORDER, WO_COMMENTS, EM_EMPLOYEE, EQ_EVENTS, SY_WO_STATUS, EQ_LOCATIONS

WHERE WO_WORK_ORDER.WO_BASE = EQ_EVENTS.WO_BASE AND

WO_WORK_ORDER.EQUIPMENT = EQ_LOCATIONS.EQUIPMENT AND

WO_WORK_ORDER.STATUS = SY_WO_STATUS.CODE AND

WO_WORK_ORDER.PLANT = SY_WO_STATUS.PLANT AND

EQ_EVENTS.WO_BASE = WO_WORK_ORDER.WO_BASE AND

EM_EMPLOYEE.EMPLOYEE_ID = WO_WORK_ORDER.ENTERED_BY AND

EM_EMPLOYEE.PLANT = WO_WORK_ORDER.PLANT AND

WO_WORK_ORDER.PLANT = WO_COMMENTS.PLANT AND

WO_WORK_ORDER.WO_BASE = WO_COMMENTS.WO_BASE AND

WO_WORK_ORDER.WO_BASE NOT LIKE 'MP%' AND

WO_WORK_ORDER.ENTERED_DATE = TO_DATE('" & Request.QueryString("Day&quot & "','MM/DD/YY') AND

EM_EMPLOYEE.SHIFT LIKE '"& SHIFT_VAR & "' AND

WO_WORK_ORDER.DEPARTMENT = '06211' OR

WO_WORK_ORDER.DEPARTMENT = '06212' ORDER BY

EQ_EVENTS.START_TIME"

 

[checkai]

i think this last part should look like this instead

EM_EMPLOYEE.SHIFT LIKE '"& SHIFT_VAR & "' AND

(WO_WORK_ORDER.DEPARTMENT = '06211' OR

WO_WORK_ORDER.DEPARTMENT = '06212') ORDER BY

dlc

 

[nicks60610]

checkai, you are a Genius!!! I cannot thank you enough for this posting, it did the trick, yeeeeaaahhhh!!! Thank you so much!!!

 

[henslecd]

Your SQL is fine. The problem occurs because department can't be both '06211' and '06212' coming from the previous page.

You need to think logically how to make this sql statement work.

If those are the only 2 departments you are worried about...

What I would do is use the array code I posted above.

arrDept = Split(department, "," )

dept06211 = arrDept(0)
dept06212 = arrDept(1)

dept06211 = LTrim(dept06211)
dept06212 = LTrim(dept06212)

WO_WORK_ORDER.DEPARTMENT = '" & dept06211 & '" OR

WO_WORK_ORDER.DEPARTMENT = '" & dept06212 & '" ....


If they are not the only two departments I would....

Just use a hidden variable t on the previous page that counts the number of department numbers being passed.

arrDept = Split(department, "," )

for k = 0 to t - 1

department = arrDept(k)
department = LTrim(department)

sql = "SELECT....AND WO_WORK_ORDER.DEPARTMENT = '" & department & "'

response.write your output here

next

Make good use of response.write and response.end to debug errors.


For example,

for n = 0 to t = 2

some code

response.write

next

response.end

This will help you debug that for statement without having to execute any thing more on the page.

Chad

 

[checkai]

nicks60610, You're welcome! now gimme a STAR!

glad I could help...