NageswaraRao .K NageswaraRao .K - 4 years ago 115
SQL Question

SQL select is query is not working properly

I am unable to apply a filter(where condition) on the query

SELECT A.Form_Id,
B.CONTAINER_ID,
A.FORM_DESC,
A.FORM_TITLE,
A.LAYOUT,
A.TOTAL_COLUMNS,
COUNT (*) Over () AS Total_Rows
ROW_NUMBER () OVER ( ORDER BY CONTAINER_ID ASC ) ROWNM
FROM FORM_DEFINITION A
LEFT JOIN
(SELECT CONTAINER_ID,FORM_ID FROM FORM_CONTAINER_DEFINITION
) B
ON A.FORM_ID = B.FORM_ID
AND ( ( UPPER(TRIM(A.FORM_ID)) LIKE '%'
|| UPPER(TRIM('FORM2'))
||'%' ) )


In the above code I applied filter like this

( ( UPPER(TRIM(A.FORM_ID)) LIKE '%'
|| UPPER(TRIM('FORM2'))
||'%' ) )


Except this part the query is giving all the info. This filter should show only 'FORM2' row.

But it is showing all the rows as normaly.
.
.
Could you resolve my issue....
.
.
Thanks in advance. :)

Answer Source

Conditions on the first table in a LEFT JOIN need to go in the WHERE clause. On the second table, in the ON clause. Also, the subquery is not necessary. So:

SELECT . ..
FROM FORM_DEFINITION A LEFT JOIN
     FORM_CONTAINER_DEFINITION B
     ON A.FORM_ID = B.FORM_ID
WHERE UPPER(TRIM(A.FORM_ID)) LIKE '%' || UPPER(TRIM('FORM2')) || '%';

The logic is actually simpler than the above rule. A LEFT JOIN keeps all rows in the first table, regardless of the condition in the ON clause. Matching rows get the values from the second table. Non-matching rows get NULL values.

This is true even when the condition is on the first table.

Also, I would encourage you to use sensible aliases for tables rather than A and B. I would suggest FD and FCD for these two tables.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download