1 2 Previous Next 19 Replies Latest reply: Oct 25, 2012 3:34 AM by ranit B RSS

    decode function

    Tshifhiwa
      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
          decode (code, 'GOVT', governmentname, null) governmentname

          or

          case code when 'GOVT' then governmentname else null end governmentname
          • 2. Re: decode function
            793965
            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
              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
                Tshifhiwa
                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
                  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
                    Ignoring the query with 'GOVERNMENTNAME' in it, the others give you the answer.
                    • 7. Re: decode function
                      793965
                      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
                        Tshifhiwa
                        its returning null value in everything
                        • 9. Re: decode function
                          John Stegeman
                          Then, I guess that you are not querying on organisationtypecode = 'GOVT'
                          • 10. Re: decode function
                            Veejays.User10302525-Oracle
                            Hi,

                            can you share some sample data?
                            • 11. Re: decode function
                              793965
                              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
                                Tshifhiwa
                                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
                                  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
                                    +1 to gogol's comment.
                                    1 2 Previous Next