nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

DQL query error while comparing coulmns number to string

VenkatKrishna

I have a registred table with coulmn acct_id_n as number data type in table

dm_dbo.v_fmtc_auth_usr_acct_cntnt

and I have custom object type and related column

f_sys_cntxt_id_n with varchar type.when I compare two columns in a DQL script it throws an error saying invalid number.

SELECT DISTINCT c.r_object_id,c.object_name FROM CWSStdRpt (all) c, dm_dbo.v_fmtc_auth_usr_acct_cntnt d WHERE UPPER(c.a_status) LIKE 'PUBLISH' AND ANY c.f_sys_cntxt_id_n = d.acct_id_n

Error occured during query execution :[DM_QUERY_E_CURSOR_ERROR]error: "A database error has occurred during the creation of a cursor ('ORA-01722: invalid number
')."

with out using the SUBSTR(d.acct_id_n) is there a way to execute the DQL query.

Find more posts tagged with

Note:

If there are Accepted Answers, those will be shown by default. You can switch to 'All Replies' by selecting the tab below.

Button

Accepted answers

DCTM_Guru

If I were you, I would create another column in the registered table to store acct number as string, so then you can use DQL to compare using same data type.

All comments

AlanZ

Your question has been moved to the Documentum Developer community so it can be seen by those who might answer.

DCTM_Guru

Do you get the same error if you dont include SUBSTR(d.acct_id_n)?

VenkatKrishna

If I remove SUBSTR(d.acct_id_n) then I get the error invalid number.If I include SUBSTR(d,acct_id_n) then I am not getting the error.

F_UI_CNTXT_ID_C VARCHAR2

ACCT_ID_N NUMBER

DCTM_Guru

DQL does not behave like SQL, so sometimes if you want to use SQL functions, you need to execute as SQL.

VenkatKrishna

I undestand that DQL doesn't behave like SQL,but could you please let me know can I compare two different data type attributes in a DQL without using SUBSTR() function.

f_ui_cntxt_id_c CHAR(15) REPEATING (Custom Object type attribute)

acct_id_n Number ( Which is an registered table column)

DCTM_Guru

If I were you, I would create another column in the registered table to store acct number as string, so then you can use DQL to compare using same data type.

VenkatKrishna

Thank you very much for the information.I will use SQL.