• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

USING DYNAMIC LIST

Hello
Is it possible to use dynamic list when using where  in (
example..........

select t1.* from mytable t1
where t1.id in (select id from mytable2)

if so then how

Regards
0
philsivyer
Asked:
philsivyer
3 Solutions
 
sdstuberCommented:
your example exactly correct

if it's not working what error do you get?
0
 
Swadhin RaySenior Technical Engineer Commented:
Check this link hope you will get solution for what you are looking for  :

http://www.oracle-base.com/articles/misc/DynamicInLists.php
0
 
philsivyerAuthor Commented:
Sorry
Should have been more specific -

using oracle pivot function as per

pivot
(
   SUM(Amount)
   FOR ID IN (SELECT  ID FROM MYTABLE2)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ThommyCommented:
Try the WITH-Clause

Coding Best Practices - Using Subqueries
http://oracleopedia.com/wiki/Coding_Best_Practices_-_Using_Subqueries
0
 
sdstuberCommented:
please provide sample data and expected results
0
 
awking00Commented:
In addition to sample data and expected results, please provide version of Oracle.
0
 
philsivyerAuthor Commented:
ORACLE 11G
0
 
philsivyerAuthor Commented:
I am (rightly or wrongly) using XMLAGG to join rows together in one string which returns something like..
'ID1','ID2','ID3','ID4'


pivot
(
   SUM(Amount)
   FOR ID IN (

SELECT
*
FROM
(
SELECT
SUBSTR(
''''||REPLACE(FEE_DESCRIPTION,',',''','''),
0,
LENGTH(''''||REPLACE(FEE_DESCRIPTION,',',''',''')
)-2) AS ID
FROM
(
SELECT
       RTRIM(
           EXTRACT(XMLAGG(XMLELEMENT("s",FEE_DESCRIPTION || ',')), '/s/text()').getstringval(),  ' '    )
           FEE_DESCRIPTION
           
FROM ETC ETC
0
 
sdstuberCommented:
please,  sample data and expected results
0
 
philsivyerAuthor Commented:
SEE ATTACHED
DATA-SET.xls
0
 
sdstuberCommented:
no you can't have the columns be dynamically generated.

It's part of the SQL rules,  the query must know all of the columns to be retrieved at the time the query is parsed.  Since you won't know the columns until after you already have the results, you can't do it in one sql.
0
 
sdstuberCommented:
as an alternate approach

either create a bunch of ID columns, sufficient to hold the maximum pivot you might attempt.  If you have too many, the pivot will simply populate the extras with NULL

or use dynamic sql.  
First query to find how many ID columns you'll need, then construct the sql query to have that many columns.
0
 
philsivyerAuthor Commented:
Thanks guys
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now