Oracle select stmt

Greeting,

I posted a question at the link below and would like to know how to sub ''JSMITH'' with multiple names in the code slightwv provided.

http://www.experts-exchange.com/Database/Oracle/Q_28196188.html

Thanks.
mrongAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
I do not understand what "how to sub ''JSMITH'' with multiple names" means.

Please provide some sample data and expected results.
0
mrongAuthor Commented:
How to use in clause and change "JSMITH" to "TOM","FRANK","DAVE"?

thx
0
sdstuberCommented:
try this...

SELECT table_name, column_name, column_data
  FROM (SELECT table_name,
               column_name,
               EXTRACT(
                   xmltype(
                       NVL(
                           DBMS_XMLGEN.getxml(
                                  'select '
                               || column_name
                               || ' from '
                               || table_name
                               || ' where rownum=1 and '
                               || column_name
                               || q'[ in  (select column_value from table(ORA_MINING_VARCHAR2_NT('TOM','FRANK','DAVE')))]'
                           ),
                           '<a/>'
                       )
                   ),
                   '//' || column_name || '/text()'
               ).getstringval()
                   column_data
          FROM (SELECT column_name, table_name
                  FROM user_tab_columns
                 WHERE column_name LIKE '%USER%' AND table_name IN ('TAB1', 'TAB2')))
 WHERE column_data IS NOT NULL

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sdstuberCommented:
or

SELECT table_name, column_name, column_data
  FROM (SELECT table_name,
               column_name,
               EXTRACT(
                   xmltype(
                       NVL(
                           DBMS_XMLGEN.getxml(
                                  'select '
                               || column_name
                               || ' from '
                               || table_name
                               || ' where rownum=1 and '
                               || column_name
                               || q'[ MEMBER OF ora_mining_varchar2_nt('TOM', 'FRANK', 'DAVE')]'
                           ),
                           '<a/>'
                       )
                   ),
                   '//' || column_name || '/text()'
               ).getstringval()
                   column_data
          FROM (SELECT column_name, table_name
                  FROM user_tab_columns
                 WHERE column_name LIKE '%USER%' AND table_name IN ('TAB1', 'TAB2')))
 WHERE column_data IS NOT NULL

Open in new window

0
mrongAuthor Commented:
sdstuber and sdstuber,

I tried both of your code and found the same problem below.
Say I got 32 returned records for "TOM" alone and 80 returned records for "FRANK" alone.
Your code gives me 80 returned records when I put both "TOM" and "FRANK", instead of 112 records.

Thanks.
0
sdstuberCommented:
sdstuber and sdstuber,

there's only one of me  :)


It's probably the  

where rownum=1

filter from the original query, I didn't change anything except the part you asked for

I'll look at the rest of the functionality and post an update
0
sdstuberCommented:
I wasn't able to duplicate.

Can you provide some sample data that shows the failure?
0
sdstuberCommented:
I made an even simpler version - I waaaaay over complicated it by not taking into account
the dynamic sql which allows embedding the list easily


SELECT table_name, column_name, column_data
  FROM (SELECT table_name,
               column_name,
               EXTRACT(
                   xmltype(
                       NVL(
                           DBMS_XMLGEN.getxml(
                                  'select '
                               || column_name
                               || ' from '
                               || table_name
                               || ' where rownum =1 and '
                               || column_name
                               || q'[ in ('TOM', 'FRANK', 'DAVE')]'
                           ),
                           '<a/>'
                       )
                   ),
                   '//' || column_name || '/text()'
               ).getstringval()
                   column_data
          FROM (SELECT column_name, table_name
                  FROM user_tab_columns
                 WHERE column_name LIKE '%USER%' AND table_name IN ('TAB1', 'TAB2')))
 WHERE column_data IS NOT NULL


note, for me, this produces the same results as my other queries, so, while it should be more efficient, it's not likely to be more correct.  So, I'm still going to need some sample data that illustrates the failure.
0
mrongAuthor Commented:
Say for "TOM", I got the following return
tbl1 user TOM
tbl2 user  TOM
tbl3 user  TOM

For "FRANK", I got the following return
tbl2 user FRANK
tbl3 user FRANK
tbl4 user FRANK

If I put both of TOM and FRANK into the code, returned the following
tbl1 user TOM
tbl2 user  TOM
tbl3 user  TOM
tbl4 user FRANK

The correct output should be
tbl1 user TOM
tbl2 user  TOM
tbl3 user  TOM
tbl2 user FRANK
tbl3 user FRANK
tbl4 user FRANK
0
sdstuberCommented:
thank you


try this...


  SELECT table_name, column_name, EXTRACTVALUE(x.COLUMN_VALUE, '/text()')
    FROM user_tab_columns,
         XMLTABLE(
             ('//' || column_name || '/text()')
             PASSING xmltype(
                         NVL(
                             DBMS_XMLGEN.getxml(
                                    'select '
                                 || column_name
                                 || ' from '
                                 || table_name
                                 || ' where  '
                                 || column_name
                                 || q'[ in ('TOM','FRANK')]'
                             ),
                             '<a/>'
                         )
                     )
         ) x
   WHERE column_name LIKE '%USER%' AND table_name LIKE 'TBL_'
ORDER BY 1, 2, 3
0
mrongAuthor Commented:
('//' || column_name || '/text()')
             *
ERROR at line 4:
ORA-19102: XQuery string literal expected
0
sdstuberCommented:
hmmm, worked for me exactly as posted.
0
sdstuberCommented:
This has a string literal that will still allow for semi-dynamic content in the column names.


  SELECT table_name, column_name, EXTRACTVALUE(x.COLUMN_VALUE, '/text()')
    FROM user_tab_columns,
         XMLTABLE(
             '//ROW/*/text()'
             PASSING xmltype(
                         NVL(
                             DBMS_XMLGEN.getxml(
                                    'select '
                                 || column_name
                                 || ' from '
                                 || table_name
                                 || ' where  '
                                 || column_name
                                 || q'[ in ('TOM','FRANK')]'
                             ),
                             '<a/>'
                         )
                     )
         ) x
   WHERE column_name LIKE '%USER%' AND table_name LIKE 'TBL_'
ORDER BY 1, 2, 3;
0
mrongAuthor Commented:
sdstuber,

Tried your suggestion and taking forever to execute it.....

Thanks.
0
mrongAuthor Commented:
what is "ORDER BY 1, 2, 3" for?
thanks.
0
sdstuberCommented:
just to order the data by table,column,name

you don't have to use it
0
sdstuberCommented:
Tried your suggestion and taking forever to execute it.....


How many distinct table/column combinations do you have that match your WHERE clause?

If you have 10 tables with one USER column each, then that sql is going to run 10 other sqls, each of which will be turned into xml output which is then parsed.

That's a lot of work
0
mrongAuthor Commented:
I have over 1000 tables match WHERE clause.
0
sdstuberCommented:
I have over 1000 tables match WHERE clause.

that's the problem

you're doing this...

select username from table1 where username in ('TOM','FRANK')
select username from table2 where username in ('TOM','FRANK')
select username from table3 where username in ('TOM','FRANK')
select username from table4 where username in ('TOM','FRANK')
select username from table5 where username in ('TOM','FRANK')
...
select username from table998 where username in ('TOM','FRANK')
select username from table999 where username in ('TOM','FRANK')
select username from table1000 where username in ('TOM','FRANK')
...

and that's assuming there is only one USER column in each table.
It'll be even more work if there are more than one column.

It's not surprising that might take a long time
0
mrongAuthor Commented:
what if use distinct?
0
sdstuberCommented:
what are you going to try to make distinct?

why do you think that would be faster?  

Distinct does all the work of a non-distinct query, but then adds the extra step of a sort and search/removal of duplicates.
0
sdstuberCommented:
I removed the

 rownum=1

clause, you could put that back, it should make the looping queries faster

It'll change the results, but only you can determine which is correct for your needs

with rownum=1 you'll get one row for each table/column that has any of your searched for names, and it'll report the first one it gets (which is effectively random)

without rownum=1 you'll get one row for each table/column/name that has any of your searched for names, including rows where the name appears more than once


I'm going to guess that "with rownum=1" is what you want, but it is a little odd with the quasi-random results
0
mrongAuthor Commented:
I want to know if is there a record in the table which has user field=tom or frank. That is the reason used rownum=1
Thx
0
mrongAuthor Commented:
I think it only return the table name once even if both tom and frank are fund in that table. How to make it showing table names for each user?
Thx
0
sdstuberCommented:
How to make it showing table names for each user?

Do the "slow" version...

Remove the rownum=1

that will show you every name


or, slightly more complicated but possibly faster...


  SELECT table_name, column_name, EXTRACTVALUE(x.COLUMN_VALUE, '/text()')
    FROM user_tab_columns,
         XMLTABLE(
             '//ROW/*/text()'
             PASSING xmltype(
                         NVL(
                             DBMS_XMLGEN.getxml(
                                    'select '
                                 || column_name
                                 || ' from ('
                                 || 'select '
                                 || column_name
                                 || ', row_number() over(partition by '
                                 || column_name
                                 || ' order by 1) rn'
                                 || ' from '
                                 || table_name
                                 || ' where  '
                                 || column_name
                                 || q'[ in ('TOM','FRANK')]'
                                 || ') where rn = 1'
                             ),
                             '<a/>'
                         )
                     )
         ) x
   WHERE column_name LIKE '%USER%' AND table_name LIKE 'TBL_'

Open in new window



that will show you every column/table/name combination, but only once for each
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mrongAuthor Commented:
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.