Sorted Popup parameter queries

(6 posts) (3 voices)
  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.