The UNIX and Linux Forums  

Go Back   The UNIX and Linux Forums > Top Forums > Shell Programming and Scripting
Google UNIX.COM


Shell Programming and Scripting Post questions about KSH, CSH, SH, BASH, PERL, PHP, SED, AWK and OTHER shell scripts here.

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
script for nested rlogin and telnet mcburke38 Shell Programming and Scripting 5 03-06-2008 02:05 PM
sed in awk ? or nested awk ? varungupta UNIX for Advanced & Expert Users 11 02-08-2008 06:34 AM
Nested Arrays guysporty Shell Programming and Scripting 5 04-13-2005 04:44 AM
nested read TioTony Shell Programming and Scripting 2 03-05-2004 11:11 AM
Nested calls omran Shell Programming and Scripting 6 08-16-2002 01:18 AM

Reply
 
Submit Tools LinkBack Thread Tools Display Modes
  #1 (permalink)  
Old 09-20-2005
Registered User
 

Join Date: Aug 2005
Posts: 43
Stumble this Post!
can nested SQl be run in Unix Script?

can nested SQl be run in Unix Script?

I tried some and found only simply sql(one select) can work well in unix script.
Reply With Quote
Forum Sponsor
  #2 (permalink)  
Old 09-20-2005
Registered User
 

Join Date: Jan 2005
Posts: 682
Stumble this Post!
If you mean:

a) can this nested SQL be run in a shell script?
Code:
{
sqlplus un/pw <<EOF
select col_a
       , col_b
from   table_a a
where exists (
      select 1 
      from   table_b b 
      where a.col_a = b.col_b
);
EOF
} | while read COL_A COL_B
do
   echo $COL_A $COL_B
done
Answer: Yes


or


b) can this nested SQL be run in a shell script?

Code:
{
sqlplus un/pw <<EOF
select col_a
from   table_a a;
EOF | while read COL_A
do
    sqlplus un/pw <<EOF
select col_c
from   table_b
where col_a = ${COL_A};
EOF 
} | while read COL_B
    do
        echo ${COL_B}
    done
done
Answer: Yes

Last edited by tmarikle; 09-20-2005 at 08:13 PM.
Reply With Quote
  #3 (permalink)  
Old 09-20-2005
Registered User
 

Join Date: Aug 2005
Posts: 43
Stumble this Post!
how about:
select x,y
from (select blah1,blah2) a ,(select blah1,blah2 ) b
where a.blah1=b.blah1;

mine one doesnt work at all.
Reply With Quote
  #4 (permalink)  
Old 09-20-2005
Registered User
 

Join Date: Jan 2005
Posts: 682
Stumble this Post!
Yes, you can. I'll post proof momentarily.
Reply With Quote
  #5 (permalink)  
Old 09-20-2005
Registered User
 

Join Date: Aug 2005
Posts: 43
Stumble this Post!
what is problem with mine one. the output file was always empty, how can I test where is the problem
Reply With Quote
  #6 (permalink)  
Old 09-20-2005
Registered User
 

Join Date: Jan 2005
Posts: 682
Stumble this Post!
Code:
{
typeset IFS='
'
sqlplus -s un/pw <<EOF
select t.table_name, c.column_name
 from
   (select table_name
          ,column_name
    from   all_tab_columns) c
  ,(select table_name
    from   all_tables) t
where t.table_name = c.table_name
and   t.table_name = 'PLAN_TABLE'
;
EOF
} | while read line
do
    echo "$line"
done

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
PLAN_TABLE                     STATEMENT_ID
PLAN_TABLE                     TIMESTAMP
PLAN_TABLE                     REMARKS
PLAN_TABLE                     OPERATION
PLAN_TABLE                     OPTIONS
PLAN_TABLE                     OBJECT_NODE
PLAN_TABLE                     OBJECT_OWNER
...

27 rows selected.
Reply With Quote
  #7 (permalink)  
Old 09-20-2005
Registered User
 

Join Date: Jan 2005
Posts: 682
Stumble this Post!
Quote:
Originally Posted by YoYo
how about:
select x,y
from (select blah1,blah2) a ,(select blah1,blah2 ) b
where a.blah1=b.blah1;

mine one doesnt work at all.
I need a real example. "x" and "y" are not in your sub selects so this example is problematic. Also "(select blah1, blah2)" is incomplete.
Reply With Quote
Google The UNIX and Linux Forums
Reply

« empty line | telnet »
Thread Tools
Display Modes




All times are GMT -7. The time now is 07:40 AM.


Powered by: vBulletin, Copyright ©2000 - 2006, Jelsoft Enterprises Limited.
The UNIX and Linux Forums Content Copyright ©1993-2008 The CEP Blog All Rights Reserved -Ad Management by RedTyger Visit The Global Fact Book

Content Relevant URLs by vBSEO 3.2.0