How to use SELECT result record as DECODE parameters?

Question

Is it possible to use SELECT result as DECODE parameters when this SELECT returns only one record with prepared string? For example:

SELECT replace(replace(serialized_data)..)..) as result FROM table

Returns following result in ONE ROW:

0,'label0',1,'label1',2,'label2'

But when I put this to DECODE it's being interpreted as ONE PARAMETER. Is there possiblity to convert this result "string" to "pure" sql code? ;)

Thanks for any help.

I'm doing a report, but fields with multiply choice are returning only ID of a choice. And translation of those ID is saved like this: '0=BLACK;1=GREEN;2=YELLOW' (in one column) — mack80, Dec 28 '11 at 13:48

score 0 · Answer 1 · answered Dec 28 '11 at 12:39

0

list_agg or wm_concat depending on version of oracle.

http://docs.oracle.com/cd/E14072_01/server.112/e10592/functions087.htm

answered Dec 28 '11 at 12:39

xQbert

34,733
2
41
62

thanks. unfortunately I've got 8i so LISTAGG is not available. I'm not sure how to use wm_concat. There's no clear documentation for analytic use, how to convert this result string to array/table? – mack80 Dec 28 '11 at 13:51

score 0 · Answer 2 · answered Dec 28 '11 at 12:57

0

You can use dynamic SQL:

declare
  DECTXT   varchar2(4000);
begin
  select replace(replace(serialized_data)..)..) as result into dectxt from table;
  execute immediate 'select decode(col1,' || DECTXT || ') from tab1';
end;

This is just a quick example, not showing any output, etc.

answered Dec 28 '11 at 12:57

John Doyle

7,475
5
33
40

@Mack80 - Can you call a stored procedure as this could be placed in a Stored Proc. – Robert Love Dec 28 '11 at 18:55

score 0 · Accepted Answer · answered Dec 28 '11 at 15:56

You could kind of replicate decode by use of instr and substr. An example below (which could probably be tidied up considerably but works):

select DTXT
      ,Nvl(
         Substr(
           DTXT
          ,Instr(DTXT, SEARCHTXT || VALMTCH)
           + Length(SEARCHTXT || VALMTCH)
          ,  Instr(DTXT, VALSEP, Instr(DTXT, SEARCHTXT || VALMTCH))
           - Instr(DTXT, SEARCHTXT || VALMTCH)
           - Length(SEARCHTXT || VALMTCH))
        ,CASEOTHER)
         as TXTMATCH
  from (select '0=BLACK;1=GREEN;2=YELLOW;' as DTXT
              ,'1' as SEARCHTXT
              ,';' as VALSEP
              ,'=' as VALMTCH
              ,'OTHER' as CASEOTHER
          from Dual)

You do need to have a semi-colon (VALSEP) at the end of the text though to ensure you can find the last value (though it's possible to work around that if I looked into it further).

How to use SELECT result record as DECODE parameters?

3 Answers3