defog

/

sqlcoder-7b-2

like 300

Follow

Defog.ai 94

Hi @akhil2204 sqllite generally has the more liberal query syntax (eg less strict about types) so what you generated in postgres format should work with sqllite. If there are any edge cases that the query doesn't cover, please feel free to share a reproducible example.

Hi @wongjingping I am getting the keyword ILIKE in the query but sqllite expects like.How would I tackle these issues

Hi @wongjingping This is the reproducible example ,If you can see the last line in the query it returns ILIKE.
I want it to be "LIKE".
SELECT DISTINCT p.ProductCode,
p.ProductLine,
p.MSRP
FROM Products p
JOIN Orders o ON p.ProductCode = o.ProductCode
JOIN Customers c ON o.CustomerName = c.CustomerName
WHERE c.CustomerName ILIKE '%kwai%'

If you can see the 4 line in Instruction I have mentioned to use LIKE

Here is the prompt which I have been using:
"""

Task

Generate a SQL query to answer [QUESTION]{question}[/QUESTION]

Instructions

-This schema runs on sqlite server and not postgres server
-The database schema provided is in sqlite3 syntax so make sure you generate sql in sqlite3 format
-If you cannot answer the question with the available database schema, return 'I do not know'
-Instead of ILIKE in the query use "LIKE" in the query
-Instead of 'true or false ' use 1 or 0 in the  query

CREATE TABLE IF NOT EXISTS Products (
    ProductCode TEXT PRIMARY KEY,
    ProductLine TEXT,
    MSRP INTEGER
);### Database Schema
This query will run on a SQLLITE database whose schema is represented in this string:
-ProductCode is the primarykey

CREATE TABLE IF NOT EXISTS Customers (
    CustomerName TEXT PRIMARY KEY,
    Phone TEXT,
    AddressLine1 TEXT,
    AddressLine2 TEXT,
    City TEXT,
    State TEXT,
    PostalCode TEXT,
    Country TEXT,
    Territory TEXT,
    ContactLastName TEXT,
    ContactFirstName TEXT
);
This query will run on a SQLLITE database whose schema is represented in this string:
-CustomerName is the primarykey

CREATE TABLE IF NOT EXISTS Orders (
    OrderNumber INTEGER PRIMARY KEY,
    QuantityOrdered INTEGER NOT NULL,
    PriceEach REAL NOT NULL,
    OrderLineNumber INTEGER,
    Sales REAL,
    OrderDate TEXT NOT NULL,
    Status TEXT,
    Qtr_ID INTEGER,
    Month_ID INTEGER,
    Year_ID INTEGER,
    ProductLine TEXT,
    MSRP INTEGER,
    ProductCode TEXT NOT NULL,
    CustomerName TEXT NOT NULL,
    DealSize TEXT,
    FOREIGN KEY (ProductCode) REFERENCES Products(ProductCode),
    FOREIGN KEY (CustomerName) REFERENCES Customers(CustomerName)
);
This query will run on a SQLLITE database whose schema is represented in this string:
-OrderNumber is the primarykey
-orders.ProductCode can join on Products.ProductCode
-Customers.CustomerName can join on Orders.CustomerName

Answer

Given the database schema, here is the SQL query that answers [QUESTION]{question}[/QUESTION]
[SQL]
"""

was anyone able to work around or fix this issue ?

I think maybe we can use another text-to-text llm model for fixing non-sqllite command. With this extra layer sql sentences could be suitable for sqllite.

You can also have the model correct the error that occurs, perhaps the accuracy rate will not be as low as MSSQL, but correction can be made with a second response cycle.

I settled on a simple replace using python. The generated sql had ilike statement which is not supported by sqlite, so I just replaced it with like.

Just changed the prompt and it worked

Could you please share the prompt that worked for you. I'm also facing similar issues like (CURRENT_DATE - INTERVAL '10 days').
PostgreSQL requires quotes (INTERVAL '10 days'). MySQL requires no quotes and uses DAY (INTERVAL 10 DAY).