Problems with loop - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problems with loop

Author

Topic

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-08-24 : 09:16:02

Morning,

I have a statement that I have been trying to run. What I want to happen, in english is:

check if Facility, listed in the master location table, is not in the view vwUnionResults for today if it is not then print: "No upload today for Facility Name". If it is then print upload information.

What I have listed below stops at the first statement and won't go to the next step. Any thoughts or suggestions would be most welcome.

Laura


IF (SELECT Facility
    FROM dbo.tblLocation 
    WHERE Active = 'yes'
    and Facility NOT IN
	(SELECT Facility
	 FROM dbo.vwUnionResults 
	 where convert(varchar, UpLoadDate, 101) = convert(varchar, Getdate(), 101))) is not null
BEGIN 
    SELECT Facility +' is missing their yc'
    FROM dbo.tblLocation 
    WHERE Active = 'yes'
    and Facility NOT IN
	(SELECT Facility
	 FROM dbo.vwUnionResults 
	 where convert(varchar, UpLoadDate, 101) = convert(varchar, Getdate(), 101))
END
ELSE
   IF (SELECT Facility
	 FROM dbo.vwUnionResults 
	 where convert(varchar, UpLoadDate, 101) = convert(varchar, Getdate(), 101))is not null
Begin
   select * from dbo.vwUnionResults 
end

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-08-24 : 10:18:47

You might need to post the table structures, but from what I can tell, the first IF block is checking all Facilities against that view. If even one facility made it in the upload, then I believe it will see that as OK and move on.

If I am missing something, please let me know and give us a table structure to work with.

Aj

Hey, it compiles.

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-08-24 : 10:34:03

Okay I've been working away in the background :) This is what I have now. I get what I want, again sort of. It pulls the Facility name and says "Aberdeen is missing their yc' and then goes on to show the results of the query.

Is there a way I can make this into one query? Like have it display both results together?

So it would look like

Brattleboro ---lots of data
North Hatfield ----lots of data
Aberdeen is missing their yc
Westfield --- lots of data

Thanks for the help.

Laura


IF exists (SELECT Facility
    FROM dbo.tblLocation 
    WHERE Active = 'yes'
    and Facility NOT IN
	(SELECT Facility
	 FROM dbo.vwUnionResults 
	 where convert(varchar, UpLoadDate, 101) = convert(varchar, Getdate(), 101))) 

SELECT Facility +' is missing their yc'
    FROM dbo.tblLocation 
    WHERE Active = 'yes'
    and Facility NOT IN
	(SELECT Facility
	 FROM dbo.vwUnionResults 
	 where convert(varchar, UpLoadDate, 101) = convert(varchar, Getdate(), 101))

if exists (select * from dbo.vwUnionResults)
select * from dbo.vwUnionResults

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-08-24 : 10:42:21

What does that mean "it stops at the first statement"? Porobably both (select ...) returned one null value (I think that was intention, you can use exists operator instead), so no data was returned.
[Edited] I haven't seen your last post when I posted my answer.

saleyoun
Starting Member

4 Posts

Posted - 2005-08-24 : 11:09:33

The problem you're having is because you're not looping through the records. There are several ways you can accomplish your task. Below is only one way.

--TEMPORARY TABLE WITH IDENTITY COLUMN TO DO YOUR LOOP
CREATE TABLE dbo.#MyFacilities (
       FacilityID int IDENTITY(1,1) NOT NULL,
       Facility varchar(50))

--VARIABLE THAT YOU'LL USE TO LOOP THROUGH TEMPORARY TABLE
DECLARE @FacilityID int


--INSERT ALL RECORDS FROM YOUR VIEW
INSERT INTO dbo.#MyFacilities (
       Facility)
SELECT L.Facility + ' is missing their yc'
  FROM dbo.tblLocation L
 WHERE L.Active = 'yes' AND
       L.Facility NOT EXISTS (SELECT 'TRUE'
                                FROM dbo.vwUnionResults 
                               WHERE convert(varchar(10), UpLoadDate, 101) = convert(varchar, Getdate(), 101))


--AT THIS POINT YOU MAY SELECT THE CONTENT OF THE TEMPORARY TABLE OR DO THE LOOP BELOW
SELECT Facility
  FROM dbo.#MyFacilities


--OR


--ASSIGN FIRST RECORD FROM TEMP TABLE TO VARIABLE
SELECT @FacilityID = MIN(FacilityID)
  FROM dbo.#MyFacilities


WHILE @FacilityID IS NOT NULL
BEGIN
      SELECT Facility
        FROM dbo.#MyFacilities
       WHERE FacilityID = @FacilityID

      SELECT @FacilityID = MIN(FacilityID)
        FROM dbo.#MyFacilities
       WHERE FacilityID > @FacilityID
END

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-24 : 11:19:51

Or you could use a cursor....

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-08-24 : 11:31:09

I was always leary of using cursors. So I don't really know how I would it in this instance.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-24 : 12:05:15

won't this work:


select	Facility + 
		case when exists(
			select 'laura' 
			from dbo.vwUnionResults
			where datediff(day, UpLoadDate, getdate()) = 0
			and facility = a.facility
			)
		then ' ----lots of data'
		else ' is missing their yc'
		end
from	dbo.tblLocation a

Be One with the Optimizer
TG

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-08-24 : 12:47:20

That is so close to what I have

Although mine won't give me the "Facility +' is missing their yc" message.


SELECT 
CASE
    WHEN not exists(SELECT Facility 
	  FROM dbo.tblLocation 
	  WHERE Active = 'yes'
	  and Facility NOT IN
		(SELECT Facility
		 FROM dbo.vwUnionResults 
		 where uploaddate = '08/24/2005'))
         THEN Facility +' is missing their yc'
    ELSE Facility
END as Facility,
bodytype,uploaddate
from dbo.vwUnionResults
order by printorder,bodytype desc

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-24 : 12:54:16

>>That is so close to what I have

I guess great minds think alike

. Does that mean your problem is solved? Or do both our minds still need a little schooling?

Be One with the Optimizer
TG

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-08-24 : 13:03:19

Close but not quite

I still need to figure out why it won't give me both messages.

Getting closer though.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-24 : 13:27:06

did you try mine? Seems to display both messages:


set nocount on
declare @tblLocation table (facility varchar(50))
declare @vwUnionResults table (Facility varchar(50), UpLoadDate datetime)

insert @tblLocation
select 'Brattleboro' union all ---lots of data
select 'North Hatfield' union all ----lots of data
select 'Aberdeen' union all -- is missing their yc
select 'Westfield' --- lots of data

insert @vwUnionResults
select 'Brattleboro', getdate() union all
select 'North Hatfield', getdate() union all
select 'Aberdeen', getdate()-1 union all
select 'Westfield', getdate()

select	Facility + 
		case when exists(
			select 'tg' 
			from @vwUnionResults
			where datediff(day, UpLoadDate, getdate()) = 0
			and facility = a.facility
			)
		then ' ----lots of data'
		else ' is missing their yc'
		end
from	@tblLocation a

Be One with the Optimizer
TG

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-08-24 : 13:45:56

Nope, I must admit I saw the date diff and got confused

thanks for the push I will check it out.

Thansk so much

Laura

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-08-24 : 14:00:07

Thanks TG you were right on! I have to pull the rest of the query out but this will definitely work.

Thanks again

Laura

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources