|
View previous topic :: View next topic
|
| Author |
Message |
bandi
New User
Joined: 21 Jul 2005
Posts: 12
|
|
|
|
I need to find all view names which start with P_ (P and an underscore symbol). So, I wrote this query....
| Code: |
SELECT * FROM SYSIBM.SYSTABLES
WHERE NAME LIKE 'P_%'
FETCH FIRST 10 ROWS ONLY |
| Code: |
NAME
---------+---------+---------+---------+---------+----
PLAN_TABLE
PLAN_TABLE
POL
PROD
PROMO
P_CODE_TRANSLATION
P_HASH_TOTAL
PLAN_TABLE
PAB_DESC
PAPEROFF#T |
clearly not what I was looking for... right?
So, what is wrong with this SQL? Can you correct it to satisfy my requirement ? |
|
| Back to top |
|
 |
MFRASHEED
Active User
Joined: 14 Jun 2005
Posts: 186
Location: USA
|
|
|
|
Try adding one more condition in Where clause
AND TYPE = 'V' |
|
| Back to top |
|
|
bandi
New User
Joined: 21 Jul 2005
Posts: 12
|
|
|
|
Hi ,
Could you please let me know give me correct sql still I'm not getting currect result? |
|
| Back to top |
|
|
mahi
New User
.jpg)
Joined: 04 Apr 2006
Posts: 86
Location: Hyderabad
|
|
|
|
Hi bandi,
'_' represents any one single charector and '%' represents group of charectors.
So as per your query after P there must be one charector.
I think it will help you. |
|
| Back to top |
|
|
meetreks
New User
Joined: 28 Sep 2006
Posts: 26
Location: Edinburgh
|
|
|
|
Hi,
in the SYSIBM tables, a table is represented as type 'T' for table, Type 'V' for view and Type 'A' for alias. So either qualify your view with Type = V or you will get the same result as before.
The corresponding SQL is
| Code: |
SELECT * FROM SYSIBM.SYSTABLES
WHERE NAME LIKE 'P_%' AND
TYPE = 'V'
FETCH FIRST 10 ROWS ONLY |
Hope this helps. |
|
| Back to top |
|
|
mahi
New User
Joined: 04 Apr 2006
Posts: 86
Location: Hyderabad
|
|
|
|
you are correct ram. Here the problem is if run the above query we will get names like : PLAN_TABLE, PLANTABLE, etc.
But his requirement is to get the Tables like P_TABLE. The table name should start with ' P_'. first char must be P and second char must be '_'
Is thr any way to get this type of info |
|
| Back to top |
|
|
meetreks
New User
Joined: 28 Sep 2006
Posts: 26
Location: Edinburgh
|
|
|
|
There are two ways I can think of.
1. Qualify the table with the full Schema
2. Use WHERE name not in () and give all the system table names:)) |
|
| Back to top |
|
|
bonniem
New User
Joined: 09 Aug 2005
Posts: 67
|
|
|
|
Why not code where clause as
WHERE SUBSTR(NAME,1,2)='P_' |
|
| Back to top |
|
|
bonniem
New User
Joined: 09 Aug 2005
Posts: 67
|
|
|
|
| hi i thought i will explain what is wrong with like 'P_%'. Db2 replaces '_' with any character. So 'P_%' resolves to 'P%'. That is why results are not correct. |
|
| Back to top |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL
|
|
|
|
Bandi...
Thats where you code ESCAPE Clause to give their literal meaning to "%" (percentage) and "_" (underscore) signs.
So in your case, you would like to go something like this...
| Code: |
SELECT * FROM SYSIBM.SYSTABLES
WHERE NAME LIKE 'P+_%' ESCAPE '+';
FETCH FIRST 10 ROWS ONLY; |
See here for a complete reference... |
|
| Back to top |
|
|
|
|
