Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: Set based soultion

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

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


    Code:
    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-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    "New" is created with

    Code:
    SELECT * INTO PSI_JobFlow_New FROM PSI_JobFlow
    Data looks like:

    Code:
    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:

    Code:
    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
    Last edited by Brett Kaiser; 07-08-04 at 18:07.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what a monstrous pile of ... code

    i cannot even begin to grasp what you're trying to do

    and of course there's no way of knowing if there's a set-based solution if i can't even figure out what you're trying to do

    any chance you could state the problem in english?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That should make your head hurt! What the code seems to be doing is a "trickle down" of the JOBNAME column through the table (ordered by ID) until you find another non-null JOBNAME. I think that you can do this set based using:
    Code:
    UPDATE PSI_JobFlow_New
       SET JOBNAME = (SELECT b.JOBNAME
          FROM PSI_JobFlow_New AS b
          WHERE  b.JOBNAME IS NULL
             AND b.ID = (SELECT Max(c.id)
                FROM PSI_JobFlow_New AS c
                WHERE  c.ID < PSI_JobFlow_New.ID
                   AND c.JOBNAME IS NOT NULL))
    -PatP
    Last edited by Pat Phelan; 07-08-04 at 22:09.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    My Apologies.

    Pat has it right though...

    The damn cursor is still running...

    No blocking...

    It's an attempt to change a report (hierarchal?) into a relational format..

    So...I can use the damn data to figure out a job flow of several systems

    Anyone ever hear of a mainframe scheduler called OPC

    I know jobtrac....and they seem similar...


    what a pain....

    Pat, I'll give the code a shot...Thanks...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oh fubar! I think I goofed. I was rather tired (still am), and slipped a condition into the wrong WHERE clause. I think the prior version will NULL almost everything. I think that you really want:
    Code:
    UPDATE PSI_JobFlow_New
       SET JOBNAME = (SELECT b.JOBNAME
          FROM PSI_JobFlow_New AS b
          WHERE  b.ID = (SELECT Max(c.id)
                FROM PSI_JobFlow_New AS c
                WHERE  c.ID < PSI_JobFlow_New.ID
                   AND c.JOBNAME IS NOT NULL))
       WHERE JOBNAME IS NULL
    Sorry!

    -PatP

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah, I just looked at mine...16 hours...and because I didn't set the initial value..it was nulling everything out...

    What a total scrub..

    Thanks Pat, I'll try that now....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    16 hours ?!?! How many rows are we talkin' here, bubba ?

    -PatP

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Only 150k....

    tons of cpu, little I/O, quad box, 2 gb, no blocking...

    can't be stuck in a loop

    freaked me the f' out.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Would ANYONE like to point out to this TOTAL SCRUB that he didn't put a FETCH NEXT inside the loop?

    What an IDIOT......
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser
    Would ANYONE like to point out to this TOTAL SCRUB that he didn't put a FETCH NEXT inside the loop?

    What an IDIOT......
    I think that Brett already did. I assumed that you'd just butchered that line out somehow, but I probably should have raised the issue, just in case.

    Sorry!
    -PatP

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Damn....it's 365k...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-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    ...and sometimes you think that I'm drunk! Using ErWin to map job dependancies ?!?! I'm not even sure that I understand how that might work, but as long as you do, that's a good thing. That one really takes the cake!

    I'd be curious to know how the set based solution compared with the cursor based one. It would be nice to have at least a good guess at the difference in execution times.

    -PatP

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Dude...let me ask you...

    Your company sells a securities firm...there is NO documentation...

    You're job is to archive all data, undersatnd ALL of their system AND be prepared to handle ANY requests from legal or the street (SEC)

    99% is mainframe

    Luckily they used a scheduler...which produces a report of jobs and the jobs they kick off...

    300,000+ jobs.....

    Each system is qualified though by a 2 digit identifier...most systems are in the 100-300 job scope...which will become a subject area in erwin

    I tried to code a way with Visio, VB, ect...I figured generating ddl wioth FK would be the easiest....

    AND its not 5:00, so I'm not drunk...(hung over maybe...)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Uff-da! I can't imagine us owning a securities firm with no documention. Then again, I can't imagine us buying one either. The whole idea makes me quesy.

    You certainly could be drunk. Sometimes when you are bleary eyed in the morning, you can get a bit frisky mixing the first couple of margaritas. Mix them too strong, and whooooopie!

    You have my condolences.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •