How to fix whereclause issue in Database view table

[chanikya]

Hi All,

We are facing some issue with wherecluase in database view table.
Table not showing any data. kindly let me know what might be the issue over there

on Task record we have field "QA instance"(reference : Assessment Instance). below where clause tried but no data showing .

100 : amr2.source_id='6cxxxx5932016exxxxxxxxxxxx'

150 : ag2.sys_id=amr2.assessment_group

200 :ai2.assessment_group=ag2.sys_id

300: cst2.u_qa_instancelSNOTEMPTY&&cst2.u_qa_instance=ai2.sys_id

300 : if I take condition like : cst2.parent=ai2.task  -----data showing, but wrong data showing

but we want to compare TASK record QA Instance and Assessment instance table record too.

[palanikumar]

Try two things:

1) Remove the first condition. You may get big list but I found the query on first table breaks sometime

2) If the above on does not work the try replacing dot with underscore like below and see whether it works. I some time face issue if I use dot in the query

100 : amr2_source_id='6cxxxx5932016exxxxxxxxxxxx'

150 : ag2_sys_id=amr2_assessment_group

200 :ai2_assessment_group=ag2_sys_id

300: cst2_u_qa_instancelSNOTEMPTY&&cst2_u_qa_instance=ai2_sys_id

[chanikya]

tried with Underscore, no use, still not working.

any other solutions please

[RafaeldaSP]

Hello @chanikya ,

Hello @chanikya ,
The issue is likely with your line 300 condition:cst2.u_qa_instancelSNOTEMPTY && cst2.u_qa_instance=ai2.sys_id

Replace IS NOT EMPTY with standard SQL:cst2.u_qa_instance IS NOT NULL AND cst2.u_qa_instance = ai2.sys_id

Make sure u_qa_instance actually contains the sys_id of Assessment Instance.
Test the join step by step: first join Task and Assessment Instance on u_qa_instance = ai2.sys_id to confirm data exists.
Database views fail to return data often due to incorrect join or reference comparisons. Fix the condition and verify the reference values match exactly.

[chanikya]

Thanks for sharing your inputs, let me implement as per above.