cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
Neo
Neo
Level VI

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

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");

 

 

When it's too good to be true, it's neither
1 ACCEPTED SOLUTION

Accepted Solutions
jthi
Super User

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

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

-Jarmo

View solution in original post

1 REPLY 1
jthi
Super User

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

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

-Jarmo