• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

Query does not return rows

Happy new year to you!

I am having a strange problem today, which I can hardly understand! Thus I wanted to ask you!

I have a very simple query, which used to work as far as I remember. Now, however, the query doesn't return any rows at all. Can you please tell me what is wrong? CNT_GEBIET is a string just like lng_gebiet. Both are varchar2.

I simply need to output all records of table tbl_gebiet, which are non existent in tbl_arbeit_zu_gebiet.

I appreciate your help and am very curious if you have any ideas!

BR,
skahlert2010


Select distinct cnt_gebiet from VT_TBL_GEBIET where cnt_gebiet not in (Select distinct lng_gebiet from VT_TBL_ARBEIT_ZU_GEBIET)

Open in new window

0
skahlert2010
Asked:
skahlert2010
1 Solution
 
skahlert2010Author Commented:
Okay, I just found out that the query did not return any results as I had null values for lng_gebiet in tbl_arbeit_zu_gebiet. I think it should work nevertheless!?
0
 
zadeveloperCommented:
The syntax looks good to me,
you could try a different approach:

Select 
	distinct t1.cnt_gebiet 
from 
	VT_TBL_GEBIET t1
	left outer join lng_gebiet t2 on t1.lng_gebiet = t2.lng_gebiet
where 
	t2.lng_gebiet is null

Open in new window

0
 
Reza RadCommented:
maybe you have all records in vt_tbl_arbeit_zu_gebiet and then query result no rows.
did you checked your table data? do you have any opposite sample ?
I mean do you have any rows in VT_tbl_gebiet that his cnt_gebiet has no correspond value to lng_gebiet in vt_tbl_arbeit_zu_gebiet ??
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
tigin44Commented:
your query should work. For perfornance reason you should use a structure like this.
If the query dont return any rows then all the cnt_gebiet values have corresponding value in the lng_gebiet column.
SELECT distinct cnt_gebiet 
FROM VT_TBL_GEBIET T1
WHERE NOT EXISTS (	SELECT NULL 
					FROM VT_TBL_ARBEIT_ZU_GEBIET T2 
					WHERE T1.cnt_gebiet = T2.lng_gebiet)

Open in new window

0
 
skahlert2010Author Commented:
Thanks for your rapid help!

I took tigin44's proposal and modified it to suit my needs. It works indeed a little faster! Splendid!

Thanks a lot!

@ Zadeveloper: Your query was not quite correct, but it works correctly now. It's just not as performant as the other one.

@reza_rad: Yeah, I checked my tables. TBL_GEBIET contains all regions. The only value in cnt_gebiet that is missing in lng_gbeite is null!
Select 
        distinct t1.cnt_gebiet 
from 
        VT_TBL_GEBIET t1
        left outer join tbl_arbeit_zu_gebiet t2 on t1.cnt_gebiet = t2.lng_gebiet
where 
        t2.lng_gebiet is null 

Open in new window

0
 
skahlert2010Author Commented:
Thanks!
0
 
zadeveloperCommented:
Glad you got it working.
0
 
skahlert2010Author Commented:
@ zadeveloper

I feel a little sorry. Maybe I can award you on another question! Have a great day!
0
 
zadeveloperCommented:
No problems man....
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now