Subquery doesn't know field from parent query

SQL++
1

Hi, I have the following N1QL query. It contains a subquery in the from clause but for some reason (that I can’t seem to figure out) the subquery returns no results… I think it’s because it doesn’t know what “y.variantCode” is but I don’t know how to fix this.

When using a hardcoded value like ‘1’ instead of “y.variantCode” it returns the correct result but I need the variant code from the parent query…

SELECT ls.content.locationCode, sa.content.name as locationName,
ls.content.itemNo, ls.content.quantity, ls.content.locationType, 
Array (select v.color, v.quantity, 
        array (
                    select y.quantity, y.variantCode, 
                    (
                        select sizeDimensionValue, lengthDimensionValue 
                        from (
                                select sa2.content.sizeDimensionValue, sa2.contentlengthDimensionValue
                                from scannerapp sa2 
                                where sa2.content.itemNo = '501' 
                                and sa2.content.variantCode = y.variantCode /* PROBLEM: 0 results */
                                and sa2.content.docType = 'ArticleInfo'
                             ) as sa3
                    ) as sizeLength
              )[0] 
        for y in v.variantQuantities end as variantQuantities
      )[0] 
For v In ls.content.colorQuantities When v.color In ['0162/MARLON'] END AS colorQuantities 
From livestock ls 
left join scannerapp sa on keys 'store_' || ls.content.locationCode
Where ls.content.itemNo = '501'

An example of a working result (when using ‘1’ as variantCode):

{
    "colorQuantities": [
      {
        "color": "0162/MARLON",
        "quantity": 3,
        "variantQuantities": [
          {
            "quantity": 2,
            "sizeLength": [
              {
                "lengthDimensionValue": "36",
                "sizeDimensionValue": "33"
              }
            ],
            "variantCode": "4035"
          },
          {
            "quantity": 1,
            "sizeLength": [
              {
                "lengthDimensionValue": "36",
                "sizeDimensionValue": "33"
              }
            ],
            "variantCode": "70"
          }
        ]
      }
    ],
    "itemNo": "501",
    "locationCode": "HQ",
    "locationType": "WH",
    "quantity": 7
  }

But when running the subquery with “y.variantCode” I always get an empty “sizeLength”…

I also tried to write my subquery in the select instead of the from clause but then I run into the problem that there is no key between my 2 buckets so I can’t have a “use keys” statement…

Can someone help me please?

Thanks!

2

Check this out

SELECT ls.content.locationCode, sa.content.name AS locationName,
ls.content.itemNo, ls.content.quantity, ls.content.locationType,
ARRAY  {cq.color, cq.quantity,
        "variantQuantities": ARRAY {vq.quantity, vq.variantCode,
                                    "sizeLength": FIRST {s.sizeDimensionValue, s.lengthDimensionValue}
                                                  FOR s IN subq WHEN s.variantCode = vq.variantCode END
                                   }
                             FOR vq IN cq.variantQuantities END
       }
       FOR cq IN ls.content.colorQuantities WHEN cq.color IN ['0162/MARLON'] END AS colorQuantities
FROM livestock AS ls
LEFT JOIN scannerapp AS sa ON KEYS 'store_' || ls.content.locationCode
LET subq = (SELECT sa2.content.sizeDimensionValue, sa2.content.lengthDimensionValue, sa2.content.variantCode
            FROM scannerapp AS sa2
            WHERE sa2.content.itemNo = '501' AND sa2.content.docType = 'ArticleInfo')
WHERE ls.content.itemNo = '501';
3

wow, that’s exactly what I needed! I had been trying with “LET” too but couldn’t get it to work. Thank you very much vsr1, you saved me again! :slight_smile: