PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
[RESOLVED] SQL Two Column Comparing Error-VBForums
Results 1 to 9 of 9

Thread: [RESOLVED] SQL Two Column Comparing Error

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    25

    Resolved [RESOLVED] SQL Two Column Comparing Error

    Please am not getting the desire result
    using the following SQL statments

    Code:
    SELECT distinct RTRIM(Sn),RTRIM(Name),RTRIM(Type),SUM(TotalPaid) as TotalPaid ,month, TotalFee , Status from PaymentRpt Group by Sn,Name, Type, Month, TotalFee, Status order by 2
    update PaymentRpt set Status = 'paid' where TotalFee = TotalPaid
    update PaymentRpt set Status = 'owing' where TotalFee != TotalPaid
    This is the result i get

    Sn Name Type TotalPaid Month TotalFee Status
    1 Agnes K.P.D 3500 July 3500 owing
    2 Agnes K.P.D 14900 June 14900 Paid
    3 Rita K.P.D 3500 July 3500 owing
    4 Rita K.P.D 14900 June 14900 Paid
    5 Rita K.P.D 1000 August 3500 owing



    Row 1 and Row 3 has totalpaid to be 3500 which is equal to totalfee of 3500 but with my statment it show owing instead of paid
    am expecting the Result to be like this


    Sn Name Type TotalPaid Month TotalFee Status
    1 Agnes K.P.D 3500 July 3500 Paid
    2 Agnes K.P.D 14900 June 14900 Paid
    3 Rita K.P.D 3500 July 3500 Paid
    4 Rita K.P.D 14900 June 14900 Paid
    5 Rita K.P.D 1000 August 3500 owing

    i have tried
    update PaymentRpt set Status = 'owing' where TotalFee > TotalPaid
    no success
    Last edited by rickabbam; Sep 11th, 2018 at 03:10 AM. Reason: errors

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,788

    Re: SQL Two Column Comparing Error

    SQL questions belong in the Database Development forum. I've asked the mods to move this thread.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,788

    Re: SQL Two Column Comparing Error

    You could afford to format your code a bit better, to make it more readable for us and for you.
    sql Code:
    1. SELECT DISTINCT RTRIM(Sn), RTRIM(Name), RTRIM(TYPE), SUM(TotalPaid) AS TotalPaid , MONTH, TotalFee, STATUS
    2. FROM PaymentRpt
    3. GROUP BY Sn, Name, TYPE, MONTH, TotalFee, STATUS
    4. ORDER BY 2
    5.  
    6. UPDATE PaymentRpt SET STATUS = 'paid' WHERE TotalFee = TotalPaid
    7. UPDATE PaymentRpt SET STATUS = 'owing' WHERE TotalFee != TotalPaid
    Are you under the impression that those UPDATE statements are going to affect the data based on the result set of that query? That's not going to happen. The 'TotalPaid' in both those UPDATE statements is referring to individual values in the PaymentRpt table.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,118

    Re: SQL Two Column Comparing Error

    Thread moved from the 'Other Programming Languages' forum to the 'Database Development' forum


    If the TotalFee and TotalPaid fields use a floating-point data type, the issue may well be rounding 'errors' (even tho they display as the same thing, they have minor differences in insignificant decimal places).

    If that is the case, try rounding the values in your comparisons, eg: where Round(TotalFee,3) = Round(TotalPaid,3)
    (the exact syntax depends on which database system you are using)

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    25

    Re: SQL Two Column Comparing Error

    jmcilhinney
    is there a better query to achieve the same result

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    25

    Re: SQL Two Column Comparing Error

    @jmcilhinney
    is there a better query to achieve the same result


    @si_the_geek
    Am using MicroSoft SQL Sever 2017
    i try
    UPDATE PaymentRpt SET STATUS = 'paid' WHERE Round(TotalFee,3) = Round(TotalPaid,3)
    UPDATE PaymentRpt SET STATUS = 'owing' WHERE Round(TotalFee,3) != Round(TotalFee,3)
    Same Result

  7. #7
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,118

    Re: SQL Two Column Comparing Error

    Rather than getting the Status from a field, try calculating it on demand, eg:
    Code:
    SELECT RTRIM(Sn), RTRIM(Name), RTRIM(TYPE), SUM(TotalPaid) AS TotalPaid , MONTH, TotalFee, 
           CASE WHEN Round(SUM(TotalPaid),3) = Round(TotalFee,3) THEN 'paid' ELSE 'owing' END as STATUS
    FROM PaymentRpt
    GROUP BY Sn, Name, TYPE, MONTH, TotalFee
    ORDER BY 2

  8. #8
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,926

    Re: SQL Two Column Comparing Error

    I can't see any reason why those update statements wouldn't achieve the desired result. The only thing I can see is that you're issuing the Select before the updates. Are you sure you're looking at the result of the Updates and not the data beforehand. What happens if you do this:-

    sql Code:
    1. SELECT DISTINCT RTRIM(Sn), RTRIM(Name), RTRIM(TYPE), SUM(TotalPaid) AS TotalPaid , MONTH, TotalFee, STATUS
    2. FROM PaymentRpt
    3. GROUP BY Sn, Name, TYPE, MONTH, TotalFee, STATUS
    4. ORDER BY 2
    5.  
    6. UPDATE PaymentRpt SET STATUS = 'paid' WHERE TotalFee = TotalPaid
    7. UPDATE PaymentRpt SET STATUS = 'owing' WHERE TotalFee != TotalPaid
    8.  
    9. SELECT DISTINCT RTRIM(Sn), RTRIM(Name), RTRIM(TYPE), SUM(TotalPaid) AS TotalPaid , MONTH, TotalFee, STATUS
    10. FROM PaymentRpt
    11. GROUP BY Sn, Name, TYPE, MONTH, TotalFee, STATUS
    12. ORDER BY 2
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    25

    Re: SQL Two Column Comparing Error

    Quote Originally Posted by si_the_geek View Post
    Rather than getting the Status from a field, try calculating it on demand, eg:
    Code:
    SELECT RTRIM(Sn), RTRIM(Name), RTRIM(TYPE), SUM(TotalPaid) AS TotalPaid , MONTH, TotalFee, 
           CASE WHEN Round(SUM(TotalPaid),3) = Round(TotalFee,3) THEN 'paid' ELSE 'owing' END as STATUS
    FROM PaymentRpt
    GROUP BY Sn, Name, TYPE, MONTH, TotalFee
    ORDER BY 2
    This Worked Thank You All

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width