706891_en-US

liliconcom

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)?

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

 

 

Automation and ScriptingWindows