For multiple item cardinality violation in SQL qurey

asked 2017-06-24 09:37:26 +0200

koolninja gravatar image

updated 2017-06-24 13:54:42 +0200

For Single item this its work ok but for multiple selection its show SQL Status: 21000

Error code: -3201

cardinality violation

SELECT "store"."Product" as "Materioal 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.") FROM "store", "store_filter1" WHERE ( UPPER ( "store"."Product" ) LIKE '%' || UPPER ( "store_filter1"."Product" ) || '%' ) OR ( "store_filter1"."Product" IS NULL ) GROUP BY "store"."Product"

Database brief:

Created two table one store and other store_filter1 copy of previous table with primary key as boolen

Sl No, Product, Type, Quantity, Date---> store Note:- Sl No Auto increment

Sl No, Product, Type, Quantity, Date, Date2---> store_filter1 Note:- Sl No as boolen

Product--A, B, C, D Type--Recieved, Issue

If i want to display only one item A then work fine but when try to display all A, B, C, D then cardinality violation.

i tried but not get an way. some suggestion to solve this problem

edit retag flag offensive close merge delete