a BIG problem with a select statement
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 1 of 1

Thread: a BIG problem with a select statement

  1. #1
    Join Date
    Jul 2003
    Posts
    4

    Unhappy a BIG problem with a select statement

    Hello guys im having troubles with this select statement:
    What is exactly my trouble?
    Well.
    This script should returns me all the guys that i have in my database, but, it should also show me who is still using my services or not, it should show their children, wifes and parents situation too.
    On that places where im using functions that returns me '1' or '0' if there are active values, i want it to force the select statement to put 'INACTIVE' or 'ACTIVE' instead of '1' or '0'.
    every time the select find '1' or '0' it should return 'INACTIVE' or 'ACTIVE' acording with what i want it to do in that place of the statement, how can i do it?
    Note that the function invert the values on some parts of the statement.

    Thanks in advance

    The select statement is below:

    select *
    from
    (
    SELECT
    'BEN' AS KIND,
    SUBSTR(SIS.sfn_SIT_MAN(a.COD_BEN, 1, TO_DATE('10/07/2003','dd/mm/yyyy')),1,65) AS SITUATION,
    a.COD_BEN ,a.NAME ,a.BORNDT,
    /* here is my trouble */
    (DECODE(sis.pcg_serv_guy.sfn_est_ativ_usr(a.COD_BEN, TO_DATE('10/07/2003 23:59:59','dd/mm/yyyy hh24:mi:ss'),
    a.COD_EMP),0,1,0))AS ACTIVE,
    /*This one above show me if there are
    some guys still using my services, it returns 0=INACTIVE 1=ACTIVE*/

    (DECODE(sis.pcg_serv_guy.sfn_est_ativ_usr(a.COD_BEN, TO_DATE('10/07/2003 23:59:59','dd/mm/yyyy hh24:mi:ss'),
    a.COD_EMP),0,0,1))AS INACTIVE
    /*This one above show me if the guys arent using my services, it returns 0=ACTIVE 1=INACTIVE*/
    FROM
    SIS.GUYS_TABLE a

    UNION

    SELECT
    'DEP' AS KIND,
    SUBSTR(SIS.SIS.sfn_SIT_MAN(a.COD_DEP, 2, TO_DATE('10/07/2003','dd/mm/yyyy')),1,65) AS SITUATION,
    a.COD_DEP,a.NAME,a.BORNDT,
    /* here is my trouble */
    (DECODE(sis.pcg_serv_guy.sfn_est_ativ_usr(a.COD_DEP, TO_DATE('10/07/2003 23:59:59','dd/mm/yyyy hh24:mi:ss'),
    a.COD_EMP),0,1,0)) AS ACTIVE,

    /*This one above show me if there are
    some guyss children still using my services, it returns 0=INACTIVE 1=ACTIVE*/

    (DECODE(sis.pcg_serv_guy.sfn_est_ativ_usr(a.COD_DEP, TO_DATE('10/07/2003 23:59:59','dd/mm/yyyy hh24:mi:ss'), a.COD_EMP),0,0,1)) AS INACTIVE

    /*This one above show me if the guyss children arent using my services, it returns 0=ACTIVE 1=INACTIVE*/

    FROM

    SIS.GUYS_CHILD_WIVES_TABLE a,
    SIS.HUSB_WIFES b

    WHERE
    a.COD_HUSB_WIFES = b.COD_HUSB_WIFES (+)
    AND
    b.MARRIED <> 'T'

    UNION

    SELECT

    'HW' AS KIND,
    SUBSTR(SIS.sfn_SIT_MAN(a.COD_DEP, 2, TO_DATE('10/07/2003','dd/mm/yyyy')),1,65) AS SITUATION,
    a.COD_DEP,a.NAME,a.BORNDT,

    /* here is my trouble */

    (DECODE(sis.pcg_serv_guy.sfn_est_ativ_usr(a.COD_DEP, TO_DATE('10/07/2003 23:59:59','dd/mm/yyyy hh24:mi:ss'),
    a.COD_EMP),0,1,0)) AS ACTIVE,

    /*This one above show me if there are
    some guyss wifes,parents...etc still using my services, it returns 0=INACTIVE 1=ACTIVE*/

    (DECODE(sis.pcg_serv_guy.sfn_est_ativ_usr(a.COD_DEP, TO_DATE('10/07/2003 23:59:59','dd/mm/yyyy hh24:mi:ss'), a.COD_EMP),0,0,1)) AS INACTIVE

    /*This one above show me if the guyss wifes,parents... arent using my services, it returns 0=ACTIVE 1=INACTIVE*/

    FROM
    SIS.GUYS_CHILD_WIVES_TABLE a,
    SIS.HUSB_WIFES b
    WHERE
    a.COD_HUSB_WIFES = b.COD_HUSB_WIFES (+)
    AND
    b.MARRIED = 'T'
    order by 1, 2)
    Last edited by brdarkmoon; 07-10-2003 at 03:40 PM.
    That which is dreamed can never be undreamed...
    Share on Google+

Posting Permissions

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



Click Here to Expand Forum to Full Width