-1

I'm not sure how to translate this from Access to SQL. We are currently using SQL Server 2008. I am trying to migrate Access reports into SQL views.

SELECT 
  First(IIf(IsNull([dbo_CUST_ADDRESS]![STATE]),IIf(IsNull([dbo_CUSTOMER]![BILL_TO_STATE]),[dbo_CUSTOMER]![STATE],[dbo_CUSTOMER]![BILL_TO_STATE]),[dbo_CUST_ADDRESS]![STATE])) AS STATE
  , dbo_RECEIVABLE.INVOICE_ID
  , dbo_RECEIVABLE.INVOICE_DATE
  , dbo_RECEIVABLE.CUSTOMER_ID
  , First(IIf(IsNull([dbo_CUST_ADDRESS]![NAME]),IIf(IsNull([dbo_CUSTOMER]![BILL_TO_NAME]),[dbo_CUSTOMER]![NAME],[dbo_CUSTOMER]![BILL_TO_NAME]),[dbo_CUST_ADDRESS]![NAME])) AS COMPANY
  , Sum(IIf([dbo_RECEIVABLE_LINE]![GL_ACCOUNT_ID]='4005-008',0,[dbo_RECEIVABLE_LINE]![AMOUNT])) AS Inv_Total_Amt
  , Sum(IIf([dbo_RECEIVABLE_LINE]![REFERENCE]<>"LA-COUNTY" And [dbo_RECEIVABLE_LINE]![REFERENCE]<>"FREIGHT" And [dbo_RECEIVABLE_LINE]![GL_ACCOUNT_ID]<>'4005-008',[dbo_RECEIVABLE_LINE]![AMOUNT],0)) AS Subtotal
  , Sum(IIf([dbo_RECEIVABLE_LINE]![REFERENCE]="LA-COUNTY",[dbo_RECEIVABLE_LINE]![AMOUNT],0)) AS TAX
  , Sum(IIf([dbo_RECEIVABLE_LINE]![REFERENCE]="FREIGHT",[dbo_RECEIVABLE_LINE]![AMOUNT],0)) AS Freight
  , dbo_RECEIVABLE.SALESREP_ID    
FROM (((dbo_RECEIVABLE RIGHT JOIN dbo_RECEIVABLE_LINE ON dbo_RECEIVABLE.INVOICE_ID = dbo_RECEIVABLE_LINE.INVOICE_ID) LEFT JOIN dbo_CUSTOMER_ORDER ON dbo_RECEIVABLE_LINE.CUST_ORDER_ID = dbo_CUSTOMER_ORDER.ID) LEFT JOIN dbo_CUST_ADDRESS ON (dbo_CUSTOMER_ORDER.CUSTOMER_ID = dbo_CUST_ADDRESS.CUSTOMER_ID) AND (dbo_CUSTOMER_ORDER.SHIP_TO_ADDR_NO = dbo_CUST_ADDRESS.ADDR_NO)) LEFT JOIN dbo_CUSTOMER ON dbo_RECEIVABLE.CUSTOMER_ID = dbo_CUSTOMER.ID    
GROUP BY 
  dbo_RECEIVABLE.INVOICE_ID
  , dbo_RECEIVABLE.INVOICE_DATE
  , dbo_RECEIVABLE.CUSTOMER_ID
  , dbo_RECEIVABLE.SALESREP_ID    
ORDER BY 
  First(IIf(IsNull([dbo_CUST_ADDRESS]![STATE]),IIf(IsNull([dbo_CUSTOMER]![BILL_TO_STATE]),[dbo_CUSTOMER]![STATE],[dbo_CUSTOMER]![BILL_TO_STATE]),[dbo_CUST_ADDRESS]![STATE]))
  , dbo_RECEIVABLE.INVOICE_ID;

This is what I created on 10/21/15. I changed the WHERE to look for an invoice number to test it and added nesting CASEs. It shows 2 lines for that invoice and isn't grouping them into 1.

SELECT
(SELECT TOP 1 CASE WHEN CUST_ADDRESS.STATE IS NULL THEN CASE WHEN CUSTOMER.BILL_TO_STATE IS NULL THEN CUSTOMER.STATE ELSE CUSTOMER.BILL_TO_STATE END ELSE CUST_ADDRESS.STATE END FROM dbo.RECEIVABLE RIGHT OUTER JOIN dbo.RECEIVABLE_LINE ON dbo.RECEIVABLE.INVOICE_ID = dbo.RECEIVABLE_LINE.INVOICE_ID LEFT OUTER JOIN dbo.CUSTOMER_ORDER ON dbo.RECEIVABLE_LINE.CUST_ORDER_ID = dbo.CUSTOMER_ORDER.ID LEFT OUTER JOIN dbo.CUST_ADDRESS ON dbo.CUSTOMER_ORDER.CUSTOMER_ID = dbo.CUST_ADDRESS.CUSTOMER_ID AND dbo.CUSTOMER_ORDER.SHIP_TO_ADDR_NO = dbo.CUST_ADDRESS.ADDR_NO LEFT OUTER JOIN dbo.CUSTOMER ON dbo.RECEIVABLE.CUSTOMER_ID = dbo.CUSTOMER.ID WHERE dbo.RECEIVABLE.INVOICE_ID = 'iv184546') AS STATE, dbo.RECEIVABLE.INVOICE_ID, dbo.RECEIVABLE.INVOICE_DATE, dbo.RECEIVABLE.POSTING_DATE, dbo.RECEIVABLE.CUSTOMER_ID, (SELECT TOP 1 CASE WHEN dbo.CUST_ADDRESS.NAME IS NULL THEN dbo.CUSTOMER.NAME ELSE dbo.CUST_ADDRESS.NAME END FROM dbo.RECEIVABLE RIGHT OUTER JOIN dbo.RECEIVABLE_LINE ON dbo.RECEIVABLE.INVOICE_ID = dbo.RECEIVABLE_LINE.INVOICE_ID LEFT OUTER JOIN dbo.CUSTOMER_ORDER ON dbo.RECEIVABLE_LINE.CUST_ORDER_ID = dbo.CUSTOMER_ORDER.ID LEFT OUTER JOIN dbo.CUST_ADDRESS ON dbo.CUSTOMER_ORDER.CUSTOMER_ID = dbo.CUST_ADDRESS.CUSTOMER_ID AND dbo.CUSTOMER_ORDER.SHIP_TO_ADDR_NO = dbo.CUST_ADDRESS.ADDR_NO LEFT OUTER JOIN dbo.CUSTOMER ON dbo.RECEIVABLE.CUSTOMER_ID = dbo.CUSTOMER.ID WHERE dbo.RECEIVABLE.INVOICE_ID = 'iv184546') AS COMPANY, SUM(CASE WHEN RECEIVABLE_LINE.GL_ACCOUNT_ID = '4005-008' THEN 0 ELSE RECEIVABLE_LINE.AMOUNT END) AS Inv_Total_Amt, SUM(CASE WHEN RECEIVABLE_LINE.REFERENCE <> 'LA-COUNTY' AND RECEIVABLE_LINE.REFERENCE <> 'FREIGHT' AND RECEIVABLE_LINE.GL_ACCOUNT_ID <> '4005-008' THEN RECEIVABLE_LINE.AMOUNT ELSE 0 END) AS Subtotal, SUM(CASE WHEN RECEIVABLE_LINE.REFERENCE = 'LA-COUNTY' THEN RECEIVABLE_LINE.AMOUNT ELSE 0 END) AS TAX, SUM(CASE WHEN RECEIVABLE_LINE.REFERENCE = 'FREIGHT' THEN RECEIVABLE_LINE.AMOUNT ELSE 0 END) AS Freight, dbo.RECEIVABLE.SALESREP_ID

FROM dbo.RECEIVABLE RIGHT OUTER JOIN dbo.RECEIVABLE_LINE ON dbo.RECEIVABLE.INVOICE_ID = dbo.RECEIVABLE_LINE.INVOICE_ID LEFT OUTER JOIN dbo.CUSTOMER_ORDER ON dbo.RECEIVABLE_LINE.CUST_ORDER_ID = dbo.CUSTOMER_ORDER.ID LEFT OUTER JOIN dbo.CUST_ADDRESS ON dbo.CUSTOMER_ORDER.CUSTOMER_ID = dbo.CUST_ADDRESS.CUSTOMER_ID AND dbo.CUSTOMER_ORDER.SHIP_TO_ADDR_NO = dbo.CUST_ADDRESS.ADDR_NO LEFT OUTER JOIN dbo.CUSTOMER ON dbo.RECEIVABLE.CUSTOMER_ID = dbo.CUSTOMER.ID WHERE dbo.RECEIVABLE.INVOICE_ID = 'iv184546' GROUP BY CASE WHEN CUST_ADDRESS.STATE IS NULL THEN CASE WHEN CUSTOMER.BILL_TO_STATE IS NULL THEN CUSTOMER.STATE ELSE CUSTOMER.BILL_TO_STATE END ELSE CUST_ADDRESS.STATE END, dbo.CUSTOMER.NAME, dbo.RECEIVABLE.INVOICE_ID, dbo.RECEIVABLE.INVOICE_DATE, dbo.RECEIVABLE.POSTING_DATE, dbo.RECEIVABLE.CUSTOMER_ID, dbo.RECEIVABLE.SALESREP_ID, CASE WHEN dbo.CUST_ADDRESS.NAME IS NULL THEN dbo.CUSTOMER.NAME ELSE dbo.CUST_ADDRESS.NAME END
ORDER BY dbo.RECEIVABLE.INVOICE_ID, dbo.RECEIVABLE.INVOICE_DATE;

  • 5
    I'm sure you know this, but your query is pretty messy. It would probably be easier to get a good answer if you also explain the relationships and your desired output with some sample data. That would let us rewrite the query, and may give you a performance boost, instead of asking someone to translate nested IIF logic... – Erik Oct 21 '15 at 0:12
  • 1
    As @Erik said, please add some details concerning the definitions of the source tables, sample data for them, and desired output. – Max Vernon Oct 21 '15 at 0:21
  • 1
    Instead of IIF use CASE. Instead of First use MIN or MAX. The rest can remain as it is. It would not be optimal, but it would produce same result as original query. Normally you'd rewrite the query in such a way, that you don't need First. – Vladimir Baranov Oct 21 '15 at 1:28
  • 3
    FIRST is not the same as MIN. FIRST in Access returns the first inserted row; you may be able to mimic that using the ROW_NUMBER functionality, assuming you have a monotonically increasing column. – Max Vernon Oct 21 '15 at 2:08
  • @MaxVernon, Yes, First is not the same as MIN, but... "They simply return the value of a specified field in the first or last record, respectively, of the result set returned by a query. Because records are usually returned in no particular order (unless the query includes an ORDER BY clause), the records returned by these functions will be arbitrary." In this query ordering itself is done by the result of the First. If this query is correct, then all values that First chooses from must be identical. – Vladimir Baranov Oct 21 '15 at 2:44
8

Instead of rewriting the whole query, I'll give you some hints of how to do it:

  • IIf(condition, a, b) becomes: CASE WHEN condition THEN a ELSE b END
  • IsNull(x) becomes: x IS NULL
  • The combined IIf(IsNull(x), y, x)) can then be written as: CASE WHEN x IS NULL THEN y ELSE x END and simplified further to COALESCE(x, y).
  • The table-column expressions: [tab]![col] becomes: [tab].[col] and if the names are not reserved, this is more readable: tab.col
  • Use single quotes (and not double quotes) for string literals. Eg, replace "FREIGHT" with 'FREIGHT'. Double quotes are used in standard SQL (and in SQL Server, besides brackets) for quoting table and columns' names and aliases, not for literals.
  • The parentheses in the FROM clause can be safely removed. SQL Server's parser is happy with or without them.
  • Additionally, readability will increase if you use aliases for tables:

    FROM (((dbo_RECEIVABLE RIGHT JOIN dbo_RECEIVABLE_LINE ON dbo_RECEIVABLE.INVOICE_ID = dbo_RECEIVABLE_LINE.INVOICE_ID) LEFT JOIN dbo_CUSTOMER_ORDER ON dbo_RECEIVABLE_LINE.CUST_ORDER_ID = dbo_CUSTOMER_ORDER.ID) LEFT JOIN dbo_CUST_ADDRESS ON (dbo_CUSTOMER_ORDER.CUSTOMER_ID = dbo_CUST_ADDRESS.CUSTOMER_ID) AND (dbo_CUSTOMER_ORDER.SHIP_TO_ADDR_NO = dbo_CUST_ADDRESS.ADDR_NO)) LEFT JOIN dbo_CUSTOMER ON dbo_RECEIVABLE.CUSTOMER_ID = dbo_CUSTOMER.ID 
    

    becomes:

    FROM dbo_RECEIVABLE 
      RIGHT JOIN dbo_RECEIVABLE_LINE 
        ON  dbo_RECEIVABLE.INVOICE_ID = dbo_RECEIVABLE_LINE.INVOICE_ID
      LEFT JOIN dbo_CUSTOMER_ORDER 
        ON  dbo_RECEIVABLE_LINE.CUST_ORDER_ID = dbo_CUSTOMER_ORDER.ID
      LEFT JOIN dbo_CUST_ADDRESS 
        ON  dbo_CUSTOMER_ORDER.CUSTOMER_ID = dbo_CUST_ADDRESS.CUSTOMER_ID 
        AND dbo_CUSTOMER_ORDER.SHIP_TO_ADDR_NO = dbo_CUST_ADDRESS.ADDR_NO
      LEFT JOIN dbo_CUSTOMER 
        ON  dbo_RECEIVABLE.CUSTOMER_ID = dbo_CUSTOMER.ID 
    

    and then:

    FROM dbo_RECEIVABLE AS rec
      RIGHT JOIN dbo_RECEIVABLE_LINE AS line
        ON  rec.INVOICE_ID = line.INVOICE_ID
      LEFT JOIN dbo_CUSTOMER_ORDER AS ord
        ON  line.CUST_ORDER_ID = ord.ID
      LEFT JOIN dbo_CUST_ADDRESS AS addr
        ON  ord.CUSTOMER_ID = addr.CUSTOMER_ID 
        AND ord.SHIP_TO_ADDR_NO = addr.ADDR_NO
      LEFT JOIN dbo_CUSTOMER AS cust
        ON  rec.CUSTOMER_ID = cust.ID 
    

    adjusting accordingly all the references in the SELECT and GROUP BY.

  • In ORDER BY you can use columns included or aliases defined in the SELECT list. No need to repeat complicated expressions or table aliases:

    ORDER BY STATE, INVOICE_ID; ; 
    
  • By the way, that RIGHT JOIN doesn't look right. Especially since the GROUP BY is based on the nullable side columns (dbo_RECEIVABLE table). It should probably be INNER or LEFT JOIN.

  • the First() function is the most difficult part. You could replace it with MIN() or MAX() but you may not get the same results back and it may not be possible to use this trick in all queries, only those with GROUP BY.
    The equivalent would be quite complicated, using either ROW_NUMBER() and altering the structure of the whole query (wrapping it in a derived table or a CTE and moving the GROUP BY from the internal level to the external one). In 2012+ versions, the FIRST_VALUE() function might be used, too.

  • I will try this. I created something yesterday too, but it isn't working properly. @ypercube – Matthew Oct 22 '15 at 15:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.