?
Solved

Converting Access queries to T-SQL problems

Posted on 2010-01-05
7
Medium Priority
?
166 Views
Last Modified: 2013-11-27
I'm trying to convert some old Access queries to SQL Server T-SQL queries.  Access yields 354 rows, while the other pulls 338.  Is there something obvious I'm missing?

The top part is the Access code, and the break where the T-SQL code is pretty obvious.  Thanks!
--ACCESS CODE

SELECT DISTINCT tbl_Temp_4.Patient_Id, tbl_Temp_4.FromDate, tbl_Temp_4.Post_Date, tbl_Temp_4.Enc_Units, tbl_Temp_4.LastOfLocation, tbl_Temp_4.Doctor_Id, tbl_Temp_4.LastOfInsurance_Id, tbl_Temp_4.LastOfReport_Payor_Type, tbl_Temp_4_1.LastOfReport_Payor_Type INTO tbl_Temp_5
FROM tbl_Temp_4 INNER JOIN tbl_Temp_4 AS tbl_Temp_4_1 ON (tbl_Temp_4.Patient_Id = tbl_Temp_4_1.Patient_Id) AND (tbl_Temp_4.FromDate = tbl_Temp_4_1.FromDate) AND (tbl_Temp_4.LastOfLocation = tbl_Temp_4_1.LastOfLocation)
WHERE (((tbl_Temp_4.LastOfReport_Payor_Type) Is Null) AND ((tbl_Temp_4_1.LastOfReport_Payor_Type) Is Not Null)) OR (((tbl_Temp_4.LastOfReport_Payor_Type) Is Not Null) AND ((tbl_Temp_4_1.LastOfReport_Payor_Type) Is Null))
ORDER BY tbl_Temp_4.Patient_Id, tbl_Temp_4.FromDate, tbl_Temp_4.LastOfReport_Payor_Type;

/****************************************************************************************
-SQL SERVER CODE
*****************************************************************************************\
SELECT DISTINCT 
                      t1.Patient_Id, t1.FromDate, t1.Post_Date, t1.Enc_Units, t1.LastOfLocation, t1.Doctor_Id, t1.LastOfInsurance_Id, t1.LastOfReport_Payor_Type, 
                      tbl_Temp_4_1.LastOfReport_Payor_Type AS Expr1
FROM         tbl_Temp_4 AS t1 INNER JOIN
                      tbl_Temp_4 AS tbl_Temp_4_1 ON t1.Patient_Id = tbl_Temp_4_1.Patient_Id AND t1.FromDate = tbl_Temp_4_1.FromDate AND 
                      t1.LastOfLocation = tbl_Temp_4_1.LastOfLocation
WHERE     (t1.LastOfReport_Payor_Type IS NULL) AND (tbl_Temp_4_1.LastOfReport_Payor_Type IS NOT NULL) OR
                      (t1.LastOfReport_Payor_Type IS NOT NULL) AND (tbl_Temp_4_1.LastOfReport_Payor_Type IS NULL)
ORDER BY t1.Patient_Id, t1.FromDate, t1.LastOfReport_Payor_Type

Open in new window

0
Comment
Question by:IntercareSupport
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 26185326
try this
SELECT DISTINCT 
                      t1.Patient_Id, t1.FromDate, t1.Post_Date, t1.Enc_Units, t1.LastOfLocation, t1.Doctor_Id, t1.LastOfInsurance_Id, t1.LastOfReport_Payor_Type, 
                      tbl_Temp_4_1.LastOfReport_Payor_Type AS Expr1
FROM tbl_Temp_4 AS t1 
	INNER JOIN tbl_Temp_4 AS tbl_Temp_4_1 ON t1.Patient_Id = tbl_Temp_4_1.Patient_Id 
										 AND t1.FromDate = tbl_Temp_4_1.FromDate 
										 AND t1.LastOfLocation = tbl_Temp_4_1.LastOfLocation
WHERE (t1.LastOfReport_Payor_Type IS NULL) 
  AND (tbl_Temp_4_1.LastOfReport_Payor_Type IS NOT NULL) 
   OR ((t1.LastOfReport_Payor_Type IS NOT NULL) 
  AND (tbl_Temp_4_1.LastOfReport_Payor_Type IS NULL))
ORDER BY t1.Patient_Id, t1.FromDate, t1.LastOfReport_Payor_Type

Open in new window

0
 
LVL 26

Accepted Solution

by:
tigin44 earned 668 total points
ID: 26185356
also this is the clear one
SELECT DISTINCT 
                      t1.Patient_Id, t1.FromDate, t1.Post_Date, t1.Enc_Units, t1.LastOfLocation, t1.Doctor_Id, t1.LastOfInsurance_Id, t1.LastOfReport_Payor_Type, 
                      tbl_Temp_4_1.LastOfReport_Payor_Type AS Expr1
FROM tbl_Temp_4 AS t1 
	INNER JOIN tbl_Temp_4 AS tbl_Temp_4_1 ON t1.Patient_Id = tbl_Temp_4_1.Patient_Id 
										 AND t1.FromDate = tbl_Temp_4_1.FromDate 
										 AND t1.LastOfLocation = tbl_Temp_4_1.LastOfLocation
WHERE t1.LastOfReport_Payor_Type IS NULL 
  AND tbl_Temp_4_1.LastOfReport_Payor_Type IS NOT NULL 
   OR (t1.LastOfReport_Payor_Type IS NOT NULL 
        AND tbl_Temp_4_1.LastOfReport_Payor_Type IS NULL)
ORDER BY t1.Patient_Id, t1.FromDate, t1.LastOfReport_Payor_Type

Open in new window

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26185465
looks same to me, make sure you have exactly the same data, pay attention to datetime columns
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 61

Assisted Solution

by:HainKurt
HainKurt earned 664 total points
ID: 26185486
just this part is a bit confusing...

      (t1.LastOfReport_Payor_Type IS NULL) AND (tbl_Temp_4_1.LastOfReport_Payor_Type IS NOT NULL) OR
      (t1.LastOfReport_Payor_Type IS NOT NULL) AND (tbl_Temp_4_1.LastOfReport_Payor_Type IS NULL)

-->

      ((t1.LastOfReport_Payor_Type IS NULL) AND (tbl_Temp_4_1.LastOfReport_Payor_Type IS NOT NULL))
OR
      ((t1.LastOfReport_Payor_Type IS NOT NULL) AND (tbl_Temp_4_1.LastOfReport_Payor_Type IS NULL))

maybe...
0
 
LVL 10

Expert Comment

by:dwe761
ID: 26191282
Looks like you're missing some parens.  Try below.
SELECT DISTINCT 
                      t1.Patient_Id, t1.FromDate, t1.Post_Date, t1.Enc_Units, t1.LastOfLocation, t1.Doctor_Id, t1.LastOfInsurance_Id, t1.LastOfReport_Payor_Type, 
                      tbl_Temp_4_1.LastOfReport_Payor_Type AS Expr1
FROM         tbl_Temp_4 AS t1 
INNER JOIN tbl_Temp_4 AS tbl_Temp_4_1 
	ON t1.Patient_Id = tbl_Temp_4_1.Patient_Id AND 
		t1.FromDate = tbl_Temp_4_1.FromDate AND 
		t1.LastOfLocation = tbl_Temp_4_1.LastOfLocation
WHERE    
( 
	(t1.LastOfReport_Payor_Type IS NULL) AND (tbl_Temp_4_1.LastOfReport_Payor_Type IS NOT NULL) 
)
OR
(
    (t1.LastOfReport_Payor_Type IS NOT NULL) AND (tbl_Temp_4_1.LastOfReport_Payor_Type IS NULL)
)

ORDER BY t1.Patient_Id, t1.FromDate, t1.LastOfReport_Payor_Type

Open in new window

0
 
LVL 10

Assisted Solution

by:dwe761
dwe761 earned 668 total points
ID: 26191474
Oops.  My bad.  The first time I opened your question, I didn't get any of the prior experts answers. Now after submitting my answer I see that tigin444 and Hainkurt have already given good answers.

To build on Haindurt's reply, also watch out for NULLs because Access is a little more forgiving with NULLs than with T-SQL.
0
 

Author Closing Comment

by:IntercareSupport
ID: 31673199
It was related to the NULLS, I think....  Thanks to all
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question