Ask Your Question

SQL balance display with conditional search formatting

asked 2017-12-06 08:20:30 +0100

koolninja gravatar image

updated 2017-12-14 06:09:36 +0100

selective with search with "MaterialName" and "LoaNo" also "MaterialName" or "LoaNo" on a form, use of table filter(Contractor_filter).

Design sql query for checking balance in respect of received and released on material basis of "MaterialName","LoaNo"

Below SQL query design to display Distinct item, so how to filter option applied on below query so that search with "MaterialName" and "LoaNo" Also "MaterialName" or "LoaNo" on a form

SELECT "LoaNo", "MaterialName", RECEIVED, RELEASED, RECEIVED - RELEASED BALANCE FROM (SELECT DISTINCT "LoaNo", "MaterialName", CASEWHEN("TotalRcvd" IS NULL,0,"TotalRcvd") RECEIVED, CASEWHEN("TotalReleased" IS NULL,0,"TotalReleased") RELEASED FROM "Contractor" LEFT JOIN (SELECT "Type", "MaterialName", "LoaNo", SUM("Quantity") AS "TotalRcvd" FROM "Contractor" WHERE "Type" = 'Recieved' GROUP BY "MaterialName", "LoaNo", "Type") "a" ON ("Contractor"."MaterialName" = "a"."MaterialName") AND "Contractor"."LoaNo" = "a"."LoaNo" LEFT JOIN (SELECT "Type", "MaterialName", "LoaNo", SUM("Quantity") AS "TotalReleased" FROM "Contractor" WHERE "Type" = 'Released' GROUP BY "MaterialName", "LoaNo", "Type") "b" ON ("Contractor"."MaterialName" = "b"."MaterialName") AND "Contractor"."LoaNo" = "b"."LoaNo")

I modified above as

SELECT "Contractor"."LoaNo", "Contractor"."MaterialName", "RECEIVED", "RELEASED", "RECEIVED" - "RELEASED" "BALANCE" FROM ( SELECT DISTINCT "Contractor"."LoaNo", "Contractor"."MaterialName", CASEWHEN( "TotalRcvd" IS NULL, 0, "TotalRcvd" ) "RECEIVED", CASEWHEN( "TotalReleased" IS NULL, 0, "TotalReleased" ) "RELEASED" FROM "Contractor", "Contractor_filter" LEFT JOIN ( SELECT "Contractor"."Type", "Contractor"."MaterialName", "Contractor"."LoaNo", SUM( "Contractor"."Quantity" ) AS "TotalRcvd" FROM "Contractor", "Contractor_filter" WHERE "Contractor"."Type" = 'Recieved' GROUP BY "Contractor"."MaterialName", "Contractor"."LoaNo", "Contractor"."Type" ) "a" ON ( "Contractor"."MaterialName" = "a"."MaterialName" ) AND "Contractor"."LoaNo" = "a"."LoaNo" LEFT JOIN ( SELECT "Contractor"."Type", "Contractor"."MaterialName", "Contractor"."LoaNo", SUM( "Contractor"."Quantity" ) AS "TotalReleased" FROM "Contractor","Contractor_filter" WHERE "Contractor"."Type" = 'Released' GROUP BY "Contractor"."MaterialName", "Contractor"."LoaNo", "Contractor"."Type" ) "b" ON ( "Contractor"."MaterialName" = "b"."MaterialName" ) AND "Contractor"."LoaNo" = "b"."LoaNo" ) WHERE ( ( UPPER ( "Contractor"."MaterialName" ) LIKE '%' || UPPER ( "Contractor_filter"."MaterialName" ) || '%' ) OR ( "Contractor_filter"."MaterialName" IS NULL ) ) And ( ( UPPER ( "Contractor"."LoaNo" ) LIKE '%' || UPPER ( "Contractor_filter"."LoaNo" ) || '%' ) OR ( "Contractor_filter"."LoaNo" IS NULL ) )

showing error

User lack privilage or objectnot found "Contractor"."MaterialName"

so guide me for this

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-12-06 23:02:45 +0100

Ratslinger gravatar image

updated 2017-12-14 06:50:19 +0100


This is no different than any other form using a table filter such as in your question here. I really don't see doing all of what is wanted on one form. Material AND LoaNo on one form (sample in my answer here - ShippingInvoice.odb) where one listbox is dependent upon another, and a second form for Material OR LoaNo selection with a listbox for each.

This is nothing more that table filtering with a listbox. Your needs just originate from a query and not a table.

Edit 12/13/2017:

Many things wrong. Mostly wrong table names in first Select & then wrong parenthesis throughout. Corrected code:

FROM (SELECT DISTINCT "Contractor"."LoaNo",
             CASEWHEN("TotalRcvd" IS NULL,0,"TotalRcvd") "RECEIVED",
             CASEWHEN("TotalReleased" IS NULL,0,"TotalReleased") "RELEASED"
      FROM "Contractor", "Contractor_filter"
        LEFT JOIN (SELECT "Contractor"."Type",  "MaterialName", "LoaNo",
                          SUM("Quantity") AS "TotalRcvd"
                   FROM "Contractor", "Contractor_filter"
                   WHERE "Type" = 'Recieved'
                   GROUP BY "MaterialName","LoaNo", "Type") "a"
               ON ("Contractor"."MaterialName" = "a"."MaterialName")
              AND ("Contractor"."LoaNo" = "a"."LoaNo")
        LEFT JOIN (SELECT "Type", "MaterialName", "LoaNo",
                          SUM("Quantity") AS "TotalReleased"
                   FROM "Contractor", "Contractor_filter"
                   WHERE "Type" = 'Released'
                   GROUP BY "MaterialName", "LoaNo", "Type") "b"
               ON ("Contractor"."MaterialName" = "b"."MaterialName")
              AND ("Contractor"."LoaNo" = "b"."LoaNo")
WHERE ((UPPER("MaterialName") LIKE '%' ||UPPER("Contractor_filter"."MaterialName") || '%') OR ("Contractor_filter"."MaterialName" IS NULL))
AND   ((UPPER("LoaNo") LIKE '%' ||UPPER("Contractor_filter"."LoaNo") || '%') OR ("Contractor_filter"."LoaNo" IS NULL)))
edit flag offensive delete link more


I am trying with urs tips

koolninja gravatar imagekoolninja ( 2017-12-09 16:55:10 +0100 )edit

i have added new sql query to so guide me for this...

koolninja gravatar imagekoolninja ( 2017-12-14 05:59:30 +0100 )edit

I need help for filter out when "BALANCE" is zero.thanks for yours support

koolninja gravatar imagekoolninja ( 2017-12-14 13:32:14 +0100 )edit

That is easy but needs consideration. Since "Balance" is only in the final Select and is calculated you cannot check that field. Just base the condition on the calculation of what fields are available and add to the WHERE conditions:

Ratslinger gravatar imageRatslinger ( 2017-12-14 18:47:15 +0100 )edit

where to input AND ("RECEIVED" - "RELEASED" <> 0) in above query

koolninja gravatar imagekoolninja ( 2017-12-15 06:42:52 +0100 )edit

This is a good enough spot at the end :

."LoaNo" IS NULL)) AND ("RECEIVED" - "RELEASED" <> 0))
Ratslinger gravatar imageRatslinger ( 2017-12-15 07:21:51 +0100 )edit

i tried eartlier but error showing object not found "RECEIVED" i statement

koolninja gravatar imagekoolninja ( 2017-12-15 12:08:31 +0100 )edit

Works for me. Check your spellings. It could also be affected if you changed something else; especially in the beginning Select line. Best to use with the working statement I have already provided.

Ratslinger gravatar imageRatslinger ( 2017-12-15 15:31:23 +0100 )edit

I have added to working statement only, no other alteration made.

koolninja gravatar imagekoolninja ( 2017-12-15 15:40:55 +0100 )edit

Please post your entire WHERE statement from the end of the statement.

Ratslinger gravatar imageRatslinger ( 2017-12-15 15:51:20 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-12-06 08:20:30 +0100

Seen: 27 times

Last updated: yesterday