In my application the following abbreviation allows me to input a value and choose its type within the application e.g.
:input
in this case I created a variable
:Lang1 as a varchar
I have the following code simply asking how many speak a particular language from an array using a case statement
What I want to know is how many students know a language from an array and to check against the input field if it has a value inside it or not
(Case When Length(:Lang1)>4 --I'm assuming any language has more than 4 letters
then languages[1] = :Lang1 --When this condition statement is true then I get the children
--and the number of children speaking the entered language as a 1st language
else (ignore condition) end) --Simply trying to ignore the else side of the condition
I tried this code
(Case When Length(:Lang1)>4 then languages[1] = :Language
else cast(:Lang1 as varchar(20)) end)
ERROR: CASE types character varying and boolean cannot be matched
and this code
(Case When Length(:Lang1)>4 then languages[1] = :Language
end)
which produced a bunch 0 0 0s
This post is related to its former predecessors Part 2: how to get the Sum of a partition based query without actually pivoting and is placed in the following area
JOIN schoollevel sl ON sl.id = p.schoollevelid
GROUP BY s.studentnumber, p.firstname
) t
Where (Case When Length(:Lang1)>4 then languages[1] = :Lang1
else cast(:Lang1 as varchar(20)) end)
)
select *
A case expression returns a value, not a fragment of syntax you can use as a condition. You could instead use the or logical operator to create this logic:
WHERE LENGTH(:Lang1) <= 4 OR languages[1] = :Lang1