Hi everyone
I am frequently asked how to create a custom SQL folder that will generate a known list of values. The following two examples of SQL are identical in their output and you are free to choose the one that you prefer. In terms of performance, the first is more efficient but is a little more difficult to understand.
Both pieces of code produce a folder with two items: an item called PRODLINE and an item called SEQUENCE. You can use the SEQUENCE when creating an alternate sort. Have fun!
SELECT
DECODE(ROWNUM,
1,'MINI-WIDGET',
2,'SUPER-WIDGET',
3,'MEGA-WIDGET',
4,'WONDER-WIDGET') PRODLINE,
ROWNUM SEQUENCE
FROM DUAL
CONNECT BY LEVEL <= 4
or
SELECT 'MINI-WIDGET' PRODLINE, 1 SEQUENCE FROM DUAL
UNION
SELECT 'SUPER-WIDGET' PRODLINE, 2 SEQUENCE FROM DUAL
UNION
SELECT 'MEGA-WIDGET' PRODLINE, 3 SEQUENCE FROM DUAL
UNION
SELECT 'WONDER-WIDGET' PRODLINE, 4 SEQUENCE FROM DUAL
Fortune 500 and the Art of Execution
-
The Fortune 500 Companies 2018 rankings came out last week, and browsing
the list, the following random thoughts struck me about the list and the
technolo...
6 years ago
3 comments:
Hi,
your second example should be:
SELECT 'MINI-WIDGET' Prodline, 1 sequence from dual
UNION
SELECT 'SUPER-WIDGET' Prodline, 2 sequence from dual
UNION
SELECT 'MEGA-WIDGET' Prodline, 3 sequence from dual
UNION
SELECT 'WONDER-WIDGET' Prodline, 4 sequence from dual
order by sequence;
Kind of typo, I guess.
First example is very usefull in reducing code.
Hi Ingmar
You are absolutely correct. I will adjust the code. Thanks for pointing this out.
Michael
Hi Michael
Good job congratulations. I have one question. How Do I create calculation field in Discoverer Desktop, for instance:
Header
======
Field1 = 5000
Detail
======
Calculation Field =
Field1 + amount (6500)
Next record
Calculation Field =
(6500) + amount (7500)
Next record
Calculation field =
(7500) + amount (8000)
.....
.....
Please Help me
Post a Comment