help with interesting 'query'

help with interesting 'query'

Post by Dav » Tue, 18 Aug 1998 04:00:00



hi

        i need the 'groups' help with a query.  hopefully someone will be
able to help.  i have two tables ...

create table foo
(
        id      varchar (10)
        f1      varchar (5)
        f2      int
        f3      varchar (5)
        f4      int
        f5      varchar (5)
        f6      int
        f7      varchar (5)
        f8      int
)

create table fooExtended
(
        id      varchar (10)
        f1      varchar (5)
        f2      int
        f3      varchar (5)
        f4      int
        f5      varchar (5)
        f6      int
        f7      varchar (5)
        f8      int
)

one is obviously a copy of the other.  the contents of foo will be
overriden on a daily basis - we purchase our data from an outside
provider.  however, we extend the data by changing the value of some
subset of the non-primary key fields (id is the primary key).  i need to
be able to perform a query that given an 'id' will retrieve any non null
values from fooExtended but if a field is null in fooExtended will
retrieve its value from foo.

        i realize that one way of solving this is to make fooExtended a
complete copy of foo when and if we extend any of its values - but that
is expensive in terms of storage.  i'm hoping to use a more
efficient/elegant solution.

        here's another question....

        is it possible to programmatically supply a table name, the fields
and predicates used in a query ?  for instance

exec doQuery 'employee', 'emp_id, fname, lname', 'where fname like a%'

doQuery is a stored procedure that programmatically 'builds' a query.  
the above will result in the following query being built

select emp_id, fname, lname
from employee
where fname like 'a%'

for what its worth i'm using ms-sql server 6.5.

any help would be greatly appreciated.

thanks
dave

 
 
 

help with interesting 'query'

Post by Gus » Tue, 18 Aug 1998 04:00:00



> hi

>         i need the 'groups' help with a query.  hopefully someone will be
> able to help.  i have two tables ...

> create table foo
> (
>         id      varchar (10)
>         f1      varchar (5)
>         f2      int
>         f3      varchar (5)
>         f4      int
>         f5      varchar (5)
>         f6      int
>         f7      varchar (5)
>         f8      int
> )

> create table fooExtended
> (
>         id      varchar (10)
>         f1      varchar (5)
>         f2      int
>         f3      varchar (5)
>         f4      int
>         f5      varchar (5)
>         f6      int
>         f7      varchar (5)
>         f8      int
> )

> one is obviously a copy of the other.  the contents of foo will be
> overriden on a daily basis - we purchase our data from an outside
> provider.  however, we extend the data by changing the value of some
> subset of the non-primary key fields (id is the primary key).  i need to
> be able to perform a query that given an 'id' will retrieve any non null
> values from fooExtended but if a field is null in fooExtended will
> retrieve its value from foo.

>         i realize that one way of solving this is to make fooExtended a
> complete copy of foo when and if we extend any of its values - but that
> is expensive in terms of storage.  i'm hoping to use a more
> efficient/elegant solution.

>         here's another question....

>         is it possible to programmatically supply a table name, the fields
> and predicates used in a query ?  for instance

> exec doQuery 'employee', 'emp_id, fname, lname', 'where fname like a%'

> doQuery is a stored procedure that programmatically 'builds' a query.
> the above will result in the following query being built

> select emp_id, fname, lname
> from employee
> where fname like 'a%'

> for what its worth i'm using ms-sql server 6.5.

> any help would be greatly appreciated.

> thanks
> dave

The dynamic SQL is not possible in MS-SQL Server 6.5 I believe. It does
not allow for compilation and optimisation as the database does not know
anything about the data sources. It would not be possible to compile,
and you would best solve the problem by using dynamic SQL, or embedded
SQL.

As for the other question, try:

select f.id, IsNull (fe.f1, f.f1), IsNull (fe.f2, f.f2), IsNull (fe.f3,
f.f3), .....
from foo f,
     fooExtended fe
where fe.id = f.id

By the nature of the query I would guess that you do not necessarily
have a record in fooExtended for every row in foo, that is, there are
more rows in foo than fooExtended. You would then want to use an outer
join, and it would have to look like this:

select f.id, IsNull (fe.f1, f.f1), IsNull (fe.f2, f.f2), IsNull (fe.f3,
f.f3), .....
from foo f,
     fooExtended fe
where f.id *= fe.id

The logic in the above query can be thought of as follows: "Look for all
records in foo. If there is also a record in fooExtended, then use the
data in fooExtended (if it is not null), otherwise use the info from
foo."

Hope that helps

Gus

 
 
 

help with interesting 'query'

Post by Phil Edmond » Thu, 20 Aug 1998 04:00:00


Don't know about the first one, it'll take me too long to work out.
For the second, use the DBMS_SQL package. As usual on this newsgroup,
it's all in the manual if you could be bothered to look instead of
asking us.

>hi

> i need the 'groups' help with a query.  hopefully someone will be
>able to help.  i have two tables ...

>create table foo
>(
> id varchar (10)
> f1 varchar (5)
> f2 int
> f3 varchar (5)
> f4 int
> f5 varchar (5)
> f6 int
> f7 varchar (5)
> f8 int
>)

>create table fooExtended
>(
> id varchar (10)
> f1 varchar (5)
> f2 int
> f3 varchar (5)
> f4 int
> f5 varchar (5)
> f6 int
> f7 varchar (5)
> f8 int
>)

>one is obviously a copy of the other.  the contents of foo will be
>overriden on a daily basis - we purchase our data from an outside
>provider.  however, we extend the data by changing the value of some
>subset of the non-primary key fields (id is the primary key).  i need to
>be able to perform a query that given an 'id' will retrieve any non null
>values from fooExtended but if a field is null in fooExtended will
>retrieve its value from foo.

> i realize that one way of solving this is to make fooExtended a
>complete copy of foo when and if we extend any of its values - but that
>is expensive in terms of storage.  i'm hoping to use a more
>efficient/elegant solution.

> here's another question....

> is it possible to programmatically supply a table name, the fields
>and predicates used in a query ?  for instance

>exec doQuery 'employee', 'emp_id, fname, lname', 'where fname like a%'

>doQuery is a stored procedure that programmatically 'builds' a query.
>the above will result in the following query being built

>select emp_id, fname, lname
>from employee
>where fname like 'a%'

>for what its worth i'm using ms-sql server 6.5.

>any help would be greatly appreciated.

>thanks
>dave

 
 
 

help with interesting 'query'

Post by Kris Klindwort » Thu, 20 Aug 1998 04:00:00



> create table foo
> (
>         id      varchar (10)
>         f1      varchar (5)
>         f2      int
>         f3      varchar (5)
>         f4      int
>         f5      varchar (5)
>         f6      int
>         f7      varchar (5)
>         f8      int
> )

> create table fooExtended
> (
>         id      varchar (10)
>         f1      varchar (5)
>         f2      int
>         f3      varchar (5)
>         f4      int
>         f5      varchar (5)
>         f6      int
>         f7      varchar (5)
>         f8      int
> )

> one is obviously a copy of the other.  the contents of foo will be
> overriden on a daily basis - we purchase our data from an outside
> provider.  however, we extend the data by changing the value of some
> subset of the non-primary key fields (id is the primary key).  i need to
> be able to perform a query that given an 'id' will retrieve any non null
> values from fooExtended but if a field is null in fooExtended will
> retrieve its value from foo.

If I understand your problem, you want something like this...

select
    foo.*
from foo, fooExtended
where foo.id=fooExtended.id and fooExtended.f1 is null
union
select *
from fooExtended
where fooExtended.f1 is not null
;
---------------------------------------------------------

Oracle DBA                     602 West University
Carle Clinic Association        Urbana, IL 61801

 
 
 

1. help with interesting 'query'

hi

        i need the 'groups' help with a query.  hopefully someone will be
able to help.  i have two tables ...

create table foo
(
        id      varchar (10)
        f1      varchar (5)
        f2      int
        f3      varchar (5)
        f4      int
        f5      varchar (5)
        f6      int
        f7      varchar (5)
        f8      int
)

create table fooExtended
(
        id      varchar (10)
        f1      varchar (5)
        f2      int
        f3      varchar (5)
        f4      int
        f5      varchar (5)
        f6      int
        f7      varchar (5)
        f8      int
)

one is obviously a copy of the other.  the contents of foo will be
overriden on a daily basis - we purchase our data from an outside
provider.  however, we extend the data by changing the value of some
subset of the non-primary key fields (id is the primary key).  i need to
be able to perform a query that given an 'id' will retrieve any non null
values from fooExtended but if a field is null in fooExtended will
retrieve its value from foo.

        i realize that one way of solving this is to make fooExtended a
complete copy of foo when and if we extend any of its values - but that
is expensive in terms of storage.  i'm hoping to use a more
efficient/elegant solution.

        here's another question....

        is it possible to programmatically supply a table name, the fields
and predicates used in a query ?  for instance

exec doQuery 'employee', 'emp_id, fname, lname', 'where fname like a%'

doQuery is a stored procedure that programmatically 'builds' a query.  
the above will result in the following query being built

select emp_id, fname, lname
from employee
where fname like 'a%'

for what its worth i'm using ms-sql server 6.5.

any help would be greatly appreciated.

thanks
dave

2. CASE, IF in the WHERE clause

3. Query help with an 'averageing' query

4. Member Exists

5. **************!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Help me !!!!!!!!!!!!!!!!!!!!!!!!'''''''''''''''''''''''*************

6. SQL and Dates (D1)

7. Interesting 'undocumented feature'

8. [Fwd: How to avoid cc warnings?]

9. Interesting behaviour of : LIKE @v + '%'

10. er, 'interesting' new 9i feature

11. Help with 'group by' SQL Query

12. Help on Simple 'OR' Query

13. Need help with SQL query 'AND'ing a field against a constant