Query question 4

[Zargo]

Hi all,

SELECT * FROM XX_TABLEA WHERE T1 LIKE (SELECT T1 FROM XX_TABLEB WHERE USER_NAME = 'ZARGO')

The tables are look like:

XX_TABLEA
T1
1100023
1012132
2012121

And the other table:

XX_TABLEB
T1 USER_NAME
10% ZARGO
11% ZARGO
20% OTHER

I want to get these results:

1100023
1012132

The query at the top doesn't work, does anybody know what im doing wrong? How to write this query to get the right results

Any Idea is appreciated?

 

[Dagon]

Does the table literally contain '10%'. If so, Oracle will actually try to match the '%' character as a literal rather than treating it as a wildcard. You would be better off removing it from the table and putting it into your SQL, then doing:

SELECT * FROM XX_TABLEA WHERE T1 LIKE (SELECT T1||'%' FROM XX_TABLEB WHERE USER_NAME = 'ZARGO')

 

[SantaMufasa]

Zargo,

I'll translate into human terms what your code requests:

1) Read XX_TABLEB and gather the percentages from the T1 column if the USER_NAME = 'ZARGO'. This produces this result set: (10%, 11%)

2) Read XX_TABLEA and display all the columns if the seven-digit number in the T1 column matches either "10%" or "11%".

Since none of "1100023", "1012132", or "2012121" match "10%" or "11%", you should see "no rows selected" as your result.

Let us know if this does not match your understanding.

Also, let us know the logic that you really wanted, and we can help you construct that.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Zargo]

Dagon and Mufasa, thanks so many for your reply. Dagon you understand me correctly, the table literally contains '10%' and i want to use the percent sign as a wildcard. When i use your statement i will get the following error message:

ORA-01427: Single-row subquery returns more than one row.

Do you have any idea to solve this?

Thanks a lot...

 

[SantaMufasa]

Zargo,

Sorry that I misinterpretted your question in my earlier reply.

I believe that the tightest and most efficient code to do what you want is:

SELECT * FROM XX_TABLEA X
 WHERE EXISTS (SELECT NULL FROM XX_TABLEB y
                WHERE INSTR(x.t1,y.t1) = 1
                  AND USER_NAME = 'ZARGO')
/

   1100023
   1012132

2 rows selected.

Let us know if you have questions about the code.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

BTW, my code, above, depends on there being no "%" in your data. To get rid of all of the extraneous "%"s, you can say:

update XX_TABLEB
   set t1 = replace(t1,'%',null)
 where instr(t1,'%') > 0;

Let us know your outcome.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Zargo]

SantaMufasa thanks for your reply. My table is very huge, and in this example i can use WHERE INSTR(x.t1,y.t1) = 1 but in the live situation i would like to use the data in the table with the wilcards. Do you know another way to get the right result. To make it more difficult in my application i could only write code after the static part "SELECT * FROM XX_TABLEA WHERE T1"

so i can say

SELECT * FROM XX_TABLEA WHERE T1 in (select blabala
or
SELECT * FROM XX_TABLEA WHERE T1 like (select blabala
and so on...

Do you know another way to get the right results?

 

[Dagon]

If you have a restriction like that, then you can always work around it by doing something like:

SELECT * FROM XX_TABLEA X
 WHERE T1 = T1 
   AND EXISTS (SELECT NULL FROM XX_TABLEB y
                WHERE INSTR(x.t1,y.t1) = 1
                  AND USER_NAME = 'ZARGO')
/

It's a bit silly, but a silly restriction deserves a silly workaround.

 

[Zargo]

Hi Dagon thanks for your reply. THe table is very huge. What if i have the following content:

XX_TABLEB
T1 USER_NAME
10% ZARGO
11% ZARGO
20% OTHER
53% ZARGO
8776% ZARGO

I want to build the where clause more flexible then INSTR(x.t1,y.t1) = 1, how to write our statement?

Thanks a lot for your contribution

 

[Dagon]

What do you mean by "more flexible then INSTR(x.t1,y.t1) = 1" ? What do you want to do that is more difficult than this ?

 

[Zargo]

Sorry i had not test the solution yet. But it worksss thanks a lot both. A big star again..