Ask Your Question
0

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

asked 2018-01-01 09:29:21 +0200

koolninja gravatar image

I tried sql qurey for this as below

SELECT "store"."Product" AS "Material Name", ( SELECT SUM( "store"."Quantity" ) AS "Quantity" FROM "store", "store_filter1" WHERE "store"."Type" = 'Recieved' AND ( ( ( UPPER ( "store"."Product" ) LIKE '%' || UPPER ( "store_filter1"."Product" ) || '%' ) OR ( "store_filter1"."Product" IS NULL ) ) AND ( ( ( "store_filter1"."Date2" IS NULL ) ) OR ( ( "store"."Date" < "store_filter1"."Date" ) ) ) ) GROUP BY "store"."Product" ) - ( SELECT SUM( "store"."Quantity" ) AS "Quantity" FROM "store", "store_filter1" WHERE "store"."Type" = 'Issue' AND ( ( ( UPPER ( "store"."Product" ) LIKE '%' || UPPER ( "store_filter1"."Product" ) || '%' ) OR ( "store_filter1"."Product" IS NULL ) ) AND ( ( ( "store_filter1"."Date" IS NULL ) ) OR ( ( "store"."Date" < "store_filter1"."Date" ) ) ) ) GROUP BY "store"."Product" ) AS "Pre Bal.", ( SELECT SUM( "store"."Quantity" ) AS "Quantity" FROM "store", "store_filter1" WHERE "store"."Type" = 'Recieved' AND ( ( ( UPPER ( "store"."Product" ) LIKE '%' || UPPER ( "store_filter1"."Product" ) || '%' ) OR ( "store_filter1"."Product" IS NULL ) ) AND ( ( ( "store_filter1"."Date" IS NULL ) AND ( "store_filter1"."Date2" IS NULL ) ) OR ( ( "store"."Date" >= "store_filter1"."Date" ) AND ( "store"."Date" <= "store_filter1"."Date2" ) ) ) ) GROUP BY "store"."Product" ) AS "Recieved Bal.", ( SELECT SUM( "store"."Quantity" ) AS "Quantity" FROM "store", "store_filter1" WHERE "store"."Type" = 'Issue' AND ( ( ( UPPER ( "store"."Product" ) LIKE '%' || UPPER ( "store_filter1"."Product" ) || '%' ) OR ( "store_filter1"."Product" IS NULL ) ) AND ( ( ( "store_filter1"."Date" IS NULL ) AND ( "store_filter1"."Date2" IS NULL ) ) OR ( ( "store"."Date" >= "store_filter1"."Date" ) AND ( "store"."Date" <= "store_filter1"."Date2" ) ) ) ) GROUP BY "store"."Product" ) AS "Issue Bal.", ( SELECT SUM( "store"."Quantity" ) AS "Quantity" FROM "store", "store_filter1" WHERE "store"."Type" = 'Recieved' AND ( ( ( UPPER ( "store"."Product" ) LIKE '%' || UPPER ( "store_filter1"."Product" ) || '%' ) OR ( "store_filter1"."Product" IS NULL ) ) AND ( ( ( "store_filter1"."Date2" IS NULL ) ) OR ( ( "store"."Date" <= "store_filter1"."Date2" ) ) ) ) GROUP BY "store"."Product" ) - ( SELECT SUM( "store"."Quantity" ) AS "Quantity" FROM "store", "store_filter1" WHERE "store"."Type" = 'Issue' AND ( ( ( UPPER ( "store"."Product" ) LIKE '%' || UPPER ( "store_filter1"."Product" ) || '%' ) OR ( "store_filter1"."Product" IS NULL ) ) AND ( ( ( "store_filter1"."Date2" IS NULL ) ) OR ( ( "store"."Date" <= "store_filter1"."Date2" ) ) ) ) GROUP BY "store"."Product" ) AS "Post Bal.", ( SELECT SUM( "store"."Quantity" ) AS "Quantity" FROM "store", "store_filter1" WHERE "store"."Type" = 'Recieved' AND ( ( ( UPPER ( "store"."Product" ) LIKE '%' || UPPER ( "store_filter1"."Product" ) || '%' ) OR ( "store_filter1"."Product" IS NULL ) ) ) GROUP BY "store"."Product" ) - ( SELECT SUM( "store"."Quantity" ) AS "Quantity" FROM "store", "store_filter1" WHERE "store"."Type" = 'Issue' AND ( ( ( UPPER ( "store"."Product" ) LIKE '%' || UPPER ( "store_filter1"."Product" ) || '%' ) OR ( "store_filter1"."Product" IS NULL ) ) ) GROUP BY "store"."Product" ) AS "Present Bal." FROM "store", "store_filter1" WHERE ( UPPER ( "store"."Product" ) LIKE '%' || UPPER ( "store_filter1"."Product" ) || '%' ) OR ( "store_filter1"."Product" IS NULL ) GROUP BY "store"."Product"

its works fine for on single item selection through combo box but when for all item then error cardinality violation. suggestion for improvemet to get this result multiitem

material Name Pre Bal Recived Bal Issue Bal Post Bal Present Bal

    A           10          20        5      25         10

Suppose material is Jan to April, selected period Feb so Pre Bal is before Feb .. Recived Bal and Issue Bal of Feb.. Post Bal after Feb and Present Bal after April

may my sql qurey is drafted complex. some suggestion can be converted to results as desired for multi selection of item at time


edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2018-01-02 17:01:22 +0200

Ratslinger gravatar image

Hello,

It would be nice if you were to learn by some of the previous answers you have been given. This statement should be basically the same as the one presented in this answer. Your statement does NOT work on a single item as you claim. You have not done enough testing. If ISSUE is greater than RECEIVED then you have a problem. You are over complicating things by not normalizing information. You are also duplicating selections unnecessarily. You also have some logic problems. Your statement is nothing more than a bunch of small select statements put together. Get each of the individual items separately and join them into single records. Then work with that result to finalize the information you want. Here is a PARTIAL solution.

First start with a record containing all the Products:

Select DISTINCT "Product" From "store"

Now work on the Pre issue/received (you misspelled this again) items only selecting based upon date:

Received Items:

SELECT "Product", SUM("store"."Quantity") AS "Pre_Rcvd"
        FROM "store", "store_filter1"
        WHERE "store"."Type" = 'Recieved'
        AND   (("store_filter1"."Date2" IS NULL) OR ("store"."Date" < "store_filter1"."Date"))
        GROUP BY "store"."Product"

Issued Items:

SELECT "Product", SUM("store"."Quantity") AS "Pre_Issued"
        FROM "store", "store_filter1"
        WHERE "store"."Type" = 'Issue'
        AND   (("store_filter1"."Date" IS NULL) OR ("store"."Date" < "store_filter1"."Date"))
        GROUP BY "store"."Product"

Take a close look at these two statements. This is YOUR code. In one statement you check Date for NULL & the other you check Date2 for NULL. My example here will not change this. You must make the necessary corrections since you know what you want & expect.

Now you can JOIN these last two statements to the first. Then, as shown in linked answer make sure values are in each field by using a CASEWHEN. Finally you select the results wanted with proper calculations and selection of Products based upon the filter:

Select A."Product", "PriorRcvd" - "PriorIssued" "Pre-Balance" From "store_filter1",
(Select PRODUCTS."Product", CASEWHEN("Pre_Rcvd" IS NULL,0,"Pre_Rcvd") "PriorRcvd", CASEWHEN("Pre_Issued" IS NULL,0,"Pre_Issued") "PriorIssued" From
    (Select DISTINCT "Product" From "store")PRODUCTS
    LEFT JOIN (SELECT "Product", SUM("store"."Quantity") AS "Pre_Rcvd"
        FROM "store", "store_filter1"
        WHERE "store"."Type" = 'Recieved'
        AND   (("store_filter1"."Date2" IS NULL) OR ("store"."Date" < "store_filter1"."Date"))
        GROUP BY "store"."Product")PRERCVD on PRERCVD."Product" = PRODUCTS."Product"
    LEFT JOIN (SELECT "Product", SUM("store"."Quantity") AS "Pre_Issued"
        FROM "store", "store_filter1"
        WHERE "store"."Type" = 'Issue'
        AND   (("store_filter1"."Date" IS NULL) OR ("store"."Date" < "store_filter1"."Date"))
        GROUP BY "store"."Product")PREISSUED on PREISSUED."Product" = PRODUCTS."Product")A
WHERE (UPPER(A."Product") LIKE '%' || UPPER("store_filter1"."Product") || '%') OR ("store_filter1"."Product" IS NULL)

This now gives you a basis for the rest of the statement to be completed. This forum is not a free consulting service and you should not treat it as such. You have been given many SQL answers in the past yet you continue to present much the same questions over & over along with many simple errors.

New questions are always welcome, but don't ... (more)

edit flag offensive delete link more

Comments

sorry for this, above part i get done with yours previous help but i only stuck when when multiple results... next time i not trouble for this type question ...

koolninja gravatar imagekoolninja ( 2018-01-02 18:35:28 +0200 )edit
0

answered 2018-01-15 11:50:42 +0200

koolninja gravatar image

updated 2018-01-15 11:51:12 +0200

I found my full answer by using left join properly. here is my working SQL

SELECT "A"."Product", "PriorRcvd" - "PriorIssued" "Pre-Balance", "RriorRcvd" "Recived-Bal", "RriorIssued" "Issued-Bal", "IriorRcvd" - "IriorIssued" "Post-Bal", "PIriorRcvd" - "PIriorIssued" "Present-Bal" FROM "store_filter1", ( SELECT DISTINCT "PRODUCTS"."Product", CASEWHEN( "Pre_Rcvd" IS NULL, 0, "Pre_Rcvd" ) "PriorRcvd", CASEWHEN( "Pre_Issued" IS NULL, 0, "Pre_Issued" ) "PriorIssued", CASEWHEN( "Rre_Rcvd" IS NULL, 0, "Rre_Rcvd" ) "RriorRcvd", CASEWHEN( "Rre_Issued" IS NULL, 0, "Rre_Issued" ) "RriorIssued", CASEWHEN( "Ire_Rcvd" IS NULL, 0, "Ire_Rcvd" ) "IriorRcvd", CASEWHEN( "Ire_Issued" IS NULL, 0, "Ire_Issued" ) "IriorIssued", CASEWHEN( "PIre_Rcvd" IS NULL, 0, "PIre_Rcvd" ) "PIriorRcvd", CASEWHEN( "PIre_Issued" IS NULL, 0, "PIre_Issued" ) "PIriorIssued" FROM ( SELECT "Product" FROM "store" ) "PRODUCTS" LEFT JOIN ( SELECT "Product", SUM( "store"."Quantity" ) AS "Pre_Rcvd" FROM "store", "store_filter1" WHERE "store"."Type" = 'Recieved' AND ( ( "store_filter1"."Date2" IS NULL ) OR ( "store"."Date" < "store_filter1"."Date" ) ) GROUP BY "store"."Product" ) "PRERCVD" ON "PRERCVD"."Product" = "PRODUCTS"."Product" LEFT JOIN ( SELECT "Product", SUM( "store"."Quantity" ) AS "Pre_Issued" FROM "store", "store_filter1" WHERE "store"."Type" = 'Issue' AND ( ( "store_filter1"."Date" IS NULL ) OR ( "store"."Date" < "store_filter1"."Date" ) ) GROUP BY "store"."Product" ) "PREISSUED" ON "PREISSUED"."Product" = "PRODUCTS"."Product" LEFT JOIN ( SELECT "Product", SUM( "store"."Quantity" ) AS "Rre_Rcvd" FROM "store", "store_filter1" WHERE "store"."Type" = 'Recieved' AND ( ( ( "store_filter1"."Date" IS NULL ) AND ( "store_filter1"."Date2" IS NULL ) ) OR ( ( "store"."Date" >= "store_filter1"."Date" ) AND ( "store"."Date" <= "store_filter1"."Date2" ) ) ) GROUP BY "store"."Product" ) "RRERCVD" ON "RRERCVD"."Product" = "PRODUCTS"."Product" LEFT JOIN ( SELECT "Product", SUM( "store"."Quantity" ) AS "Rre_Issued" FROM "store", "store_filter1" WHERE "store"."Type" = 'Issue' AND ( ( ( "store_filter1"."Date" IS NULL ) AND ( "store_filter1"."Date2" IS NULL ) ) OR ( ( "store"."Date" >= "store_filter1"."Date" ) AND ( "store"."Date" <= "store_filter1"."Date2" ) ) ) GROUP BY "store"."Product" ) "RREISSUED" ON "RREISSUED"."Product" = "PRODUCTS"."Product" LEFT JOIN ( SELECT "Product", SUM( "store"."Quantity" ) AS "Ire_Rcvd" FROM "store", "store_filter1" WHERE "store"."Type" = 'Recieved' AND ( ( ( "store_filter1"."Date2" IS NULL ) ) OR ( ( "store"."Date" <= "store_filter1"."Date2" ) ) ) GROUP BY "store"."Product" ) "IRERCVD" ON "IRERCVD"."Product" = "PRODUCTS"."Product" LEFT JOIN ( SELECT "Product", SUM( "store"."Quantity" ) AS "Ire_Issued" FROM "store", "store_filter1" WHERE "store"."Type" = 'Issue' AND ( ( ( "store_filter1"."Date2" IS NULL ) ) OR ( ( "store"."Date" <= "store_filter1"."Date2" ) ) ) GROUP BY "store"."Product" ) "IREISSUED" ON "IREISSUED"."Product" = "PRODUCTS"."Product" LEFT JOIN ( SELECT "Product", SUM( "store"."Quantity" ) AS "PIre_Rcvd" FROM "store", "store_filter1" WHERE "store"."Type" = 'Recieved' GROUP BY "store"."Product" ) "PIRERCVD" ON "PIRERCVD"."Product" = "PRODUCTS"."Product" LEFT JOIN ( SELECT "Product", SUM( "store"."Quantity" ) AS "PIre_Issued" FROM "store", "store_filter1" WHERE "store"."Type" = 'Issue' GROUP BY "store"."Product" ) "PIREISSUED" ON "PIREISSUED"."Product" = "PRODUCTS"."Product" ) "A" WHERE ( UPPER ( "A"."Product" ) LIKE '%' || UPPER ( "store_filter1"."Product" ) || '%' ) OR ( "store_filter1"."Product" IS NULL )

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-01-01 09:29:21 +0200

Seen: 33 times

Last updated: Jan 15 '18