Topic
2 replies Latest Post - ‏2013-06-04T10:42:19Z by BVNChowdary
Dan-M
Dan-M
4 Posts
ACCEPTED ANSWER

Pinned topic URGENT: Cognos SQL error when using embedded SELECT stmt

‏2009-04-30T18:06:33Z |
Folks,

I have a Data Manager build that uses Cognos SQL (to take make the code work for DB2 and Oracle). However, I run into an issue with COALESCE or CASE statements if they use a sub-select as a parameter:

Stmt 1:
COALESCE(EVW."SHIPFROMBUSINESSLOCATIONCODE_I",9999) AS SHIP_FROM_BUS_LOCATION_CODE_ID

Stmt 2:
COALESCE((SELECT "NAME" FROM "RCMSCHEMA"."LOCATION_VW" WHERE INTERNAL_ID = VW.SHIPFROMBUSINESSLOCATIONCODE_I AND VOCABULARY IN ('urn:epcglobal:epcis:vtype:BusinessLocation')), '9999') AS SHIP_FROM_BUS_LOCATION_CODE

Both of these statements work in native SQL for DB2 and Oracle.
However, when I turn on Cognos SQL, Stmt 2 will generate this error:

1. ERROR
DM-DBM-0400 UDA driver reported the following on connection ITS-RCM:

UDA-SQL-0357 A syntax error was detected in the SQL request.

<Message Name="CCLMessage" File="udamsgs" Severity="Error" Nesting="6" ><MessageComponents ID="0xaa6df68d" ><MessageParms><MessageParm Name="CCLMessageParm" Type="string" Value="select" /></MessageParms></MessageComponents></Message>

Any help is greatly appreciated!

Dan
  • Paulo Lacerda
    Paulo Lacerda
    1 Post
    ACCEPTED ANSWER

    Re: URGENT: Cognos SQL error when using embedded SELECT stmt

    ‏2013-04-27T18:18:56Z  in response to Dan-M

    Click on Advanced >> then clear the Cognos SQL checkbox.

  • BVNChowdary
    BVNChowdary
    3 Posts
    ACCEPTED ANSWER

    Re: URGENT: Cognos SQL error when using embedded SELECT stmt

    ‏2013-06-04T10:42:19Z  in response to Dan-M

    try changing your query to

     

    SELECT COALESCE("NAME", '9999') AS SHIP_FROM_BUS_LOCATION_CODE
     FROM "RCMSCHEMA"."LOCATION_VW" WHERE INTERNAL_ID = VW.SHIPFROMBUSINESSLOCATIONCODE_I AND VOCABULARY IN ('urn:epcglobal:epcis:vtype:BusinessLocation')

     

     

    Thanks,

    Chowdary