Home » SQL & PL/SQL » SQL & PL/SQL » Query getting 2 extra rows. Could somebody crack it please?
Query getting 2 extra rows. Could somebody crack it please? [message #241301] Tue, 29 May 2007 02:46 Go to next message
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 #241308 is a reply to message #241301] Tue, 29 May 2007 03:38 Go to previous messageGo to next message
cutsmartprem
Messages: 62
Registered: November 2006
Member
Hi,
The query you have written does not have the correct join condition and your not clear of what is your final expectation is. Can you explain that well. I will try to help you out. If your joining all the four tables, there should be perfect join conditions.
Re: Query getting 2 extra rows. Could somebody crack it please? [message #241309 is a reply to message #241301] Tue, 29 May 2007 03:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and apply How to format your posts
Remove all useless blank lines.
Post your Oracle version.

Regards
Michel
Re: Query getting 2 extra rows. Could somebody crack it please? [message #241323 is a reply to message #241308] Tue, 29 May 2007 04:32 Go to previous messageGo to next message
ramhind
Messages: 3
Registered: May 2007
Junior Member
Hi Folks,

I have attached the file with the scenarios.

Please let me know if i am not clear...

Thanks a ton,
Ram
  • Attachment: scenario.txt
    (Size: 3.38KB, Downloaded 127 times)
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 Go to previous messageGo to next message
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.
Re: Query getting 2 extra rows. Could somebody crack it please? [message #241372 is a reply to message #241370] Tue, 29 May 2007 06:49 Go to previous message
ramhind
Messages: 3
Registered: May 2007
Junior Member
thanks for your help.

unique thing would be the "task_tag"

[Updated on: Tue, 29 May 2007 06:50]

Report message to a moderator

Previous Topic: How to associate a user-defined exception with a message (merged)
Next Topic: I need a query
Goto Forum:
  


Current Time: Fri Dec 09 02:24:20 CST 2016

Total time taken to generate the page: 0.10960 seconds