[ic] Userdb is View of Union - query / scan problems
paul at gishnetwork.com
Tue Dec 7 19:56:39 UTC 2010
> From: chris at endpoint.com
> Paul Jordan wrote:
> > IC 5.7.6
> > Mysql 5.0.77
> > I have a userdb that is a Mysql View of a Union. The union ties
> > together TableA and TableB.
> > Using the [Query] and [search-region] I can only retrieve records from
> > this userdb that are from the TableA member of the Union.
> > However, in Admin, I can see all records in userdb (TableA's and
> > tableB's) just fine, and I believe it uses scan to search this.
> > Is there something fundamentally different with [query] that would
> > make it have trouble working on a Union? I don't need to do anything
> > fancy, I really just select a value.
> > No errors in Cat or Vend error.log
> Does your dbconf/userdb.mysql file reflect the actual table structure? I
> do know that Interchange sees views as tables so if the dbconf files are
> different from the actual table structure, the query and search-region
> tags will not have access to the columns.
I can [query] the View'd userdb just fine as long as the result is from TableA of the union, so I think [query] is at least partially happy with what is defined.
In Admin under database information, IC sees the View'd userdb as I have defined it in it's .mysql file. The two tables (in the union) are the same WRT to the columns in the union at least.
To me it looks like it is defined okay, however, it is a View, so it is not like I am entirely sure what the column datatypes are, unless I just assume they are the same as the Unioned tables (which is what I did).
To summarize, I have a View of a Union of two tables. I can only [query] the View and retrieve results from one of the tables in the Union, but /scan/ sees everything on the same IC.
The View and Union are done in Mysql, and AFAIK IC thinks its just a plain old userdb table.
More information about the interchange-users