Tabname Substitution Query !
Hi all Again !
Just a small query this time...
I have seen a query like ...
select (select col1 from table1), col2 from table2
It works fine...
(BTW, It was told by A.S.K. - 10q)
Is there any way i can give a query like this ...
select * from (select tabname from systables where tabname like "sys%")
(coz this doesnt work )-; )
Please suggest a way to run something like this.
I have to see the data in all the tables matching a particular pattern.
Currently what i am doing is :-
output to a.sql
select "Select * from "|| tabname ||";" from systables where tabname like
"sys%"
dbaccess <database> a.sql
it is a two step process...
any shortcuts... ?
TIA,
Nayan Jain @Tata Infotech.Com
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
"To laugh often and much;
to win the respect of intelligent people and the affection of children;
to earn the appreciation of honest critics and
endure the betrayal of false friends;
to appreciate beauty, to find the best in others;
to leave the world a little better;
whether by a healthy child, a garden patch or a redeemed social condition;
to know even one life has breathed easier because you have lived.
This is the meaning of success."
-Ralph Waldo Emerson
Shortcut:
(
dbaccess mydatabase - <<EOF
select "Select * from "|| tabname ||";" from systables
where tabname like "sys%";
EOF
) | dbaccess mydatabase -
You could do this less painfully with a 10 line 4GL program or a 15
line ESQL/C program.
Art S. Kagel
Nayan Jain wrote:
>
> Hi all Again !
>
> Just a small query this time...
>
> I have seen a query like ...
>
> select (select col1 from table1), col2 from table2
>
> It works fine...
> (BTW, It was told by A.S.K. - 10q)
>
> Is there any way i can give a query like this ...
>
> select * from (select tabname from systables where tabname like "sys%")
> (coz this doesnt work )-; )>
> Please suggest a way to run something like this.
> I have to see the data in all the tables matching a particular pattern.
>
> Currently what i am doing is :-
>
> output to a.sql
> select "Select * from "|| tabname ||";" from systables where tabname like
> "sys%"
>
> dbaccess <database> a.sql
>
> it is a two step process...
> any shortcuts... ?
>
> TIA,
> Nayan Jain @Tata Infotech.Com
>
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
>
> "To laugh often and much;
> to win the respect of intelligent people and the affection of children;
> to earn the appreciation of honest critics and
> endure the betrayal of false friends;
> to appreciate beauty, to find the best in others;
> to leave the world a little better;
> whether by a healthy child, a garden patch or a redeemed social condition;
> to know even one life has breathed easier because you have lived.
> This is the meaning of success."
> -Ralph Waldo Emerson
>Is there any way i can give a query like this ...
>
>select * from (select tabname from systables where tabname like "sys%")
>(coz this doesnt work )-; )>
>Please suggest a way to run something like this.
>I have to see the data in all the tables matching a particular pattern.
If you do this on a regular basis, it might be worth creating a view based
on a union of select statements on all sys tables. You will need to spend
some time on the select statements as they must all return the same number
of columns (i.e., select * will not work). You can also put the table name
as a column in the view. You are also assuming that the number of sys tables
does not change.
Bashar Chalabi
CTL, London