• Sign In
  1. Messages

Can't include TargetStudy in query on custom assay Data table

Installation Forum (Inactive)

View Message

view list print
Can't include TargetStudy in query on custom assay Data table Andy Straw  2014-02-21 07:59
Status: Closed
 
We have some custom assays where we include a TargetStudy property at the Results/Data level. Our code populates that automatically. We don't use the Run level TargetStudy because we sometimes have results/specimens from multiple studies in a single run.

When I write a query (using Schema Browser) that selects both SpecimenID and the TargetStudy, it saves without parse errors, but I get an error when I execute the query. Something as simple as:

SELECT Data.SpecimenID,
Data.TargetStudy
FROM Data

Causes an error:

ERROR: syntax error at or near "OUTER" Position: 1644

I've tried this on several of our custom assays - same behavior.

If I remove either SpecimenID OR TargetStudy from the SELECT, no error. But including both causes an error. Similarly, using SpecimenID.GlobalUniqueID instead of SpecimenID also results in no error.

The Assay Results tables display fine, including the TargetStudy

I've attached the labkey.log entry for the above query and error message. We're running 13.3 r30648.

Please help. Thanks.

Andy Straw
University of Rochester
 
 
Matthew Bellew responded:  2014-02-21 11:43
Hi Andy, can you try one more thing? There is a funny implementation issue with this foreign key that's a little hard to fix now because of backwards compatibility. This join _appears_ to be based on the specimen column, but it really requires the PK of the assay table. Can you make sure the PK of the data table is selected (maybe ObjectID, double check in the schema browser)? Does that work?
 
Andy Straw responded:  2014-02-21 11:49
Thanks. That works: I simply added Data.RowId to the SELECT clause, and now it works.

Andy
 
Andy Straw responded:  2014-02-25 13:18
I've got a particular query I can't seem to make work with this work-around. I was trying to do:

SELECT DISTINCT Data.SpecimenID,
Data.TargetStudy
FROM Data

If I add Data.RowId to the SELECT clause, that defeats the purpose of the DISTINCT: I get multiple rows with same SpecimenID, instead of one for each. Is there a reasonable way to re-write this to still include Data.RowId (to work around the issue you mention), but get only one row per SpecimenID?

Thanks.

Andy
 
Matthew Bellew responded:  2014-02-25 14:47
This is the same essential problem, the rowid needs to be available when the lookup (implicit join) is resolved. Try something like this



SELECT DISTINCT SpecimenID, <<OTHER FIELD>>, TargetStudy
FROM
(
  SELECT RowId, SpecimenID, SpecimenId.<<OTHER FIELD>>, TargetStudy
  FROM Data
) x
 
Andy Straw responded:  2014-02-26 05:26
That doesn't work. I get the same error as I originally got, just different "position":

ERROR: syntax error at or near "OUTER" Position: 4033

Andy
 
Matthew Bellew responded:  2014-02-26 16:14
Sorry, you 're having trouble with this. I'll need to investigate further. I'm wondering if we're being to clever and optimizing away the rowid reference in the inner query... I won't have time to dive into this right away, but I can give one more idea. Maybe this would work???


SELECT SpecimenID, <<OTHER FIELD>>, TargetStudy
FROM
(
  SELECT RowId, SpecimenID, SpecimenId.<<OTHER FIELD>>, TargetStudy
  FROM Data
) x
GROUP BY SpecimendId, <<OTHER FIELD>>, TargetStudy

Matt
 
Andy Straw responded:  2014-02-27 04:35
Thanks for the suggestions, but this latest one behaves just like the previous ones - ERROR.

Andy
 
jeckels responded:  2014-03-09 23:26
Hi Andy,

I tried creating a GPAT with a TargetStudy field in the Results/Data domain in 14.1. I then created your original query against the assay Data table and it ran without errors:

SELECT DISTINCT Data.SpecimenID,
Data.TargetStudy
FROM Data

First, can you export your assay design (deleting irrelevant fields, if desired/needed) so I can be sure that I'm working with a similar configuration.

Second, can you try running against 14.1 at your convenience? As you know, we've made significant changes to the specimen tables, and the very special lookup code that points to them from assays.

Thanks,
Josh
 
Andy Straw responded:  2014-03-10 05:40
See attached assay design export. Not sure it matters, but we have a TargetStudy property at the result level, and generally set the TargetStudy at that level, since the lab sometimes runs samples from multiple studies in one run.

Andy
 
Andy Straw responded:  2014-03-10 13:54
I tried my query on 14.1 built from source (checked out the source this morning). It works fine. I can include TargetStudy without RowId, and get the results I expect. So the problem I'm having with 13.3 seems to be fixed in 14.1.

Thanks.

Andy
 
Greg Taylor responded:  2014-03-10 15:13
Great, I'm glad that the upgrade solved the problem! We are aiming to release LabKey Server 14.1 at the end of this week. I would offer to make our beta build available but it looks like you got your hands on the most recent version.
Greg