Qfyilyi

Why below sql statement keeps getting missing FROM clause entry for table error?

How to adjust this?

WITH SUBID AS (

    SELECT * FROM "B_COLLECTION"."COLL_C_RECORD"

),

TR AS (

  SELECT * FROM "B_TRACE"."PERSONAL_TC_RECORD"

)

SELECT "SUBJECT_C_ID" 

FROM "B_COLLECTION"."COLL_C_RECORD"

WHERE ( SUBID.SUBJECT_ID = TR.PERSONAL_S_ID ) 

  AND ( TR.STATE_ID ='5' OR TR.STATE_ID  = 'A' OR TR.STATE_ID = 'C');

You are declaring SUBID and TR just fine, but since these are tables, your select statement does not know them yet.

Here you need to enter SUBID and TR in FROM clauses of your query. It should look somewhat like

WITH SUBID AS (

        SELECT * FROM "B_COLLECTION"."COLL_C_RECORD"

                  ) ,

        TR AS (

                      SELECT * FROM "B_TRACE"."PERSONAL_TC_RECORD"

                  )

SELECT "SUBJECT_C_ID" FROM SUBID

WHERE SUBID."SUBJECT_ID" IN 

   (SELECT "PERSONAL_S_ID" FROM TR

    WHERE TR."STATE_ID" = '5'

    OR TR."STATE_ID" = 'A'

    OR TR."STATE_ID" = 'C');

That is why you use WITH Clause only on repeating queries. Here it would actually be much nicier to use:

SELECT "SUBJECT_ID" FROM "B_COLLECTION"."COLL_C_RECORD" SUBID

WHERE SUBID."SUBJECT_ID" IN 

  (SELECT "PERSONAL_S_ID" FROM "B_TRACE"."PERSONAL_TC_RECORD" TR

   WHERE TR."STATE_ID" = '5'

   OR TR."STATE_ID" = 'A'

   OR TR."STATE_ID" = 'C');