DQL - Integer as String

Dhuff

I have a saved DQL query that users can run. I've been asked if we can change how the values are displayed in the query such that the column review_cycle(integer) will show 'Critical' if the value of the integer is 12, and 'Normal' if it is not equal to 12.

Using SQL this seems like a CASE statement. How can I use DQL to achieve this?

Michael McCollough

There is not equivalent in DQL. To achieve what you are wanting changes the data type returned from int to string.

There are 2 ways I can think of to do this and get the result you want:

1. Build a SQL View and register that view as a registered table. Your view can do the select query and include the DB CASE function to return the string you want
2. If you are using Documentum Client (D2 Classic or Smart View), you can use D2-Config's display mapping to have the client UI display alternative forms of the data.

#1 sounds like what you may want as DQL is not client specific. I used similar trick (view as a registered table) to get XML output/hierarchy of some fields. You can do a lot with views as registered tables to increase what you can deliver with DQL queries.

MohamedYousuff

Assuming your custom type name is mytype where you have the integer column review_cycle , you could try DQL like below

select r_object_id, object_name, 'Critical' as review_cycle
from mytype
where
review_cycle = 12
union all
select r_object_id, object_name, 'Normal' as review_cycle
from mytype
where
review_cycle != 12

Michael McCollough

There is not equivalent in DQL. To achieve what you are wanting changes the data type returned from int to string.

There are 2 ways I can think of to do this and get the result you want:

1. Build a SQL View and register that view as a registered table. Your view can do the select query and include the DB CASE function to return the string you want
2. If you are using Documentum Client (D2 Classic or Smart View), you can use D2-Config's display mapping to have the client UI display alternative forms of the data.

#1 sounds like what you may want as DQL is not client specific. I used similar trick (view as a registered table) to get XML output/hierarchy of some fields. You can do a lot with views as registered tables to increase what you can deliver with DQL queries.

MohamedYousuff

Assuming your custom type name is mytype where you have the integer column review_cycle , you could try DQL like below

select r_object_id, object_name, 'Critical' as review_cycle
from mytype
where
review_cycle = 12
union all
select r_object_id, object_name, 'Normal' as review_cycle
from mytype
where
review_cycle != 12

Michael McCollough

That is a great answer too @MohamedYousuff