| Query getting 2 extra rows. Could somebody crack it please? [message #241301] |
Tue, 29 May 2007 02:46  |
ramhind
Messages: 3
Registered: May 2007
|
Junior Member |
|
|
select * from ecev;
entity_Tag
----------
7
8
9
10
1
2
3
4
5
6
SQL> select * from dg;
DIGP_TAG CEPT_TAG
---------- --------------------- ----------
600 1
700 1
800 1
900 1
100 1
200 1
500 1
75 2
SQL> select * from enow;
ENTITY_CODE ENTITY_TAG DIGP_TAG TARGET_DA CEPT_TAG
------------------------------ ---------- ---------- --------- ----------
EXT 7 600 26-MAY-07 1
EXT 8 700 26-MAY-07 1
EXT 9 800 26-MAY-07 1
EXT 10 800 22-APR-76 1
EXT 1 100 20-OCT-06 1
EXT 2 200 20-OCT-06 1
EXT 3 500 20-OCT-06 1
EXT 4 100 20-OCT-06 1
EXT 5 100 20-OCT-06 1
EXT 6 80 20-OCT-06 1
EXT 1 75 2
SQL> select * from tk;
TK_TAG DIGP_TAG NARRATIVE DUE_DATE CEPT_TAG
---------- ---------- ---------- --------- ----------
35 90 raj 27-MAY-07 1
36 900 kkd 27-MAY-07 1
37 100 kim 28-MAY-07 1
30 100 ram 23-MAY-07 1
31 100 kish 23-MAY-07 1
32 200 mar 23-MAY-07 1
33 100 gy 23-MAY-07 1
34 100 mm 27-MAY-07 1
8 rows selected.
SQL> select * from entl;
TK_TAG ENTITY_TAG
---------- ----------
35 5
36 10
30 1
31 1
32 2
33 3
34 3
For the following query
select d.ecev_tag ecev_tag, a.digp_tag entity_owner , c.digp_tag task_owner, c.tk_tag task_tag, c.narrative from enow a, entl b, tk c, ecev d where ( a.DIGP_TAG=100 or c.digp_tag=100) and a.entity_tag=b.entity_tag(+) and b.tk_tag=c.tk_tag(+) and a.entity_tag=d.entity_tag
Result:
I get
entity_tag ENTITY_OWNER TASK_OWNER TASK_TAG NARRATIVE
---------- ------------ ---------- ---------- ----------
5 100 90 35 raj
1 100 100 30 ram
1 75 100 30 ram row3
1 100 100 31 kish
1 75 100 31 kish
3 500 100 33 gy
3 500 100 34 mm
4 100
Row1: yes, since entity_tag 5 is owned by digp_tag 100 (enow)
Row2: yes, since entity_tag 1 is owned by digp_tag 100 (enow)
Row3: NO, since entity_tag 1 is owned by digp_tag 75, which in turn is in cept_tag 2, since the passed digp_tag is 100 and it just belongs to cept_tag 1 we should not be getting this row.
Row4: Yes, since tk_tag 31 is owned by digp_tag 100 (tk)
Row5 NO, since entity_tag 1 is owned by digp_tag 75, which in turn is in cept_tag 2 since the passed digp_tag is 100 and it just belongs to cept_tag 1 we should not be getting this row.
Row6: Yes, since tk_tag 33 is owned by digp_tag 100 (enow)
Row7: Yes, since tk_tag 34 is owned by digp_tag 100(enow)
Row8: Yes, since entity_tag 4 is owned by 100(enow
I guess i have to join dg.cept_tag and a.cept_tag, but it is not working.....
Can somebody understand this and help...
Thanks in advance.
Ram
|
|
|
|
|
|
|
|
|
|
| Re: Query getting 2 extra rows. Could somebody crack it please? [message #241370 is a reply to message #241323] |
Tue, 29 May 2007 06:47  |
cutsmartprem
Messages: 62
Registered: November 2006
|
Member |
|
|
Hi,
Still I do know why you need this one. You can use the below
query as a table in your select case and link it with the other tables, and hope so you can achieve what you requested.
select entity_tag,digp_tag,cept_tag from enow where
digp_tag in
(select digp_tag from enow where
(digp_tag = 100
or
digp_tag in
(select parent_digp_tag from dghi where child_digp_tag = 100
union
select parent_digp_tag from dghi where child_digp_tag in
(select parent_digp_tag from dghi where child_digp_tag = 100)
)
)
)
/
ENTITY_TAG DIGP_TAG CEPT_TAG
---------- ---------- ----------
7 600 1
8 700 1
5 100 1
4 100 1
1 100 1
Even if this does not help you, tell the unique thing in the output which represents a single row.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
