Java SQL Lookup Question: /home/Support/LabKey Support Forum

Java SQL Lookup Question

LabKey Support Forum (Inactive)

View Message

Java SQL Lookup Question

dennisw

2016-03-14 18:07

Status: Closed

Hi,

If I run the following query in the Query Editor:

SELECT VisitDescription, PrimaryType, DerivativeType, Count(DerivativeType) as Total FROM SpecimenDetail WHERE AtRepository = 1 group by VisitDescription, PrimaryType, DerivativeType

... the result will show the text Primary Type and Derivative Type. If I run this using the Java API on a given Study then I will get the numerical values for the types. Is there an easy way to phrase the query so that I can get the same result as I get in the LabKey Query Editor?

I've looked at the Lookup documentation at https://www.labkey.org/home/Documentation/wiki-page.view?name=lookups but it seems to work differently when using the Java API. I'm sure I can come up with a Join if necessary, just wondering if there is an easier way.

Thanks

jeckels responded:	2016-03-14 22:18
Hi Dennis, Yes, you can use LabKey SQL's special lookup syntax to get the text value for these columns. In short, you can use "Column.LookupColumn" instead of doing a JOIN from "Column" to get the value of "LookupColumn" in the lookup table. Something like: SELECT VisitDescription, PrimaryType.Description AS PrimaryTypeDescription, DerivativeType.description AS DerivativeTypeDescription, Count(DerivativeType.description) as Total FROM SpecimenDetail WHERE AtRepository = true group by VisitDescription, PrimaryType.description, DerivativeType.description https://www.labkey.org/wiki/home/Documentation/page.view?name=lookups Thanks, Josh

dennisw responded:	2016-03-16 08:31
Thanks! That works a charm.. I was overthinking it :-)