JSL function with SQL Join pulling data rows 3 times compared to one without JOIN -why?

Neo — Thu, 07 Dec 2023 20:06:26 GMT

The first function below with SQL INNER JOIN pulls each data rows (for LNo) two additional times from the database compared to the second function below without JOIN. Where am I going wrong?

Names Default To Here( 1 );
opnDtb = Function( {R, M, T}, {dtm}, 
	dtm = Open Database(
	"Driver=SQL Server;
	Server=ABCD-EFGH;
	DATABASE=myDB;
    Trusted_Connection=Yes;",
		"SELECT SM.[LNo], SM.[TestDate] AS [orgTestDate], SM.[Median], MT.[EntryDate] AS [TestDate]
	     FROM [myDB].[dbo].[P_STD_" || R|| "_" || T || "_" || M || "_SUMMARY] AS SM 
	     INNER JOIN [myDB].[dbo].[P_STD_" || R || "_" || T || "_" || M || "_META] AS MT 
	     ON SM.LNo =  MT.LNo"
	);
	Return( dtm );
);
////TEST  
clear log (); R  =  "D12"; T = "7"; M = "5"; dtm = opnDtb(R, M, T); dtm << set name ("sTable");

Names Default To Here( 1 );
opnDtb = Function( {R, M, T}, {dtm}, 
	dtm = Open Database(
	"Driver=SQL Server;
	Server=ABCD-EFGH;
	DATABASE=myDB;
    Trusted_Connection=Yes;",
		"SELECT SM.[LNo], SM.[TestDate], SM.[Median] FROM [myDB].[dbo].[P_STD_" || R|| "_" || T || "_" || M || "_SUMMARY] AS SM"
	);
	Return( dtm );
);
////TEST  
clear log (); R  =  "D12"; T = "7"; M = "5"; dtm = opnDtb(R, M, T); dtm << set name ("sTable");

Re: JSL function with SQL Join pulling data rows 3 times compared to one without JOIN -why?

jthi — Thu, 07 Dec 2023 17:59:30 GMT

Have you checked what type of data you have in MT table (maybe there are multiple rows for each LNo in that table)?

topic JSL function with SQL Join pulling data rows 3 times compared to one without JOIN -why? in Discussions

JSL function with SQL Join pulling data rows 3 times compared to one without JOIN -why?

Re: JSL function with SQL Join pulling data rows 3 times compared to one without JOIN -why?