SQL to SQLite

  • Can anyone point me to a SQL to SQlite converter? I have tried RebaseData, but it failed.

    The query I want to convert is :

    UPDATE whB
    SET yearclass = top_payclasses.payclass
    FROM dbo.wbridge_history whB
    INNER JOIN (
    SELECT grower, block, section, oesjaar, payclass,
    --COUNT(*) AS payclass_count, SUM(weight) AS payclass_tie_breaker,
    ROW_NUMBER() OVER(PARTITION BY grower, block, section, oesjaar
    ORDER BY COUNT(*) DESC, SUM(net) DESC) AS row_num
    FROM dbo.wbridge_history
    GROUP BY grower, block, section, oesjaar, payclass
    ) AS top_payclasses ON
    top_payclasses.row_num = 1 AND
    top_payclasses.grower = whB.grower AND
    top_payclasses.block = whB.block AND
    top_payclasses.section = whB.section AND
    top_payclasses.oesjaar = whB.oesjaar

    Regards

     

  • Honestly, I don't really trust converters. You're far better off doing this yourself. Yes, it might seem like a longer task, but converters make mistakes, can't replicate certain behaviour, or functionality isn't quite the same between different RDBMS. As a result if you use a converter, you'll likely be spending weeks (months) troubleshooting errors, and unexpected/undesired behaviour for code you haven't written or possible understand.

    Do the work yourself, and you don't have that problem, and things will likely be working as intended far faster.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • You do understand that you're making a major paradigm shift in code, right?  SQL <> SQL between different RDBMS engines.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanx Tom. You are right,but I just cannot figure out how to update an SQlite table without using inner join. I understand SQL, but SQLite has me stumped. I want to update a column from the most used text in a column based on conditions in other columns.

  • Thank you Jeff. Yes I am painfully aware that the code is different. It took me a very long time to figure out the SQL query and am really struggling to change it to sqlite.

  • gideon.e wrote:

    Thank you Jeff. Yes I am painfully aware that the code is different. It took me a very long time to figure out the SQL query and am really struggling to change it to sqlite.

    Have you tried posting in a SQLITE forum? They should know the required syntax better than we do here.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Hi Phil - thank you.  Can you suggest a forum? I have tried https://sqlite.org/support.html, just cannot figure out how to ask a question.

    • This reply was modified 4 years, 3 months ago by  gideon.e.
  • gideon.e wrote:

    Hi Phil - thank you.  Can you suggest a forum? I have tried https://sqlite.org/support.html, just cannot figure out how to ask a question.

    No. I'm a SQL Server guy. That's why I'm in this forum 🙂

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Thank you anyway Phil.

  • I don't know much about sqlite but I doubt if joins in updates are allowed.

    You could try something simple like:

    UPDATE wbridge_history
    SET yearclass =
    (
    SELECT D.payclass
    FROM
    (
    SELECT grower, block, section, oesjaar, payclass,
    ROW_NUMBER() OVER(PARTITION BY grower, block, section, oesjaar
    ORDER BY COUNT(*) DESC, SUM(net) DESC) AS row_num
    FROM wbridge_history
    GROUP BY grower, block, section, oesjaar, payclass
    ) D
    WHERE D.grower = wbridge_history.grower
    AND D.block = wbridge_history.block
    AND D.section = wbridge_history.section
    AND D.oesjaar = wbridge_history.oesjaar
    AND D.row_num = 1
    );

    • This reply was modified 4 years, 3 months ago by  Ken McKelvey.
  • gideon.e wrote:

    Thank you Jeff. Yes I am painfully aware that the code is different. It took me a very long time to figure out the SQL query and am really struggling to change it to sqlite.

    I do feel your pain there.  Imagine how I felt when they forced me to use Oracle for 3 years.  Just like the problem with not being able to do joined Updates (and you can if you write the join as a correlated subquery) and a wad of other stuff.  My way around the UPDATE problem was to use MERGE instead of UPDATEs because you CAN do a joined update that way.  It's almost as easy as writing a joined update in SQL Server.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thank you very much Ken. Works perfectly in SQL, but in Sqlite it complains :

    Error while executing SQL query on database 'weegbrugGeskiedenis': near "(": syntax error

    The squigly line starts at  (PARTITION ....

     

  • Hi all, thank you for your help. The problem seems to be SQL studio. DB Browser (SQLite) as well as a VB.net application executes the query fine.

     

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply