CODEWITHSUNDEEP
sqloracle11g
PowerUp
PowerUp

Reputation: 55

SQL Filter by Excluding Specific String

I am looking to run my query (below) by displaying latest value for "DATA_POINT_UPLOAD_DATA"."VALUE" , except 'READY'. Currently, it displays all 'READY' values, however, I want to do the opposite by displaying any values up to the time of execution except 'READY'.

Here is my current query:

select  "DATA_POINT_UPLOAD_DATA"."LAST_UPDATED_TIMESTAMP" as "TIMESTAMP",
               "DATA_POINT_UPLOAD_DATA"."VALUE" as "COMMENTS"
 from   "DB"."COMPONENT" "COMPONENT",
    "DB"."COMPONENT_DATA_POINT" "COMPONENT_DATA_POINT",
    "DB"."DATA_POINT_UPLOAD_DATA" "DATA_POINT_UPLOAD_DATA" 
 where   "COMPONENT_DATA_POINT"."ID"="DATA_POINT_UPLOAD_DATA"."COMPONENT_DATA_POINT_ID"
 and     "COMPONENT"."ID"="COMPONENT_DATA_POINT"."COMPONENT_ID"
  and    "DATA_POINT_UPLOAD_DATA"."VALUE" ='READY' 
   and   "DATA_POINT_UPLOAD_DATA"."LAST_UPDATED_TIMESTAMP" between ('01-JUN-17') and ('30-JUN-17') 
   and   "COMPONENT_DATA_POINT"."NAME" ='StateOfItem' 
   and   "COMPONENT"."SITE_ID" in('abc123');

Any help would be greatly appreciated.

Upvotes: 0

Views: 3077

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

You are asking for the latest record per VALUE now. You are only selecting VALUE and LAST_UPDATED_TIMESTAMP, however. So what you are asking is merely the maximum LAST_UPDATED_TIMESTAMP per VALUE. In SQL this translates to MAX(last_updated_timestamp) with GROUP BY value.

Select  
  max(last_updated_timestamp) as "timestamp",
  value as comments
From db.data_point_upload_data
Where value <> 'READY' 
and last_updated_timestamp between '2017-06-01' and '2017-06-30'
and cdp_id in
(
  select id
  from db.component_data_point
  where name = 'StateOfItem'
  and component_id in (select id from db.component where site_id = 'abc123')
)
Group by value;

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

Sorry to say, but that is a horrible query. Almost only upper case so as to minimize readability, table alias names that are no alias names, a join syntax that was made redundant twentyfive years ago, date string literals that only work in certain language settings, and unnecessary joins.

Then you select records with value = 'READY' and say that you want records that are not 'READY'. Well, then: WHERE NOT value = 'READY' or simply WHERE value <> 'READY'.

Here is the altered query:

Select  
  last_updated_timestamp as "timestamp",
  value as comments
From db.data_point_upload_data
Where value <> 'READY' 
and last_updated_timestamp between '2017-06-01' and '2017-06-30'
and cdp_id in
(
  select id
  from db.component_data_point
  where name = 'StateOfItem'
  and component_id in (select id from db.component where site_id = 'abc123')
);

If you only want to see the latest n rows, then order by last_updated_timestamp desc limit <n>.

Upvotes: 0

MagdielAybar
MagdielAybar

Reputation: 187

In your WHERE clause you have this: "DATA_POINT_UPLOAD_DATA"."VALUE" ='READY'. That means you want to display the rows where DATA_POINT_UPLOAD_DATA has the value 'READY'.

Change your query and instead of using = try using != or <>.

SELECT "DATA_POINT_UPLOAD_DATA"."LAST_UPDATED_TIMESTAMP" AS "TIMESTAMP",
  "DATA_POINT_UPLOAD_DATA"."VALUE"                       AS "COMMENTS"
FROM "DB"."COMPONENT" "COMPONENT",
  "DB"."COMPONENT_DATA_POINT" "COMPONENT_DATA_POINT",
  "DB"."DATA_POINT_UPLOAD_DATA" "DATA_POINT_UPLOAD_DATA"
WHERE "COMPONENT_DATA_POINT"."ID"    ="DATA_POINT_UPLOAD_DATA"."COMPONENT_DATA_POINT_ID"
AND "COMPONENT"."ID"                 ="COMPONENT_DATA_POINT"."COMPONENT_ID"
AND "DATA_POINT_UPLOAD_DATA"."VALUE" !='READY'
AND "DATA_POINT_UPLOAD_DATA"."LAST_UPDATED_TIMESTAMP" BETWEEN ('01-JUN-17') AND ('30-JUN-17')
AND "COMPONENT_DATA_POINT"."NAME" ='StateOfItem'
AND "COMPONENT"."SITE_ID"        IN('abc123');

Upvotes: 1

Related Questions