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 i´m 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 i´m 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 aren´t 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 guys´s 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 guys´s children aren´t 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 guys´s 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 guys´s wifes,parents... aren´t 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