Concatenate function in nsql - dynamic wry for display value

[Anon Anon]

Hi
I was trying to concatenate two date fields in a dynamic lookup to get a display value. Below is the query.
Select @select:c.code@,
@select:c.name@,
@select:c.start_date:start@,
@select:c.finish_date:finish@,
@select:to_char(c.start_date,'dd/mm/YY')||' '||to_char(finish_date,'dd/mm/YY'):sfdate@
From rqp_releases c where @FILTER@

When I save I get the following syntax error...
Unable to register dynamic wry........'to_char' is not a recognized built I. Function ........

Can anybody help me on this where I went wrong.......appreciate your help.

Thanks
Pmp

[Anon Anon]

Hi,

Just try this code...

Select @select:c.code@,
@select:c.name@,
@select:c.start_date:start@,
@select:c.finish_date:finish@,
@select:c.sfdate:sfdate@
From

(select code,name,start_date,finish_date, to_char(c.start_date,'dd/mm/YY')||' '||to_char(finish_date,'dd/mm/YY') sfdate from rqp_releases) c where @FILTER@


Thanks
Senthil

[mansriva]

As senthi suggested,
You can use the oracle function in the inline view ans select the data in the properties of the portlet NSQL.

Thanks,
Manish

[Deactivated User]

If it is Oracle

Select @select:c.code:code@,
@select:c.name:name@,
@select:c.start_date:start_date@,
@select:c.end_date:finish_date@,
@select:to_char(c.start_date,'dd/mm/YY')||' '||to_char(end_date,'dd/mm/YY'):sfdate@
From rqp_releases c where @FILTER@

Should work as well

Note that the field name is end_date not finish_date and code, start and finish are reserved words

Martti K.

[Anon Anon]

Thank you all for the solutions.

I tried with convert function varchar in the nsql and it works.

Thanks
PMP

How do i change the status of the thread to RESOLVED?

[System]

Hi PMP,

In order to check "Accepted Solution" you need to mark the original post as a question. I have done that and marked for you.

Regards,
Chris

[Giriraj Gupta]

I am also creating parameterized-NSQL based lookup. But here i am calling a DB function to return dynamic value and getting error. So Do you have any suggestion on how to call DB function in NSQL?

 

Code - 

SELECT
@SELECT:X.msg:msg@
FROM
(
SELECT RO_Check_PDP0_Info (@WHERE:PARAM:USER_DEF:INTEGER:PRJ_ID@) as msg from DUAL
) X
WHERE @FILTER@

 

but getting below error - 

 

Clarity PPM TEST :: Lookup: PDP0 Basic Info - Query

ErrorODF-0001: Unable to register dynamic query lookup: Could not retrieve or register the nsql::NSQL Syntax exception: NSQL_SYNTAX_ERROR_DB_ERROR, Info: [CA Clarity][SQLServer JDBC Driver][SQLServer]'RO_Check_PDP0_Info' is not a recognized built-in function name. SQL Text: SELECT X.msg msg FROM ( SELECT RO_Check_PDP0_Info (?) as msg from DUAL ) X WHERE 1=? and 1=1 .

DB Function RO_Check_PDP0_Info is valid and on DB, it's working fine.

[David Morton]

Try putting the database schema owner name on the front of the DB function - e.g. 

SELECT niku.RO_Check_PDP0_Info (@WHERE:PARAM:USER_DEF:INTEGER:PRJ_ID@) as msg from DUAL

[Giriraj Gupta]

Yes, it worked. Thanks