Board index » delphi » Invalid use of keyword 'WHEN'

Invalid use of keyword 'WHEN'

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

 

Re:Invalid use of keyword 'WHEN'


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

--

Alain Quesnel
alainqsanss...@compuserve.com

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

news:3e973b0d@newsgroups.borland.com...
Quote
> 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

Re:Invalid use of keyword 'WHEN'


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=...

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

news:3e973b0d@newsgroups.borland.com...
Quote
> 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

Re:Invalid use of keyword 'WHEN'


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

Quote
"Robert Cerny" <robert.qwe.ce...@neosys.xrs.qwe.si> wrote in message

news:b7e0c1.3ak.1@neosys.xrs.si...
Quote
> 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=...

> "Srinivas Chundi" <nospample...@hotmail.com> wrote in message
> news:3e973b0d@newsgroups.borland.com...
> > 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

Re:Invalid use of keyword 'WHEN'


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=...

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

news:3e9ad12c@newsgroups.borland.com...
Quote
> 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?

Re:Invalid use of keyword 'WHEN'


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

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

news:3e973b0d@newsgroups.borland.com...
Quote
> 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

Re:Invalid use of keyword 'WHEN'


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

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

news:3e973b0d@newsgroups.borland.com...
Quote
> 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

Re:Invalid use of keyword 'WHEN'


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.

Quote
"Greg Gaughan" <b...@thinksql.co.uk> wrote in message

news:3e9b2d83@newsgroups.borland.com...
Quote
> 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" <nospample...@hotmail.com> wrote in message
> news:3e973b0d@newsgroups.borland.com...
> > 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

Re:Invalid use of keyword 'WHEN'


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

Quote
> 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

Re:Invalid use of keyword 'WHEN'


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=...

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

news:3e9ee15f$1@newsgroups.borland.com...
Quote
> 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.

Re:Invalid use of keyword 'WHEN'


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

--

Alain Quesnel
alainqsanss...@compuserve.com

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

Quote
> "Srinivas Chundi" <nospample...@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

Re:Invalid use of keyword 'WHEN'


Quote
"Alain Quesnel" <alainqsanss...@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

Quote
> Alain Quesnel
> alainqsanss...@compuserve.com

> "Brian Evans @promaxis.com>" <brian<nospam> wrote in message
> news:3e9efd50$1@newsgroups.borland.com...
> > "Srinivas Chundi" <nospample...@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

Other Threads