sqlplus and Korn case statement

[awood69]

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
}

 

[feherke]

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 ) :

[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.

http://feherke.github.com/

 

[awood69]

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

 

[feherke]

Hi

No. Use a pattern :

  [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.

http://feherke.github.com/

 

[awood69]

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
}

 

[PHV]

case "$group_type" [!]in[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[awood69]

Thanks but that didn't do it, I get the same messages.

 

[feherke]

Hi

Either
[ul]
[li]Move the [tt]case[/tt] out from the here-document, as shown earlier.[/li]
[li]Use command substitution :

[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.

http://feherke.github.com/