Problems to set the right SQL code to appear the right values in a table column

Problems to set the right SQL code to appear the right values in a table column

  

Hi


I have a table in the front end which has a column called Situation. That column has an expression connected to a parameter of a structure AND the expression has the following value

SyntaxEditor Code Snippet

TableRecords1.List.Current.XXXXX.Situation


situation is a parameter set in the structure, and its data type is TEXT as we want text in that column: activo OR inactivo.


To feed the table we have a SQL block which makes an union of several entities. 


SyntaxEditor Code Snippet

SELECT (several attributes), table_x.Situation
FROM table_x
left join table_y on (condition)
left join table_a on (condition)
where (some attribute) = @User

UNION

SELECT (several attributes), table_w.Situation
FROM table_w
left join table_v on (condition)
left join table_a on (condition)
where (some attribute) = @User

UNION

SELECT (several attributes), table_t.Situation
FROM table_t
left join table_s on (condition)
left join table_a on (condition)
where (some attribute) = @User


this collects the data from the DATABASE where situation has the BIT as data type (0 for inactivo, and 1 for activo). 

When we publish the eSpace and check the webscreen where the table is present.... the column SITUATION is always empty. I need to map correctly from the bit data type (from the database) to a text data type (to the table in the front end) but never did this in SQL. How can we put the Situation column filled with activo when the bit=1 and inactivo when the bit=0?  Any suggestions?


I have tried with 

case table_x.Situation 
 when 0 then 'inactivo' 
 else 'activo'
end

after the where clause, and using the same way for the other two (after) where clauses... but it does not work

case table_w.Situation
 when 0 then 'inactivo'
 else 'activo'
end

and so on...

i'll try in the select clause..


the error shown is attached as jpg file.

Solution

Hi Jorge,

The sql statement with the case seems to work but I think you have to put it after the select when you declare the atributtes.

Example:

SELECT

 ProductNumber,  

Category = CASE ProductLine 

WHEN 'R' THEN 'Road' 

WHEN 'M' THEN 'Mountain' 

WHEN 'T' THEN 'Touring' 

WHEN 'S' THEN 'Other sale items' ELSE 'Not for sale' END,

 Name 

FROM table

Regards,

Rita

Solution

Hi Jorge,

How are you?

The correct place, in this situation, is to use the CASE clause in the SELECT clause.

SELECT ..., (CASE WHEN table_w.situation = 1THEN 'inactivo' ELSE 'activo' END), ...

FROM ...

You are creating a new field instead using the original.

Remember to set the field in the output structure.

Hope this helps. 

Cheers

Eduardo Jauch

Hi RITA and Eduardo


I am glad to see you both here. :)  Many thanks for your input. It seems it worked. :) Cândido was also a great help. Many thanks to both you three. 


Cheers

Jorge