Обсуждение: Case statement with different data types
Hi everyone, I am currently working on an app that is split into several databases with the same table but a column with differing data type. eg. Database 1 tablename: gp column: available - data type boolean Database 2 tablename: gp column: available - data type character(1) I would like to be able to create a query that returns 'T' or 'F' using the same code & query. I have tried a few different cast() options without success - and then tried: SELECT CASE WHEN ((SELECT data_type FROM information_schema.columns WHERE table_name = 'gp' and column_name = 'available') = 'boolean') THEN CASE WHEN (available) THEN 'T' ELSE 'F' END ELSE CASE WHEN (available='T' OR available='t') then 'T' ELSE 'F' END END as available FROM gp When I run this query on the boolean data type database it works correctly - however on the character field I get the error <font color="#ff0000">'ERROR: argument of CASE/WHEN must be type boolean, not type character' I really don't want to go through and change data types just at the moment - can anyone advise any way around this? Many thanks, Gus
Hello try to explicit cast select 't'::boolean; regards Pavel Stehule 2009/10/14 Gus Waddell <angus.waddell@palcare.com.au>: > Hi everyone, > > I am currently working on an app that is split into several databases with > the same table but a column with differing data type. > > eg. Database 1 > tablename: gp > column: available - data type boolean > > Database 2 > tablename: gp > column: available - data type character(1) > > I would like to be able to create a query that returns 'T' or 'F' using the > same code & query. > > I have tried a few different cast() options without success - and then > tried: > > SELECT > CASE > WHEN ((SELECT data_type FROM information_schema.columns WHERE > table_name = 'gp' and column_name = 'available') = 'boolean') THEN > CASE > WHEN (available) THEN 'T' > ELSE 'F' > END > ELSE > CASE > WHEN (available='T' OR available='t') then 'T' > ELSE 'F' > END > END as available > FROM > gp > > > When I run this query on the boolean data type database it works correctly - > however on the character field I get the error 'ERROR: argument of > CASE/WHEN must be type boolean, not type character' > > I really don't want to go through and change data types just at the moment - > can anyone advise any way around this? > > Many thanks, > Gus > >