[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