"Parameter xxx has no default value" error when using Order by in SQL statement

1
votes

I am trying to run the following SQL statement:

"SELECT *, (IIF([Field]=TRUE,'StringValue1','StringValue2') AS [NewField] FROM [Table1] ORDER BY [NewField] ASC"

But this gives me an error "Parameter NewField has no default value". How can I solve it?

I am using Microsoft Access (MDB) database using Jet Engine from Delphi 7.

Thank you!

2
sqldelphims-access
I know, that the SQL above doesn't really make sense, but in the actual statement I have several IIF statements nested with more complex condition, but it still gives the same error - Tofig Hasanov
Why the mysql tag? Are you using MySQL? - Mark Byers
Sorry, I am not really good in distinguishing these names. - Tofig Hasanov
You may want to put those several IIf statements in a Function. - JeffO

2 Answers

2
votes

In the ORDER BY clause, you can reference a column by its ordinal number:

SELECT
  IIF(T.[Field]=TRUE, 'StringValue1', 'StringValue2') AS [NewField],
  T.*
FROM [Table1] T
ORDER BY 1 ASCENDING
0
votes

I found another weird way to solve this problem:

I just repeat the IIF statement instead of using field name, like:

SELECT *, (IIF([Field]=TRUE,'StringValue1','StringValue2')) AS [NewField] FROM [Table1] ORDER BY (IIF([Field]=TRUE,'StringValue1','StringValue2')) ASC