Null Values and Correlated Subqueries - General SQL Database Question & Answer

Member of the LearnItFirst.com Video Training Network	\| LearnSqlServer.com \| SQL SSIS Training \| SQL Programming Tutorials \|

Welcome Guest

Search | New Posts | Members | Log In | Register

LearnSqlServer.com's SQL Server Forums » SQL Server Database Administration » General SQL Database Question & Answer » Null Values and Correlated Subqueries

Null Values and Correlated Subqueries

Options

Previous Topic · Next Topic

marygrl2327

Posted: Saturday, June 14, 2008 3:03:24 AM

Rank: Newbie

Joined: 6/13/2008
Posts: 7
Points: 21
Location: TX

Hey Scott,
I was in the class you taught this week(great job btw). So, after being released from class, I started revamping a query I have been working on for the last week and a half. Basically, I had two temp tables that would retrieve my data(because when I started SQL I was taught that temp tables are better to work with than perm tables). Anyway, I decided to chunk those temp tables and use correlated queries in my select statements to get the data I retrieved. And I will say that the cost is much more efficient. Using the 2 temp tables would run about 12 min, while using the correlated queries would only run about 2 min.

My question is, is there anyway I can retrieve only rows that have data from the correlated query instead of seeing rows with data and rows with null values?

Here's the basic concept of my code:

select s.site_no,
sp.permit_no,
p.permit_eff_date,
(select max(ii.start_date)
from incident_issue ii with (nolock), issue_employee ie with (nolock)
where ii.incident_no = ie.incident_no
and ie.iscomp_id = 'PMTLTR'
and ii.start_date > '3/31/08') as letter_date
from system s with (nolock)
join site_permit sp with (nolock) on sp.site_no = s.site_no
join permit p with (nolock) on p.permit_no = sp.permit_no
where sp.agencytype_id = 'p'
and not exists (select 'x' from system_status ss with (nolock)
where s.system_no =ss.system_no
and ss.oos_end_date > getdate())

Now that I'm thinking about it, would it be better to use an exists clause instead of a correlated subquery for the letter_date(that's the value I want returned with no nulls)?

Thanks for your input.
And thanks again for sharing your knowledge. I'm excited with what I've learned and can't wait to see what I can do with this newfound knowledge.

Mary

What's with today, today?

Scott Whigham

Posted: Monday, June 23, 2008 5:01:10 PM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

Hey Mary

Glad you liked class and I'm glad to see that you are applying it Smile

But hold up - this query is funky. How could you do an EXISTS clause and a correlated subquery to calculate the "letter_date"? That column is a simple subquery so what would you correlate it on? If you want no NULLs returned for it, you'll need to wrap it inside an ISNULL or COALESCE.

Maybe I'm not fully understanding your question either...

marygrl2327

Posted: Tuesday, June 24, 2008 4:45:30 PM

Rank: Newbie

Joined: 6/13/2008
Posts: 7
Points: 21
Location: TX

Hey Scott

Thanks for your reply! As for as the exists clause and the subquery, I was actually going to only use one or the other. But, for what I wanted, neither worked. I also tried using ISNULL, but wasn't exactly sure how to say "don't give me the result of the row if the letter_date is null". I did get the query figured out though by using a temp table, which was my last resort. I was able to reorganize the table to allow it to use less execution time.

But, if I did want to use ISNULL, how would I be able to say "don't give me the result of the row if the column is null"? I'm sure it's probably staring me in the face and I just don't see it.

Smile

What's with today, today?

Scott Whigham

Posted: Friday, June 27, 2008 1:55:43 PM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

If you can post the original query that works, maybe I can help ?

marygrl2327

Posted: Monday, June 30, 2008 10:58:49 AM

Rank: Newbie

Joined: 6/13/2008
Posts: 7
Points: 21
Location: TX

Alrighty, here's the query I'm currently using. Right now I'm using a temp table, but if I can make the query more efficient that would be great. Remember, my main concern is to only show customers who have received a letter, which the select statement in the temp table accomplishes. Any advice is definitely appreciated. Thanks! Big Grin

Code:

set ansi_nulls on
set nocount on
go

--drop table #issues

create table #issues
(cust_no integer null,
site_no integer null,
incident_no integer null,
letter_date datetime null,
system_no integer null,
cs_no varchar(10) null,
agency_no integer null,
agency_name varchar(30) null,
site_name varchar(60) null,
city_name varchar (30) null,
state_id char(3) null,
zip_code varchar (10) null,
branch_no integer null,
region_id varchar(6) null,
permit1 varchar(16) null,
permit1_eff datetime null,
permit1_exp datetime null,
change_user integer null,
change_date datetime null,
department integer null)

insert into #issues (cust_no, site_no, incident_no, letter_date)

select ii.cust_no,
      ii.site_no,
      ii.incident_no,
      ii.start_date
from incident_issue ii with (nolock)
  join issue_employee ie with (nolock)on ii.incident_no = ie.incident_no
where ie.iscomp_id = 'pmtltr'
and ii.start_date > '3/31/08'

---This will update the table with accounts that are active

update #issues
set #issues.system_no = s.system_no,
  #issues.cs_no = s.cs_no
from system s with (nolock)
  join #issues on #issues.site_no = s.site_no
where s.cs_no between 'R001' and 'R170'
and not exists (select 'x' from system_status ss with (nolock)
              where s.system_no = ss.system_no
              and ss.oos_end_date > getdate())

---This will update the table with agencies that are Police dispatch types

update #issues
set #issues.agency_no = sa.agency_no
from site_agency sa with (nolock)
  join #issues on #issues.site_no = sa.site_no
where sa.agencytype_id = 'p'

update #issues
set #issues.agency_name = a.agency_name
from agency a with (nolock)
  join #issues on #issues.agency_no = a.agency_no

---This will update the table with the customer's site information

update #issues
set #issues.site_name = si.site_name,
  #issues.city_name = si.city_name,
  #issues.state_id = si.state_id,
  #issues.zip_code = si.zip_code,
  #issues.branch_no = si.branch_no
from site si with (nolock)
  join #issues on #issues.site_no = si.site_no

---This will update the table with the regions according to the permit map

update #issues
set #issues.region_id = b.region_id
from branch b with (nolock)
  join #issues on #issues.branch_no = b.branch_no

---This will update the table with the site permit information

update #issues
set #issues.permit1 = (select min(sp.permit_no)
from site_permit sp with (nolock)
where sp.site_no = #issues.site_no
and sp.agency_no = #issues.agency_no)

update #issues
set #issues.permit1_eff = (select min (p.effective_date)
from permit p with (nolock), site_permit sp with (nolock)
where sp.site_no = #issues.site_no
and sp.permit_no = #issues.permit1
and sp.agency_no = #issues.agency_no
and p.agency_no = #issues.agency_no)

update #issues
set #issues.permit1_exp = (select min (p.expire_date)
from permit p with (nolock), site_permit sp with (nolock)
where sp.site_no = #issues.site_no
and sp.permit_no = #issues.permit1
and sp.agency_no = #issues.agency_no
and p.agency_no = #issues.agency_no)

---This will update the table with the rep who entered the permit information

update #issues
set #issues.change_user = sp.change_user,
  #issues.change_date = sp.change_date
from site_permit sp with (nolock)
  join #issues on #issues.permit1 = sp.permit_no
  and #issues.site_no = sp.site_no

update #issues
set #issues.department = e.branch_no
from employee e with (nolock)
  join #issues on #issues.change_user = e.emp_no

---At this time, we do not want to look at accounts that already have a permit on file

delete #issues where letter_date > change_date

select count(*) from #issues
where system_no is not null
and permit1 is not null

What's with today, today?

Scott Whigham

Posted: Monday, June 30, 2008 11:22:01 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

Whoa - I'm sorry, Mary, but I don't have time to go through *10* update statements and understand them - Sorry Frown

Just looking at this, my hunch is that this should be a single query. So much of your updates seem to be just PK->FK relationships - why not just join all tables that are related? I didn't look at it in total detail so I could be off-base. Queries like these seem like they could be one query though. Here are the first 2 update statements - I don't know if they work or not but they look right to me:

Code:

SELECT ii.cust_no, ii.site_no, ii.incident_no, ii.start_date
      , s.system_no, s.cs_no
      , sa.agency_no
FROM incident_issue ii JOIN issue_employee ieON ii.incident_no = ie.incident_no
JOIN [system] s
  ON ii.site_no = s.site_no
JOIN site_agency sa
  ON ii.site_no = sa.site_no
WHERE ie.iscomp_id = 'pmtltr'
  AND ii.start_date > '3/31/08'
  AND s.cs_no BETWEEN 'R001' AND 'R170'
  AND NOT EXISTS (SELECT 'x' FROM system_status ss
WHERE s.system_no = ss.system_no
AND ss.oos_end_date > getdate())
  AND sa.agencytype_id = 'p'

marygrl2327

Posted: Monday, June 30, 2008 5:03:40 PM

Rank: Newbie

Joined: 6/13/2008
Posts: 7
Points: 21
Location: TX

Scott - That's perfectly fine. I really wasn't expecting you to analyze the entire query. I think I can take what you have and test it. Like I said, any advice is well appreciated. Thank you so much for your time. Smile

What's with today, today?

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.