CODEWITHSUNDEEP
sqloracle-database
John Doe
John Doe

Reputation: 55

Oracle SQL placeholders and generating text

Please help me articulate this question better by reading my scenario and question.

I am wondering if the following scenario is possible:

I have a table like this:

ID # of APPLES
1  3
2 15
3 87
4 56

And another table like this:

ID Description
1 I have %d Apples
2 You have %d Apples

What I want is to fetch data from these two tables such that my result would look like:

I have 3 Apples
I have 15 Apples
I have 87 Apples
I have 56 Apples
You have 3 Apples
You have 15 Apples
You have 87 Apples
You have 56 Apples

My question is, can this be done in Oracle SQL?

EDIT: modified how the result should look like

Upvotes: 2

Views: 483

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

You may use left & right outer joins with the help of ceil,trunc, replace & lpad functions and operators :

select result from
 (
  select replace(l.Description,'%d',lpad(numApples,2,' ')) result, numApples
    from apples a left outer join lu_apples l on ( ceil(a.id/2) = l.id ) 
   union all
  select replace(l.Description,'%d',lpad(numApples,2,' ')) result, numApples
    from apples a right outer join lu_apples l on ( trunc(3-a.id/2) = l.id )  
 )  
order by result, numApples;

RESULT
---------------
I have 3 Apples
I have 15 Apples
I have 56 Apples
I have 87 Apples
You have 3 Apples
You have 15 Apples
You have 56 Apples
You have 87 Apples

Demo

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270513

If your result had two rows, then you can use replace() and a join:

select replace(t2.description, '%d', t1.num_apples)
from t1 join
     t2
     on t1.id = t2.id;

If it had eight rows, you could use a cross join:

select replace(t2.description, '%d', t1.num_apples)
from t1 cross join
     t2
     on t1.id = t2.id;

To get 4 rows, I suppose you could do:

select t.*
from (select replace(t2.description, '%d', t1.num_apples)
      from t1 cross join
           t2
           on t1.id = t2.id
      order by row_number() over (partition by t2.id order by t2.id)
     ) t
where rownum <= (select count(*) from t2);

This matches the values arbitrarily. It is unclear to me if there is deterministic logic in the matching between the tables.

Upvotes: 1

Daniel Marcus
Daniel Marcus

Reputation: 2686

assuming your two tables are #temp3 and #temp 4 try this:

select replace(description, '%d', apples) from #temp3 a join #temp4 b on a.id=b.id

Upvotes: 0

Related Questions