# Newbie Query Question

**URL:** <https://forums.toadworld.com/t/newbie-query-question/37721>\
**Category:** Toad for Oracle & DevOps Toolkit\
**Created:** [May 16, 2016, 6:57am UTC](https://forums.toadworld.com/t/newbie-query-question/37721 "2016-05-16T06:57:37Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Michael\_W](https://avatars.discourse-cdn.com/v4/letter/m/8e7dd6/32.png) [@Michael\_W](https://forums.toadworld.com/u/Michael_W)\
**Post date:** [May 16, 2016, 6:57am UTC](https://forums.toadworld.com/t/newbie-query-question/37721/1 "2016-05-16T06:57:37Z")

</div>

Greetings All!

I am Extremely new to SQL and TOAD. My new job requires some knowledge on writing queries. I have enrolled in some classes but need to jump ahead with this query. For you this is child’s play! 🙂

I am selecting multiple columns from a single table. One column has multiple possible statuses - “Y, A, A-I, I and I-I” I need to write a query that will return ten examples of each status from within this table.

I Believe that covers what I need. Thank You in Advance for your kind replies! As time goes on (and I know what the hell I am doing! 🙂 ) I look forward to contributing and helping some other newbie along!

Have a Great Day!

---

<div class="post-metadata">

**Author:** ![Dennis\_Paulus](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/dennis_paulus/32/138_2.png) [@Dennis\_Paulus](https://forums.toadworld.com/u/Dennis_Paulus)\
**Post date:** [May 16, 2016, 10:46am UTC](https://forums.toadworld.com/t/newbie-query-question/37721/2 "2016-05-16T10:46:25Z")

</div>

I’m not sure if I’m answering your question, but I’m assuming you have a table with many rows and that column can have one of those possible values, and you want to translate that into the actual value. Here’s an example how to do that. If the column value can have all those values separated by a comma (like you showed), then that is a little more tricky and would probably need to use pl/sql to parse each value.

select distinct col1

from multicolumn\_test;

select distinct

```
   col1 col_value,

   decode(col1,'A','Active',

               'I','Inactive',

               'Y','You',

               'A-I','Active-Inactive',

               'I-I','Inactive-Inactive','Other') translated_value

```

from multicolumn\_test;

---

<div class="post-metadata">

**Author:** ![Dennis\_Paulus](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/dennis_paulus/32/138_2.png) [@Dennis\_Paulus](https://forums.toadworld.com/u/Dennis_Paulus)\
**Post date:** [May 16, 2016, 11:24am UTC](https://forums.toadworld.com/t/newbie-query-question/37721/3 "2016-05-16T11:24:40Z")

</div>

btw…if they are all in one cell (comma delimited) - here’s an example of the pl/sql you could use to parse the values from the column…

declare

n integer;

l\_string varchar2(255) := null;

l\_tmp varchar2(255) := null;

/_– Use your table and columns here –_/

cursor cur\_process\_records is

select col\_row c\_col\_row,

col1 c\_col1

from multicolumn\_test;

begin

/_– Loop thru each row –_/

for cur\_rec in cur\_process\_records loop

l\_string := cur\_rec.c\_col1||’,’;

/_– Loop thru comma delimited string –_/

loop exit when l\_string is null;

n := instr(l\_string, ‘,’);

l\_tmp := substr(l\_string, 1, n-1);

l\_string := substr(l\_string, n+1);

/_– Display results to output –_/

dbms\_output.put\_line('Row: ‘||cur\_rec.c\_col\_row||’ - Column Values: '||l\_tmp);

end loop;

end loop;

end;

---

<div class="post-metadata">

**Author:** ![Michael\_W](https://avatars.discourse-cdn.com/v4/letter/m/8e7dd6/32.png) [@Michael\_W](https://forums.toadworld.com/u/Michael_W)\
**Post date:** [May 16, 2016, 11:28am UTC](https://forums.toadworld.com/t/newbie-query-question/37721/4 "2016-05-16T11:28:17Z")

</div>

Thank You for the quick reply!

Still learning how to even ask the correct question. Within the Status column possible values for each cell of the column are

Y

A

A-I

I

I-I

not separated by commas. There are thousands of rows in this table. The goal is to select 10 of each status (say along with LastName, FirstName, and MYID) and display within the result 50 rows (because there are 5 statuses). Using one of each example the output would look like:

Smith Will12345 Y

Jones Shirley 23456 A

Williams John 34567 A-I

Jolie Angelina 45678 I

Parton Dollie 56789 I-I

All Grouped by that Status.

Does that make more sense? Let’s call the table Members\_Names and the columns of interest are LastName, FirstName, MYID and Status.

Generally when I see the correct format for a few different examples I can start piecing them together and synthesize new requests.

I very much appreciate your guidance.

---

<div class="post-metadata">

**Author:** ![Michael\_W](https://avatars.discourse-cdn.com/v4/letter/m/8e7dd6/32.png) [@Michael\_W](https://forums.toadworld.com/u/Michael_W)\
**Post date:** [May 16, 2016, 11:42am UTC](https://forums.toadworld.com/t/newbie-query-question/37721/5 "2016-05-16T11:42:40Z")

</div>

Oh Wow! Thanks for that Dennis! That looks like a lot of work on your part! Again - Very Appreciative! I’m going to save that and parse it when I have a little more experience - I’ll bring it up as an example in class - the teacher will love it!

Did my last post clarify or obfuscate the issue?

---

<div class="post-metadata">

**Author:** ![Dennis\_Paulus](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/dennis_paulus/32/138_2.png) [@Dennis\_Paulus](https://forums.toadworld.com/u/Dennis_Paulus)\
**Post date:** [May 16, 2016, 11:52am UTC](https://forums.toadworld.com/t/newbie-query-question/37721/6 "2016-05-16T11:52:28Z")

</div>

Here’s a simple example that should work for ya…

select LastName, FirstName, MYID, Status

from Members\_Names

where Status = ‘Y’

and rownum \< 6

union

select LastName, FirstName, MYID, Status

from Members\_Names

where Status = ‘A’

and rownum \< 6

union

select LastName, FirstName, MYID, Status

from Members\_Names

where Status = ‘A-I’

and rownum \< 6

union

select LastName, FirstName, MYID, Status

from Members\_Names

where Status = ‘I’

and rownum \< 6

union

select LastName, FirstName, MYID, Status

from Members\_Names

where Status = ‘I-I’

and rownum \< 6;

---

<div class="post-metadata">

**Author:** ![Dennis\_Paulus](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/dennis_paulus/32/138_2.png) [@Dennis\_Paulus](https://forums.toadworld.com/u/Dennis_Paulus)\
**Post date:** [May 16, 2016, 11:57am UTC](https://forums.toadworld.com/t/newbie-query-question/37721/7 "2016-05-16T11:57:53Z")

</div>

Yes, your last post helped in explaining what you needed. It may look like a lot, but it’s really not - it’s the same query (with the exception of the status), just connected by unions. There’s other ways to do the same thing, but thought this was easier to understand.

---

<div class="post-metadata">

**Author:** ![Michael\_W](https://avatars.discourse-cdn.com/v4/letter/m/8e7dd6/32.png) [@Michael\_W](https://forums.toadworld.com/u/Michael_W)\
**Post date:** [May 16, 2016, 12:06pm UTC](https://forums.toadworld.com/t/newbie-query-question/37721/8 "2016-05-16T12:06:30Z")

</div>

BINGO! The “Union” operator was the key. It now makes perfect sense to me and I can replicate that. Thank You so much for the 101 class! You did a very good deed for the day - Bless You!

Have a Great Day and Thank You Again!

---

<div class="post-metadata">

**Author:** ![Dennis\_Paulus](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/dennis_paulus/32/138_2.png) [@Dennis\_Paulus](https://forums.toadworld.com/u/Dennis_Paulus)\
**Post date:** [May 16, 2016, 12:06pm UTC](https://forums.toadworld.com/t/newbie-query-question/37721/9 "2016-05-16T12:06:54Z")

</div>

oh btw…I thought you wanted 5 rows returned. I see you want 10, so instead of ‘rownum \< 6’, you would change it to ‘rownum \< 11’ for each query.

---

<div class="post-metadata">

**Author:** ![Michael\_W](https://avatars.discourse-cdn.com/v4/letter/m/8e7dd6/32.png) [@Michael\_W](https://forums.toadworld.com/u/Michael_W)\
**Post date:** [May 16, 2016, 12:14pm UTC](https://forums.toadworld.com/t/newbie-query-question/37721/10 "2016-05-16T12:14:04Z")

</div>

Understand! This is the type of query structure that intuitively makes sense to me. That first possibility struck fear into me - I know with time it will become second nature. It sounded like a full discourse on Greek Logic explained in Mandarin. The last one sounded like “Where can I buy some tacos and beer?” in Spanish - I can at least put enough together to understand that! LOL Perfect! Thank You Dennis!

---

<div class="post-metadata">

**Author:** ![NormTeamT](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/normteamt/32/53_2.png) [@NormTeamT](https://forums.toadworld.com/u/NormTeamT)\
**Post date:** [May 16, 2016, 2:11pm UTC](https://forums.toadworld.com/t/newbie-query-question/37721/11 "2016-05-16T14:11:02Z")

</div>

I’m already late to the party, but here’s my £0.02 anyway.

Union invokes a sort as it uses that to get rid of duplicates. If any of your data returned had the same values, then the union would get rid of all the duplicated and you would end up with fewer rows than you might be expecting.

I think the test data used has avoided this as no two rows are the same. So no problems. However beware that this can happen.

Union all, on the other hand, does not get rid of duplicates and so would be a better choice here, just in case the data could return duplicates. Additionally, avoiding a sort chews up less cpu and saves on server resources. Every little helps after all.

HTH

Cheers,

Norm [TeamT]

–

Sent from my Android device with K-9 Mail. Please excuse my brevity.

---

<div class="post-metadata">

**Author:** ![Michael\_W](https://avatars.discourse-cdn.com/v4/letter/m/8e7dd6/32.png) [@Michael\_W](https://forums.toadworld.com/u/Michael_W)\
**Post date:** [May 16, 2016, 2:22pm UTC](https://forums.toadworld.com/t/newbie-query-question/37721/12 "2016-05-16T14:22:45Z")

</div>

Excellent information! Just the sort of thing that polishes the result. I Will keep that in mind. Thank You for your £0.02 worth! I appreciate it!

On May 16, 2016 2:12 PM, "Norm [TeamT]" [bounce-NormTeamT@toadworld.com](mailto:bounce-NormTeamT@toadworld.com) wrote:

> **[RE: Newbie Query Question](http://www.toadworld.com/products/toad-for-oracle/f/10/p/30709/60677#60677)**

> Reply by Norm [TeamT]  
> I'm already late to the party, but here's my £0.02 anyway.
> 
> Union invokes a sort as it uses that to get rid of duplicates. If any of your data returned had the same values, then the union would get rid of all the duplicated and you would end up with fewer rows than you might be expecting.
> 
> I think the test data used has avoided this as no two rows are the same. So no problems. However beware that this can happen.
> 
> Union all, on the other hand, does not get rid of duplicates and so would be a better choice here, just in case the data could return duplicates. Additionally, avoiding a sort chews up less cpu and saves on server resources. Every little helps after all.
> 
> HTH
> 
> Cheers,
> 
> Norm [TeamT]
> 
> --
> 
> Sent from my Android device with K-9 Mail. Please excuse my brevity.

> To reply, please reply-all to this email.
> 
> [Stop receiving emails](http://www.toadworld.com/products/toad-for-oracle/f/10/t/30709?subscribe=false) on this subject.
> 
> Or [Unsubscribe from Toad for Oracle - General](http://www.toadworld.com/products/toad-for-oracle/f/10?subscribe=false) notifications altogether.
> 
> Toad for Oracle - Discussion Forum

> [Flag](http://www.toadworld.com/products/toad-for-oracle/f/10/p/30709/60677?AbuseContentId=842e99f4-fe7e-4920-a172-7e9c41652d5e&AbuseContentTypeId=f586769b-0822-468a-b7f3-a94d480ed9b0&AbuseFlag=true) this post as spam/abuse.

---

<div class="post-metadata">

**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\
**Post date:** [May 16, 2016, 2:38pm UTC](https://forums.toadworld.com/t/newbie-query-question/37721/13 "2016-05-16T14:38:39Z")

</div>

You can use analytic functions to make the query simpler, so you don’t have to “union all” each possible value. Here’s an example using the DBA\_TABLES view (you can change it to ALL\_TABLES if you get a “table or view does not exist” error).

select \*

from

( select owner, table\_name, tablespace\_name,

row\_number() over ( partition by tablespace\_name

order by owner, table\_name nulls last ) tablespace\_row\_number

from dba\_tables)

where tablespace\_row\_number \< 11;

The inner query has a row\_number column that counts each row, starting over every time you to a new tablespace. The outer query limits it to the first 10 rows for each tablespace.

You should be able to change this query pretty easily to work with your data, using your table name instead of DBA\_TABLESPACES, the STATUS column instead of TABLESPACE\_NAME.

---

<div class="post-metadata">

**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\
**Post date:** [May 16, 2016, 2:47pm UTC](https://forums.toadworld.com/t/newbie-query-question/37721/14 "2016-05-16T14:47:09Z")

</div>

> You can use analytic functions to make the query simpler...

Well, make that more complicated but shorter. 🙂

---

<div class="post-metadata">

**Author:** ![Michael\_W](https://avatars.discourse-cdn.com/v4/letter/m/8e7dd6/32.png) [@Michael\_W](https://forums.toadworld.com/u/Michael_W)\
**Post date:** [May 17, 2016, 6:57am UTC](https://forums.toadworld.com/t/newbie-query-question/37721/15 "2016-05-17T06:57:30Z")

</div>

Thank You John! I am sure I will grow into this. I can see the logic behind it and at this point I am just learning to stand much rather alone walk or run! LOL I will add this to my saved queries and double back to it as soon as I can come up for air in my new company!

Thanks Again and Have a Great Day!

---

<div class="post-metadata">

**Author:** ![NormTeamT](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/normteamt/32/53_2.png) [@NormTeamT](https://forums.toadworld.com/u/NormTeamT)\
**Post date:** [May 18, 2016, 12:38am UTC](https://forums.toadworld.com/t/newbie-query-question/37721/16 "2016-05-18T00:38:30Z")

</div>

Complicated? Never! 🙂

I’m only now getting my head around lag () and lead () as part of a query I run when I’m importing tables with far too many indexes (46!) And I want to get a rough idea of when I can expect it to finish. I think 59 hours plus is a tad excessive!

Cheers,

Norm [TeamT].

–

Sent from my Android device with K-9 Mail. Please excuse my brevity.

---

<div class="post-metadata">

**Author:** ![duneria](https://avatars.discourse-cdn.com/v4/letter/d/df788c/32.png) [@duneria](https://forums.toadworld.com/u/duneria)\
**Post date:** [May 18, 2016, 10:01pm UTC](https://forums.toadworld.com/t/newbie-query-question/37721/17 "2016-05-18T22:01:33Z")

</div>

bakwas sms

On Wed, May 18, 2016 at 1:09 PM, Norm [TeamT] [bounce-NormTeamT@toadworld.com](mailto:bounce-NormTeamT@toadworld.com) wrote:

> **[RE: Newbie Query Question](http://www.toadworld.com/products/toad-for-oracle/f/10/p/30709/60727#60727)**

> Reply by Norm [TeamT]

> Complicated? Never! 🙂
> 
> I'm only now getting my head around lag () and lead () as part of a query I run when I'm importing tables with far too many indexes (46!) And I want to get a rough idea of when I can expect it to finish. I think 59 hours plus is a tad excessive!
> 
> Cheers,
> 
> Norm [TeamT].
> 
> --
> 
> Sent from my Android device with K-9 Mail. Please excuse my brevity.

> To reply, please reply-all to this email.
> 
> [Stop receiving emails](http://www.toadworld.com/products/toad-for-oracle/f/10/t/30709?subscribe=false) on this subject.
> 
> Or [Unsubscribe from Toad for Oracle - General](http://www.toadworld.com/products/toad-for-oracle/f/10?subscribe=false) notifications altogether.
> 
> Toad for Oracle - Discussion Forum

> [Flag](http://www.toadworld.com/products/toad-for-oracle/f/10/p/30709/60727?AbuseContentId=e6189a0b-327f-4e9b-8d79-561925966310&AbuseContentTypeId=f586769b-0822-468a-b7f3-a94d480ed9b0&AbuseFlag=true) this post as spam/abuse.

--  
_ **Thanku & Regards** _

_**Sunil Kumar  
9872588906**_

---

<div class="post-metadata">

**Author:** ![chonaramos](https://avatars.discourse-cdn.com/v4/letter/c/f08c70/32.png) [@chonaramos](https://forums.toadworld.com/u/chonaramos)\
**Post date:** [June 2, 2016, 9:59am UTC](https://forums.toadworld.com/t/newbie-query-question/37721/18 "2016-06-02T09:59:57Z")

</div>

this might help:

WITH MyRowSet

AS (SELECT

LastName, FirstName, MYID, Status,

ROW\_NUMBER () OVER (PARTITION BY Status ORDER BY Status DESC) AS r\_num

FROM Members\_Names )

SELECT \*

FROM MyRowSet

WHERE r\_num \<= 10;

---

<div class="post-metadata">

**Author:** ![Michael\_W](https://avatars.discourse-cdn.com/v4/letter/m/8e7dd6/32.png) [@Michael\_W](https://forums.toadworld.com/u/Michael_W)\
**Post date:** [June 3, 2016, 3:37pm UTC](https://forums.toadworld.com/t/newbie-query-question/37721/19 "2016-06-03T15:37:47Z")

</div>

Haven’t tried it yet (crazy at work). It certainly looks elegant though. How does one handle the different Statuses (statusii?) 🙂

---

<div class="post-metadata">

**Author:** ![Michael\_W](https://avatars.discourse-cdn.com/v4/letter/m/8e7dd6/32.png) [@Michael\_W](https://forums.toadworld.com/u/Michael_W)\
**Post date:** [June 7, 2016, 7:57am UTC](https://forums.toadworld.com/t/newbie-query-question/37721/20 "2016-06-07T07:57:07Z")

</div>

Wow! VERY Nice! Worked like a charm. Can’t say I could write it from scratch – I only vaguely understand what it is doing.

Thank You Very Much!

**From:** chonaramos [[mailto:bounce-chonaramos@toadworld.com](mailto:bounce-chonaramos@toadworld.com)]  
**Sent:** Thursday, June 02, 2016 10:01 AM  
**To:** [toadoracle@toadworld.com](mailto:toadoracle@toadworld.com)  
**Subject:** RE: [Toad for Oracle - Discussion Forum] Newbie Query Question

**[RE: Newbie Query Question](http://www.toadworld.com/products/toad-for-oracle/f/10/p/30709/60961#60961)**

Reply by chonaramos

this might help:

WITH MyRowSet

AS (SELECT

LastName, FirstName, MYID, Status,

ROW\_NUMBER () OVER (PARTITION BY Status ORDER BY Status DESC) AS r\_num

FROM Members\_Names )

SELECT \*

FROM MyRowSet

WHERE r\_num \<= 10;

To reply, please reply-all to this email.

[Stop receiving emails](http://www.toadworld.com/products/toad-for-oracle/f/10/t/30709?subscribe=false) on this subject.  
Or [Unsubscribe from Toad for Oracle - General Forum](http://www.toadworld.com/products/toad-for-oracle/f/10?subscribe=false) notifications altogether.  
Toad for Oracle - Discussion Forum

[Flag](http://www.toadworld.com/products/toad-for-oracle/f/10/p/30709/60961?AbuseContentId=08a86749-944c-4de1-8d9f-d3df802755e9&AbuseContentTypeId=f586769b-0822-468a-b7f3-a94d480ed9b0&AbuseFlag=true) this post as spam/abuse.