Ask Your Question
0

SQL statement returns error expected )

asked 2016-10-18 10:27:23 +0200

koolninja gravatar image

update "Test" set "Balance" = ((Select sum("Quantity") from "Test " where "Type"='Recieved' group by "LoaNo" and "MaterialName") - (Select sum("Quantity") from "Test " where "Type"='Released' group by "LoaNo" and "MaterialName")) where "Balance" is null

cant't excute this statement as its return error expected )

i want to subtract selected item from same table and update coulum of same table

Table name Test

SlNo Cname Type LoaNo AgreementNo MaterialName Date Quantity Unit Stock Balance

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
0

answered 2016-10-18 19:24:14 +0200

Ratslinger gravatar image

updated 2016-10-19 20:24:12 +0200

Although this is a complex situation, part of the problem lies within the which record is to be updated?. Given:

image description

There are three different records in the table where LoaNo = 123 and MaterialName = linen. Since the Balance for each of these is NULL which is to be updated? Also there are multiple results so the Update statement becomes confused.

What do expect you end result to be?

Edit: Not knowing what you are expecting the following SQL:

UPDATE "Test"
   SET "Balance" = (SELECT COALESCE("a"."TotalRcvd" - "b"."TotalReleased", "a"."TotalRcvd") AS "Balance"
            FROM (SELECT "Type",
                         "LoaNo",
                         "MaterialName",
                         SUM("Quantity") AS "TotalRcvd"
                  FROM "Test"
                  WHERE "Type" = 'Recieved'
                  GROUP BY "LoaNo",
                           "MaterialName",
                           "Type") "a"
              LEFT JOIN (SELECT "Type",
                                 "LoaNo",
                                 "MaterialName",
                                 SUM("Quantity") AS "TotalReleased"
                          FROM "Test"
                          WHERE "Type" = 'Released'
                          GROUP BY "LoaNo",
                                   "MaterialName",
                                   "Type") "b"
                      ON ("a"."LoaNo" = "b"."LoaNo")
                     AND ("a"."MaterialName" = "b"."MaterialName")
            WHERE ("a"."LoaNo" = "Test"."LoaNo")
            AND   ("a"."MaterialName" = "Test"."MaterialName"))
WHERE ("Test"."Balance" IS NULL)

Will give the following results based upon the data used in this answer:

image description

edit flag offensive delete link more

Comments

@koolninja you are placing comments under the wrong answer. If I didn't see my Karma going up and down so much, I never would have seen your comment. Comment notice is only given to a person when attached to their answer or when their full user name is mentioned - i.e. @Ratslinger.

I have corrected the SQL and replaced the result example in my answer.

Ratslinger gravatar imageRatslinger ( 2016-10-19 20:14:07 +0200 )edit

Thanks for solving problem in right way

koolninja gravatar imagekoolninja ( 2016-10-20 06:35:39 +0200 )edit
0

answered 2016-10-18 19:09:11 +0200

koolninja gravatar image

C:\fakepath\Database.odb

i am uploading odb file with "Test" table with some data in it, so u can help me out

edit flag offensive delete link more

Comments

I get an error with this file - The connection to the datasource "Database" could not be established

Ratslinger gravatar imageRatslinger ( 2016-10-18 21:04:12 +0200 )edit
0

answered 2016-10-18 11:26:40 +0200

updated 2016-10-18 12:30:36 +0200

I made changes to it cuz the official doc doesn't approve of the way you used the GROUP statement, and the first subquery looking for Recieved can't be right.

update "Test" set "Balance" = (
    (
        Select
            sum("Quantity")
        from "Test"
        where
            "Type"='Received'
        group by "LoaNo", "MaterialName"
    ) - (
        Select
            sum("Quantity")
        from "Test"
        where
            "Type"='Released'
        group by "LoaNo", "MaterialName"
    )
) where "Balance" is null
edit flag offensive delete link more

Comments

now this error :-unexpected token: - required: ) : line: 12

koolninja gravatar imagekoolninja ( 2016-10-18 12:14:15 +0200 )edit

@koolninja, edited the table from 'Test ' to 'Test'.

rautamiekka gravatar imagerautamiekka ( 2016-10-18 12:31:34 +0200 )edit

same error :-unexpected token: - required: ) : line: 12

koolninja gravatar imagekoolninja ( 2016-10-18 14:18:09 +0200 )edit

@koolninja: ok, I'm starting to think HSQLDB 1.8 doesn't support something this complex like MySQL does. Do you mind posting the data in some form so I can run my own tests on it ? The ODB file itself would be best.

rautamiekka gravatar imagerautamiekka ( 2016-10-18 15:17:03 +0200 )edit

thank rautamiekka for solving this complex sql and upgrading my knowlage using sql in new type

koolninja gravatar imagekoolninja ( 2016-10-19 14:13:48 +0200 )edit

@koolninja: Uh, I didn't even solve this thing yet.

rautamiekka gravatar imagerautamiekka ( 2016-10-19 14:15:53 +0200 )edit

yes u are right but if same item Quantity received and released then update to Balance is ok. if item Quantity only received then Balance shows null. This problem need to be rectified.

koolninja gravatar imagekoolninja ( 2016-10-19 17:14:35 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-10-18 10:27:23 +0200

Seen: 427 times

Last updated: Oct 19 '16