Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Posts
    2

    Unanswered: problem with query very urgent

    Hi,

    Iam really new to postgresql. I really dont know how to write the query in it. can any one help me in writing the following oracle query in postgreSQL. Here is the oracle query:


    SELECT DECODE((SELECT MODULE_ID FROM HR_USER_MODULE WHERE USER_ID=1049 AND MODULE_ID= C.CURRENT_MODULE_ID
    UNION
    SELECT R.MODULE_ID FROM HR_ROLE_MODULE R,HR_USER_ROLE RT WHERE R.ROLE_ID=RT.ROLE_ID AND RT.USER_ID=1049 AND
    MODULE_ID= C.CURRENT_MODULE_ID),NULL,NULL,A.ACTION_ID),
    A.NAME FROM WF_ACTION A,WF_CONTROL C WHERE
    A.ACTION_ID=C.ACTION_ID AND C.STATUS_ID=1101 AND
    C.WF_ITEM_TYPE_ID=1 ORDER BY A.ACTION_ID ;


    I have tried to conver the query but it failed to execute,it is giving parse error. Can any one help to sove it out. The query i have converted into postgreSQL is:


    SELECT (CASE WHEN ((SELECT MODULE_ID FROM HR_USER_MODULE WHERE
    USER_ID=1049 AND MODULE_ID= C.CURRENT_MODULE_ID UNION
    SELECT R.MODULE_ID FROM HR_ROLE_MODULE R,HR_USER_ROLE RT WHERE
    R.ROLE_ID=RT.ROLE_ID AND RT.USER_ID=1049 AND MODULE_ID= C.CURRENT_MODULE_ID) = NULL
    THEN
    NULL ELSE A.ACTION_ID END)) AS TEST,A.NAME FROM WF_ACTION A,WF_CONTROL C WHERE
    A.ACTION_ID=C.ACTION_ID
    AND C.WF_ITEM_TYPE_ID IN(1,2) ORDER BY A.ACTION_ID



    phani.

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Put the CASE around the MODULE_ID

    e.g.
    SELECT CASE( WHEN MODULE_ID IS NULL ...

    Remember you must compare nulls with "IS NULL" and not "= NULL".
    Thanks,

    Matt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •