none
Would like a review of this conversion query RRS feed

  • Question

  • Hello,  i would like if someone just glanced over this query i re wrote in SQL...just a once over glance to see if i got everything correct  (especially the derieved table)

    Thanks

    SELECT Referen.frepgllr, Referen.fredescr, Referen.lemn,;
      Transac.lendp, Transac.ftrancod, Referen.fretrans,;
      COUNT(Transac.ftrtotal) AS numtrans,;
      SUM(Transac.ftrtotal) AS ftrtotal,;
      IIF(Transac.ftrancod="DI","2","A") AS chekp,;
      IIF(Transac.ftridnbr#0,1,2) AS cash,;
      IIF(LEFT(Transac.ftrancod,1)#"C",1,2) AS checancel;
     FROM ;
         referen ;
        INNER JOIN transac ;
       ON  Referen.lemn= Transac.ftrancod;
       OR  Referen.lemn= Transac.lendp;
     WHERE  Transac.ftrtrans = ( "A" );
       AND  Referen.fretrans IN ("A","2");
     GROUP BY Referen.fretrans, Referen.lemn, 11, 10;
     HAVING  ( chekp ) = Referen.fretrans;
     ORDER BY 10, 11, Referen.fretrans, Referen.lemn;

    SQL

    SELECT * FROM(
    SELECT 
      MAX(Referen.frepgllr) AS frepgllr, 
       MAX(Referen.fredescr) AS fredescr,
       Referen.lemn,
       MAX(Transac.lendp) AS lendp,
       MAX(Transac.ftrancod) AS ftrancod, 
       Referen.fretrans,
       COUNT(Transac.ftrtotal) AS numtrans,
      SUM(Transac.ftrtotal) AS ftrtotal,
      --IIF(Transac.ftrancod='DI','2','A') AS chekp,
    MAX(
    CASE WHEN
    (Transac.trancod LIKE 'DI') THEN '2'ELSE 'A' END )
    AS chekp,
    
    --  IIF(Transac.ftridnbr#0,1,2) AS cash,
    MAX(
    CASE WHEN
    Transac.ftridnbr <> 0 THEN 1  ELSE 2 END)
     AS cash,
    MAX(
    CASE WHEN
    LEFT(Transac.ftrancod,1) NOT LIKE 'C' THEN 1 ELSE 2 END )
    AS checancel
    --select right(Transac.ftrancod,1) as test from Dbo.transac
    
     FROM 
         Dbo.referen 
        INNER JOIN Dbo.transac 
       ON  Referen.lemn= Transac.ftrancod
       OR  Referen.lemn= Transac.lendp
     WHERE  Transac.ftrtrans = ( 'A' )
      AND  Referen.fretrans IN ('A','2')
      
     GROUP BY Referen.fretrans, Referen.lemn, 
     CASE WHEN
    LEFT(Transac.ftrancod,1) NOT LIKE 'C' THEN 1 ELSE 2 END, 
    CASE WHEN
    (Transac.trancod LIKE 'DI') THEN '2'ELSE 'A' END
    /*
    ORDER BY  CASE WHEN
    LEFT(Transac.ftrancod,1) NOT LIKE 'C' THEN 1 ELSE 2 END, 
    CASE WHEN
    (Transac.trancod LIKE 'DI') THEN '2'ELSE 'A' END, 
    Referen.fretrans, Referen.lemn
    */
    
    )chekp,
         Dbo.referen 
        INNER JOIN Dbo.transac 
       ON  Referen.lemn= Transac.ftrancod
       OR  Referen.lemn= Transac.lendp
       
     WHERE  ( chekp ) = Referen.fretrans
     
    ORDER BY  CASE WHEN
    LEFT(Transac.ftrancod,1) NOT LIKE 'C' THEN 1 ELSE 2 END, 
    CASE WHEN
    (Transac.trancod LIKE 'DI') THEN '2'ELSE 'A' END, 
    Referen.fretrans, Referen.lemn

    Friday, February 17, 2012 6:07 PM

Answers

  • 1st Query: This isn't a SQL Query. A Foxpro query I guess.

    2nd Query: Why didn't you use CTE.

    Web search for common table expression in sql server and you can find quite a few materials. Learning should be a part of your development.


    Planet Earth is at risk. Global warming is on a high tide.
    Take Responsibility. Plant Trees. Keep your City Clean and Green.

    Mark all Helping Posts and Close your Threads. Keep the Forum Green.
     - Arun Kumar Allu

    • Proposed as answer by Naomi NModerator Friday, February 17, 2012 6:31 PM
    • Unproposed as answer by Kemnet Sunday, February 19, 2012 8:15 PM
    • Marked as answer by Kemnet Friday, May 10, 2013 1:26 PM
    Friday, February 17, 2012 6:24 PM

All replies

  • 1st Query: This isn't a SQL Query. A Foxpro query I guess.

    2nd Query: Why didn't you use CTE.

    Web search for common table expression in sql server and you can find quite a few materials. Learning should be a part of your development.


    Planet Earth is at risk. Global warming is on a high tide.
    Take Responsibility. Plant Trees. Keep your City Clean and Green.

    Mark all Helping Posts and Close your Threads. Keep the Forum Green.
     - Arun Kumar Allu

    • Proposed as answer by Naomi NModerator Friday, February 17, 2012 6:31 PM
    • Unproposed as answer by Kemnet Sunday, February 19, 2012 8:15 PM
    • Marked as answer by Kemnet Friday, May 10, 2013 1:26 PM
    Friday, February 17, 2012 6:24 PM
  • Yea the 1st Query isnt SQL i had to convert it into SQL but is the 2nd Query wrong?

    Friday, February 17, 2012 6:26 PM
  • 2nd Query: Why didn't you use CTE.

    I dont know why i didnt use CTE but i know why i used a derived table, i needed to say "HAVING" by an alias

    HAVING  ( chekp ) = Referen.fretrans;
    but i cant say that cause chekp isnt a column so i did a derived table?is that wrong?

    Friday, February 17, 2012 7:42 PM
  • WITH Sales_CTE (frepgllr,fredescr,lemn,ffslendp,ftrancod,fretrans,numtrans, ftrtotal,chekp,cash,checancel) 
    AS ( SELECT MAX(Referen.frepgllr) AS frepgllr, 
    MAX(Referen.fredescr) AS fredescr, Referen.lemn,
     MAX(Transac.ffslendp) AS ffslendp, 
     MAX(Transac.ftrancod) AS ftrancod,
      Referen.fretrans, 
      COUNT(Transac.ftrtotal) AS numtrans,
       SUM(Transac.ftrtotal) AS ftrtotal,
        --IIF(Transac.ftrancod='DI','2','A') AS chekp,
         MAX( CASE WHEN (Transac.ftrancod LIKE 'DI') THEN '2'ELSE 'A' END ) AS chekp,
          -- IIF(Transac.ftridnbr#0,1,2) AS cash, 
          MAX( CASE WHEN Transac.ftridnbr <> 0 THEN 1 ELSE 2 END) AS cash,
           MAX( CASE WHEN LEFT(Transac.ftrancod,1) NOT LIKE 'C' THEN 1 ELSE 2 END ) AS checancel 
           --select right(Transac.ftrancod,1) as test from Dbo.transac 
           FROM 
           --Reference.lending_program referen
            Dbo.referen INNER JOIN Dbo.transac
             ON Referen.lemn = Transac.ftrancod 
             OR Referen.lemn = Transac.ffslendp
              WHERE Transac.ftrtrans = ( 'A' ) 
             AND Referen.fretrans IN ('A','2')
              --select * from Reference.funding_source GROUP BY Referen.fretrans, Referen.lemn,
               CASE WHEN LEFT(Transac.ftrancod,1) NOT LIKE 'C' THEN 1 ELSE 2 END, 
               CASE WHEN (Transac.ftrancod LIKE 'DI') THEN '2'ELSE 'A' END )
               
        SELECT frepgllr,fredescr,lemn,ffslendp,ftrancod,fretrans,numtrans, ftrtotal,chekp,cash,checancel 
        FROM Sales_CTE
         -- HAVING (chekp) = rferen.retrans 
    --HAVING  ( chekp ) = Sales_CTE.fretrans
    
    ORDER BY  CASE WHEN
    LEFT(Sales_CTE.ftrancod,1) NOT LIKE 'C' THEN 1 ELSE 2 END, 
    CASE WHEN
    (Sales_CTE.ftrancod LIKE 'DI') THEN '2'ELSE 'A' END, 
    Sales_CTE.fretrans, Sales_CTE.freelemn

    Sorry had to re post this, didnt know the code was badly formatted making it hard to read -

    Anyways i turned it to a CTE, is this better than the derived query i had?

    Saturday, February 18, 2012 11:31 PM
  • yea i posted my progress..just that no 1 hasnt commented on it as yet :)
    Friday, February 24, 2012 2:14 PM
  • Can you repost your WORKING query without any commented-out lines?

    You can beautify your query at: http://sqlusa.com/sqlformat/

    Do you have performance issue? Thanks.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES

    Monday, March 5, 2012 3:26 PM
    Moderator