Re: help with interesting 'query'

Dave wrote:
>
> 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 Received on Mon Aug 17 1998 - 00:00:00 CEST