1

I have a problem with BIRT. I realised that i cannot pass 301,90,96,121,139,367,291,296,298 as a string into this query "SELECT dishes.name from dishes where dishes.id IN (?)". But my database is structured in such a way that it has to use the comma denominated ids. Is there a way out on this? thanks!

Moses Liao GZ
  • 1,556
  • 5
  • 20
  • 45
  • In your dataset Before Open property set: this.queryText=this.queryText.replaceAll('billbill',params["RP_mylist_selection_param"].value.join(",")); where billbill is replaced (in your dataset query) before your query is executed: ie... from TABLE where ( TABLE.STARTTIME >= TRUNC( ? ) and TABLE.STARTTIME <= last_day( ? ) ) and TABLE.SC IN 'billbill' ) – The HCD Oct 16 '13 at 12:33

1 Answers1

1

The workaround is to use a MySQL function FIND_IN_SET():

SELECT dishes.name from dishes where FIND_IN_SET(dishes.id, ?)

But be aware that this will perform terribly. There's no way to index a search into the middle of a comma-separated list of values. So this is bound to cause a table-scan every time.

The better choice is to avoid storing lists as comma-separated strings, if you need to search for individual values in the list.

Re your comment:

Normalize as a default. Then denormalize to optimize specific queries. Note that denormalizing optimizes for one query against the data, at the expense of other queries.

For example, if you have books <--> authors, you could denormalize by storing the authors of a book as a comma-separated list in the books table. That makes it quick to query for all authors of a book, because you avoid a join. But it makes it less efficient to query for all books of a given author.

So you have to know which queries you need to be most efficient, and which queries you can afford to make less efficient. This depends entirely on your application.

If you need all queries to be equally efficient, keep the data in normalized form.

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks, it works. I have another question. Under what circumstances is it better to denormalised than normalised the data? – Moses Liao GZ Mar 23 '13 at 03:21