1. 

   situ, Member

   Hi Team,

   Can you advise me on how to sort a popup parameter query, the difficulty is that it has a union in the query meaning I need a dummy parameter to sort the unioned result set. The query I have is as follows

   (SELECT null,'All' as ob2,'1' as ob) union all (select id,tm.TAXVALDESC as ob2, '2' as ob from sipo_db.tm_m_ibm_taxdtls tm where tm.type_id=1 and tm.state_id=1) order by ob,ob2;

   The 1st parameter is the ID, the 2nd is the description and the 3rd is a dummy sort parameter.

   The 3rd parameter throws an error in MyDBR. If exclude the 3rd parameter it display fine but in the wrong order.

   DB error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'All' as ob2,'1' as ob) union all (select id,tm.TAXVALDESC as ob2, '2' as ob fr' at line 1
   SQL: select definer, user() from information_schema.routines where routine_schema=database() and routine_name='(SELECT null,'All' as ob2,'1' as ob) union all (select id,tm.TAXVALDESC as ob2, '2' as ob from sipo_db.tm_m_ibm_taxdtls tm where tm.type_id=1 and tm.state_id=1) order by ob,ob2; '

   Any ideas?
2. 

   situ, Member

   Ahhhhh.

   MyDBR does not like the braces "(" in the query, remove them and it all works fine

   SELECT null,'All' as ob2,1 as ob union all select id,tm.TAXVALDESC as ob2, 2 as ob from sipo_db.tm_m_ibm_taxdtls tm where tm.type_id=1 and tm.state_id=1 order by ob,ob2
3. 

   situ, Member

   but....

   I now get the dummy parameters 1 and 2 in the filter list.

   Is there a way to prevent the dummy parameter appearing?
4. 

   myDBR Team, Key Master

   Hi,
   First three popup parameter's columns are reserved for a purpose:

   - 1st is the ID
   - 2nd is the visible value
   - 3rd is the grouping value

   To add an extra sorting column, place it as a fourth column. If you do not need the grouping, just use NULL in the 3rd column.

   --
   myDBR Team
5. 

   situ, Member

   Magic. :-)
6. 

   cupidsrose, Member

   yes

Reply

You must log in to post.