Why am I getting "token unknown" in this SQL select statment?

[breirden]

Windows 10, fully updated. LO: 6.4.4, embedded Firebird database.

I am trying to select the first “n” rows of an internal table. The number of records to be selected is unknown until a count is taken. I have researched the internet, and according to the Firebird website what I am attempting should be a valid statement, but I keep getting “token unknown” on the CASE statement. Is what I am attempting to do here valid? Here is the select statement:

Note: If I replace the CASE statement with an integer, it works.

/* Qry_Index_Calc - calculates handicap index from top 10 differentials of last 20 games /
/ Source is “VW_Plyr_Diff” which has handicap differentials already calculated. */

SELECT (AVG (“T10”.“HDIF”) * .96) “Index”, (Count (“T10”.“HDIF”)) FROM

/* Get only the games needed if less than 20 games have been played. */

(SELECT FIRST 	CASE (SELECT (COUNT ( "L20"."HDIF") FROM "L20")) 	
					WHEN <5 			THEN 0
					WHEN >4 and <7		THEN 1
					WHEN >6 and <9		THEN 2
					WHEN >8 and <11 	THEN 3
					WHEN >10 and <13 	THEN 4
					WHEN >12 and <15 	THEN 5
					WHEN >14 and <17 	THEN 6
					WHEN = 17 			THEN 7									
					WHEN = 18 			THEN 8
					WHEN = 19 			THEN 9
										ELSE 10				
				END						

"L20"."HDIF" FROM

/* Get up to 20 of the most recent (last) games played. */

		( SELECT FIRST 20 "PlayerID" "PID", "GID" "GID", 
		RANK ( ) OVER ( PARTITION BY "PlayerID" ORDER BY "Diff" ) "Rnk", 
		"Diff" "HDIF", "Date" "Gdate"
		FROM "Vw_Plyr_Diff"
		WHERE "PlayerID" = 1) 

"L20" ) 

“T10”

[Ratslinger]

Hello,

Do not see your CASE syntax in the Firebird post → CASE construct

WHEN <5             THEN 0

<, >, = are not valid in the SEARCHED case (you are using this).

For SIMPLE case something such as this is possible:

Select  CASE  When  COUNT( "L20"."HDIF") < 3  Then 0
                  When  COUNT(L20."HDIF") < 7  Then 2
                  When  COUNT(L20."HDIF") < 8  Then 4
            ELSE 1
       end

Probably easiest just to use DECODE() instead. See → DECODE()

As with all questions, if this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.

[breirden]

Thanks, Ratslinger. The DECODE worked and apparently Firebird wanted me to make the innermost SELECT a CTE. I appreciate your help.

[Ratslinger]

@breirden,

As with all questions, as you have been helped, please help others to know the question has been answered by clicking on the in upper left area of answer which satisfied the question.