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 and shell scripting languages 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 6 03-19-2009 10:37 AM
sed in awk ? or nested awk ? varungupta UNIX for Advanced & Expert Users 11 02-08-2008 10:34 AM
Nested Arrays guysporty Shell Programming and Scripting 5 04-13-2005 08:44 AM
nested read TioTony Shell Programming and Scripting 2 03-05-2004 03:11 PM
Nested calls omran Shell Programming and Scripting 6 08-16-2002 05:18 AM

Closed Thread
English Japanese Spanish French German Portuguese Italian Dutch Swedish Russian Norwegian Hungarian Hebrew Danish Bulgarian Greek Powered by Powered by Google
 
LinkBack Thread Tools Search this Thread Rate Thread Display Modes
  #1 (permalink)  
Old 09-20-2005
YoYo YoYo is offline
Registered User
  
 

Join Date: Aug 2005
Posts: 43
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.
  #2 (permalink)  
Old 09-21-2005
tmarikle tmarikle is offline Forum Advisor  
Registered User
  
 

Join Date: Jan 2005
Posts: 683
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-21-2005 at 12:13 AM..
  #3 (permalink)  
Old 09-21-2005
YoYo YoYo is offline
Registered User
  
 

Join Date: Aug 2005
Posts: 43
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.
  #4 (permalink)  
Old 09-21-2005
tmarikle tmarikle is offline Forum Advisor  
Registered User
  
 

Join Date: Jan 2005
Posts: 683
Yes, you can. I'll post proof momentarily.
  #5 (permalink)  
Old 09-21-2005
YoYo YoYo is offline
Registered User
  
 

Join Date: Aug 2005
Posts: 43
what is problem with mine one. the output file was always empty, how can I test where is the problem
  #6 (permalink)  
Old 09-21-2005
tmarikle tmarikle is offline Forum Advisor  
Registered User
  
 

Join Date: Jan 2005
Posts: 683

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.

  #7 (permalink)  
Old 09-21-2005
tmarikle tmarikle is offline Forum Advisor  
Registered User
  
 

Join Date: Jan 2005
Posts: 683
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.
Closed Thread

Bookmarks

« empty line | telnet »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On




All times are GMT -4. The time now is 04:11 PM.


Powered by: vBulletin, Copyright ©2000 - 2006, Jelsoft Enterprises Limited. Language Translations Powered by .
vBCredits v1.4 Copyright ©2007 - 2008, PixelFX Studios
The UNIX and Linux Forums Content Copyright ©1993-2009. All Rights Reserved.Ad Management by RedTyger

Content Relevant URLs by vBSEO 3.2.0