How can I turn JAQL query into a SQL query?

currently we are using JAQL to query data that we use to create some reports.. but now we want to get the same data by using SQL so I looking for a way to convert our JAQL queries into an equivalent SQL query to get the same data

asael

Posted 11 months ago·Last reply 10 months ago

7 comments

HamzaJ

·11 months ago

It's the following api: POST /datasources/{cubeName}/jaql/sql

asael

OP11 months ago

I tested that endpoint and actually it returned a SQL query based on my JAQL query but that sql query was generated with the table names modified.. I meat that the it added other letters to my table names...

and when I tried to execute those SQL queries on this api : /api/datasources/{cubeName}/sql?query=
it returns error:

in some sql queries return this error

"status": 401,
"httpMessage": "Unauthorized"

in other returns :
"error": true,
"details": "{\"error\":\"Query could not be compiled.\\nFound '.' but expected ')'.\\nFound '.' but expected '<Identifier>'.\\nFound '.' but expected ')'.\\nFound '.' but expected '<Identifier>'.\\nFound '.' but expected '<End of Expression>'.\"}",
"type": "500",

do you know if do i need to take into account something that i did not be aware

asael

OP11 months ago

the sql generated is doing a INNER JOIN with a table that does not exist (i do not have any table in our cube with that name)

DRay

·10 months ago

HamzaJ,

Are you able to continue assisting?

HamzaJ

·10 months ago

Only admins can run sql?query= if I am not mistaken. That jaql/sql endpoint is the only endpoint that translates jaql to sql. If it isnt usable, then I am afraid there is no way to translate a jaql to sql within Sisense itself. You would have to translate it somehow yourself unfortunately.

HamzaJ

·11 months ago

Hey asael,

If i am not mistaken there is an api that does exactly this. If you need to automate the process this could be the way.

If it is more a one of kind of thing , then you can edit the widget , hit the three dots and select sql query. It will give you the sql code

Hamza

asael

OP11 months ago

Hi HamzaJ

What is that API?
I was seeking for something like that but I have not found it..

Actually I am not using any widget of Sisense.. we just use the data that we get form our datasource using JAQL and with that data we create our report (the report is created by us- coding)... maybe in the future we could use some widgets but now we are trying is to convert the JAQL query into a SQL..

can you share to me the API name?