0

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?

user2134182
  • 3
  • 2

2 Answers2

0

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.

Cooper
  • 59,616
  • 6
  • 23
  • 54
0
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/alasql@1.7"></script>
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • I am now getting a parse error: SyntaxError: Parse error on line 1: ...FROM ? WHERE [0] IN ? -----------------------^ Expecting 'LITERAL', 'BRALITERAL', 'LPAR', 'AT', 'IF', 'REPLACE', 'DATEADD', 'DATEDIFF', 'INTERVAL', got 'QUESTION' I made a cleaned-up sheet with the GAS code attached and have posted that in the original question above. – user2134182 Aug 22 '22 at 20:39
  • @user2134182 Check edit. Should be fixed now. – TheMaster Aug 22 '22 at 21:51
  • Really appreciate your help with this - looks like I have an array/object problem. Haven't figured out exactly what the problem is yet. I'll update this once I do. I can currently run the code this way without issues: const query = "SELECT MATRIX [0] FROM ? WHERE [0] LIKE ('" +name+"%')"; – user2134182 Aug 23 '22 at 02:35
  • @user2134182 Was there a error thrown with my code? – TheMaster Aug 23 '22 at 07:24
  • There isn't an error with your code per see - it returns an empty array. Meaning it can't find the 'name' in 'values'. The values array is a 3D array and I haven't figured out how to populate it so the query can actually find the 'name' and return the values in the 3rd dimension. Not sure why it works with LIKE (it really shouldn't). I am working on taking it apart and figuring out how to set this up correctly. I am new to JS and it might take me a little while to get it sorted. – user2134182 Aug 23 '22 at 07:36
  • @user2134182 Where does `values` come from? If it comes from a sheet, it would be a 2D array – TheMaster Aug 23 '22 at 07:38
  • It loops though like 40 sheets and gets values from about 15 column from each sheet: let values = []; var range = sheets[i].getRange(9,1,row-9,14).getValues(); values.push(range); Output looks like this: [ [ [ 'Tribiani, Joe', '', 17197.25, '', '', '', '', '', 17197.25, '', '', 0, '', '' ], [ 'Fisher, Mark', ....... – user2134182 Aug 23 '22 at 08:44
  • @user2134182 Do you need 3D? Can't you flatten the array with `values.flat()`? I don't see how sql can work with 3D – TheMaster Aug 23 '22 at 11:29
  • I don't need 3D - it just automatically created the array in 3D. Thanks for the values.flat() idea - will try that next. – user2134182 Aug 23 '22 at 11:36