• Sign In
  1. Messages

Complex Query

LabKey Support Forum (Inactive)

View Message

view list print
Complex Query Ole  2015-05-22 04:49
Status: Closed
 
Hi,

I do have a question about the posibility of simplifying my complex queries i'm using at the moment.
I'm using rather long queries to collect and calculate data from different (list) tables. These lists contain different values all assigned to identical primary keys. For example

table 1 (measured data):
| labID (primary key)| element1_concentration| element2_concentration |

table 2 (data needed for calculation):
| labID (primary key) | weight | volume |

table 3 (sampletable):
| labID (primary key) | description | label |

My query then calculates new values based on the entries in all of the tables, resulting in a new one:

result table:
| labID (primary key) | label | calculated_element1_concentration | calculated_element2_concentration |

At the moment i have to "manually" calculate each of the elemt1, element2 ... concentrations, resulting in a very long query and more or less the same code over and over again. It would be really nice to cycle through all the required elements 1, 2 ... using a loop, so that my query only contains one calculation function.
Is this possible using the given sql-language in labkey? Or is my understanding of databases and sql just to limited to get it the right way?

Thank you very much
Ole
 
 
Jon (LabKey DevOps) responded:  2015-06-16 15:17
Hi Ole,

I'm confused as to what you mean when you say you're "'manually' calculating each element1 & element2 concentrations" here.

Looking at your table examples, you only have one table listing element concentrations. So what is this calculation you're having to do manually? Can you provide us with your SQL code that you're using that is producing this result table so we can get a better understanding to what you're doing?

Regards,

Jon
 
Ole responded:  2015-07-14 00:59
Hi,

sorry for my late response. I'm traveling a lot at the moment and will in the next time.

For example I use such code for calculating different elements:

#########################################################
SELECT rawdata_ic.labID, sampletable_solids.label,
-----------------Chlorid
CASE
    WHEN (rawdata_ic.Cl/rawdata_ic.Cl_factor) <= loq_ic.Cl THEN '< NWG'
    WHEN (rawdata_ic.Cl/rawdata_ic.Cl_factor) <= loq_ic.Cl*3 THEN '< BG'
    ELSE TO_CHAR((rawdata_ic.Cl*rawdata_ic.sample_volume/rawdata_ic.sample_weight), '999G990')
END AS "Cl [mg/kg]",
------------------Bromid
CASE
    WHEN (rawdata_ic.Br/rawdata_ic.Br_factor) <= loq_ic.Br THEN '< NWG'
    WHEN (rawdata_ic.Br/rawdata_ic.Br_factor) <= loq_ic.Br*3 THEN '< BG'
    ELSE TO_CHAR((rawdata_ic.Br*rawdata_ic.sample_volume/rawdata_ic.sample_weight), '999G990D999')
END AS "Br [mg/kg]"
-------------------
FROM
    Project."XXX".lists.sampletable_solids,
    Project."XXX".lists.rawdata_ic,
    "XXX".lists.loq_ic,
WHERE
    sampletable_solids.labID = rawdata_ic.labID AND
    loq_icpoes.icpID LIKE 'loq_001'
ORDER BY
    rawdata_ic.labID
#########################################################

Repeating over and over for different elements. It would be much easier to just simply fill a loop with the according elements and only have one "fed printout-code".

Regards
Ole
 
kevink responded:  2015-07-14 15:16
I would recommend either (a) writing a script to create the sql for you or (b) de-pivot the rawdata_ic table into a long and skinny table along the lines of (labid, name, value). If you de-pivot the table, you can probably just do a simple query and wrap it in a pivot query.