 | |
Use default value when no rows returns.
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Use default value when no rows returns. -
10-23-2008
, 05:55 AM
|
sqlplus -s user/password | while read nr3 |
to_date('01/11/2010','dd/mm/yyyy') ;" no value is returned and when no
row return i would like to assign a default value like '-1 ' for
example.
value
--------
-1
Thanks in advance!!
#2
| |||
| |||
|
|
Hi All, I need a help. I have a query inside one shell script and i'm wondering how to return any default value when no rows return from my query. For example: printf "%s\n" "set pages 0" "set feed off" "SELECT value FROM tableA WHERE date > to_date('01/11/2010','dd/mm/ yyyy') ;" | sqlplus -s user/password | while read nr3 from my query "SELECT value FROM tableA WHERE date to_date('01/11/2010','dd/mm/yyyy') ;" no value is returned and when no row return i would like to assign a default value like '-1 ' for example. value -------- *-1 Thanks in advance!! |
example
select nvl(max(value),-1) FROM tableA
WHERE date > to_date('01/11/2010','dd/mm/yyyy') ;
guarantees one row. Would using some aggregate function work for you?
Ed
#3
| |||
| |||
|
|
Hi All, I need a help. I have a query inside one shell script and i'm wondering how to return any default value when no rows return from my query. For example: printf "%s\n" "set pages 0" "set feed off" "SELECT value FROM tableA WHERE date > to_date('01/11/2010','dd/mm/ yyyy') ;" | sqlplus -s user/password | while read nr3 from my query "SELECT value FROM tableA WHERE date to_date('01/11/2010','dd/mm/yyyy') ;" no value is returned and when no row return i would like to assign a default value like '-1 ' for example. value -------- *-1 Thanks in advance!! |
example
select nvl(max(value),-1) FROM tableA
WHERE date > to_date('01/11/2010','dd/mm/yyyy') ;
guarantees one row. Would using some aggregate function work for you?
Ed
#4
| |||
| |||
|
|
Hi All, I need a help. I have a query inside one shell script and i'm wondering how to return any default value when no rows return from my query. For example: printf "%s\n" "set pages 0" "set feed off" "SELECT value FROM tableA WHERE date > to_date('01/11/2010','dd/mm/ yyyy') ;" | sqlplus -s user/password | while read nr3 from my query "SELECT value FROM tableA WHERE date to_date('01/11/2010','dd/mm/yyyy') ;" no value is returned and when no row return i would like to assign a default value like '-1 ' for example. value -------- *-1 Thanks in advance!! |
example
select nvl(max(value),-1) FROM tableA
WHERE date > to_date('01/11/2010','dd/mm/yyyy') ;
guarantees one row. Would using some aggregate function work for you?
Ed
#5
| |||
| |||
|
|
Hi All, I need a help. I have a query inside one shell script and i'm wondering how to return any default value when no rows return from my query. For example: printf "%s\n" "set pages 0" "set feed off" "SELECT value FROM tableA WHERE date > to_date('01/11/2010','dd/mm/ yyyy') ;" | sqlplus -s user/password | while read nr3 from my query "SELECT value FROM tableA WHERE date to_date('01/11/2010','dd/mm/yyyy') ;" no value is returned and when no row return i would like to assign a default value like '-1 ' for example. value -------- *-1 Thanks in advance!! |
example
select nvl(max(value),-1) FROM tableA
WHERE date > to_date('01/11/2010','dd/mm/yyyy') ;
guarantees one row. Would using some aggregate function work for you?
Ed
#6
| |||
| |||
|
|
I have a query inside one shell script and i'm wondering how to return any default value when no rows return from my query. For example: printf "%s\n" "set pages 0" "set feed off" "SELECT value FROM tableA WHERE date > to_date('01/11/2010','dd/mm/ yyyy') ;" | sqlplus -s user/password | while read nr3 from my query "SELECT value FROM tableA WHERE date to_date('01/11/2010','dd/mm/yyyy') ;" no value is returned and when no row return i would like to assign a default value like '-1 ' for example. value -------- -1 |
the case where rows are actually returned. However, it does include a
UNION and a subquery.
SELECT value FROM tableA WHERE date > (...)
UNION
SELECT '-1' FROM dual WHERE (SELECT count(*) FROM tableA WHERE date > (...))=0;
It may be more efficient to catch the lack of rows returned in the shell
script somehow rather than trying to do it from the select, in this case.
--
"Care must be exorcised when handring Opiticar System as it is apts to
be sticked by dusts and hand-fat." --Japanese Translators
"Keep your fingers off the lens." --Elton Byington, English Translator
#7
| |||
| |||
|
|
I have a query inside one shell script and i'm wondering how to return any default value when no rows return from my query. For example: printf "%s\n" "set pages 0" "set feed off" "SELECT value FROM tableA WHERE date > to_date('01/11/2010','dd/mm/ yyyy') ;" | sqlplus -s user/password | while read nr3 from my query "SELECT value FROM tableA WHERE date to_date('01/11/2010','dd/mm/yyyy') ;" no value is returned and when no row return i would like to assign a default value like '-1 ' for example. value -------- -1 |
the case where rows are actually returned. However, it does include a
UNION and a subquery.
SELECT value FROM tableA WHERE date > (...)
UNION
SELECT '-1' FROM dual WHERE (SELECT count(*) FROM tableA WHERE date > (...))=0;
It may be more efficient to catch the lack of rows returned in the shell
script somehow rather than trying to do it from the select, in this case.
--
"Care must be exorcised when handring Opiticar System as it is apts to
be sticked by dusts and hand-fat." --Japanese Translators
"Keep your fingers off the lens." --Elton Byington, English Translator
#8
| |||
| |||
|
|
I have a query inside one shell script and i'm wondering how to return any default value when no rows return from my query. For example: printf "%s\n" "set pages 0" "set feed off" "SELECT value FROM tableA WHERE date > to_date('01/11/2010','dd/mm/ yyyy') ;" | sqlplus -s user/password | while read nr3 from my query "SELECT value FROM tableA WHERE date to_date('01/11/2010','dd/mm/yyyy') ;" no value is returned and when no row return i would like to assign a default value like '-1 ' for example. value -------- -1 |
the case where rows are actually returned. However, it does include a
UNION and a subquery.
SELECT value FROM tableA WHERE date > (...)
UNION
SELECT '-1' FROM dual WHERE (SELECT count(*) FROM tableA WHERE date > (...))=0;
It may be more efficient to catch the lack of rows returned in the shell
script somehow rather than trying to do it from the select, in this case.
--
"Care must be exorcised when handring Opiticar System as it is apts to
be sticked by dusts and hand-fat." --Japanese Translators
"Keep your fingers off the lens." --Elton Byington, English Translator
#9
| |||
| |||
|
|
I have a query inside one shell script and i'm wondering how to return any default value when no rows return from my query. For example: printf "%s\n" "set pages 0" "set feed off" "SELECT value FROM tableA WHERE date > to_date('01/11/2010','dd/mm/ yyyy') ;" | sqlplus -s user/password | while read nr3 from my query "SELECT value FROM tableA WHERE date to_date('01/11/2010','dd/mm/yyyy') ;" no value is returned and when no row return i would like to assign a default value like '-1 ' for example. value -------- -1 |
the case where rows are actually returned. However, it does include a
UNION and a subquery.
SELECT value FROM tableA WHERE date > (...)
UNION
SELECT '-1' FROM dual WHERE (SELECT count(*) FROM tableA WHERE date > (...))=0;
It may be more efficient to catch the lack of rows returned in the shell
script somehow rather than trying to do it from the select, in this case.
--
"Care must be exorcised when handring Opiticar System as it is apts to
be sticked by dusts and hand-fat." --Japanese Translators
"Keep your fingers off the lens." --Elton Byington, English Translator
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.