Problem with Sql in Quick screen

[Brown, Diane]

> I have the problem with the following piece of code.  This is a
> subscreen(which
>  functions as a lookup screen).  The sql gives the error everytime I run
> it.  I
>  have used different sql in a similiar subscreen and it works.  I think
> the
> problem is that CALL is a reserved word.  In the Powerhouse manual, CALL
> in the
>  sql is used to call different procedures within the sql.  
<...>

I think you are correct -- the syntax error occurs because CALL is a
reserved word in SQL.  

You can avoid this problem by using the standard SQL "delimited identifier"
notation -- in other words, put the name of the table or column (or
whatever) that conflicts with an SQL reserved word in double-quotes.

For example:
declare x cursor for select * from "CALL"
access x
...

Note that when you put names in double-quotes in SQL, the names are
considered *case-sensitive*, so the name within double-quotes much match the
exact name stored in the database's system tables.
For example
	select * from "Call"
is not the same as
	select * from "call"
and so on.

For many (most?) database products, the names of tables are stored in upper
case by default, but that's not true of all databases.

Try changing your SQL statement to something like this:

> declare cSLTCID cursor for &
> select  &
>         ca.call_id, &
>         c.call_desc, &
>         r.area_id &
> from &
>         wkbem.call_area_alloc ca, &
>         wkdis."CALL" c,&    <== double-quotes around "call" keyword; I'm
> guessing names are in upper case
>         wkdis.route_call rc, &
>         wkdis.route r &
> where &
>         ca.call_id = c.call_id &
>         and c.call_id = rc.call_id &
>         and rc.route_id = r.route_id &
>         and upper(c.call_desc) like :tCALL_DESC
> 
> 
Hope that helps,
db (brownd@cognos.com)

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
powerh-l@lists.swau.edu is gatewayed one-way to bit.listserv.powerh-l
This list is closed, thus to post to the list, you must be a subscriber.