Is this a DW limitation?

[newbiejane]

Hi, guys.

I have this very long sql statement that works perfectly in Query analyzer,
and even in the asp page I made. I added it in DW by using Bindings (Dataset).
When I tested the query in that Bindings(Dataset) window, it showed me correct
results and without a problem. But when I hit the OK button, it appears in the
bindings window(small window on the right side of the screen) that there's a
syntax error in Line 2 using ISNULL.

I have used ISNULL twice in that query. And honestly, I dont see any problem
with it or the query itself.

Can anyone help me on this, please? I can post the query here if needed.

Please reply. I really need your expert help.

:confused;

[Lionstone]

I've never known dreamweaver to give a spurious error message unless I
edited the recordset code myself. If you haven't edited the page code at
all, then please post the query so we can take a look.

"newbiejane" <webforumsuser@macromedia.com> wrote in message
news:e4hr8i$fd6$1@forums.macromedia.com...

> I have used ISNULL twice in that query. And honestly, I dont see any
> problem
> with it or the query itself.

[newbiejane]

Here's the query.

SELECT
ISNULL(LEVEL1,PC) as company,
ISNULL(BRAVO.PLEV,ALPHA.PRIORITYLEVEL) as pl,
COUNTER,
AGE,
PERCENTAGE_SLA,
WITHINSLA,
TOTAL
FROM
(Select
A.PRIORITYLEVEL,
C.Client_Co_Name as PC,
Count(A.client_company_ref) as Counter,
max(A.AGE) as Age
from
(select
c.call_priority as PRIORITYLEVEL,
s.name as GROUP_NAME,
c.call_number as CALL_NUMBER,
datediff(mi, timedate_call_log, getdate()) as AGE,
client_company_ref
from logtable c join supportteam s
on c.assign_group = s.ref
where c.open_flag = 1 and (c.ref_composite in
(select ref_composite from cl_problem_type where ref_1 = 1)
And c.partition in (1,2)
or (c.partition = 20 and c.ref_composite = 2756 and c.call_ref = 'logged
online')))A
Join ClientsTable B
On A.client_company_ref = B.client_co_ref
Join ClientsTable C
On B.ref_1 = C.client_co_ref
Group by C.Client_Co_Name, A.PRIORITYLEVEL) ALPHA
FULL OUTER JOIN
(Select
D.CLIENT_CO_NAME as LEVEL1,
B.call_priority as PLEV,

CONVERT(VARCHAR,Round((Convert(float,sum(A.WITHINS LA))/Convert(float,sum(B.tot
al)))*100,2)) +'%' as percentage_SLA,
sum(A.WITHINSLA) as WITHINSLA,
sum(B.total) as TOTAL
from
(select
call_priority,
c.client_company_ref as COM_REF,
count(timedate_call_log)as WITHINSLA
from logtable c join supportteam s
on c.assign_group = s.ref
where datediff(d, c.timedate_call_log, getdate())=0
and (c.partition in (1,2) or (c.partition = 20 and c.ref_composite = 2756))
and ((call_priority=1 and datediff(mi, timedate_call_log,
first_call_time)<=15)
or (call_priority=2 and datediff(mi, timedate_call_log,
first_call_time)<=60)
or (call_priority=3 and datediff(mi, timedate_call_log,
first_call_time)<=120))
group by c.client_company_ref, call_priority) A
RIGHT OUTER JOIN
(select
call_priority,
c.client_company_ref as COM_REF,
count(timedate_call_log) as total
from logtable c join supportteam s
on c.assign_group = s.ref
where datediff(d, c.timedate_call_log, getdate())=0
and (c.partition in (1,2) or (c.partition = 20 and c.ref_composite = 2756))
group by c.client_company_ref, call_priority) B
on A.call_priority = B.call_priority and A.COM_REF = B.COM_REF
Join ClientsTable C
On B.COM_REF = C.client_co_ref
Join ClientsTable D
On C.ref_1 = D.client_co_ref
Group by D.Client_Co_Name, B.call_priority)BRAVO
ON ALPHA.PC = BRAVO.LEVEL1 AND ALPHA.PRIORITYLEVEL = BRAVO.PLEV
order by company,pl

As I mentioned earlier, it works perfectly in Query Analyzer and during the
test in DW. I get the results. But when I click the Ok button in the Dataset
window, it loads then I see an error message saying "MM_Error:Line2:Incorrect
syntax near 'PLEV'. The only thing that comes to mind is the PLEV variable in
The ISNULL statement.

I am really getting confused and frustrated because i know it works. Please
help.

[Lionstone]

Thats... quite a query.
Anyway, I don't think DW itself interprets the query at all; I think it
actually tries to run it, so what you're describing is a bit strange. I
can't think of why the second ISNULL would trip it up, either. Do you have
a parameter called PLEV, perhaps? That would cause problems. Or the line
number could be wrong, and it's being tripped up by one of your table
aliases (you have a couple with no space between the closing parenthesis and
the alias).

Because of the strangeness of it all, and especially because of the length
of the query, I'd suggest transferring this to a stored procedure and then
calling the stored procedure. It should alleviate any hiccups in the query
interpretation.

[newbiejane]

Thanks for your help. As it turned out, my boss wants to change some criteria for this and I had to create a different query.
Just wanted to say I really appreciated your help.