SQL balance display with conditional search formatting

English
#1

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

Multiple selection of item to display Pre Bal, Received Bal,Issue Bal, Post Bal, Present Bal when compared with Date
#2

Hello,

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:

SELECT "LoaNo","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",  "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)))
#3

I am trying with urs tips

#4

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

#5

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

#6

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:

AND   ("RECEIVED" - "RELEASED" <> 0)
#7

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

#8

This is a good enough spot at the end :

."LoaNo" IS NULL)) AND ("RECEIVED" - "RELEASED" <> 0))
#9

i tried eartlier but error showing object not found “RECEIVED” i statement

#10

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.

#11

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

#12

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

#13

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)) AND (“RECEIVED” - “RELEASED” <> 0))

#14

To test, I created a new query. I copied the statement from my answer into the query. It worked. Then I took your portion of the statement you just posted and replaced that part of the original post. No problem. Don’t know what is wrong at your end. The statement works as is. Maybe you can post a screenshot of the actual error you are receiving.

#15

Thanks for help, As I am out so I will send u screen shot tomorrow

#16

https://image.ibb.co/gwHepm/Capture.png image of error statement uploaded

#17

I see nothing obvious in the error. I can only get this type of error by incorrect spelling. Does original statement (in answer) work without problem? If so, try manually re-entering the additional line; and maybe a copy/paste from comment. As stated I have no problem with this.

#18

OK I will try urs suggestions

#19

Forget last now deleted comment.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.