I'm thinking Theta join...but my brain hurts

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

SQL Server Development (2000)

I'm thinking Theta join...but my brain hurts

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-08 : 17:00:31

Yeah..my brain hurts...they're actually making me work....

It's running now, but there's got to be a set based solution


DECLARE myCursor99 CURSOR FOR
SELECT [ID], JOBNAME FROM PSI_JobFlow ORDER BY [ID]

DECLARE @ID int, @JOBNAME varchar(20), @SAVE_JOBNAME varchar(20)

OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @ID, @JOBNAME

WHILE @@FETCH_STATUS = 0
  BEGIN
	IF @JOBNAME IS NOT NULL AND @JOBNAME <> @SAVE_JOBNAME
	  BEGIN	  
		SELECT @SAVE_JOBNAME = @JOBNAME
	  END
	IF @JOBNAME IS NULL
	  BEGIN
		UPDATE PSI_JobFlow_New SET JOBNAME = @SAVE_JOBNAME WHERE [ID] = @ID
	  END
  END

CLOSE myCursor99
DEALLOCATE myCursor99

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-08 : 17:08:38

"New" is created with



SELECT * INTO PSI_JobFlow_New FROM PSI_JobFlow

Data looks like:


ID          APPL_ID             Status OP_ID     JOBNAME    PREDECESSORS_1    PREDECESSORS_2 PREDECESSORS_3 S    SUCCESSOR_1       SUCCESSOR_2 SUCCESSOR_3      
----------- ------------------- ------ --------- ---------- ----------------- -------------- -------------- ---- ----------------- ----------- ---------------- 
55350       0ET#BKPV1           A      CPU_010   ET#BKPV1   ETBOFIC           CPU_010        ETBOFIC        NULL NULL              NULL        NULL
55351       NULL                NULL   NULL      NULL       NULL              NULL           NULL           A    ET0040D           CPU_010     ET0040D
55352       NULL                NULL   NULL      NULL       NULL              NULL           NULL           A    ET0070D           CPU_010     ET0070D
55353       0ET#BKPV2           A      CPU_010   ET#BKPV2   ET0040D           CPU_010        ET0040D        NULL NULL              NULL        NULL
55354       NULL                A      NULL      NULL       ET0070D           CPU_010        ET0070D        NULL NULL              NULL        NULL
55355       NULL                NULL   NULL      NULL       NULL              NULL           NULL           A    BRET#S1D          BUSR_010    BRET#S1D
55356       NULL                NULL   NULL      NULL       NULL              NULL           NULL           A    ETBOFIO           CPU_010     ETBOFIO
55357       0ETBOFIC            NULL   CPU_010   ETBOFIC    NULL              NULL           NULL           A    ET#BKPV1          CPU_010     ET#BKPV1
55358       NULL                NULL   NULL      NULL       NULL              NULL           NULL           A    GT00050D          CPU_010     GT00050D
55359       0ETBOFIO            A      CPU_010   ETBOFIO    ET#BKPV2          CPU_010        ET#BKPV2       NULL NULL              NULL        NULL
55360       NULL                NULL   NULL      NULL       NULL              NULL           NULL           A    ET0320D           CPU_010     ET0320D
55361       NULL                NULL   NULL      NULL       NULL              NULL           NULL           A    ET0330W           CPU_010     ET0330W
55362       NULL                NULL   NULL      NULL       NULL              NULL           NULL           A    SOWETSD           CPU_010     SOWETSD
55363       NULL                NULL   NULL      NULL       NULL              NULL           NULL           A    SQ4040D

And DDL:


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PSI_JOBFLOW]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PSI_JOBFLOW]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PSI_JobFlow_New]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PSI_JobFlow_New]
GO

CREATE TABLE [dbo].[PSI_JOBFLOW] (
	[ID] [int] NOT NULL ,
	[APPL_ID] [nvarchar] (19) NULL ,
	[Status] [nvarchar] (2) NULL ,
	[OP_ID] [nvarchar] (9) NULL ,
	[JOBNAME] [nvarchar] (10) NULL ,
	[PREDECESSORS_1] [nvarchar] (17) NULL ,
	[PREDECESSORS_2] [nvarchar] (9) NULL ,
	[PREDECESSORS_3] [nvarchar] (11) NULL ,
	[S] [nvarchar] (2) NULL ,
	[SUCCESSOR_1] [nvarchar] (17) NULL ,
	[SUCCESSOR_2] [nvarchar] (9) NULL ,
	[SUCCESSOR_3] [nvarchar] (16) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PSI_JobFlow_New] (
	[ID] [int] NOT NULL ,
	[APPL_ID] [nvarchar] (19) NULL ,
	[Status] [nvarchar] (2) NULL ,
	[OP_ID] [nvarchar] (9) NULL ,
	[JOBNAME] [nvarchar] (10) NULL ,
	[PREDECESSORS_1] [nvarchar] (17) NULL ,
	[PREDECESSORS_2] [nvarchar] (9) NULL ,
	[PREDECESSORS_3] [nvarchar] (11) NULL ,
	[S] [nvarchar] (2) NULL ,
	[SUCCESSOR_1] [nvarchar] (17) NULL ,
	[SUCCESSOR_2] [nvarchar] (9) NULL ,
	[SUCCESSOR_3] [nvarchar] (16) NULL 
) ON [PRIMARY]
GO

 CREATE  INDEX [PSI_JobFlow_New_IX1] ON [dbo].[PSI_JobFlow_New]([ID], [JOBNAME]) ON [PRIMARY]
GO

Brett

8-)

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-07-08 : 18:16:00

Hey Brett,

What is it your are trying to perform here? Can you explain what you need?

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-08 : 18:30:33

I think he's trying to update the NULL jobnames to the job name above it in ID order.

ID          APPL_ID             Status OP_ID     JOBNAME    PREDECESSORS_1    PREDECESSORS_2 PREDECESSORS_3 S    SUCCESSOR_1       SUCCESSOR_2 SUCCESSOR_3      
----------- ------------------- ------ --------- ---------- ----------------- -------------- -------------- ---- ----------------- ----------- ---------------- 
55350       0ET#BKPV1           A      CPU_010   ET#BKPV1   ETBOFIC           CPU_010        ETBOFIC        NULL NULL              NULL        NULL
55351       NULL                NULL   NULL      NULL       NULL              NULL           NULL           A    ET0040D           CPU_010     ET0040D
55352       NULL                NULL   NULL      NULL       NULL              NULL           NULL           A    ET0070D           CPU_010     ET0070D
55353       0ET#BKPV2           A      CPU_010   ET#BKPV2   ET0040D           CPU_010        ET0040D        NULL NULL              NULL        NULL
55354       NULL                A      NULL      NULL       ET0070D           CPU_010        ET0070D        NULL NULL              NULL        NULL
55355       NULL                NULL   NULL      NULL       NULL              NULL           NULL           A    BRET#S1D          BUSR_010    BRET#S1D

becomes (note JOBNAME field)

ID          APPL_ID             Status OP_ID     JOBNAME    PREDECESSORS_1    PREDECESSORS_2 PREDECESSORS_3 S    SUCCESSOR_1       SUCCESSOR_2 SUCCESSOR_3      
----------- ------------------- ------ --------- ---------- ----------------- -------------- -------------- ---- ----------------- ----------- ---------------- 
55350       0ET#BKPV1           A      CPU_010   ET#BKPV1   ETBOFIC           CPU_010        ETBOFIC        NULL NULL              NULL        NULL
55351       NULL                NULL   NULL      ET#BKPV1      NULL              NULL           NULL           A    ET0040D           CPU_010     ET0040D
55352       NULL                NULL   NULL      ET#BKPV1      NULL              NULL           NULL           A    ET0070D           CPU_010     ET0070D
55353       0ET#BKPV2           A      CPU_010   ET#BKPV2   ET0040D           CPU_010        ET0040D        NULL NULL              NULL        NULL
55354       NULL                A      NULL      ET#BKPV2      ET0070D           CPU_010        ET0070D        NULL NULL              NULL        NULL
55355       NULL                NULL   NULL      ET#BKPV2      NULL              NULL           NULL           A    BRET#S1D          BUSR_010    BRET#S1D

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-08 : 18:31:18

Mabybe this:

UPDATE PSI_JobFlow_New
SET JOBNAME = SAVEJOBNAME
FROM (
SELECT id, (
SELECT TOP 1 JOBNAME
FROM PSI_JobFlow_New WHERE id < o.id
AND JOBNAME IS NOT NULL ORDER BY ID DESC) AS SAVEJOBNAME
FROM PSI_JobFlow_New o
WHERE JOBNAME IS NULL
) x
WHERE PSI_JobFlow_New.id = x.id

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-07-08 : 18:40:04

Hi Preston,

If so this should work:


DECLARE @last_jobname nvarchar(10)

SELECT @last_jobname = jobname
FROM PSI_jobflow
WHERE id IN (SELECT MIN(id) FROM psi_jobflow)

UPDATE PSI_jobflow_new
SET
	jobname = CASE WHEN jobname IS NULL THEN @last_jobname ELSE jobname END,
	@last_jobname = CASE WHEN jobname IS NULL THEN @last_jobname ELSE jobname END

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-09 : 09:16:06

Damn cursor is still running...

Lots of CPU, no blocking, and yes Preston is correct...

It's a f'n report...

Need to make the damn data relational, so I can use it to determine the damn job flow....

damn...

I'll give the queries a shot..thanks guys

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-09 : 10:19:31

Ahem....do you think it might have been useful to actualy put a FETCH NEXT in the f'n loop?

What a scrub

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-07-09 : 10:23:18

I should have a word with the bloke that put the cursor in there in the first place ...

Kristen

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-09 : 10:25:16

Damn cursors!

Just not the same as While rdr.Read() .... End While

Duane.

Kristen
Test

22859 Posts

Posted - 2004-07-09 : 10:30:58

Here's your answer then Brett: "you can do it in VB" ... <thud!>

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-09 : 10:52:44

Damn....it's 365k rows...and 10 minutes later....DONE!

What a maroon.......

OH, and now that I've got that done...I'm going to gen ddl and pretend the jobs are tables...using the successor jobs as column names, and then gen ALTERS to establish RI between each "job" and use ERWIN to produce a job flow...

Well at least thats the idea....

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-07-09 : 12:10:44

Ah, sounds like acceptance tests: "Continuous rewrites of the original specification until they match the product we actually built"

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources