[Solved] How to use case when

[AfTech54]

Hi, I'm trying to replace a Boolean with text but I'm not sure what I'm doing wrong here. Thankful for any help!

Code: Select all

Select S."Source" "Källa", Owner_1 "Ägare 1", Owner_2 "Ägare 2", Owner_3 "Ägare 3", S."Period", S."Comment" "Kommentar",
[i]S.(Case When "Mixed_Cams" = 1 Then 'Yes') as "MIX"[/i] 

From
	(
	Select A."ID_Source",
	Max(case when SLOT = 0 then PE."Name" end) as Owner_1,
	Max(case when SLOT = 1 then PE."Name" end) as Owner_2,
	Max(case when SLOT = 2 then PE."Name" end) as Owner_3


	From
		(
		Select
		SP."ID_Source", SP."ID_Person",
		mod(Count(SP1."ID_Person"), 3) SLOT
		From "SourcePerson" SP
		Left Join "SourcePerson" SP1
			On SP1."ID_Source" = SP."ID_Source" and SP1."SourcePerson" < SP."SourcePerson"
		Group By SP."ID_Source", SP."ID_Person"
		) A

	Join "Person" PE 
		On PE."ID_Person" = A."ID_Person"
	Group By A."ID_Source"
	) B

Join "Source" S 
	On S."ID_Source" = B."ID_Source"
Where S."Source" = 'RESS 2007'  --Ange Källa eller kommentera bort (--...) raden för att visa alla album
Order By S."Source"

[Sliderule]

Hi, I'm trying to replace a Boolean with text but I'm not sure what I'm doing wrong here. Thankful for any help!

Select S."Source" "Källa", Owner_1 "Ägare 1", Owner_2 "Ägare 2", Owner_3 "Ägare 3", S."Period", S."Comment" "Kommentar",
S.(Case When "Mixed_Cams" = 1 Then 'Yes') as "MIX"

I will assume ( since you did NOT tell us ) that the database back-end you are using is HSQL .

CASE WHEN...

CASE WHEN expr1 THEN v1[WHEN expr2 THEN v2] [ELSE v4] END

when expr1 is true return v1 [optionally repeated for more cases] [otherwise v4 or null if there is no ELSE]

Your portion of the CASE WHEN is:

Code: Select all

Case When "Mixed_Cams" = 1 Then 'Yes') as "MIX"

However, you have neither:

1. an ELSE portion
2. an END portion

So, try the following:

Code: Select all

Case When S."Mixed_Cams" = 1 Then 'Yes' When S."Mixed_Cams" = 0 Then 'No' Else '???' END) as "MIX"

The above, will provide for text output of the three possible return values of a Boolean value:

1. False
2. True
3. NULL

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[AfTech54]

Thanks!!