I need to use IIF with ORDER BY for CiCharacter and Integer fields, something like:

SELECT * FROM tablename WHERE ... ORDER BY iif(Field1CiCharacter IS NOT NULL, Field2CiCharacter, Field3Integer)

But this results in an error message: iif: Incompatible parameter data types

How to solve this?

asked 21 May '15, 01:25

Marc's gravatar image

Marc
522202539
accept rate: 20%

edited 21 May '15, 09:14

Jens%20M%C3%BChlenhoff's gravatar image

Jens Mühlenhoff
1.3k51037


IIF only works when the second and third argument have the same data type. You can use CONVERT (or CAST) to make this work:

TRY DROP TABLE #tablename; CATCH ALL END TRY;

CREATE TABLE
  #tablename
(
    Field1CiCharacter CICHAR(50)
  , Field2CiCharacter CICHAR(50)
  , Field3Integer INTEGER
);

SELECT
  * 
FROM #tablename 
ORDER BY 
  IIF(Field1CiCharacter IS NOT NULL, Field2CiCharacter, CONVERT(Field3Integer, SQL_CHAR))
;

This really has nothing to do with ORDER BY and the solution is documented in the help for error code 2191:

Problem: Incompatible data types are given to the UNION statements, IFNULL, IIF, or CASE expressions. For example: "SELECT 1 FROM system.iota UNION SELECT 'abc' FROM system.iota" will resulted in 2191 error because the integer value in the first SELECT is not compatible with the string value in the second SELECT for the purpose of the UNION. Similarly, "SELECT IIF( bVal, 1, 'abc' ) FROM system.iota" will also return 2191 error.

Solution: Use Cast() or Convert() function to force the expressions into the compatible type.

Just for fun:

How can we extend this solution to sort numerical correct?

TRY DROP TABLE #tablename; CATCH ALL END TRY;

CREATE TABLE
  #tablename
(
    Field1CiCharacter CICHAR(50)
  , Field2CiCharacter CICHAR(50)
  , Field3Integer INTEGER
);

INSERT INTO #tablename VALUES (NULL, NULL, 2147483647);
INSERT INTO #tablename VALUES (NULL, NULL, 9);
INSERT INTO #tablename VALUES (NULL, NULL, 10);
INSERT INTO #tablename VALUES (NULL, NULL, -9);
INSERT INTO #tablename VALUES (NULL, NULL, -10);
INSERT INTO #tablename VALUES (NULL, NULL, -2147483647);
INSERT INTO #tablename VALUES ('X' , 'A' , NULL);
INSERT INTO #tablename VALUES ('X' , 'A' , NULL);
INSERT INTO #tablename VALUES ('X' , 'B' , NULL);

SELECT
  drt.*
FROM
(
SELECT
    tab.*
  , CASE
      WHEN Field1CiCharacter IS NOT NULL THEN 
        Field2CiCharacter
      WHEN SIGN(Field3Integer) = -1 THEN 
        '-' + RIGHT(REPEAT('0', 10) + TRIM(CONVERT(2147483647 + Field3Integer, SQL_CHAR)), 10) COLLATE ADS_DEFAULT_CI
      ELSE                               
        '+' + RIGHT(REPEAT('0', 10) + TRIM(CONVERT(             Field3Integer, SQL_CHAR)), 10) COLLATE ADS_DEFAULT_CI
    END AS "sort" 
FROM #tablename tab 
) drt
ORDER BY
  drt.sort

This relies on a few tricks:

  1. A derived table is used to pull the sort order in as a field value (which is nice for debugging)
  2. This code uses CASE analysis instead of IIF
  3. It only works on INTEGER (32 bit) as used in the ADT field format, that's where the 2147483647 value comes from
  4. This is a one level ORDER BY only, so there is no distinction between the different sort "kinds".
link

answered 21 May '15, 04:24

Jens%20M%C3%BChlenhoff's gravatar image

Jens Mühlenhoff
1.3k51037
accept rate: 18%

edited 21 May '15, 09:34

Thank you. I know about cast or convert but I need numerical sorting.

(21 May '15, 04:48) Marc

@Marc Your question is a bit vague, you should add a bit more context.

(21 May '15, 08:59) Jens Mühlenhoff

One way to accomplish this:

SELECT
  * 
FROM #tablename 
ORDER BY 
  IIF(Field1CiCharacter IS NOT NULL, Field2CiCharacter, ''),
  Field3Integer

The more general form:

SELECT
  * 
FROM #tablename 
ORDER BY 
  IIF(Field1CiCharacter IS NOT NULL, Field2CiCharacter, ''),
  IIF(Field1CiCharacter IS NULL, Field3Integer, 0)
link

answered 21 May '15, 08:53

Alex%20Wong's gravatar image

Alex Wong
5.3k2488
accept rate: 42%

edited 22 May '15, 04:21

Jens%20M%C3%BChlenhoff's gravatar image

Jens Mühlenhoff
1.3k51037

1

Where you could just use Field3Integer as the second ORDER BY expression if Field3Integer is always to be used to disambiguate the order when Field2CiCharacter has identical values.

(21 May '15, 09:04) Jens Mühlenhoff

Good point. I edited post to reflect that. If there are more fields to be checked than the general form can be used.

(21 May '15, 09:36) Alex Wong

Another solution if you want to have more control over the "sort group":

SELECT
  drt.*
FROM
(
SELECT
    tab.*
  , IIF(Field1CiCharacter IS NOT NULL, 0, 1) AS "sort_group"
FROM #tablename tab 
) drt
ORDER BY
    drt.sort_group
  , IIF(sort_group = 0, Field2CiCharacter, '')
  , IIF(sort_group = 1, Field3Integer, 0)

This will sort by Field2 if Field1 IS NOT NULL or by Field3 if Field1 IS NULL. Field2 sorted records will appear before Field3 sorted records. This can be easily adjusted.

link

answered 21 May '15, 09:40

Jens%20M%C3%BChlenhoff's gravatar image

Jens Mühlenhoff
1.3k51037
accept rate: 18%

Thank you both Jens and Alex for your very interesting suggestions. I'll try them thoroughly this weekend. Can you please add Alex's first answer back so it is documented inside this thread? This would be nice. @Jens, yes, the question may be a bit vague but this might be the beginning of further questions with even more complicated sorting (numerical and alphabetical) if I don't succeed with applying this first information. Thank you again!

(22 May '15, 00:42) Marc

I have added the original code back into Alexs answer.

(22 May '15, 04:22) Jens Mühlenhoff
Your answer to the original question.
If responding to a request for additional information, please edit the question or use the comment functionality.
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×325
×6
×2
×1
×1

Asked: 21 May '15, 01:25

Seen: 2,292 times

Last updated: 22 May '15, 04:22

Advantage Developer Zone Contact Us Privacy Policy Copyright Info


Powered by Advantage Database Server and OSQA
Disclaimer: Opinions expressed here are those of the poster and do not necessarily reflect the views of the company.