Formatting the query output using shell script

Shell Programming and Scripting
1

Hi All,

I'm finding it difficult to format the query output that was returned in a shell script.

Actually i have one shell script which does some DB stuff and depending on the result it will do some more tasks. My question here is to format the query output returned by mysql.

Intitally my script connects to Mysql database and get the output of a query into a variable. The output is some thing like below

REQUEST_ID DESCRIPTION 39154 desc1 39303 desc2 39338 desc3 39342 desc4 35678 desc5 36758 desc6

result=` DB login info << EOF
select REQUEST_ID,DESCRIPTION from table1 where REQUEST_GROUP_ID in (select ID from table2 where DATE(PLANNED_START_DATE) = DATE (NOW()));
EOF`

I wanted to know few of the things as below
1) if i can format it in more readable way?
2) If you look at my query REQUEST_ID,DESCRIPTION belong to a REQUEST_GROUP_ID and there can be more number of REQUEST_GROUP_ID. So is there a way to display the output of which REQUEST_ID,DESCRIPTION belongs to which REQUEST_GROUP_ID ?

Any pointers are much more appreciated. I'm doing it all these activities using shell script. Please point me if this can be easily achieved even through some perl routines also.

---------- Post updated 06-18-11 at 01:11 AM ---------- Previous update was 06-17-11 at 11:35 PM ----------

Also please let me know if this can be done easily using awk or sed utilities?

2

Hi
Please post your sample output of your query, and also, how you would like it t be formatted. I mean how you would like the formatted output to look like.

Guru.

3

Sample output

REQUEST_ID DESCRIPTION 39154 desc1 39303 desc2 39338 desc3 39342 desc4 35678 desc5 36758 desc6

Desired Format:

REQUEST_GROUP_ID1

REQUEST_ID DESCRIPTION

39154 desc1
39303 desc2
39338 desc3

REQUEST_GROUP_ID2
39342 desc4
35678 desc5

REQUEST_GROUP_ID3
36758 desc6

Please note that REQUEST_GROUP_ID are part of inner query in my post and they are numerical values

---------- Post updated at 02:45 AM ---------- Previous update was at 01:32 AM ----------

Seems if i put echo "$result" the output will be formatted.

Please help me how to classify the requests as per request_group id...

4

We didn't get the exact result you want.

How you are identifying :
First 3 items for Group1 ,
Second 2 for Group2.. and so on.. ?

or otherwise try to change the query to get the desired output.. -->> will be more easy / better. Try to use (order by , sort , as etc in your query)

.

5

My actual query is
select REQUEST_ID,DESCRIPTION from table1 where REQUEST_GROUP_ID in (select ID from table2 where DATE(PLANNED_START_DATE) = DATE (NOW()));

I'm getting the output collectively for all the REQUEST_GROUP_ID's. But I want to see the output as i mentioned in the above post REQUEST_GROUP_ID wise.

I'm novice at DB concepts. But if some body can give me some hints is there a way to execute multiple sql statements in a single DB connection and assign it to different variables in a shell script that would help me to tweak my shell script and build the logic to get the desired output.

6

I think the right approach should be to do all this at DB level (SQL,SQL+,PL/SQL).