SQL & PL/SQL

Hi All,

When i execute the below script, im facing the invalid identifier error.

Can someone help me to get through this error.

DB Version : 12C

Create table km_master(

ID VARCHAR2(36),

ID_STATE VARCHAR2(20),

STATUS VARCHAR2(100),

ID_TYPE VARCHAR2(30))

/

Create table km_country(

COUNTRY_ID VARCHAR2(36),

COUNTRY_ID_STATE VARCHAR2(20),

ID VARCHAR2(100),

ID_STATE VARCHAR2(20))

/

Create table km_task(

TASK_ID VARCHAR2(36),

TASK_TYPE VARCHAR2(100),

TASK_NAME VARCHAR2(100),

TASK_STATUS VARCHAR2(100),

ITEM_CEATE_DATE DATE)

/

Script:-

SELECT COUNT(1)

FROM km_master km

WHERE km.id_state='ACTIVE'

AND km.status NOT IN ('APPR','PENDING')

AND KM.id_type ='GFCID'

AND EXISTS(SELECT 1

FROM km_task gt1

WHERE gt1.task_type IN ('REVIEW', 'PERREVIEW', 'CLIENT')

AND gt1.task_id = km.id)

AND EXISTS

(SELECT 1 FROM

(SELECT rank() over(partition BY task\_id order by ITEM\_CREATE\_DATE DESC) rnk,

  gt1.item\_status,

  task\_id,

  task\_type,

  gt1.task\_name

FROM km\_task gt1

WHERE gt1.task\_type IN ('REVIEW', 'PERREVIEW', 'CLIENT')

AND gt1.task\_id      =   km.id

AND gt1.task\_name NOT   IN( 'SUB','R\_SUB','NAME\_SAPR'))

where rnk =1

AND item_status NOT IN ('In_Progress','Refred')

AND task_id = km.id)

AND NOT EXISTS

(SELECT 1 FROM

(SELECT rank()over(partition BY ca.id order by ITEM\_CREATE\_DATE DESC) rnk ,

  gt1.item\_status,

  ca.id ,

  task\_type ,

  gt1.task\_name

FROM km\_task gt1,

  km\_country ca

WHERE gt1.task\_type  IN ('PEREVIEW','PERPX','CLIENT')

AND gt1.task\_name NOT    IN ( 'SUB','R\_SUB','NAME\_SAPR')

AND item\_status      IN ('In\_Progress','Refred')

AND task\_id           =ca.country\_id

AND ca.id\_state='ACTIVE')

WHERE rnk=1

AND id = km.id);

ERROR at line 19:

ORA-00904: "KM"."ID": invalid identifier

Screenshot:-

Please help on this to get it corrected and executed.

Regards,

Lazar T