Visit Our UNIX and Linux User Community


Formatting the query output using shell script


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Formatting the query output using shell script
# 1  
Old 06-18-2011
Formatting the query output using shell script

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  
Old 06-18-2011
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  
Old 06-18-2011
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  
Old 06-18-2011
Quote:
Originally Posted by RSC1985
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...

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  
Old 06-18-2011
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  
Old 06-18-2011
I think the right approach should be to do all this at DB level (SQL,SQL+,PL/SQL).

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Not able to write SQL query output in to .csv file with shell script.

I am trying to write SQL query output into a .csv file. But in the output columns are displaying in different lines instead of coming in one line. Main Code shell script: this is my code: #!/bin/bash file="db_detail.txt" . $file rm /batch/corpplan/bin/dan.csv... (6 Replies)
Discussion started by: sandeepgoli53
6 Replies

2. Shell Programming and Scripting

Shell script appending output of sql query

I am writing the following script to create the file v_out.txt. sqlplus -s /nolog << EOF CONNECT scott/tiger@orcl; whenever sqlerror exit sql.sqlcode; set newpage 0; SET PAGESIZE 0; SET ECHO OFF; SET FEEDBACK OFF; SET HEADING OFF; SET VERIFY OFF; SET LINESIZE 100; set tab off; set... (7 Replies)
Discussion started by: itzkashi
7 Replies

3. Shell Programming and Scripting

Run sql query in shell script and output data save as delimited text

I want to run sql query in shell script and output data save as delimited text (delimited text would be comma) Code: SPOOL_FILE=/pgedw/dan.txt SQL=/pgedw/dan.sql sqlplus -s username/password@myhost:port/servicename <<EOF set head on set COLSEP , set linesize 32767 SET TRIMSPOOL ON SET... (8 Replies)
Discussion started by: Jaganjag
8 Replies

4. Shell Programming and Scripting

SQL Query in Shell Script output formatting

Hi All, #!/bin/ksh call_sql () { sql=$1 sqlplus -s $sqlparam_sieb <<EOF SET ECHO OFF; SET NEWPAGE NONE; SET SQLBL OFF; SET VERIFY OFF; SET LINESIZE 2000; SET... (2 Replies)
Discussion started by: Kevin Tivoli
2 Replies

5. Shell Programming and Scripting

How to print the output of a select query using shell script?

HI, I want to connect to database and fetch the count from a table. The sql query is as below : select count(*) from table_test where test_column='read'; How can I print the output of this statement using shell script. Thanks in advance. (4 Replies)
Discussion started by: confused_info
4 Replies

6. Shell Programming and Scripting

Formatting Shell script output to Excel

I am facing a problem formatting the output of my shell script in excel. We are directing the output of the script to an excel sheet and need long integer type data printed in Excel as it is (i.e. not in the default scientific notation). Also, leading zeroes(if any) in the output are getting... (4 Replies)
Discussion started by: bornali.p
4 Replies

7. Shell Programming and Scripting

Problem in formatting output of SQL query in excel sheet in shell script

Hi Guys.. Need your help to format the output of my shell script. I am using spool command to take out put in csv file. below is my code. (for example) col USERNAME for a15 col EMAIL for a30 col FULL_NAME for a20 col LAST_LOGIN for a40 col DATE_CREATED for a40 SPOOL 120.csv... (3 Replies)
Discussion started by: Agupte
3 Replies

8. Shell Programming and Scripting

Problems with storing oracle sqlplus query output shell script

Hello everyone, I have a RHEL 5 system and have been trying to get a batch of 3-4 scripts each in a separate variables and they are not working as expected. I tried using following syntax which I saw a lot of people on this site use and should really work, though for some reason it doesn't... (3 Replies)
Discussion started by: rockf1bull
3 Replies

9. Shell Programming and Scripting

sql select command output formatting in shell script

Hi, I need to connect to the database and retrieve two variables from the database and store them in a variable,out of these two variables I need to get lastdigit appended to the variable 1 retrieved and variable 2 with out any modification in short select var,data from usage; o/p=... (1 Reply)
Discussion started by: rkrish
1 Replies

10. Shell Programming and Scripting

Noob, script formatting query

Hi all, im new to the forums and i hope im not asking a heavily posted Q but here goes. I use the following script to do a simple calculation, the problem is, the data i receive is in the form of numbers with commas i.e. 1,000,000 This code below wont recognise the commas so i have to remove... (2 Replies)
Discussion started by: benjo
2 Replies

Featured Tech Videos