SQL to SQLite

  • gideon.e

    SSC Veteran

    Points: 273

    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

     

  • Thom A

    SSC Guru

    Points: 98461

    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.

  • Jeff Moden

    SSC Guru

    Points: 995161

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • gideon.e

    SSC Veteran

    Points: 273

    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.

  • gideon.e

    SSC Veteran

    Points: 273

    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.

  • Phil Parkin

    SSC Guru

    Points: 243862

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • gideon.e

    SSC Veteran

    Points: 273

    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 1 week, 4 days ago by  gideon.e.
  • Phil Parkin

    SSC Guru

    Points: 243862

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • gideon.e

    SSC Veteran

    Points: 273

    Thank you anyway Phil.

  • Ken McKelvey

    SSCoach

    Points: 18242

    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 1 week, 4 days ago by  Ken McKelvey.
  • Jeff Moden

    SSC Guru

    Points: 995161

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • gideon.e

    SSC Veteran

    Points: 273

    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 ....

     

  • gideon.e

    SSC Veteran

    Points: 273

    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 13 (of 13 total)

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