Transpose SQL code into MATLAB

Hi,

I have an SQL query that is ok in Workbench, the results are I expected:

SELECT * FROM prices.prices WHERE (Var5 <= 125 and Var5 >= 124 OR Var6 <= 125 and Var6 >= 124 OR Var7 <= 125 and Var7 >= 124) AND (Var8 <= 6 and Var8 >= 5 OR Product <= 6 and Product >= 5 OR Var10 <= 6 and Var10 >= 5);

In Matlab I want to do a query in data base, based on 4 variables 2 min and 2 max values that search in 6 different columns . For only 2 variables min and max value work perfectly but if I add the other two variables to extend the selection in other three columns it failed to show wanted results.

This is the half working:

sqlquery = ['SELECT * FROM prices.prices WHERE Var5 <= ',caseta2 ' and Var5 >= ',caseta1 ' OR Var6 <= ',caseta2 ' and Var6 >= ',caseta1 ' OR Var7 <= ',caseta2 ' and Var7 >= ',caseta1];

And full code:

sqlquery = ['SELECT * FROM prices.prices WHERE Var5 <= ',caseta2 ' and Var5 >= ',caseta1 ' OR Var6 <= ',caseta2 ' and Var6 >= ',caseta1 ' OR Var7 <= ',caseta2 ' and Var7 >= ',caseta1 ' AND Var8 <= ',caseta4 ' and Var8 >= ',caseta3 ' OR Product <= ',caseta4 ' and Product >= ',caseta3 ' OR Var10 <= ',caseta4 ' and Var10 >= ',caseta3 ];

selectie = fetch(conn,sqlquery);

In the Workbench I could used paranthesis and "AND" between criteria but in Matlab I don't know how to use it

Answer 1

[Shantanu Dixit]

Hi Cristian,

You can correctly execute your SQL query in MATLAB by ensuring proper syntax and concatenation of strings for your SQL statement. MATLAB "fetch" function doesn't interpret SQL differently from Workbench, but improper string concatenation or syntax can cause issues.

You can fix the errors possibly by following the below steps:

• Proper parentheses in SQL queries ensure correct logical grouping.
• You can use "sprintf" or "strcat" to concatenate strings cleanly and avoid errors.

% Based on the SQL query provided above
% Assuming caseta1, caseta2, caseta3, caseta4 are numeric variables
sqlquery = sprintf(['SELECT * FROM prices.prices WHERE ' ...
    '(Var5 <= %f AND Var5 >= %f OR Var6 <= %f AND Var6 >= %f OR Var7 <= %f AND Var7 >= %f) ' ...
    'AND (Var8 <= %f AND Var8 >= %f OR Product <= %f AND Product >= %f OR Var10 <= %f AND Var10 >= %f)'], ...
    caseta2, caseta1, caseta2, caseta1, caseta2, caseta1, ...
    caseta4, caseta3, caseta4, caseta3, caseta4, caseta3);

selectie = fetch(conn, sqlquery);

The above code ensures the SQL query functions appropriately through proper paranthesis usage.

Additionally you can refer to the detailed MathWorks documentation on "fetch": https://www.mathworks.com/help/database/ug/database.odbc.connection.fetch.html along with the several examples provided.