Matching Across Multiple Columns

Toad Data Point & Toad Intelligence Central
1

Hi there,

First off, I’m new here, and I did search to see if someone has asked this question before, but I didn’t find any - so if I missed it, I am sorry.

I just started using Toad the other day after discovering my company had a license - but no one had thought to tell us - go figure.

Anyway.

I’ve been trying to figure out how to do something for a while now, and I’m wondering if Toad can help me.

I have a table in an Oracle database where there is a need to be able to match a value across multiple columns, each of which is capable of storing the same value. I know this probably isn’t the best implementation, but it’s not changeable.

Previously the only way I’ve been able to locate records with a specific value in one of these 20 columns has been my running my query 20 separate times, just changing the field name each time - with Toad, I realize I could automate that task, but that’s still not ideal.

So my question is this - is there anyway to build a query in such a way that I can supply the value that I’m looking for, and it will attempt to match it across this range of 20 possible locations within the record?

I apologize if this question is somewhat confusing; I’ll be happy to clarify if needed.

2

Suppose I have table MYTABLE with columns COL1, COL2, COL3, COL4.

I am looking for rows that have ‘ABC’. The value ‘ABC’ can be in COL1 or in COL3.

My SQL could be:

select * from mytable where col1 = ‘ABC’ or col3 = ‘ABC’ ;

or it could be

select * from mytable where ‘ABC’ in (col1, col3) ;

You should be able to build either of those statements in Toad (I assume you mean Toad for Data Analysis, not Toad for Oracle).

3

That’s exactly the situation I’m in, though it’s more like COL1 - COL20 and ‘ABC’ could be anywhere in COL1 through COL20.

Hmmm, previously I’d tried using OR with SQL*Tools and it would make me redo the entire select - it apparently interpreted the OR to be the end of the SQL statement, and I had to restate the rest of my criteria all over again.

I haven’t tried the second solution, but it’s definitely worth a shot. Can you tell me how I would set that up in Toad for Data Analysts, or would it just be better for me to manually key it in to the select?

And yes, I’m mostly using Toad for Data Analysts, but I do have Toad for Oracle available to me as well.

4

TEST_R.COLUMN_2,

TEST_R.COLUMN_3,

TEST_R.COLUMN_4,

TEST_R.COLUMN_5,

TEST_R.COLUMN_6,

TEST_R.COLUMN_7

WHERE (TEST_R.COLUMN_1 = :a)

OR (TEST_R.COLUMN_3 = :a)

OR (TEST_R.COLUMN_5 = :a)

OR (TEST_R.COLUMN_7 = :a)

I tried what you and Jacques were talking about in the Query Builder. I added all columns and used a bind var on the OR line. The query looks like this below. Now when I execute it I only have to enter the new value I am looking for.
This can be saved and resued. Also, in version 2.5 of TDA you will be able to use bind vars in automation and also send an email when rows are found.

Debbie
SELECT TEST_R.COLUMN_1,FROM QUEST_STAGE.TEST_R TEST_ROR (TEST_R.COLUMN_2 = :a)OR (TEST_R.COLUMN_4 = :a)OR (TEST_R.COLUMN_6 = :a)

BindVar.jpeg
BindVar.jpeg1048×745 131 KB