466258_en-US

liliconcom

How to assign Multiple values to SQL variable

Hi,

 

I'm trying to input multiple values from the jmp file into SQL variable. Something doesn't seem to work

 

User will input the following file and the columns values are stored in the variable specVal .

I declared @ SPEC = ^specVal^ (Contains 3 values ) in SQL 

 

Something doesn't seem to be correct. 

 

 

Names Default To Here( 1 );
Clear Globals();
Clear Log();

specVal = "";
NW = New Window( "DLT Data",
	<<MODAL></MODAL>

Thanks

 

Advanced Statistical ModelingConsumer and Market ResearchData AccessDesign of ExperimentsQuality and Process EngineeringRe: How to assign Multiple values to SQL variable

I really appreciate this. Thanks a LOT

Re: How to assign Multiple values to SQL variable

@jthi is on the right track.  Print out the SQL code and try running it manually in SQL server.  

I would point a variable at the data table to avoid scoping errors.

This is what your SQL code looks like:

DECLARE @SPEC1 VARCHAR (50)
SET @SPEC1 = '{"F14CDCE", "F141CDC5", "F141CDLU"}'
SELECT t1.[vcLotCode] 
FROM [dbo].[vSMTDatat]  t1 
WHERE  (  (  (  ( t1.[vcLotCode] IN  ( @SPEC1 )  )  )  ) ;

Will that work in SQL Server?  In Oracle I create IN lists and substitute them like this:

 

// Point dt to your table of values
dt = New Table( "SpecValues", Add Rows( 3 ),
 New Column( "Column 1", Character, "Nominal",
  Set Values( {"F14CDCE", "F141CDC5", "F141CDLU"} ) )
);

// Relevant section to create IN list
   Button Box( "OK",
    specVal = dt:Column 1 << Get Values;
    spec_in_list = "'" || concat items(specval, "', '") || "'";
    specVal1 = Start_date << get text();
    specVal2 = End_date << get text();
   ),

// SQL Code
 SQL = Eval Insert(
"SELECT t1.[vcLotCode] 
   FROM [dbo].[vSMTDatat]  t1 
  WHERE  (  (  (  ( t1.[vcLotCode] IN  ( ^spec_in_list^ )  )  )  ) ;" // Input from the user
 ) ; 
 write(sql);

Re: How to assign Multiple values to SQL variable

I would suggest printing the SQL-query to see that it is being built as you want it to be built.

Re: How to assign Multiple values to SQL variable

@txnelson 

Any suggestion?