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

Thread: a BIG problem witha select statement

Hybrid View

  1. #1
    Join Date
    Jul 2003
    Posts
    4

    Unhappy a BIG problem witha select statement

    Hello guys i´m having troubles with this select statement:
    What is exactly my trouble?
    well
    on that places where i´m using functions that returns me if there are active values with 1 or 0, i want it to force the select statement to put INACTIVE OR ACTIVE instead of 1 or 0...did you undertand?
    every time the select found 1 or 0 it should return INACTIVE OR ACTIVE acording with the function syntax.

    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)
    That which is dreamed can never be undreamed...

  2. #2
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422

    Re: a BIG problem witha select statement

    Originally posted by brdarkmoon
    /* 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*/
    Is this what you want instead?
    Code:
    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,'ACTIVE','INACTIVE')
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

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