Visit Our UNIX and Linux User Community


SQL output vertically aligned?


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting SQL output vertically aligned?
# 1  
Old 09-11-2009
SQL output vertically aligned?

I used the SQL query (taken from other threads here) to get the expected values to be written into a file.
Code:
myQuery=`sqlplus -s cr_appsrvr/appsrvr@qwi << EndofFile
set heading off;
set tab off;
set wrap off;
set pages 0;
set feedback off;

SELECT CLEARINGHOUSE_TRACE_NUM, INSURED_ID FROM CR_APPSRVR.APRC_OUTBOUND_CLAIM C
WHERE C.PAYER_ID='MCAREGHI' AND C.INSURED_ID='060406043A';
exit;
EndofFile`

echo $myQuery>sql_out
echo "Query Output -  $myQuery"

This makes the file sql_out to look like:
NEIS0MDL-00022 060406043A NEIS2FTE-00111 060406043A NEIS2FTE-00112 060406043A NEIS2FTE-00113 060406043A NEIS2FTE-00114 060406043A NEIS2FTE-00115 060406043A NEIS2FTE-00116 060406043A 060406043A NEIUPVOV-00039 060406043A NEIUPVOV-00040 060406043A 10 rows selected.

But I want the output file to look like:

NEIS0MDL-00022 060406043A
NEIS2FTE-00111 060406043A
NEIS2FTE-00112 060406043A
NEIS2FTE-00113 060406043A
NEIS2FTE-00114 060406043A
NEIS2FTE-00115 060406043A
NEIS2FTE-00116 060406043A
<blank value> 060406043A
NEIUPVOV-00039 060406043A
NEIUPVOV-00040 060406043A

Kindly help me to make the output file like the above.

Regards,
Swami
# 2  
Old 09-11-2009
one way:
Code:
sqlplus -s cr_appsrvr/appsrvr@qwi << EndofFile > myfile
set heading off;
set tab off;
set wrap off;
set pages 0;
set feedback off;

SELECT CLEARINGHOUSE_TRACE_NUM, INSURED_ID FROM CR_APPSRVR.APRC_OUTBOUND_CLAIM C
WHERE C.PAYER_ID='MCAREGHI' AND C.INSURED_ID='060406043A';
exit;
EndofFile

cat myfile

The problem is you are putting multiple lines of output into a variable - the shell ignores the newlines.

Code:
somevar=$(sqlplus -s cr_appsrvr/appsrvr@qwi << EndofFile
set heading off;
set tab off;
set wrap off;
set pages 0;
set feedback off;

SELECT CLEARINGHOUSE_TRACE_NUM, INSURED_ID || '|'
FROM CR_APPSRVR.APRC_OUTBOUND_CLAIM C
WHERE C.PAYER_ID='MCAREGHI' AND C.INSURED_ID='060406043A';
exit;
EndofFile )

echo "$somevar" | tr -s '|' '\n'

This lets you keep the variable, but you have to decode the | characters into newline characters

you should just use the Oracle spool command really.
# 3  
Old 09-11-2009
your option...

Thanks for your reply. I appreciate for spending time on this.

I tried both the options you gave, first works fine and values are listed in the file as i expected. Regarding the second, it reported the following error:

Code:
This will be written to a file
WHERE C.PAYER_ID='MCAREGHI' AND C.INSURED_ID='060406043A'
                                             *
ERROR at line 3:
ORA-00904: "060406043A": invalid identifier

Actually my requirement is to verify these values from DB with another flat file. For using these values I think I can have a special character in between the two values while writing them into a file.

I'm not sure which of the above two would work for me good. Help me to choose.
# 4  
Old 09-11-2009
That ORA- error message does not appear to be related to jim's scripts. Post your actual script here.

tyler_durden
# 5  
Old 09-11-2009
I think i got it right now. I was helped by the user "sanjay.login"
Thanks everybody.

Previous Thread | Next Thread
Test Your Knowledge in Computers #701
Difficulty: Easy
MySQL NOT LIKE is a sardonic DB operator used to exclude those rows which are not liked by Oracle Corporation executives.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Re-aligned text file into one raw

Hi, Anyone can help on how to re-aligned the data into one raw; Input Text File: TESTA Carbon A China 06/28/2016 Active Vol. 8210, No. 2048, New Era 02/25/2010 ... (1 Reply)
Discussion started by: fspalero
1 Replies

2. Shell Programming and Scripting

Storing multiple sql queries output into variable by running sql command only once

Hi All, I want to run multiple sql queries and store the data in variable but i want to use sql command only once. Is there a way without running sql command twice and storing.Please advise. Eg : Select 'Query 1 output' from dual; Select 'Query 2 output' from dual; I want to... (3 Replies)
Discussion started by: Rokkesh
3 Replies

3. Shell Programming and Scripting

[SH] TUI not always properly aligned

Heyas I havent found a thread to introduce, so i combine it with the issue i have. EDIT /* Removed Problem because solved */ My first contact with Linux was back in 1995 with slackware, beeing a gamer back then, i (sadly) didnt bother to dig deeper. I finaly joined the Linux community in... (0 Replies)
Discussion started by: sea
0 Replies

4. Shell Programming and Scripting

Awk script to run a sql and print the output to an output file

Hi All, I have around 900 Select Sql's which I would like to run in an awk script and print the output of those sql's in an txt file. Can you anyone pls let me know how do I do it and execute the awk script? Thanks. (4 Replies)
Discussion started by: adept
4 Replies

5. Shell Programming and Scripting

Counting characters vertically

I do have a big file in the following format >A1 ATGCGG >A2 TCATGC >A3 -TGCTG The number of characters will be same under each subheader and only possible characters are A,T,G,C and - I want to count the number of A's, T's,G's, C's & -'s vertically for all the positions so that I... (5 Replies)
Discussion started by: Lucky Ali
5 Replies

6. Shell Programming and Scripting

SQL*PLUS Spool Output

Hi, Im writing a script to run a bit of sql(via sqlplus) that pulls back some data and spools it to a file, I want the spool file to only display the data, with no sql command at the top and no reports at the bottom ie(# of records recieved). I am currently doing it via a grep command but... (1 Reply)
Discussion started by: Magezy
1 Replies

7. Shell Programming and Scripting

Output columns needs to be aligned

Hi All I'm running a shell script and the output is something like: Col1 Col2 Col3 aaaa aaaaaaa aaaaa bbbbb bbbbb bbbbbb ccc cccccc ccccccc But I require the output to printed as given below: Col1 Col2 Col3 aaaa ... (4 Replies)
Discussion started by: nkamalkishore
4 Replies

8. Shell Programming and Scripting

Appending two files vertically

Hi Need ur help for the below question. I have two files File-1 & File-2. File-1(This is a fixed file i.e. the content of this file is not going to change over a period of time) ------ a b c d e File-2 (This is a file which changes daily but the record count remains the same)... (1 Reply)
Discussion started by: 46019
1 Replies

9. Solaris

Error: Memory Address Not aligned

Hi, The following error message occured when I was trying to reboot my SUN machine: Memory address not aligned Its a Sun 280 R , Ultra SPARC III What should I do. Varma (3 Replies)
Discussion started by: gunnervarma
3 Replies

10. AIX

df output is not aligned.

When I issue a "df" command the columns of the output are not aligned. QMN012:.../oracle> df -k Filesystem 1024-blocks Free %Used Iused %Iused Mounted on /dev/hd4 524288 439592 17% 5423 6% / /dev/M121A_HOME 3080192 2343764 24% 7263 2%... (1 Reply)
Discussion started by: marcogilbert
1 Replies

Featured Tech Videos