Nested SQL queries within Shell script

Shell Programming and Scripting
1

Hi,

Would someone know if I can fire nested sql queries in a shell script? Basically what I am trying to do is as follows:

my_sql=$(sqlplus -s /nolog<<EOF|sed -e "s/Connected. *//g"
connect gidicm/gidicm@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.5.199.106)(PORT=1521))(CONNECT_DATA=(SID=PHYGD1)))'
set pages 0
select sync_cmpl
from gidicm.gid_inv_sync_hdr_t
where dc_id ='12'
and max to_char(sync_req_dttm,'YYYYMMDD')= '20100401'
and inv_sync_id =
(select max(inv_sync_id)
from gidicm.gid_inv_sync_hdr_t;)
EOF)

This code runs absolutely fine if I fire the following query:

select sync_cmpl
from gidicm.gid_inv_sync_hdr_t
where dc_id ='12'
and max to_char(sync_req_dttm,'YYYYMMDD')= '20100401'

But it fails to work with the additional select statement that I give. It is unable to recognize the additional select statement.

Is there a way to fire such nested queries? Or do I need to fire these 2 queries separately in this script, store the output of the first query in a variable and thenpass it on to another?

Thanks!

2

take out the go symbol in your nested query: ";" and put it on its own line prior to the EOF.

1 Like
3

I am sorry - There is a typo in the queries i pasted - Please ignore the keyword 'max' before to_char.

Thanks,
Shruti