CODEWITHSUNDEEP
javascriptsqlgoogle-apps-scriptalasql
user2134182
user2134182

Reputation: 3

Google Apps Script: Array Reference in SQL Query (Javascript/AlaSQLGS)

I cannot get my query to accept my WHERE entry:

const query = "SELECT MATRIX Col1 FROM ? WHERE Col1 IN (name) ";
var res = alasql(AlaSQLGS.transformQueryColsNotation(query), [values]);
console.log(res.length);
console.log(res[0][0][0]);

res.length returns 0 and I get the following error for res[0][0][0]: "TypeError: Cannot read property '0' of undefined"

Entry for name is coming from an array but after trying everything I could possibly think of, I created a simple var name = "Tribiani, Joe". Didn't work. I can't even get this to work typing in the name:

const query = "SELECT MATRIX Col1 FROM ? WHERE Col1 IN ('Tribiani, Joe') ";

The only way I get this to work is: const query = "SELECT MATRIX Col1 FROM ? WHERE Col1 LIKE ('Tribiani, Joe%') "; but I can't use the variable 'name' in this case either, has to be written in.

Anybody has any ideas what I am doing wrong? And how do I correctly point to an array element in a AlaSQL query?

Upvotes: 0

Views: 405

Answers (2)

TheMaster
TheMaster

Reputation: 50462

const query = "SELECT MATRIX [0] FROM ? WHERE [0] IN @(?) ",
  name = ['Tribiani', 'Joe'],
  res = alasql(query, [values, name]);
console.log(res);

Live demo:

/*<ignore>*/console.config({maximize:true,timeStamps:false,autoScroll:false});/*</ignore>*/ 
const data = [[1,2,3],[4,5,6],[1,4,8]];
const name = [1,2];
const res = alasql('SELECT MATRIX [0] FROM ? WHERE [0] IN @(?)',[data, name]);
console.log(res)   
<script src="https://gh-canon.github.io/stack-snippet-console/console.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/[email protected]"></script>

Upvotes: 1

Cooper
Cooper

Reputation: 64072

Does this work?

var name = "Tribiani, Joe";
const query = `SELECT MATRIX Col1 FROM ? WHERE Col1 IN (${name})`;

I would have put it into a comment but the backticks do not show up properly.

Upvotes: 0

Related Questions