[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index] [Date Index] [Thread Index]
[SQR-USERS Info] [SQRUG Home Page]

Re: Order by a selected values DECODE



Judith,
   I usually use DECODE - especially for more complex expressions. It
varies by database so I didn't use that as an example. For instance in
oracle the function reads DECODE whereas SQLBase reads @DECODE... I
don't know if all databases have a DECODE function or something
similar...

 ORACLE:
 order by DECODE(a.type,'om',1,'dt',2,'msc',3,4)

 SQLBase:
 order by @DECODE(a.type,'om',1,'dt',2,'msc',3,4)

For anyone who'd like to see DECODE used in a slightly more complex
situation here is an example. This is an Oracle SQL*Plus script I wrote
that simulates the Oracle DESCRIBE command - Don't ask me why I wrote
it... I just write stuff!

/* ***************************************************************** */
/* *                                                               * */
/* *    MODULE: ALLCOL01.SQL                                       * */
/* *    AUTHOR: TONY DELIA.                                        * */
/* *      DATE: 08/27/96.                                          * */
/* *      DESC: ALL TABLE COLUMNS 01.                              * */
/* *     USAGE: start allcol01 <table name> <owner>                * */
/* *                                                               * */
/* ***************************************************************** */

START rptbeg02.sql

TTITLE 'ALL_TAB_COLUMNS Describe      ALLCOL01.SQL'   SKIP 2 -
       'Table: ' ttab                                 SKIP 2

COL htab  NEW_VALUE ttab                              NOPRINT
COL hcol  HEADING 'Name'                              FORMAT A31
COL hnul  HEADING 'Null?'                             FORMAT A8
COL htyp  HEADING 'Type'                              FORMAT A16
COL hid   HEADING 'ID#'                               FORMAT 999
COL hdis  HEADING 'Distinct|Rows'                     FORMAT 999,999

BREAK ON REPORT ON htab SKIP PAGE

COMPUTE COUNT   OF hcol ON REPORT
COMPUTE COUNT   OF hcol ON htab

/* ***************************************************************** */
/* *   Select Table Columns                                        * */
/* ***************************************************************** */

SELECT table_name                                     htab,
       column_name                                    hcol,
       DECODE(nullable,'N','NOT NULL',NULL)           hnul,
       data_type ||
         DECODE(data_type,
           'DATE',NULL,
           'LONG',NULL,
           'NUMBER',
              DECODE(data_precision,
                NULL,DECODE(data_scale,
                       NULL,NULL,'(38)'),
                '(' || TO_CHAR(data_precision) ||
                DECODE(data_scale,
                  NULL,NULL,
                  ','||TO_CHAR(data_scale) ) || ')' ),
           '(' || TO_CHAR(data_length) || ')' )       htyp,
       column_id                                      hid,
       num_distinct                                   hdis
  FROM all_tab_columns
 WHERE table_name LIKE UPPER('&&1')
   AND owner         = UPPER('&&2')
 ORDER BY table_name   ASC,
          column_id    ASC

/* ***************************************************************** */
/

START rptend01.sql

/* ***************************************************************** */
/* *     END OF SQL PROGRAM                                        * */
/* ***************************************************************** */

                                          -Tony DeLia

-------------------------------------------------------------------------------
SAMPLE OUTPUT:

ALL_TAB_COLUMNS Describe      ALLCOL01.SQL

Table: PS_LIFE_ADD_BEN

                                                               Distinct
Name                            Null?    Type              ID#     Rows
------------------------------- -------- ---------------- ---- --------
EMPLID                          NOT NULL VARCHAR2(11)        1
EMPL_RCD#                       NOT NULL NUMBER(38)          2
PLAN_TYPE                       NOT NULL VARCHAR2(2)         3
BENEFIT#                        NOT NULL NUMBER(38)          4
EFFDT                           NOT NULL DATE                5
DEDUCTION_END_DT                         DATE                6
COVERAGE_BEGIN_DT               NOT NULL DATE                7
COVERAGE_END_DT                          DATE                8
COVERAGE_ELECT                  NOT NULL VARCHAR2(1)         9
COVERAGE_ELECT_DT               NOT NULL DATE               10
BENEFIT_PLAN                    NOT NULL VARCHAR2(6)        11
LIFE_ADD_COVRG                  NOT NULL VARCHAR2(1)        12
FLAT_AMOUNT                     NOT NULL NUMBER(38)         13
FACTOR_XSALARY                  NOT NULL NUMBER(4,3)        14
BENEFITS_BASE                   NOT NULL VARCHAR2(1)        15
-------------------------------
                             15

----------------------------------------------------------------------------------

Weaver, Judith R wrote:
>
> We had a similar situation, but used decode instead - does anyone have any
> input on the relative merits (timewise) for one method over another?
> Judy Weaver
> United Space Alliance
> Application Engineering Services
> WeaverJR@usafoo.unitedspacealliance.com
> Phone: (407) 799-6019       Fax: (407) 799-5970
>
> > ----------
> > From:         Tony DeLia[SMTP:tdelia@EROLS.COM]
> > Reply To:     SQR-USERS@USA.NET
> > Sent:         Wednesday, November 04, 1998 7:51 PM
> > To:   Multiple recipients of list SQR-USERS
> > Subject:      Re: Order by a selected values
> >
> > Ngo,
> >    Also try...
> >
> > begin-select
> >
> > a.type
> >
> >  from ps_bi_hdr  a
> > order by instr('*om*dt*mst*',a.type,1)
> >
> > end-select
> >
> > The idea being if type = 'om'  instr returns 2...
> >                if type = 'dt'  instr returns 5...
> >                if type = 'mst' instr returns 8
> >
> > Rearrange the instr string to change the order you need...
> >
> > The 3rd parameter of INSTR is the starting position...
> >
> > Use a character you know will never be used in the type column as a
> > delimiter for your values... this simplifies the operation...
> >
> >                                          -Tony DeLia
> >
> > Ray Ontko wrote:
> > >
> > > Ngo,
> > >
> > > order by instr(' om mst dt ',' '||type||' ')
> > >
> > > Ray
> > > > Hi every body.
> > > >
> > > >         I have a select in my sqr like this
> > > > begin-select
> > > > type
> > > > from
> > > > ps_bi_hdr
> > > > end-select.
> > > >         Type in my select have those values (om,msc,dt).  I want my
> > select order by om first then dt then msc.  How could I do this.
> > > >
> > > > Merci
> > > >
> > > > Ngo Baochau
> > > > baochaun@mail.transcontinental.ca
> > > > tel: (514) 335 - 1466/4943
> > > > http://www.transcontinental.ca
> > >
> > > [application/ms-tnef is not supported, skipping...]
> > >
> > > ----------------------------------------------------------------------
> > > Ray Ontko       |  Ray Ontko & Co  |  "Time for a new signature line."
> > > rayo@ontko.com  |  Richmond, In    |  See us at http://www.ontko.com/
> >
> > --
> > Tony DeLia
> > AnswerThink Consulting Group
> > PeopleSoft Solutions Practice - Delphi Partners
> > tdelia@erols.com
> >

--
Tony DeLia
AnswerThink Consulting Group
PeopleSoft Solutions Practice - Delphi Partners
tdelia@erols.com