Toad World® Forums

Newbie Query Question

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! :slight_smile:

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! :slight_smile: ) I look forward to contributing and helping some other newbie along!

Have a Great Day!

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;

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;

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.

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?

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;

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.

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!

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.

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!

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.

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 wrote:

RE: Newbie Query Question

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 on this subject.

Or Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

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.

You can use analytic functions to make the query simpler…

Well, make that more complicated but shorter. :slight_smile:

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!

Complicated? Never! :slight_smile:

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.

bakwas sms

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

RE: Newbie Query Question

Reply by Norm [TeamT]

Complicated? Never! :slight_smile:

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 on this subject.

Or Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.


Thanku & Regards

Sunil Kumar
9872588906

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;

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

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]
Sent: Thursday, June 02, 2016 10:01 AM
To: toadoracle@toadworld.com
Subject: RE: [Toad for Oracle - Discussion Forum] Newbie Query Question

RE: Newbie Query Question

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 on this subject.
Or Unsubscribe from Toad for Oracle - General Forum notifications altogether.
Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.