Alasql: Error when do JOIN between 2 CSV files...

[tiagowippel]

Hi,

It´s possible to do a JOIN between 2 CSV ?

I have try to do this:

alasql("select * from csv('ITVPE.CSV',{headers:true}) itv \
 join csv('PROPE.CSV',{headers:true}) pro \
 on itv.codipro = pro.codipro",[],(data) => {
  console.log(data);
}); 

get this error on nodejs: _Error: Unknown type of FROM clause_

PROPE.CSV

CODIPRO,NOMEPRO
"01001","TESTE 01001"
"01002","ZTESTE 01002"
"01003","TESTE 01003"

ITVPE.CSV

NRNOTA,CODIPRO,QUANTI
123,"01001",10
123,"01002",20
123,"01003",30
122,"01001",30
122,"01003",50

[mathiasrw]

There is no reason why this should not be possible.

There could be a bug in the parser.

What happens if you do a ... JOIN (SELECT * FROM csv('PROPE.CSV'))...?

---

Bonus info: headers are set to true as default and params can be skipped, so your SQL could also be written as

alasql("select * from csv('ITVPE.CSV') itv \
 join csv('PROPE.CSV') pro \
 on itv.codipro = pro.codipro",(data) => {
  console.log(data);
});

[mathiasrw]

@tiagowippel

What happens if you do a ... JOIN (SELECT * FROM csv('PROPE.CSV'))...?

[tiagowippel]

Hello again,

i have try this:

alasql(
    `select * from CSV('ITVPE.CSV',{headers:true,separator:','}) ITV
    join (select * from CSV('PROPE.CSV',{headers:true,separator:','}) PRO)
    on ITV.CODIPRO = PRO.CODIPRO`,
    (data) => {
        console.log(data);
    });

got this error:

**C:\Users\TI\dev\dev2\node_modules\alasql\dist\alasql.fs.js:2201
throw new SyntaxError(str)
^

SyntaxError: Parse error on line 2:
...ator:','}) PRO) on ITV.CODIPRO = PRO
----------------------^
Expecting 'LITERAL', 'BRALITERAL', 'AS', got 'ON'
at Object.parser.parseError (C:\Users\TI\dev\dev2\node_modules\alasql\dist\alasql.fs.js:2201:8)
...**

[mathiasrw]

Please try replacing

(select * from CSV('PROPE.CSV',{headers:true,separator:','}) PRO)

with

(select * from CSV('PROPE.CSV',{headers:true,separator:','})) PRO

[tiagowippel]

ok..

alasql(
    `select * from CSV('ITVPE.CSV',{headers:true,separator:','}) ITV
    join (select * from CSV('PROPE.CSV',{headers:true,separator:','})) PRO
    on ITV.CODIPRO = PRO.CODIPRO`,
    (data) => {
        console.log(data);
    });

got this error,

C:\Users\TI\dev\dev2\node_modules\alasql\dist\alasql.fs.js:7829
                        source.subquery = tq.compile(query.database.databaseid);
                                             ^

TypeError: tq.compile is not a function
    at C:\Users\TI\dev\dev2\node_modules\alasql\dist\alasql.fs.js:7829:25
    at Array.forEach (native)

[kanghj]

@mathiasrw, I also run into an identical problem when trying to do joins on SELECTs. I'm thinking that this is caused by an error in alasqlparser.jison, https://github.com/agershun/alasql/blob/develop/src/alasqlparser.jison#L980

Under JoinTableAs:

 | LPAR Select RPAR Literal
        { $$ = {select: $1, as: $4} ; }
| LPAR Select RPAR AS Literal
        { $$ = {select: $1, as: $5 } ; }

looks like it should be

| LPAR Select RPAR Literal
        { $$ = {select: $2, as: $4} ; }
| LPAR Select RPAR AS Literal
        { $$ = {select: $2, as: $5 } ; }

(select on the Select term instead of the left parenthesis)

Otherwise, the value of tq is the left parenthesis which does not have a compile function. I'll try to verify this sometime later, and submit a pull request for further discussion, along with a test.

[kanghj]

Anyway, just to update this ticket, the query

select * from CSV('ITVPE.CSV',{headers:true,separator:','}) AS ITV
join (select CODIPRO,NOMEPRO from CSV('PROPE.CSV',{headers:true,separator:','})) AS PRO
 ON ITV.CODIPRO = PRO.CODIPRO

now results in a different error:

TypeError: Cannot read property 'data' of undefined
      at Object.source.datafn (.../alasql.fs.js:7836:57)
      at .../alasql.fs.js:6238:19
               `

[tiagowippel]

Little weird, but this actually works...

alasql(`select * from CSV('PROPE.CSV',{headers:true,separator:','})`,(prodata)=>{
        console.log(prodata);
        alasql(`select * from CSV('ITVPE.CSV',{headers:true,separator:','})`,(itvdata)=>{
            console.log(itvdata);
          alasql(`select * from ? as PROPE
                  join ? as ITVPE
                  on PROPE.CODIPRO = ITVPE.CODIPRO
                  order by ITVPE.NRNOTA`,[prodata,itvdata],(data)=>{
              console.log(data);
          });
      });
    });

[mathiasrw]

Nice !

thanks for the work-around!