Click here to Skip to main content
15,029,958 members
Please Sign up or sign in to vote.
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
Updated 19-Mar-21 3:29am
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

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
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900