Page 1 of 2 12 LastLast
Results 1 to 40 of 41

Thread: Very complicated but perhaps possible!

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Very complicated but perhaps possible!

    hello vbforums
    I have a very complicated query I'm asking experts if it is possible to run this query.
    This is my table

    Attachment 180822
    Name:  pic1.png
Views: 385
Size:  5.4 KB

    As shown in table the record has a debt of 60 + 10 + 20 = 90 E
    Now the record pays 80 E, My question:
    Is it possible to distribute the 80E on the 3 items?
    In other words:
    Item1 ..... (+ 60 = 100) (remain = 0)
    Item2 ...... (+ 10 = 20) (remain = 0)
    Item3 ...... (+ 10 = 20) (remain = 10)
    If this is possible , how would be the query please?
    Thank you

  2. #2
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: Very complicated but perhaps possible!

    I don’t think you can do this with a single query.

    It took me a while to see that each record is a kind of transaction.

    First you need to select the records which matter.
    The ones you made red. That’s not that obvious just based on these records.
    When you have the records in a recordset you walk through them and subtract the remainder from the payment. Then create a new transaction and proceed to the next record when there’s still money left

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: Very complicated but perhaps possible!

    Thank you sir
    Code:
    "Select * from  tbl where  acquit  = '0' And  id = '1'Group by Item_Id"
    Rs.MoveLast
    Rs!deposit = val(txt_payment.Text) - Rs! Remainder
    Rs! acquit = 1
    Now Item 1 is acquitted, I move to the second item
    Code:
    "Select * from  tbl where  acquit  = '0' And  id = '1'Group by Item_Id"
    Rs.MoveLast
    Rs!deposit = val(txt_payment.Text) - Rs! Remainder
    Rs! acquit = 1
    Now Item 2 is acquitted, I move to the third item
    I’m not testing on the project
    I’m just trying to see if this is what you suggest.
    thanks

  4. #4
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: Very complicated but perhaps possible!

    You also have to deduct the paid value from the payment value.
    And you have to whether you don’t take to much from the payment value.

  5. #5
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Very complicated but perhaps possible!

    This can be done with a single query - here are the "steps". I would do it in a SPROC, with a temp table or table variable so it's easier to read.

    If that is not possible then each "step" either needs to be a view, or a derived query placed in ()'s, so you end up with lots of sub-queries.

    Here are the steps.

    1) Use RowNumber() to order the ItemId's - use the order you want to take them from "first" as "1"
    ...Next sub-query, or view
    2) Sum based on the GROUP'ing of that RowNumber() column
    ...next
    3) Select each Row's SUM and then "add" to that a sub-query of the "rows" that are less than this "row"
    ...at this point I know id 1 as "60", id 2 as "70" (that's the sum of 60 and 10), and id 3 as "90" (60+10+20). If we had an id 4 it would be a bigger value and so on...
    4) Now you have two possibilities...
    4a) One of these "Row" sum's exactly matches your payment of 80 - none of ours does that...
    4b) One of these Row" sums is the MIN(ROW) that is GREATER than 80 - that would be id 3.
    5) Every Id below this "target" id gets fully paid.
    6) If the "target" id sum matches the payment, then apply the full "need", otherwise apply the "payment" minus the SUM(of the ROW one below the target row).

    Simple - right?

    In a SPROC this would be 5 or so action queries.

    Without a SPROC 5 complicated sub-queries
    Last edited by szlamany; Mar 27th, 2021 at 11:27 AM.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  6. #6
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: Very complicated but perhaps possible!

    I would prefer I small subroutine instead, but that’s my personal preference

  7. #7

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: Very complicated but perhaps possible!

    szlamany
    I am so grateful to you for your interest and your time
    However what you are suggesting looks out of my reach.

    Arnoutdv
    I thank you very much for your help
    Is there a simpler approach I can follow.

    thank you all

  8. #8
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    Re: Very complicated but perhaps possible!

    Don't you have a unique identification for each row?
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  9. #9

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: Very complicated but perhaps possible!

    Quote Originally Posted by dee-u View Post
    Don't you have a unique identification for each row?
    Thank you for the interest sir
    I have an auto increment column

    Name:  pic1.png
Views: 375
Size:  6.0 KB
    Last edited by newbie2; Mar 27th, 2021 at 06:01 PM.

  10. #10
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Very complicated but perhaps possible!

    What type of SQL are you using? MS SQL? MySQL?

    Do you dislike making VIEW's?

    Are you comfortable working with sub-queries and deeply nested queries?

    Answer those three questions for me please.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  11. #11

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: Very complicated but perhaps possible!

    What type of SQL are you using? MS SQL? MySQL?
    I'm using SQlite and RC5 same as ADO

    Do you dislike making VIEW's?
    As far as I know views are virtual tables, but I have not enough experience with that.
    Are you comfortable working with sub-queries and deeply nested queries?
    Sub queries yes but nested queries no idea.
    thanks

  12. #12
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Very complicated but perhaps possible!

    Ok - hopefully you have some kind of query editor window that you can place this in and run it. I've got MS SQL and SQL Server Management Studio to play with here.

    The example queries below build a table with your data that I can play with. I then show you the queries for steps 1, 2 and 3 that I laid out. Please note that after seeing your data more closely that the steps are similar to what I said initially, but got fine tuned as I wrote them.

    Code:
    Create Table #Trans (Aut int, ID int, Item_id int, price int, deposit int, Total_deposit int, remain int, acquit int)
    
    Insert into #Trans values 
    (1,1,1,100,10,10,90,0),
    (2,1,1,100,10,20,80,0),
    (3,1,1,100,20,40,60,0),
    (4,1,2,20,4,4,16,0),
    (5,1,2,20,6,10,10,0),
    (6,1,3,30,5,5,25,0),
    (7,1,3,30,10,15,20,0),
    (8,1,4,20,20,20,0,1)
    
    Select * From #Trans
    
    --1) Use RowNumber() to order the ItemId's - use the order you want to take them from "first" as "1"
    
    Select Item_id,Max(Aut) "MaxAut",Row_Number() Over (Order by Item_id) "RowNum"
    	From #Trans Group by Item_id
    
    --2) Sum based on the GROUP'ing of that RowNumber() column
    
    Select X1.Item_id,TT.remain,X1.MaxAut,X1.RowNum
    	From (Select Item_id,Max(Aut) "MaxAut",Row_Number() Over (Order by Item_id) "RowNum"
    				From #Trans Group by Item_id) as X1
    	Left Join #Trans TT on TT.Aut=X1.MaxAut
    
    --3) Select each Row's SUM and then "add" to that a sub-query of the "rows" that are less than this "row"
    
    -- This is a "test" run of the query WITHOUT the "RunningRemain" field in place
    
    Select X2.*
    		, X2.remain	-- "(Select 0)" below is a "holding" spot for the sub-query described next
    			+IsNull((Select 0),0) "RunningRemain"
    	From (Select X1.Item_id,TT.remain,X1.RowNum
    			From (Select Item_id,Max(Aut) "MaxAut",Row_Number() Over (Order by Item_id) "RowNum"
    						From #Trans Group by Item_id) as X1
    			Left Join #Trans TT on TT.Aut=X1.MaxAut) as X2
    
    -- *****************************
    -- The query below is running the same query above but doing it
    -- for Item_Id less then 3 and returning a SUM() of the "remains"
    -- This is needed in the PRIORREMAINS sub-query spot
    
    Select Sum(X2a.remain) "PriorRemains"
    	From (Select X1a.Item_id,TTa.remain,X1a.RowNum
    			From (Select Item_id,Max(Aut) "MaxAut",Row_Number() Over (Order by Item_id) "RowNum"
    						From #Trans Group by Item_id) as X1a
    			Left Join #Trans TTa on TTa.Aut=X1a.MaxAut) as X2a
    	Where X2a.Item_id<3	
    
    -- ******
    -- The query below now has the PriorRemains (SELECT 0) query replaced with the SUM() above
    Select X2.*
    		, X2.remain
    			+IsNull((Select Sum(X2a.remain) "PriorRemains"
    						From (Select X1a.Item_id,TTa.remain,X1a.RowNum
    								From (Select Item_id,Max(Aut) "MaxAut",Row_Number() Over (Order by Item_id) "RowNum"
    											From #Trans Group by Item_id) as X1a
    								Left Join #Trans TTa on TTa.Aut=X1a.MaxAut) as X2a
    						Where X2a.Item_id<X2.Item_id),0) "RunningRemain"
    	From (Select X1.Item_id,TT.remain,X1.RowNum
    			From (Select Item_id,Max(Aut) "MaxAut",Row_Number() Over (Order by Item_id) "RowNum"
    						From #Trans Group by Item_id) as X1
    			Left Join #Trans TT on TT.Aut=X1.MaxAut) as X2
    The results of running the queries above gives you the following output.

    Code:
    (8 rows affected)
    Aut         ID          Item_id     price       deposit     Total_deposit remain      acquit
    ----------- ----------- ----------- ----------- ----------- ------------- ----------- -----------
    1           1           1           100         10          10            90          0
    2           1           1           100         10          20            80          0
    3           1           1           100         20          40            60          0
    4           1           2           20          4           4             16          0
    5           1           2           20          6           10            10          0
    6           1           3           30          5           5             25          0
    7           1           3           30          10          15            20          0
    8           1           4           20          20          20            0           1
    
    (8 rows affected)
    
    Item_id     MaxAut      RowNum
    ----------- ----------- --------------------
    1           3           1
    2           5           2
    3           7           3
    4           8           4
    
    (4 rows affected)
    
    Item_id     remain      MaxAut      RowNum
    ----------- ----------- ----------- --------------------
    1           60          3           1
    2           10          5           2
    3           20          7           3
    4           0           8           4
    
    (4 rows affected)
    
    Item_id     remain      RowNum               RunningRemain
    ----------- ----------- -------------------- -------------
    1           60          1                    60
    2           10          2                    10
    3           20          3                    20
    4           0           4                    0
    
    (4 rows affected)
    
    PriorRemains
    ------------
    70
    
    (1 row affected)
    
    Item_id     remain      RowNum               RunningRemain
    ----------- ----------- -------------------- -------------
    1           60          1                    60
    2           10          2                    70
    3           20          3                    90
    4           0           4                    90
    
    (4 rows affected)
    
    
    Completion time: 2021-03-28T13:50:06.5387996-04:00
    The final result set (using that final query shown) is what you need to build from your data. I would make that a VIEW so step 4 is easier for you.

    Hopefully you have the time to digest and understand everything I posted and you are able to apply it to your data.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  13. #13

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: Very complicated but perhaps possible!

    I'm so so sorry for bothering you sir
    you are so helpful and so kind
    I spent more than 4 hours trying to adapt the codes to sqlite but syntax seems different from MS SQL.
    I must wait for someone who is talented in sqlite perhaps he can do the task.
    million thanks

  14. #14
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: Very complicated but perhaps possible!

    I don't have much time at the moment.
    I will later this day have a look at it

  15. #15

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: Very complicated but perhaps possible!

    Quote Originally Posted by Arnoutdv View Post
    I don't have much time at the moment.
    I will later this day have a look at it
    very glad to hear that
    thank you very much

  16. #16
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Very complicated but perhaps possible!

    Done in SQLite
    SQL Code:
    1. SELECT DISTINCT
    2. T1.aut,
    3. T2.remain
    4. FROM
    5. trans As T1
    6. LEFT JOIN
    7. (SELECT Aut,ID, remain, Acquit,ROW_NUMBER() OVER(PARTITION BY item_id ORDER BY Aut DESC) AS RN
    8. FROM  Trans) As T2
    9. ON
    10. T1.ID=T2.ID AND
    11. T1.Aut=T2.Aut AND
    12. T2.RN=1 AND
    13. T2.Acquit=0
    14. WHERE T2.remain is not null

    Returns
    Code:
    Aut remain
    3 60
    5 10
    7 20
    EDIT: Now you have the Primary key to the last Record per item_id, where "remain" is not 0 (could even check in query --> Sanity-check)
    Now you just have to loop through it and fire of "UPDATE"-Statements
    If "remain" falls to zero set "acquit" to 1
    Example:
    Blanket Payment = 80
    RecordSet.MoveFirst
    Loop
    Balance = RecordSet("remain") - Payment
    If balance<=0 Then
    Execute "UPDATE Transactions SET remain=0, acquit=1 WHERE Aut=" & RecordSet("Aut")
    Payment = Abs(Balance)
    If Payment=0 Then Exit Loop
    RecordSet.MoveNext
    Else
    Execute "UPDATE Transactions SET remain=remain-" & Balance & " WHERE Aut=" & RecordSet("Aut")
    Exit Loop
    End If
    End Loop
    Last edited by Zvoni; Mar 29th, 2021 at 07:14 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  17. #17

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: Very complicated but perhaps possible!

    Thank you very much Zvoni
    I have been struggling to make the first query into practice but I'm facing with this error.
    I made all possible change but still with no luck.

    Attachment 180848

    Name:  29-03-2021 17-46-22.png
Views: 292
Size:  5.3 KB

    Code:
    StrSql = " SELECT DISTINCT " & _
    " T1.aut, " & _
    " t2.remain" & _
    "  From" & _
    " trans As t1" & _
    " Left Join" & _
    " (SELECT Aut,ID, remain, Acquit,ROW_NUMBER() OVER(PARTITION BY item_id ORDER BY Aut DESC) AS RN" & _
    " FROM  Trans) As T2" & _
    " ON" & _
    " T1.ID=T2.ID AND" & _
    " T1.Aut=T2.Aut AND" & _
    " T2.RN=1 AND" & _
    " t2.Acquit = 0" & _
    " Where t2.remain Is Not Null"
    Set Rs = Cnn.OpenRecordset(StrSql)
    thank you

  18. #18
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Very complicated but perhaps possible!

    Strange.
    I did that Query in DB-Browser for SQLite (sqlitebrowser.org), and it worked.
    Maybe you have a typo or missing blank/comma somewhere
    Could you try to run that query in a DB-Tool like mine?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  19. #19

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: Very complicated but perhaps possible!

    Thank you sir
    I run the query in DB-Browser and it is throwing the same error
    This is exactly the error:

    Code:
    near "(": syntax error: SELECT DISTINCT
    T1.aut,
    t2.remain
    From
    trans As T1
    Left Join
    (SELECT Aut,ID, remain, Acquit,ROW_NUMBER() OVER(

  20. #20
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: Very complicated but perhaps possible!

    The table name in the detailed sample is "trans"
    Your table is not called "trans" is it?

    "Select * from tbl where acquit = '0' And id = '1'Group by Item_Id"

  21. #21
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Very complicated but perhaps possible!

    Quote Originally Posted by Arnoutdv View Post
    The table name in the detailed sample is "trans"
    Your table is not called "trans" is it?
    *groan**facepalm*
    Missed that one
    I actually used Arnout's CREATE TABLE/INSERT INTO Statements to create my sample data
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  22. #22
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: Very complicated but perhaps possible!

    You used the extended sample by szlamany, my SQL knowledge is limited to basic queries.
    I tend to solve more complex operations using code instead of queries.

  23. #23
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Very complicated but perhaps possible!

    Eh?
    True.
    It's time for some offtime..... overworked, underjumped, the whole slew......
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  24. #24

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: Very complicated but perhaps possible!

    Quote Originally Posted by Arnoutdv View Post
    The table name in the detailed sample is "trans"
    Your table is not called "trans" is it?
    I created a new table with the same fields based on szlamany demo.
    thanks

  25. #25
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Very complicated but perhaps possible!

    Quote Originally Posted by newbie2 View Post
    I created a new table with the same fields based on szlamany demo.
    thanks
    So it works?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  26. #26

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: Very complicated but perhaps possible!

    still no succes

  27. #27
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Very complicated but perhaps possible!

    Try it as a single line
    Code:
    SELECT DISTINCT T1.aut, T2.remain FROM trans As T1 LEFT JOIN (SELECT Aut, ID, remain, Acquit, ROW_NUMBER() OVER(PARTITION BY item_id ORDER BY Aut DESC) AS RN FROM  Trans) As T2 ON T1.ID=T2.ID AND T1.Aut=T2.Aut AND T2.RN=1 AND T2.Acquit=0 WHERE T2.remain is not null
    It definitely works for me in SQLite
    So no idea what's different with your system
    SQL Code:
    1. SELECT DISTINCT
    2. T1.aut,
    3. T2.remain
    4.  
    5. FROM trans As T1
    6.  
    7. LEFT JOIN
    8.     (SELECT
    9.         Aut,
    10.         ID,
    11.         remain,
    12.         Acquit,
    13.         ROW_NUMBER() OVER(PARTITION BY item_id ORDER BY Aut DESC) AS RN
    14.         FROM  Trans) As T2
    15. ON
    16. T1.ID=T2.ID AND
    17. T1.Aut=T2.Aut AND
    18. T2.RN=1 AND
    19. T2.Acquit=0
    20.  
    21. WHERE
    22. T2.remain is not null
    Last edited by Zvoni; Mar 30th, 2021 at 08:39 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  28. #28

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: Very complicated but perhaps possible!

    Same error all the times even with single lines

    This is my code for table creating
    Code:
    Cnn.Execute "Create Table Trans (Aut int, ID int, Item_id int, price int, deposit int, Total_deposit int, remain int, acquit int)"
    Cnn.Execute " into Trans values " & _
    "(1,1,1,100,10,10,90,0)," & _
    " (2,1,1,100,10,20,80,0)," & _
    " (3,1,1,100,20,40,60,0)," & _
    " (4,1,2,20,4,4,16,0)," & _
    " (5,1,2,20,6,10,10,0)," & _
    " (6,1,3,30,5,5,25,0)," & _
    " (7,1,3,30,10,15,20,0)," & _
    " (8,1,4,20,20,20,0,1)"
    End Sub
    I run your code on the form or in db browser I get the error:
    Code:
    near "(": syntax error: SELECT DISTINCT T1.aut, T2.remain FROM trans As T1 LEFT JOIN (SELECT Aut, ID, remain, Acquit, ROW_NUMBER() OVER(

  29. #29

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: Very complicated but perhaps possible!

    I suspect the error resides somewhere here in this piece:
    Code:
    LEFT JOIN 
        (SELECT 
            Aut,
            ID, 
            remain, 
            Acquit,
            ROW_NUMBER() OVER(PARTITION BY item_id ORDER BY Aut DESC)

  30. #30

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: Very complicated but perhaps possible!

    After some googling I think SQlite does not support ROW_NUMBER() OVER PARTITION
    but I think there is an alternative.
    However this seems very complicated regarding my modest knowledge.

  31. #31
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: Very complicated but perhaps possible!

    Oh yes it does:
    https://sqlite.org/windowfunctions.html
    https://www.sqltutorial.org/sql-wind...ql-row_number/

    Which version of sqlite do you use?
    Window function support was first added to SQLite with release version 3.25.0 (2018-09-15)

  32. #32

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: Very complicated but perhaps possible!

    I was using an old version 3.22 but now I'm using the last version 3.35.3
    However my trouble still continue.
    When I started googling about my error, I found many people were complaining about the same error.
    Perhaps these topics are old.
    However I wonder for my case, why I'm still having this error which gives the impression that ROW_NUMBER() OVER PARTITION is not supported.

  33. #33

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: Very complicated but perhaps possible!

    I followed your link and I tested the sample they provide but I 'm always having the same mistake either in db browser or on the form.
    Code:
    Cnn.Execute "CREATE TABLE t0(x INTEGER PRIMARY KEY, y TEXT);"
    Cnn.Execute "INSERT INTO t0 VALUES (1, 'aaa'), (2, 'ccc'), (3, 'bbb')"
    
    StrSql = "SELECT x, y, row_number() OVER (ORDER BY y) AS row_number FROM t0 ORDER BY x"
    Set Rs = Cnn.OpenRecordset(StrSql)
    this is the error thrown in db browser
    Code:
    near "(": syntax error: SELECT x, y, row_number() OVER (

  34. #34
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: Very complicated but perhaps possible!

    Which DB browser are you using?
    And which version.

    You are using vbRichClient don’t you? Then you can’t just download a new sqlite dll.
    Which version of vbRichClient are you using?

    Check the version you are actually using:
    SELECT sqlite_version() AS version;

  35. #35
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Very complicated but perhaps possible!

    Quote Originally Posted by Arnoutdv;[URL="tel:5516462"
    5516462[/URL]]Which DB browser are you using?
    And which version.

    You are using vbRichClient don’t you? Then you can’t just download a new sqlite dll.
    Which version of vbRichClient are you using?

    Check the version you are actually using:
    SELECT sqlite_version() AS version;
    He‘s using RC5
    See post #11
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  36. #36
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: Very complicated but perhaps possible!

    Yes he's using RC5, but maybe an older release which predates the minimum SQLite version
    At least version 5.0.68 is needed:
    Version: 5.0.68
    - vb_cairo_sqlite.dll now updated to recent SQLite 3.28.0 (with support for window-functions, plus the sqlar-extension)
    - fix on cArrayList in vbVariant-Mode (when Objects were store in those Variants, and .Pop or .Dequeue Methods were called)
    - smaller fixes/enhancements on cFormula (change of Operator-precedence of / and \ ... as well as support for e-Notation of numbers)
    - added an additional Cairo-FontFallback in case an OutputString contains symbols in the "emoji-category"
    Last edited by Arnoutdv; Mar 31st, 2021 at 04:29 AM.

  37. #37
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Very complicated but perhaps possible!

    Would it explain why he fails in DB-Browser?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  38. #38
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: Very complicated but perhaps possible!

    Also outdated??

    Sorry, but I don't have clue why it doesn't work for newbie2

    *edit:
    I just tried it in DB Browser for SQLite Version 3.12.1 and it works fine for me too.

    File -> New In-Memory Database
    Tab -> Execute SQL

    First:
    Code:
    Create Table Trans (Aut int, ID int, Item_id int, price int, deposit int, Total_deposit int, remain int, acquit int)
    Second:
    Code:
    Insert into Trans values 
    (1,1,1,100,10,10,90,0),
    (2,1,1,100,10,20,80,0),
    (3,1,1,100,20,40,60,0),
    (4,1,2,20,4,4,16,0),
    (5,1,2,20,6,10,10,0),
    (6,1,3,30,5,5,25,0),
    (7,1,3,30,10,15,20,0),
    (8,1,4,20,20,20,0,1)
    Third:
    Code:
    SELECT DISTINCT T1.aut, T2.remain FROM trans As T1 LEFT JOIN (SELECT Aut, ID, remain, Acquit, ROW_NUMBER() OVER(PARTITION BY item_id ORDER BY Aut DESC) AS RN FROM  Trans) As T2 ON T1.ID=T2.ID AND T1.Aut=T2.Aut AND T2.RN=1 AND T2.Acquit=0 WHERE T2.remain is not null
    Result
    Code:
    3  60
    5  10
    7  20
    Last edited by Arnoutdv; Mar 31st, 2021 at 06:59 AM.

  39. #39
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Very complicated but perhaps possible!

    Well, there we go.
    it‘s now on him
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  40. #40

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: Very complicated but perhaps possible!

    Thak you gentelmen . I'm so grateful to you all
    I took profit of all your notes and updated Sqlite and RC5
    And now I'm on the way to find my hapiness.
    except for some worries.
    This is the initial data.

    Name:  pic0.png
Views: 240
Size:  5.7 KB

    After running the codes I get the following output :

    Name:  pic1.png
Views: 235
Size:  5.7 KB


    But I think the query needs some refinement.
    What I was expecting is this:

    Name:  pic2.png
Views: 238
Size:  6.7 KB

    The remain is 85
    The new payment is 80
    this means there is still 5 as debt.
    So the last item is not fully acquitted.

    Also I need to update the deposit and total_deposit too, so as things would look coherent.
    I was able to update the total_deposit
    Code:
    Cnn.Execute "UPDATE trans SET total_deposit=price, remain=0, acquit=1 WHERE Aut=" & Rs("Aut")
    But I can't update the deposit.
    Thank you very much
    Last edited by newbie2; Mar 31st, 2021 at 04:43 PM.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width