|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
August 9th, 2004, 01:23 AM
|
|||
|
|||
sql statement is not working...
Hello,
I´m trying to create an sql statement with an inner join. Can anybody tell me, why this won´t works? The variables date_from and date_to are defined. Perhaps it´s a syntax probem? sql = "SELECT table2.name, COUNT(table1.category_id) AS Anzahl FROM table1 WHERE OPENTIME BETWEEN '" & date_from & "' AND '" & date_to & "' AND t_call2.QUEUE_ID = '111' INNER JOIN table2 ON table1.category_id = table2.id GROUP BY table1.category_id, table2.name ORDER BY Anzahl DESC" Thanks!
|
|
#2
August 10th, 2004, 11:19 AM
|
|||
|
|||
|
Hi there.
I think you have a couple of errors. First of all you use fields in the query that you never select, then you use the WHERE statment before the INNER JOIN. It is a bit messy to understand your SQL statment and a bit difficult to give an exact answer but try this. Code:
SELECT COUNT(table1.category_id) As Anzah1, table2.name, table2.id, OPENTIME, t_call2.QUEUE_ID
FROM table1
INNER JOIN table2 ON table1.category_id = table2.id
WHERE OPENTIME BETWEEN '" & FormatDateTime ('date_from', vblongdate) & "' AND '" & FormatDateTime ('date_to', vblongdate) & "' AND t_call2.QUEUE_ID = '111'
GROUP BY table2.name , table2.id
ORDER BY COUNT(table1.category_id)DESC
Dont think it will work but give it a go.
|
|
#3
August 10th, 2004, 01:32 PM
|
||||
|
||||
|
FormatDateTime isn't a valid SQL Function.
It appears you are trying to combine SQL with VBScript, which won't work. Code:
strSql = "SELECT Table2.Name, COUNT(Table1.Category_ID) As Anzahl FROM Table1 INNER JOIN Table2 On (Table1.CategoryID = Table2.ID) WHERE OpenTime BETWEEN '" & date_From & "' AND '" & date_to & "' AND t_call2.QueueID = '111'<--- Where is this coming from? You aren't joining on a t_call2 table? GROUP BY Table1.CategoryID, Table2.Name ORDER BY Anzahl DESC" You would have to use the VBScript FormatDateTime function to format the dates before you pass them to the sql statement.
|
|
| Viewing: ASP Free Forums > Database > SQL Development > sql statement is not working... |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
