Results 1 to 7 of 7

Thread: T-SQL noob!!!

  1. #1
    Join Date
    Aug 2005
    Location
    Sharjah, UAE
    Posts
    25

    Unanswered: T-SQL noob!!!

    In VB, I have this code...

    Code:
    lSQL = "SELECT * FROM OPENQUERY(liorder,'SELECT a.KF_ORDER_NO AS OrdNo, f.KU_NAME AS Customer, " & _
           "a.KF_ORDER_POS AS Pos, a.KF_SCHEIB_NR AS Pane, a.KF_QTY AS Qty, d.BREITE*d.HOEHE/1000*a.KF_QTY AS SQM, " & _
           "a.KF_QTY*d.SUM_NETTO AS Val, a.KF_FERT_QTY AS Done, d.BREITE*d.HOEHE/1000*a.KF_FERT_QTY AS DoneSQM, " & _
           "a.KF_FERT_QTY*d.SUM_NETTO AS DoneVal FROM LIORDER.AUF_KOPF c, LIPROD.KAPA_AUS_FERT a, LIORDER.KUST_ADR f, " & _
           "LIORDER.AUF_POS d WHERE (f.KU_VK_EK = 0) AND (a.KF_SCHR_NR = 12) AND (c.AUF_NR = a.KF_ORDER_NO) AND " & _
           "(c.KUNR = f.KU_NR) AND (a.KF_ORDER_NO = d.AUF_NR) AND (a.KF_ORDER_POS = d.AUF_POS) AND (f.KU_NAME IS NOT NULL) " & _
           "GROUP BY a.KF_ORDER_NO, f.KU_NAME, a.KF_ORDER_POS, a.KF_SCHEIB_NR, a.KF_QTY, d.BREITE, d.HOEHE, a.KF_FERT_QTY, d.SUM_NETTO')"
    Set RS = New ADODB.Recordset
    RS.Open lSQL, DB
    
    Do While Not RS.EOF
       Select Case RS!Pane
          Case "0" Or "1"
             glassSQL = "SELECT IDNR, GL_BEZ FROM OPENQUERY(liorder, 'SELECT a.IDNR, a.GL_BEZ FROM LIORDER.GLAS_DATEN a, LIORDER.AUF_POS b " & _
                        "WHERE a.IDNR = b.GLAS1 AND b.AUF_NR = " & RS!OrdNo & " AND b.AUF_POS = " & RS!Pos & "')"
             Set glassRS = New ADODB.Recordset
             glassRS.Open glassSQL, DB
             
             SQL = "INSERT INTO PendingLamination (OrdNo, Customer, Pos, Code, GlassDesc, Qty, SQM, Val, Done, DoneSQM, DoneVal) " & _
                   "VALUES ('" & RS!OrdNo & "', '" & Apostrophe(RS!Customer) & "', '" & RS!Pos & "', '" & glassRS!IDNR & "', '" & glassRS!GL_BEZ & _
                   "', '" & RS!Qty & "', '" & RS!SQM & "', '" & RS!Val & "', '" & RS!Done & "', '" & RS!DoneSQM & "', '" & RS!DoneVal & "')"
             DB.Execute SQL
          Case "2"
             glassSQL = "SELECT IDNR, GL_BEZ FROM OPENQUERY(liorder, 'SELECT a.IDNR, a.GL_BEZ FROM LIORDER.GLAS_DATEN a, LIORDER.AUF_POS b " & _
                        "WHERE a.IDNR = b.GLAS2 AND b.AUF_NR = " & RS!OrdNo & " AND b.AUF_POS = " & RS!Pos & "')"
             Set glassRS = New ADODB.Recordset
             glassRS.Open glassSQL, DB
             
             SQL = "INSERT INTO PendingLamination (OrdNo, Customer, Pos, Code, GlassDesc, Qty, SQM, Val, Done, DoneSQM, DoneVal) " & _
                   "VALUES ('" & RS!OrdNo & "', '" & Apostrophe(RS!Customer) & "', '" & RS!Pos & "', '" & glassRS!IDNR & "', '" & glassRS!GL_BEZ & _
                   "', '" & RS!Qty & "', '" & RS!SQM & "', '" & RS!Val & "', '" & RS!Done & "', '" & RS!DoneSQM & "', '" & RS!DoneVal & "')"
             DB.Execute SQL
          Case "3"
             glassSQL = "SELECT IDNR, GL_BEZ FROM OPENQUERY(liorder, 'SELECT a.IDNR, a.GL_BEZ FROM LIORDER.GLAS_DATEN a, LIORDER.AUF_POS b " & _
                        "WHERE a.IDNR = b.GLAS3 AND b.AUF_NR = " & RS!OrdNo & " AND b.AUF_POS = " & RS!Pos & "')"
             Set glassRS = New ADODB.Recordset
             glassRS.Open glassSQL, DB
             
             SQL = "INSERT INTO PendingLamination (OrdNo, Customer, Pos, Code, GlassDesc, Qty, SQM, Val, Done, DoneSQM, DoneVal) " & _
                   "VALUES ('" & RS!OrdNo & "', '" & Apostrophe(RS!Customer) & "', '" & RS!Pos & "', '" & glassRS!IDNR & "', '" & glassRS!GL_BEZ & _
                   "', '" & RS!Qty & "', '" & RS!SQM & "', '" & RS!Val & "', '" & RS!Done & "', '" & RS!DoneSQM & "', '" & RS!DoneVal & "')"
             DB.Execute SQL
       End Select
          
       ProgressBar1.Value = ProgressBar1.Value + 1
       
       If ProgressBar1.Value = 800 Then
          ProgressBar1.Value = 0
       End If
       
       RS.MoveNext
    Loop
    ...and I'm planning to use the same flow of logic in the SQL Server Agent using T-SQL. Is there any way I can do it?
    Last edited by trojanz; 12-21-05 at 04:57.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Trojanz

    I had a go at this as a bit of a challenge - untested (I couldn't be bothered creating and populating sample tables - check out the sticky at the top of the forum if this thing errors). I in no way say this is the way it should be done - I just wrote a single query that almagamated all the ones you had written. At worst, it might save the more adept SQLeers some time in figuring out your code.

    It may be better to have three queries rather than the case statement but if your tables are small then it might not be a problem.

    I converted your joins to ANSII and changed your aliases to meaningful ones - had to do this anyway as you used the same alias across queries for different tables. I also nested unnecessarily - again I just think it makes it a tiny bit more transparent (and allowed me to copy and paste your SQL rather than knock out the column aliases ). Replaced the group by with distinct as there were no agg functions.

    Lol - just noticed you deleted a load of your post - I would pop the bit about your business requirements back in at least - people prefer to start with that rather than VB & SQL.

    Usual caveats - are you sure you want to store calcualted data?
    If you are joining tables on different fields dependent on another fields data - are you sure you have normalised correctly?

    Code:
    INSERT INTO PendingLamination (OrdNo, Customer, Pos, Code, GlassDesc, Qty, SQM, Val, Done, DoneSQM, DoneVal)
    SELECT OrdNo, Customer, Pos, IDNR, GL_BEZ, Qty, SQM, Val, Done, DoneSQM, DoneVal
    FROM (
    	SELECT DISTINCT
    		KAF.KF_ORDER_NO AS OrdNo,
    		KA.KU_NAME AS Customer,
    		KAF.KF_ORDER_POS AS Pos,
    		KAF.KF_SCHEIB_NR AS Pane,
    		KAF.KF_QTY AS Qty,
    		AP.BREITE * AP.HOEHE/1000 * KAF.KF_QTY AS SQM,
    		KAF.KF_QTY * AP.SUM_NETTO AS Val,
    		KAF.KF_FERT_QTY AS Done,
    		AP.BREITE * AP.HOEHE/1000 * KAF.KF_FERT_QTY AS DoneSQM,
    		KAF.KF_FERT_QTY * AP.SUM_NETTO AS DoneVal,
    		IDNR,
    		GL_BEZ
     
    	FROM	LIORDER.AUF_KOPF AK
    			INNER JOIN LIPROAP.KAPA_AUS_FERT KAF ON
    				AK.AUF_NR = KAF.KF_ORDER_NO
    			INNER JOIN LIORDER.KUST_ADR KA ON
    				AK.KUNR = KA.KU_NR
    			INNER JOIN LIORDER.AUF_POS AP ON
    				KAF.KF_ORDER_NO = AP.AUF_NR AND
    				KAF.KF_ORDER_POS = AP.AUF_POS
    			INNER JOIN LIORDER.GLAS_DATEN GD ON
    				GD.IDNR = CASE
    								WHEN KAF.KF_SCHEIB_NR IN ('0', '1') THEN AP.GLAS1
    								WHEN KAF.KF_SCHEIB_NR = '2' THEN AP.GLAS2
    								WHEN KAF.KF_SCHEIB_NR = '3' THEN AP.GLAS3
    							End
     
    	WHERE KA.KU_VK_EK = 0 AND
    	KAF.KF_SCHR_NR = 12 AND
    	KA.KU_NAME IS NOT NULL
    	) AS TheData
    HTH
    Last edited by pootle flump; 12-21-05 at 07:50.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2005
    Location
    Sharjah, UAE
    Posts
    25
    Thanks for the reply dude!!! But before your reply, I studied briefly T-SQL programming and came out with this...

    Code:
    DECLARE @OrdNum NUMERIC, @Customer NVARCHAR(150),
            @Pos NUMERIC, @Pane NUMERIC,
            @Qty NUMERIC, @SQM FLOAT,
            @Val FLOAT, @Done NUMERIC,
            @DoneSQM FLOAT, @DoneVal FLOAT,
            @Code NUMERIC, @Desc NVARCHAR(200)
    DECLARE myCursor CURSOR FOR
    SELECT OrdNo, Customer, Pos,
           Pane, Qty, SQM,
           Val, Done, DoneSQM,
           DoneVal
    FROM OPENQUERY (liorder,'
    SELECT a.KF_ORDER_NO AS OrdNo,
           f.KU_NAME AS Customer,
           a.KF_ORDER_POS AS Pos,
           a.KF_SCHEIB_NR AS Pane,
           a.KF_QTY AS Qty,
           d.BREITE*d.HOEHE/1000*a.KF_QTY AS SQM,
           a.KF_QTY*d.SUM_NETTO AS Val,
           a.KF_FERT_QTY AS Done,
           d.BREITE*d.HOEHE/1000*a.KF_FERT_QTY AS DoneSQM,
           a.KF_FERT_QTY*d.SUM_NETTO AS DoneVal
    FROM LIORDER.AUF_KOPF c,
         LIPROD.KAPA_AUS_FERT a,
         LIORDER.KUST_ADR f,
         LIORDER.AUF_POS d 
    WHERE (f.KU_VK_EK = 0) AND
          (a.KF_SCHR_NR = 12) AND
          (c.AUF_NR = a.KF_ORDER_NO) AND 
          (c.KUNR = f.KU_NR) AND 
          (a.KF_ORDER_NO = d.AUF_NR) AND 
          (a.KF_ORDER_POS = d.AUF_POS) AND
          (f.KU_NAME IS NOT NULL) 
    GROUP BY a.KF_ORDER_NO, 
             f.KU_NAME, 
             a.KF_ORDER_POS, 
             a.KF_SCHEIB_NR, 
             a.KF_QTY, 
             d.BREITE, 
             d.HOEHE,
             a.KF_FERT_QTY,
             d.SUM_NETTO')
    OPEN myCursor
    WHILE @@FETCH_STATUS = 0
    BEGIN
       IF @Pane = 1
          BEGIN
             SELECT @Code = a.IDNR,
                    @Desc = a.GL_BEZ
             FROM liorder..LIORDER.GLAS_DATEN a,
                  liorder..LIORDER.AUF_POS b
             WHERE a.IDNR = b.GLAS1 AND
                   b.AUF_NR = @OrdNum AND
                   b.AUF_POS = @Pos
          END
          BEGIN
             INSERT INTO PendingLamination (OrdNo, Customer, Pos,
                                            Code, GlassDesc, Qty,
                                            SQM, Val, Done, DoneSQM,
                                            DoneVal)
             VALUES (@OrdNum, @Customer, @Pos, @Code, @Desc, @Qty,
                     @SQM, @Val, @Done, @DoneSQM, @DoneVal)
          END
          FETCH NEXT FROM myCursor
          INTO @OrdNum, @Customer, @Pos,
               @Pane, @Qty, @SQM,
               @Val, @Done, @DoneSQM,
               @DoneVal
       IF @Pane = 2
          BEGIN
             SELECT @Code = a.IDNR,
                    @Desc = a.GL_BEZ
             FROM liorder..LIORDER.GLAS_DATEN a,
                  liorder..LIORDER.AUF_POS b
             WHERE a.IDNR = b.GLAS2 AND
                   b.AUF_NR = @OrdNum AND
                   b.AUF_POS = @Pos
          END
          BEGIN
             INSERT INTO PendingLamination (OrdNo, Customer, Pos,
                                            Code, GlassDesc, Qty,
                                            SQM, Val, Done, DoneSQM,
                                            DoneVal)
             VALUES (@OrdNum, @Customer, @Pos, @Code, @Desc, @Qty,
                     @SQM, @Val, @Done, @DoneSQM, @DoneVal)
          END
          FETCH NEXT FROM myCursor
          INTO @OrdNum, @Customer, @Pos,
               @Pane, @Qty, @SQM,
               @Val, @Done, @DoneSQM,
               @DoneVal
       IF @Pane = 3
          BEGIN
             SELECT @Code = a.IDNR,
                    @Desc = a.GL_BEZ
             FROM liorder..LIORDER.GLAS_DATEN a,
                  liorder..LIORDER.AUF_POS b
             WHERE a.IDNR = b.GLAS3 AND
                   b.AUF_NR = @OrdNum AND
                   b.AUF_POS = @Pos
          END
          BEGIN
             INSERT INTO PendingLamination (OrdNo, Customer, Pos,
                                            Code, GlassDesc, Qty,
                                            SQM, Val, Done, DoneSQM,
                                            DoneVal)
             VALUES (@OrdNum, @Customer, @Pos, @Code, @Desc, @Qty,
                     @SQM, @Val, @Done, @DoneSQM, @DoneVal)
          END
          FETCH NEXT FROM myCursor
          INTO @OrdNum, @Customer, @Pos,
               @Pane, @Qty, @SQM,
               @Val, @Done, @DoneSQM,
               @DoneVal
    END
    CLOSE myCursor
    DEALLOCATE myCursor
    ...and it worked! But the thing is, it takes shitload of time before it finishes execution as it has to scan row by row. Not to mention the fact that I'm fetching data from an Oracle linked server! And the result set isn't small. I ran the OPENQUERY and got approximately 4500 records. So just imagine how long it would take before it finishes. But I'm going to try your suggestion though. Mine was just a test anyway. I'll check out your code and hopefully it would give me better results...

  4. #4
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    Try to avoid cursors at any cost.It will screw the perfomance.U should try pootle's code.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by mallier
    Try to avoid cursors at any cost.
    Defo - you have translated the VB (procedural) code into T-SQL (set based) very faithfully however you want to use T-SQL on sets where ever possible. Cursors (aka recordsets) are fine at the client but just chew up resources at the server - just try googling SQL Server cursor best practice and you'lll see these aren't empty words.
    As said - my code is probably better with the case function removed and split into three queries but see how you go.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Aug 2005
    Location
    Sharjah, UAE
    Posts
    25
    Yer right on this one mallier and pootle_flump. The performance sucks!!! It's killing the server actually. And if you've noticed, I'm getting records from an Oracle linked server so as I said, it takes shitload of time to insert all records. I'm trying pootle_flump's suggestion right now an' I'll get back to you as soon as I get results...
    Last edited by trojanz; 12-24-05 at 00:45.

  7. #7
    Join Date
    Aug 2005
    Location
    Sharjah, UAE
    Posts
    25
    I've tried and tested the script you gave me and it worked!!! Faster than the cursor thing I made earlier. Though I hoped I'd be able to open the result set using remote scan ('coz as I said, I'm fetching records from an Oracle linked server) to speed up opening the result set, it sure worked!!! I'm very grateful. Thanks a lot!!!

Posting Permissions

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