Oracle TRICk Question

Applications Programming
1

HI Guys ,

Below are the two columns ITEMS and STATE of table .

ITEMS                                              STATE 
'2345','ggdh','k5tg','dgyt','hFF'                  DF 
'1234','ghyt','DDD','GHTD','ABF'                   PQ

Can we get output in PL/SQL in below way ?
STATE='DF' and ITEMS in ('2345','ggdh','k5tg','dgyt','hFF') OR STATE='PQ' and ITEMS in ('1234','ghyt','DDD','GHTD','ABF')

Based on column entries it has to give output in above fashion . Please guide

2

Let me ask you a question, can you tell me the source of values for columns ITEMS and STATE? Are you fetching these values from any table or file?

'2345','ggdh','k5tg','dgyt','hFF' DF 
'1234','ghyt','DDD','GHTD','ABF' PQ
3

HI,
I am calling from table .

4

Then why don't you fetch it directly from table in your PL/SQL?

STATE='DF' and ITEMS in (select distinct ITEMS from my_table)
5

HI BIpi,

The output from that table is already displayed . I just want the output to come in this frame based on states .

V_variable= (STATE='DF' and ITEMS in ('2345','ggdh','k5tg','dgyt','hFF') OR STATE='PQ' and ITEMS in ('1234','ghyt','DDD','GHTD','ABF') )

Something like it should iterate based on state and items .
Thanks.

6

Use the xmlagg function to aggregate multiple rows onto one column:

SQL> select '(STATE=' || regexp_replace(state,'([[:alpha:]]{2})',q'['\1']') || ' AND 
ITEMS in (' || regexp_replace(rtrim (xmlagg (xmlelement (e, items || ',')).extract ('//text()'), ','),'([[:alnum:]]{3,4})',q'['\1']') || ')' 
from tmp_state_items group by state;

----------------------------------------------------------------
(STATE='DF' AND ITEMS in ('2345','ggdh','k5tg','hFF','dgyt')
(STATE='PQ' AND ITEMS in ('ABF','1234','ghyt','DDD','GHTD')
7

Hi BIPI ,

select '(STATE=' || regexp_replace(state,'([[:alpha:]]{2})',q'['\1']') || ' AND 
ITEMS in (' || regexp_replace(rtrim (xmlagg (xmlelement (e, items || ',')).extract ('//text()'), ','),'([[:alnum:]]{3,4})',q'['\1']') || ')'
from tmp_state_items group by state;

whare should i keep ITEMS values of '1234','ghyt','DDD','GHTD','ABF' in above query ??
Also add OR inbetween two outputs . Like

(STATE='DF' and ITEMS in ('2345','ggdh','k5tg','dgyt','hFF') OR STATE='PQ' and ITEMS in ('1234','ghyt','DDD','GHTD','ABF') )
8

Please always use code tag when posting code fragments or data samples.

If you want OR simply append it like I appended AND. I cannot tell where you can keep it, it totally depends upon how you want to implement it. I just gave you a method to form the frame that you requested. I hope this helps.