Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

sqlplus and Korn case statement

Status
Not open for further replies.

awood69

Programmer
Feb 27, 2012
19
US
I have a function and based on a parameter I need to call different sql statement something like below?

function get_regions {
sqlplus -s <<EOF
$CONNECT_DB2
set pause off
set heading off
set pagesize 0
set feedback off


Case $var = "XXX"
case ZZZ
do this sql statement
case DDD
do this sql statement
case VVV
do this sql statement
exit
EOF
}
 
Hi

Not sure what should happen there and what exactly is your problem, but I think you should use Ksh's [tt]case[/tt] instead ( anyway, no idea what kind of [tt]case[/tt] should that be as it is not valid neither in Ksh or Oracle ) :
Code:
[COLOR=darkgoldenrod]function get_regions[/color]
{
  [b]case[/b] [green][i]"$var"[/i][/green]
    ZZZ[teal])[/teal] [navy]sql[/navy][teal]=[/teal][green][i]'one statement'[/i][/green] [teal];;[/teal]
    DDD[teal])[/teal] [navy]sql[/navy][teal]=[/teal][green][i]'other statement'[/i][/green] [teal];;[/teal]
    VVV[teal])[/teal] [navy]sql[/navy][teal]=[/teal][green][i]'more statement'[/i][/green] [teal];;[/teal]
  [b]esac[/b]

  sqlplus -s [teal]<<[/teal]EOF
[navy]$CONNECT_DB2[/navy]
set pause off
set heading off
set pagesize [purple]0[/purple]
set feedback off
[navy]$sql[/navy]
EOF
}

Feherke.
 
The code was just to try and get my point accross, you are right it is not for either ksh or oracle. I am moving over from a Microsoft world to oracle and unix and seem to be having some challanges.

If I wanted to include an else do I just?

case "$var"
ZZZ) sql='one statement' ;;
DDD) sql='other statement' ;;
else) sql='more statement' ;;
esac
 
Hi

No. Use a pattern :
Code:
  [b]case[/b] [green][i]"$var"[/i][/green]
    ZZZ[teal])[/teal] [navy]sql[/navy][teal]=[/teal][green][i]'one statement'[/i][/green] [teal];;[/teal]
    DDD[teal])[/teal] [navy]sql[/navy][teal]=[/teal][green][i]'other statement'[/i][/green] [teal];;[/teal]
    [teal][highlight]*[/highlight])[/teal] [navy]sql[/navy][teal]=[/teal][green][i]'more statement'[/i][/green] [teal];;[/teal]
  [b]esac[/b]


Feherke.
 
I keep getting these errors...

unknown command beginning "case "PLT0..." - rest of line ignored
unknown command beginning "PLT01) sql..." - rest of line ignored
unknown command "esac" - rest of line ignored
For a list of known commands enter HELP
to leave enter EXIT

here is my code

function get_regions {
sqlplus -s <<EOF
$CONNECT_DB2
set pause off
set heading off
set pagesize 0
set feedback off

case "$group_type"
PLT01) sql='select 'R' || region, 'r' || region from region where email_flag = 'Y' and region in ('11', '12') order by region' ;;
*) sql='select 'R' || region, 'r' || region from region where email_flag = 'Y' order by region' ;;
esac
exit
EOF
}
 
case "$group_type" [!]in[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Thanks but that didn't do it, I get the same messages.
 
Hi

Either
[ul]
[li]Move the [tt]case[/tt] out from the here-document, as shown earlier.[/li]
[li]Use command substitution :
Code:
[COLOR=darkgoldenrod]function get_regions[/color]
{
sqlplus -s [teal]<<[/teal]EOF
[navy]$CONNECT_DB2[/navy]
[b]set[/b] pause off
[b]set[/b] heading off
[b]set[/b] pagesize [purple]0[/purple]
[b]set[/b] feedback off
[highlight][navy]$([/navy][/highlight]
  [b]case[/b] [green][i]"$group_type"[/i][/green]
    PLT01[teal])[/teal] [highlight]echo[/highlight] [green][i]"select 'R' || region, 'r' || region from region where email_flag = 'Y' and region in ('11', '12') order by region"[/i][/green] [teal];;[/teal]
    [teal]*)[/teal] [highlight]echo[/highlight] [green][i]"select 'R' || region, 'r' || region from region where email_flag = 'Y' order by region"[/i][/green] [teal];;[/teal]
  [b]esac[/b]
[highlight][teal])[/teal][/highlight]
[b]exit[/b]
EOF
}
[/li]
[/ul]
Oh, and thanks, PHV.

Feherke.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top