Database table with key/value pairs »

[Paul Traut]

Hello,

I have a database table like:
orderid|      key       |   value
    22     | ExpiryDate | 190606
    22     | Batch         | 123456
    22     | Product      | Test

Now I would like to see in one TextItem the value of the line where the key is "ExpiryDate" and in another TextItem the value of the line where the key is "Batch".

Hope you can understand what i mean.

Is there an easy way to do that or do i have to add for every TextItem a new datasource with the corresponding SQL query?

Paul

[Subst]

Hi!
In what order the TextItems is on the page?

[Paul Traut]

Hello,

at first I have the TextItem with the BatchNumber and then the one with the ExpiryDate but all in one DataBand.
I need something like "$S{$D{order.value} where $D{order.key} == "Batch"}" or similar.

[Subst]

I see.
You need to refactoring of your database or as you known need to create datasources with appropriate queries for each textitem.
May be Alex know some other variantes

[Arin Alex]

I can suggest to use subqueries in your sql query to combine all the values in one line.
For example:

Code: [Select]

Select (Select Value from order o where o.OrderId = order.OrderId and Key = 'Batch') Batch,
       (Select Value from order o where o.OrderId = order.OrderId and Key = 'ExpiryDate') ExpiryDate
from order
where OrderId = 22


[Subst]

Alex, if no the one order in table this variant not appropriate. In this case is needed to create more then one query all the same.

May be using group by orderid and put the resulting datasource as datasource for band?
So we'll get the datasource like this

orderid  | expirdedate | batch
______________________________
 1       | some        | some
 2       | some        | some

I've no test this idea, but it's possiblle the solution?

[Paul Traut]

Thank you for your answers.

I have tested the sql query and in my case I need to "group by orderid" otherwise I will get several lines (depending on the number of lines with the same orderid) with the same entries.

I don’t know Alex, but maybe in the future  there could be something like "$D{order.Value where order.Key = 'something'}" Something could although be a Variable $V{...}. Think this would give more flexibility

[Arin Alex]

Paul Traut
You can also use "distinct" instead "group by"
I will think about your suggestion

[Arin Alex]

I have added the function getFieldByKeyField("Datasource name", "Value field name", "Key field name", "Key value") in development branch at github

[Paul Traut]

Thanks!!

I will test it

[Paul Traut]

Hello, I`m back 

I am trying to use the function getFieldByKeyField("Datasource name", "Value field name", "Key field name", "Key value") but the designer tells me, that he can not find the variable getFieldByKeyField. I have installed thid version "https://github.com/fralx/LimeReport.git"

[Arin Alex]

Hi
You have to use Development branch at github.

[Paul Traut]

I have tried to do so, but i failed 
How can I get the Development branch?

[Arin Alex]

Sorry i mistyped the branch name is "develop".
if you use git command line client, just type: git checkout develop