Convert query from SQL server to sqlite

Question

0.00/5 (No votes)

See more:

I'm converting an existing application from an SQL Server database to SQLite I converted the tables and a lot of the queries but I couldn't convert this complex query and I need help

Here is my query:

What I have tried:

dBase

SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY 
(SELEC 1)) num_col, ID, cust_id, final, value, date, sum(balance) OVER (partition BY cust_id
ORDER BY date, seq, ID) balance, note
FROM (SELECT ID, cust_id, final, 0, final balance, date, note, 0
FROM transfers UNION ALL
SELECT ID, cust_id, 0, value, - value, date, note, 1
FROM trans_payments) t (ID, cust_id, final, value, balance, date, note, seq)
ORDER BY cust_id, date, seq

Posted 17-Mar-21 2:10am

Rabee Qabaha

Updated 19-Mar-21 3:29am

Add a Solution

Comments

Maciej Los 17-Mar-21 8:17am

What's wrong with above query? ROW_NUMBER is a part of SQLite database. UNION ALL too.

Rabee Qabaha 17-Mar-21 8:32am

I have an error on Top I know it's limit in SQLite, I'm getting also an error on (SELEC 1)) num_col, I tried a lot But I couldn't convert it I'm new to SQLite

Maciej Los 17-Mar-21 9:23am

Shouldn't be a 'SELECT'?

Richard MacCutchan 17-Mar-21 9:00am

The verb is SELECT not SELEC.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2021-03-19T03:29:00

Quite apart from your misspelling of SELECT there are several other aspects you will need to change. Exactly how and what depends on which version of SqlLite you are using and you have not shared that information.

A good starting point is always the documentation - e.g. If you have 3.5 or better, to change your Window functions ROW_NUMBER and SUM you will need to conform to the syntax at Window Functions[^]

Equally, if you look up the documentation for SELECT[^] you will see that the LIMIT will be at the end of the query so instead of

SQL

SELECT TOP 1000000 
... 
ORDER BY cust_id, date, seq;

You will have

SQL

SELECT
...
ORDER BY cust_id, date, seq
LIMIT 1000000;

I will say here though, 1,000,000> is a big limit! Most of us usually use just 1,000 (or smaller) whether it be for debugging purposes or limiting extracts intended for UI components.

I also think (I may be wrong) that you will need to change the bit

SQL

FROM trans_payments) t (ID, cust_id, final, value, balance, date, note, seq)

I'm pretty sure you have to use ) AS t and I don't think SqlLite supports the list of column names like that. If not then you can name the columns in your sub-query e.g.

SQL

SELECT ID, cust_id, final, 0 AS [Value], [final balance], [date], note, 0 AS seq

Note that I have put square brackets around [final balance] - you cannot have spaces in column names unless you delimit them. SQL Server and SQLLite use [ and ], other languages may have other delimiters.
I've done the same with [date] and [value] because those are reserved words

That is hopefully enough to get you going