Can someone offer some guidance for a newb not sure how to do this
I have a table that contains a list of skus with size after a dash, quantities and sizes
I need the query to search within the quantity/size per each grouping of same skus before the dash and
-if there 1 or greater in quantity in 2 out of the following sizes: 71, 76, 81 & 86 and not in sizes 91, 97, 102, 107 & 112 another column should say "women"
-if there 1 or greater in quantity in 2 out of the following sizes: 91, 97, 102, 107 & 112 and not in sizes 71, 76, 81 & 86 the other column should say "men"
-if there is 1 or greater in quantity in 1 of the sizes in both groups then the column should say "both"
the result should look like this
Let me know if you have any questions
[Solved] Help with query re spread of sizes
-
- Posts: 54
- Joined: Sun Sep 04, 2016 9:40 pm
[Solved] Help with query re spread of sizes
Last edited by cartoonjazz on Sun Apr 09, 2017 4:42 pm, edited 1 time in total.
openoffice 4.1.2 windows 10
Re: help with query re spread of sizes
In Base you can define queries and those query-names you can use in another query.
I assume that the size value for your sku without a dash is null. You need to add to the queries below some double quotes around the names etc, below it is just a sketch...
Call the following query "SkuGroups"
Call the following query "SkuWithQuantity"
Call the following query "SkuGender"
Call the following query "FinalResult"
I assume that the size value for your sku without a dash is null. You need to add to the queries below some double quotes around the names etc, below it is just a sketch...
Call the following query "SkuGroups"
Code: Select all
SELECT sku AS skugroup FROM table WHERE size IS NULL
Code: Select all
SELECT SkuGroups.skugroup, table.sku, table.quantity, table.size
, CASE WHEN table.size in ( 71,76,81,86) THEN 1 ELSE 0 END as men
, CASE WHEN table.size in ( 71,76,81,86) THEN 0 ELSE 1 END as woman
FROM SkuGroups INNER JOIN table ON SkuGroups.skugroup = SUBSTR(table.sku,1,6)
WHERE table.quantity > 0
Call the following query "SkuGender"
Code: Select all
SELECT skugroup
CASE WHEN SUM(men) >= 1 and SUM(women) >= 1 THEN 'both'
WHEN SUM(men) >= 2 THEN 'men'
WHEN SUM(woman) >= 2 THEN 'women'
ELSE 'alien' END as "gender"
FROM SkuWithQuantity
GROUP BY skugroup
Code: Select all
SELECT SkuWithQuantity.sku, SkuKind.gender
FROM SkuGender INNER JOIN SkuWithQuantity ON SkuGender.skugroup = SkuWithQuantity.skugroup
WHERE SkuGender.gender <> 'alien'
ORDER BY SkuWithQuantity.sku
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
-
- Posts: 54
- Joined: Sun Sep 04, 2016 9:40 pm
Re: help with query re spread of sizes
Thanks, I tried it but Im getting errors in all queries except "SkuGroups"
"SkuWithQuantity" has an error "ùnexpected token: men" here is the adjusted query with quotes
SELECT "SkuGroups"."skugroup", "table"."sku", "table"."quantity", "table"."size"
, CASE WHEN "table"."size" in ( 71,76,81,86) THEN 1 ELSE 0 END as 'men'
, CASE WHEN "table"."size" in ( 71,76,81,86) THEN 0 ELSE 1 END as 'woman'
FROM "SkuGroups" INNER JOIN "table" ON "SkuGroups"."skugroup" = SUBSTR("table"."sku",1,6)
WHERE "table"."quantity" > 0
then all the other queries rely on this query so they dont work i have put them in. If you can confirm the quotes are correct.
"SkuGender"
SELECT "skugroup"
CASE WHEN SUM('men') >= 1 and SUM('women') >= 1 THEN 'both'
WHEN SUM('men') >= 2 THEN 'men'
WHEN SUM('woman') >= 2 THEN 'women'
ELSE 'alien' END as "gender"
FROM "SkuWithQuantity"
GROUP BY "skugroup"
"result"
SELECT "SkuWithQuantity"."sku", "SkuKind"."gender"
FROM "SkuGender" INNER JOIN "SkuWithQuantity" ON "SkuGender"."skugroup" = "SkuWithQuantity"."skugroup"
WHERE "SkuGender"."gender" <> 'alien'
ORDER BY "SkuWithQuantity"."sku"
"SkuWithQuantity" has an error "ùnexpected token: men" here is the adjusted query with quotes
SELECT "SkuGroups"."skugroup", "table"."sku", "table"."quantity", "table"."size"
, CASE WHEN "table"."size" in ( 71,76,81,86) THEN 1 ELSE 0 END as 'men'
, CASE WHEN "table"."size" in ( 71,76,81,86) THEN 0 ELSE 1 END as 'woman'
FROM "SkuGroups" INNER JOIN "table" ON "SkuGroups"."skugroup" = SUBSTR("table"."sku",1,6)
WHERE "table"."quantity" > 0
then all the other queries rely on this query so they dont work i have put them in. If you can confirm the quotes are correct.
"SkuGender"
SELECT "skugroup"
CASE WHEN SUM('men') >= 1 and SUM('women') >= 1 THEN 'both'
WHEN SUM('men') >= 2 THEN 'men'
WHEN SUM('woman') >= 2 THEN 'women'
ELSE 'alien' END as "gender"
FROM "SkuWithQuantity"
GROUP BY "skugroup"
"result"
SELECT "SkuWithQuantity"."sku", "SkuKind"."gender"
FROM "SkuGender" INNER JOIN "SkuWithQuantity" ON "SkuGender"."skugroup" = "SkuWithQuantity"."skugroup"
WHERE "SkuGender"."gender" <> 'alien'
ORDER BY "SkuWithQuantity"."sku"
openoffice 4.1.2 windows 10
Re: help with query re spread of sizes
Try to use' Direct SQL' , mention the error details, Include a demo Base document with the queries.
Edit: 1: A note in your queries: Alias names (rename a column name or table name) can not be placed between single quotes, but can be placed between double quotes. Single quotes are pure reserved for string literals. So some men/women have to be written as "men" / "women" and others as 'men' / ' women'. 2: I also mixed up "women" and "woman" make sure all read as "women" |
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
-
- Volunteer
- Posts: 1549
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Help with query re spread of sizes
I've been following this thread and have found that the query SqlGender won't run on an embedded database. A simple CASE WHEN ELSE will work but CASE WHEN WHEN ELSE generates an error. The SqlGender query does work in a split database, in fact the I got it all working after one more correction to the query FinalResult. "SkuKind"."gender" should be "SkuGender"."gender"
To convert to a split database see:
viewtopic.php?f=83&t=65980
To convert to a split database see:
viewtopic.php?f=83&t=65980
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: Help with query re spread of sizes
@UnkDonald418, A "CASE WHEN WHEN ELSE END" is supported but not with the combination of SUM(). After the feedback of cartoonjazz, I tested it quickly and need to transform query "SqlGender" into
Code: Select all
SELECT "SWQ"."skugroup", "men", "women"
, CASE WHEN "SWQ"."men" >= 1 AND "SWQ"."women" >= 1 THEN 'both'
WHEN "SWQ"."men" >= 2 THEN 'men'
WHEN "SWQ"."women" >= 2 THEN 'women'
ELSE 'alien' END AS "gender"
FROM ( SELECT "skugroup", SUM( "men" ) "men", SUM( "women" ) "women"
FROM "SkuWithQuantity"
GROUP BY "skugroup" ) "SWQ"
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
-
- Posts: 54
- Joined: Sun Sep 04, 2016 9:40 pm
Re: [Solved] Help with query re spread of sizes
Here are all the adjusted queries now working thanks very much to eremmel
"SkuWithQuantity"
SELECT "SkuGroups"."skugroup", "table"."sku", "table"."quantity", "table"."size", CASE WHEN "table"."size" IN ( 71, 76, 81, 86 ) THEN 1 ELSE 0 END AS "men", CASE WHEN "table"."size" IN ( 71, 76, 81, 86 ) THEN 0 ELSE 1 END AS "women" FROM "SkuGroups" INNER JOIN "table" ON "SkuGroups"."skugroup" = SUBSTR( "table"."sku", 1, 6 ) WHERE "table"."quantity" > 0
"SkuGender"
SELECT "SWQ"."skugroup", "men", "women", CASE WHEN "SWQ"."men" >= 1 AND "SWQ"."women" >= 1 THEN 'both' WHEN "SWQ"."men" >= 2 THEN 'men' WHEN "SWQ"."women" >= 2 THEN 'women' ELSE 'alien' END AS "gender" FROM ( SELECT "skugroup", SUM( "men" ) "men", SUM( "women" ) "women" FROM "SkuWithQuantity" GROUP BY "skugroup" ) "SWQ"
"result"
SELECT "SkuWithQuantity"."sku", "SkuGender"."gender" FROM "SkuGender" INNER JOIN "SkuWithQuantity" ON "SkuGender"."skugroup" = "SkuWithQuantity"."skugroup" WHERE "SkuGender"."gender" <> 'alien' ORDER BY "SkuWithQuantity"."sku"
"SkuWithQuantity"
SELECT "SkuGroups"."skugroup", "table"."sku", "table"."quantity", "table"."size", CASE WHEN "table"."size" IN ( 71, 76, 81, 86 ) THEN 1 ELSE 0 END AS "men", CASE WHEN "table"."size" IN ( 71, 76, 81, 86 ) THEN 0 ELSE 1 END AS "women" FROM "SkuGroups" INNER JOIN "table" ON "SkuGroups"."skugroup" = SUBSTR( "table"."sku", 1, 6 ) WHERE "table"."quantity" > 0
"SkuGender"
SELECT "SWQ"."skugroup", "men", "women", CASE WHEN "SWQ"."men" >= 1 AND "SWQ"."women" >= 1 THEN 'both' WHEN "SWQ"."men" >= 2 THEN 'men' WHEN "SWQ"."women" >= 2 THEN 'women' ELSE 'alien' END AS "gender" FROM ( SELECT "skugroup", SUM( "men" ) "men", SUM( "women" ) "women" FROM "SkuWithQuantity" GROUP BY "skugroup" ) "SWQ"
"result"
SELECT "SkuWithQuantity"."sku", "SkuGender"."gender" FROM "SkuGender" INNER JOIN "SkuWithQuantity" ON "SkuGender"."skugroup" = "SkuWithQuantity"."skugroup" WHERE "SkuGender"."gender" <> 'alien' ORDER BY "SkuWithQuantity"."sku"
openoffice 4.1.2 windows 10