Invalid use of keyword 'WHEN'

[Srinivas Chundi]

I have the following code in my delphi 4.0 application connected to a SQL
server database using ODBC connection.

qryParts.Close;
with qryParts.SQL do
begin
Clear;
Add('SELECT P.SUPPLIER, P.PREFIX');
Add(', CASE WHEN (P.PREFIX = C.EVENT) THEN
C.ENGINEER_ID');
Add(' ELSE C.SQA_ENGINEER_ID');
Add(' END AS ASSIGNED_SQA_ENGINEER');
Add('FROM PART P');
Add('JOIN (SELECT DISTINCT SUPPLIER, SQA_ENGINEER_ID,
ENGINEER_ID, EVENT');
Add(' FROM CONTACTS');
Add(' WHERE CONTACT_TYPE = ''SQAPQC'') C ON C.SUPPLIER =
P.SUPPLIER');
end;

qryParts.Open;

At the last statement I receive the following error statement.

"Project **** raised an exception class EDBEngine error with message
'Invalid use of keyword'.
Token: When
Line Number: 2"

I have run the sql statement independently in the query analyzer and it runs
well.
Can you please tell me how I can get this sql statement to work. Thanks
-- M

[Alain Quesnel]

I can't test this right now, but I believe you need to remove the word
"when".

--

Alain Quesnel
alainqs...@compuserve.com

"Srinivas Chundi" <nospam...@hotmail.com> wrote in message
news:3e97...@newsgroups.borland.com...

[Robert Cerny]

1. Don't crosspost
2. set qryParts.RequestLive := false; assuming you connect using BDE
With Requestlive BDE parses SQL to retrieve additional metadata (field
origin, indexes,...).

--
Robert Cerny
http://codecentral.borland.com/codecentral/ccWeb.exe/author?authorid=18355

"Srinivas Chundi" <nospam...@hotmail.com> wrote in message
news:3e97...@newsgroups.borland.com...

[Srinivas Chundi]

1. My apologies for the cross posting.
2. I do connect using the BDE. If I need the "RequestLive := True", is there
a way around this error?

-- M

"Robert Cerny" <robert.q...@neosys.xrs.qwe.si> wrote in message
news:b7e0c1...@neosys.xrs.si...

[Robert Cerny]

You don't need requestlive. And it can't work anyway, because from such
select statement it's impossible to guess what and how to update.
If you want updatable query, set CachedUpdates := true and attach TUpdateSQL
to it.

--
Robert Cerny
http://codecentral.borland.com/codecentral/ccWeb.exe/author?authorid=18355

"Srinivas Chundi" <nospam...@hotmail.com> wrote in message

news:3e9a...@newsgroups.borland.com...

[Greg Gaughan]

You have a syntax error. You are missing a space before the first FROM
keyword (and before the JOIN keyword).

Also the double quotes are non-standard but that might be ok if you're using
a non-standard server.

Regards,
Greg Gaughan www.thinksql.co.uk

"Srinivas Chundi" <nospam...@hotmail.com> wrote in message

news:3e97...@newsgroups.borland.com...

[Gyler St. James]

Your problem seems to be the parenethsis around the (P.PREFIX =
C.EVENT). Remove those and it should be fine. If not, you could re-write the
CASE statement to use the first syntax instead.

SELECT P.SUPPLIER, P.PREFIX,
CASE
WHEN P.PREFIX = C.EVENT THEN C.ENGINEER_ID // <- removed paranthesis
ELSE C.SQA_ENGINEER_ID
END AS ASSIGNED_SQA_ENGINEER
FROM PART P
JOIN ( SELECT DISTINCT SUPPLIER, SQA_ENGINEER_ID,
ENGINEER_ID, EVENT
FROM CONTACTS

WHERE CONTACT_TYPE = 'SQAPQC') C ON C.SUPPLIER = P.SUPPLIER

OR

SELECT P.SUPPLIER, P.PREFIX,
CASE P.PREFIX // <- moved input value
WHEN C.EVENT THEN C.ENGINEER_ID
ELSE C.SQA_ENGINEER_ID
END AS ASSIGNED_SQA_ENGINEER
FROM PART P
JOIN ( SELECT DISTINCT SUPPLIER, SQA_ENGINEER_ID,
ENGINEER_ID, EVENT
FROM CONTACTS

WHERE CONTACT_TYPE = 'SQAPQC') C ON C.SUPPLIER = P.SUPPLIER

"Srinivas Chundi" <nospam...@hotmail.com> wrote in message
news:3e97...@newsgroups.borland.com...

[Srinivas Chundi]

If I am right, the Add adds a space between successive lines of the query. I
remember seeing code that does not explicitly attempt to add spaces. In any
case, I have added spaces as you suggest. It still causes the same error.

"Greg Gaughan" <be...@thinksql.co.uk> wrote in message
news:3e9b...@newsgroups.borland.com...

[Brian Evans]

"Srinivas Chundi" <nospam...@hotmail.com> wrote ...

> If I am right, the Add adds a space between successive lines of the query.
I
> remember seeing code that does not explicitly attempt to add spaces. In
any
> case, I have added spaces as you suggest. It still causes the same error.

Find a tool you can use to see the SQL being sent to the server. For
example I use OracleMonitor.EXE which comes with DOA a
set of components for accessing oracle databases. This will let
you see if the query reaching the database is what you think it is.

Brian Evans

[Robert Cerny]

Add add a new line, but that's in most cases enough.
Did you miss my other reply?

--
Robert Cerny
http://codecentral.borland.com/codecentral/ccWeb.exe/author?authorid=18355

"Srinivas Chundi" <nospam...@hotmail.com> wrote in message
news:3e9ee15f$1...@newsgroups.borland.com...

[Alain Quesnel]

For MSSQL, that would be Microsoft SQL Profiler. It comes with MSSQL 7 &
2000.

--

Alain Quesnel
alainqs...@compuserve.com

"Brian Evans @promaxis.com>" <brian<nospam> wrote in message
news:3e9efd50$1...@newsgroups.borland.com...

[Brian Evans]

"Alain Quesnel" <alainqs...@compuserve.com> wrote...

> For MSSQL, that would be Microsoft SQL Profiler. It comes with MSSQL 7 &
> 2000.

Thats more at the database level. There is usually also a client level tool
that also shows some information on the results returned. For example
going through the BDE you could use the Borland supplied SQL Monitor
to see what SQL was being sent to any BDE database. Tool is usually
specific to that delphi database connection component set.

If such a tool can't be found then the MS SQL profiler can do it but
you end up with a lot of other stuff thrown in and no info if any
query was successful, how many rows modified or returned etc.

Brian Evans