create column based on conditions

Question

So my goal here is to see if the certain conditions apply to data I have

I have this query:

select colA, colB
from tableA
where colB in ('catA','catB')
AND
colA in ('value1', 'value2', ..., 'value999') 
or colA in ('value1000', ..., 'value1999') 
or colA in ('value1000', ..., 'value1999') 
or colA in ('value1000', ..., 'value1999')

The reason I have all the OR clauses is because I have so much data and can only do 1000 items in a list at a time.

So im trying to solve two problems here.

A. The query above doesn't work...it says

ORA-00936: missing expression

It works with just the first part of the list so I know something is wrong with the OR clause.

B. How do I modify the select statement to get a new Yes/No column if this condition is met:

where colB in ('catA','catB')

I get a Yes, otherwise a No?

Here's a work around to the limitation: http://stackoverflow.com/a/9084247/1073631 -- or use the join option. In regards to your 2nd question, looking at your query, wouldn't that always be true? Or are you doing `colb in () or cola in ()`? In which case, look at using a `case` statement to create your temp column: `case when colb in ('cata','catb') then 'yes' else 'no' end as newcolumn` — sgeddes, Jan 23 '16 at 03:36
The data isn't in another table though, it's all manually pasted in. For the second question, basically I need to only check tableA for the records im pasting and then for those records, if it matches said condition, output Yes, otherwise No — john cs, Jan 23 '16 at 03:44
I would recommend to re-design your table(s), so that you will not need to specify 500+ values in your where statement, because it's a performance killer - every time your SQL (actually it's MD5 hash) is changed Oracle has to parse a new query and cost-based optimizer has to look for a new execution plan. It's too expensive! It would be easier though to give you more detailed recommendations if you would give us more details about what exactly do you want to achieve... — MaxU - stand with Ukraine, Jan 23 '16 at 11:33
Yeah I realize this is not ideal situation but this is a one time only query — john cs, Jan 23 '16 at 12:15

score 1 · Answer 1 · answered Jan 25 '16 at 03:27

Even though this is a one-time query, this is a really bad idea. Oracle has the 1,000 "in list" limitation for really good reasons. Even a single in-list with 1,000 items takes a while just to compile. If you contrast this with a simple join, the compilation time is next to nothing, and the execution time is no more than it would be with your in list.

This is also a much more courteous approach to others on the database, as you are not hogging the shared pool with thousands of literals.

Even if this is a one-time thing, I'd recommend you create a table (or a GTT), load your values and do this as a simple join:

create table temporary_values (
  in_list_string varchar2(1000) not null,
  constraint temporary_values_pk primary key (in_list_string)
);

Use a tool like SQL*Loader to load your values, or do it the old-fashioned way. Then run your query as this:

select A.colA, A.colB
from
  tableA A
  join temporary_values t on
    A.colA = t.in_list_string
where
  A.colB in ('catA', 'catB');

If the primary key is a pain, you can always drop it and do your query as a semi-join to avoid duplicates:

select A.colA, A.colB
from
  tableA A
where
  A.colB in ('catA', 'catB') and
  exists (
    select null
    from temporary_values t
    where A.colA = t.in_list_string
  );

And if your and ever becomes an or you can resolve that by using a left join:

select A.colA, A.colB
from
  tableA A
  left join temporary_values t on
    A.colA = t.in_list_string
where
  t.in_list_string is not null or
  A.colB in ('catA', 'catB');

If there are any compilation or execution errors, they will be a lot easier to find than searching for a missing comma or misplaced quote in thousands of literals.

create column based on conditions

1 Answers1