Newbie Query Question

Michael_W · May 16, 2016, 6:57am

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!

Dennis_Paulus · May 16, 2016, 10:46am

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;

Dennis_Paulus · May 16, 2016, 11:24am

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;

Michael_W · May 16, 2016, 11:28am

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.

Michael_W · May 16, 2016, 11:42am

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?

Dennis_Paulus · May 16, 2016, 11:52am

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