501 Error when changing sort order with CCDD element

[gregwong]

I want to change the default sort order of a CCDD element with a concat label. I have typed ORDER BY `elementname` DESC in SQL of a DBJ element and it works fine, but I get an 501 error when I put in the Where query of a CCDD element. What is the correct syntax and where should I put it? Thanks!

 

[gregwong]

FRIENDLY BUMP
Anyone? I have tried all the options I could think of and still getting the 501 error.

 

[gregwong]

friendly bump

 

[gregwong]

friendly bump
still cannot figure it out how to do this..

 

[gregwong]

friendly bump..again

 

[rob]

what does the error message say? Can you point us at the page?
thx
Rob

 

[gregwong]

Hi Rob, details sent via PM, thanks!

 

[gregwong]

friendly bump

 

[gregwong]

friendly bump

 

[cheesegrits]

I think you'll need to add a "dummy" WHERE clause. As the tooltip and label says, it's a WHERE query. So we expect the syntax to be a WHERE clause. So try:

1=1 ORDER BY {thistable}.whatever ASC

BTW, it's always safest to include that {thistable} as the table name, which we then replace with whatever alias we are using for that instance of the joined table name, as it avoids potential field name conflicts.

-- hugh

 

[cheesegrits]

BTW, let me know if this trick still works (I know it used to, I've done it many times). If not, a) I'll fix things so it does, and b) add a note in the tooltip about it.

-- hugh

 

[gregwong]

Hi Hugh,

Did what you have instructed, same error.

501 - 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 '}.details DESC' at line 3 SQL=SELECT DISTINCT(`1services`.`date_time`) AS value, CONCAT(1services.date_time, ' - ', REF, ' - ', description)AS text FROM `1services` AS `1services` WHERE guide = '' AND 1=1 ORDER BY {1expenses_56_repeat}.details DESC

 

[rob]

I think you replaced:

{thistable}

with

{1exepenses_56_repeat}

If so then replace "{1exepenses_56_repeat}" with "{thistable}", when we build the query we replace that with the current table alais that the element join is using.

 

[gregwong]

It works, thanks a lot!