DQL - Integer as String

Dhuff
Dhuff Member
in Documentum #1

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?

Tagged:

Best Answers

  • Michael McCollough
    Michael McCollough E Community Moderator
    edited July 10 #2 Answer ✓

    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
    MohamedYousuff Member
    #3 Answer ✓

    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

Answers

  • Michael McCollough
    Michael McCollough E Community Moderator
    edited July 10 #4 Answer ✓

    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
    MohamedYousuff Member
    #5 Answer ✓

    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
    Michael McCollough E Community Moderator
    #6

    That is a great answer too @MohamedYousuff

Categories