Welcome to the FogBugz Knowledge Exchange

Question

0

Hello,

I'm developing a plugin, and I'm in need of having the bugQuery.AddOrderBy() in the GridColumnSortQuery() return a different number depending on the Bug.ixCategory (yes, it's for one column).

My SQL statement is correct, but FogBugz does some checking of the SQL string argument that is passed in to bugQuery.AddOrderBy() (or any of the methods actually) and complains at runtime that the syntax is not correct.

The code is something like:

public CBugQuery GridColumnSortQuery(CGridColumn col,
       bool fDescending, bool fIncludeSelect)
...
bugQuery.AddOrderBy(String.Format(
      @"CASE WHEN Bug.ixCategory = 5 
             THEN {0}.{1}*{2}.{1}
             WHEN Bug.ixCategory = 1
             THEN {3}.{1}*{4}.{1}
             END {5}",
      api.Database.PluginTableName(TABLE1_NAME),
      FIELD_NAME,
      api.Database.PluginTableName(TABLE2_NAME),
      api.Database.PluginTableName(TABLE3_NAME),
      api.Database.PluginTableName(TABLE4_NAME),
      (fDescending ? "DESC" : "ASC")
      ) );

Anyone has any thoughts on this? Wild thinking here, is there a way to make FogBugz not check for the arguments and let the string go through?

thanks!

flag	asked Nov 26 2010 at 5:40 Totem 169●4

bitbounce · Answer 1 · 2010-11-26 18:06:46Z UTC

1

I believe there is no way around that since FB runs on different types of DB, and they don't all follow the same syntax. I would just take the results of the base query and post process them to the order you want. LinqBridge would be a big help in that.

answered Nov 26 2010 at 18:06

bitbounce
318●1●6

	Hi, that may work for other functions, but remember that I need to return a CBugQuery as part of GridColumnSortQuery(). I can do as much as I want in that function but FogBuz will run what return in GridColumnSortQuery() to get the sorting done. – Totem Nov 26 2010 at 20:51
	ahh right. Well, I'm not sure you're going to be able to do it as I can only assume the FB doesn't allow the "generic" use of CASE in the sql. You will probably have to add a BugJoin table, and do calculations there, adding to the new columns. You should be able to determine when a bug changes categories and update as required. You should then have available in the sorting a specific column which you can just do a nice sort on. Make sense? – bitbounce Nov 26 2010 at 21:56
	Sorry, you would do the "calculations there" by implementing the BugCommit interface. – bitbounce Nov 26 2010 at 21:57
	Yeah, I'm trying to combine the calculated columns (2 of them, one for each type of category) into one column. The only difference is the multiplier number. I can easily add another column, but I want to avoid doing that. – Totem Nov 28 2010 at 20:12

[plugin-dev] In need of SQL CASE WHEN clause in a ORDER BY?

1 Answer