SQL statement returns error expected )

[koolninja]

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

[rautamiekka]

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

[koolninja]

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

[rautamiekka]

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

[koolninja]

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

[rautamiekka]

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

[koolninja]

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

[rautamiekka]

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

[koolninja]

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]

Database.odb

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

[Ratslinger]

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

[Ratslinger]

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

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:

[Ratslinger]

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

[koolninja]

Thanks for solving problem in right way