This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Oct 25, 2012 1:34 AM by ranit B RSS

decode function

adf009 Explorer
Currently Being Moderated
hi how can i use the deceode function to make a choose for a data to display for example select decode(code,'GOVT',GOVERMANTNAME,'PRIV',NULL)GOVERMENTNAME FROM DEPARTMENT. i what to display goverment name if the code is GOVT ELSE NULL am i database
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Edited by: adf0994 on 2012/10/25 10:16 AM
  • 1. Re: decode function
    John Stegeman Oracle ACE
    Currently Being Moderated
    decode (code, 'GOVT', governmentname, null) governmentname

    or

    case code when 'GOVT' then governmentname else null end governmentname
  • 2. Re: decode function
    793965 Explorer
    Currently Being Moderated
    You are already correct. I just modified a little:

    select decode(code,'GOVT', GOVERMANTNAME, NULL) GOVERMENTNAME FROM DEPARTMENT;
  • 3. Re: decode function
    veejays_user10302525-Oracle Journeyer
    Currently Being Moderated
    Hi,

    hope below is what you are looking for.
    SQL> create table department(code varchar2(20));
    
    Table created.
    
    SQL> insert into department values('GOVT');
    
    1 row created.
    
    SQL> insert into department values('ABC');
    
    1 row created.
    
    SQL> select decode(code,'GOVT','GOVERMANTNAME',NULL)GOVERMENTNAME FROM DEPARTMENT;
    
    GOVERMENTNAME
    --------------------------------
    GOVERMANTNAME
    
    
    2 rows selected.
    In case not please share the table structure with some sample data.

    Regards,
    Vijay.
  • 4. Re: decode function
    adf009 Explorer
    Currently Being Moderated
    NO NO , i don't what to hard code the goverment name,the table structure is
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    ORGSUBTYPECODE NOT NULL CHAR(50)
    ORGSUBTYPENAME NOT NULL VARCHAR2(60)
    ORGANISATIONTYPECODE NOT NULL CHAR(5)

    i already have goverment name in my table,so i don't need to hard code the name,i what something like
    SELECT Distinct lutorgsubtypes.orgsubtypecode, decode(lutorgsubtypes.organisationtypecode,'GOVT',LutOrgsubtypes.ORGSUBTYPENAME,'PRIV')ORGANISATIONTYPE FROM LUT_ORGSUBTYPES LutOrgsubtypes
    WHERE LutOrgsubtypes.ORGANISATIONTYPECODE = :typecode
  • 5. Re: decode function
    veejays_user10302525-Oracle Journeyer
    Currently Being Moderated
    Hi,

    assuming government name is in column orgsubtypeame and if organisationtypecode is 'GOVT' you want to dislay the value from orgsubtypeame else null.

    The query would be
    SELECT Distinct lutorgsubtypes.orgsubtypecode, decode(lutorgsubtypes.organisationtypecode,'GOVT',LutOrgsubtypes.ORGSUBTYPENAME,null)ORGANISATIONTYPE FROM LUT_ORGSUBTYPES LutOrgsubtypes
    WHERE LutOrgsubtypes.ORGANISATIONTYPECODE = :typecode
  • 6. Re: decode function
    John Stegeman Oracle ACE
    Currently Being Moderated
    Ignoring the query with 'GOVERNMENTNAME' in it, the others give you the answer.
  • 7. Re: decode function
    793965 Explorer
    Currently Being Moderated
    I think you have already written the correct one.

    You wrote:
    SELECT Distinct lutorgsubtypes.orgsubtypecode, decode(lutorgsubtypes.organisationtypecode,'GOVT',LutOrgsubtypes.ORGSUBTYPENAME,'PRIV')ORGANISATIONTYPE FROM LUT_ORGSUBTYPES LutOrgsubtypes
    WHERE LutOrgsubtypes.ORGANISATIONTYPECODE = :typecode
    We suggested:
    SELECT Distinct lutorgsubtypes.orgsubtypecode, decode(lutorgsubtypes.organisationtypecode,'GOVT',LutOrgsubtypes.ORGSUBTYPENAME,null)ORGANISATIONTYPE FROM LUT_ORGSUBTYPES LutOrgsubtypes
    WHERE LutOrgsubtypes.ORGANISATIONTYPECODE = :typecode
  • 8. Re: decode function
    adf009 Explorer
    Currently Being Moderated
    its returning null value in everything
  • 9. Re: decode function
    John Stegeman Oracle ACE
    Currently Being Moderated
    Then, I guess that you are not querying on organisationtypecode = 'GOVT'
  • 10. Re: decode function
    veejays_user10302525-Oracle Journeyer
    Currently Being Moderated
    Hi,

    can you share some sample data?
  • 11. Re: decode function
    793965 Explorer
    Currently Being Moderated
    This query will give you the value of LutOrgsubtypes.ORGSUBTYPENAME column if you have 'GOVT' in lutorgsubtypes.organisationtypecode column.

    And if you do not have that then it will obviously return null.
  • 12. Re: decode function
    adf009 Explorer
    Currently Being Moderated
    sample data
    Insert into EXPORT_TABLE (ORGANISATIONTYPECODE,ORGSUBTYPECODE,ORGNAME,ORGSUBTYPENAME) values ('GOVT ','Sole Proprietorship                               ',null,'Agriculture, Forestry & Fisheries');
    Insert into EXPORT_TABLE (ORGANISATIONTYPECODE,ORGSUBTYPECODE,ORGNAME,ORGSUBTYPENAME) values ('GOVT ','DAC                                               ',null,'Arts & Culture');
    Insert into EXPORT_TABLE (ORGANISATIONTYPECODE,ORGSUBTYPECODE,ORGNAME,ORGSUBTYPENAME) values ('GOVT ','DBE                                               ',null,'Basic Education');
    Insert into EXPORT_TABLE (ORGANISATIONTYPECODE,ORGSUBTYPECODE,ORGNAME,ORGSUBTYPENAME) values ('GOVT ','DCSP                                              ',null,'Civilian Secretariat of Police');
    Insert into EXPORT_TABLE (ORGANISATIONTYPECODE,ORGSUBTYPECODE,ORGNAME,ORGSUBTYPENAME) values ('GOVT ','DCOM                                              ',null,'Communications');
    Insert into EXPORT_TABLE (ORGANISATIONTYPECODE,ORGSUBTYPECODE,ORGNAME,ORGSUBTYPENAME) values ('GOVT ','DCG                                               ',null,'Cooperative Governance');
    Insert into EXPORT_TABLE (ORGANISATIONTYPECODE,ORGSUBTYPECODE,ORGNAME,ORGSUBTYPENAME) values ('GOVT ','DCS                                               ',null,'Correctional Services');
    Insert into EXPORT_TABLE (ORGANISATIONTYPECODE,ORGSUBTYPECODE,ORGNAME,ORGSUBTYPENAME) values ('GOVT ','DOD                                               ',null,'Defence');
    Insert into EXPORT_TABLE (ORGANISATIONTYPECODE,ORGSUBTYPECODE,ORGNAME,ORGSUBTYPENAME) values ('GOVT ','DED                                               ',null,'Economic Development');
    Insert into EXPORT_TABLE (ORGANISATIONTYPECODE,ORGSUBTYPECODE,ORGNAME,ORGSUBTYPENAME) values ('PRIV ','CC                                                ',null,'Closed Corporation');
    Insert into EXPORT_TABLE (ORGANISATIONTYPECODE,ORGSUBTYPECODE,ORGNAME,ORGSUBTYPENAME) values ('PRIV ','PVT                                               ',null,'Private Limited');
    Insert into EXPORT_TABLE (ORGANISATIONTYPECODE,ORGSUBTYPECODE,ORGNAME,ORGSUBTYPENAME) values ('PRIV ','PTN                                               ',null,'Partnership');
    Insert into EXPORT_TABLE (ORGANISATIONTYPECODE,ORGSUBTYPECODE,ORGNAME,ORGSUBTYPENAME) values ('PRIV ','SPT                                               ',null,'Sole Proprietorship');
  • 13. Re: decode function
    793965 Explorer
    Currently Being Moderated
    There is space character in the right side of GOVT i.e 'GOVT '

    So did you write the in proper way in your DECODE function??
  • 14. Re: decode function
    John Stegeman Oracle ACE
    Currently Being Moderated
    +1 to gogol's comment.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points