Need a little help on N1QL query with missing value and conditional calculated value

Couchbase Server
,
1

I have this query that works fine apart from one scenario:

SELECT d.year,
       d.month,
       specieskey,
       d.sourcecreated,
       d.locationtype,
       d.statslocation,
       d.clubplacekey,
       (SUM(IFMISSINGORNULL(d.hoursfished,0))*60 + SUM(IFMISSINGORNULL(d.minutesfished,0))) minutesfished,
       COUNT(*) count,
       SUM(IFMISSINGORNULL(ARRAY_LENGTH(d.catchkeys),0)) catchcount,
       SUM(CASE WHEN ARRAY_LENGTH(d.catchkeys) = 0 THEN 1 ELSE 0 END) zerotrips
FROM data d
UNNEST targetspecies AS specieskey
WHERE d.type='FishingTrip'
    AND ((d.locationlevel2='91'
            AND d.locationtype='1')
        OR (d.locationlevel4='91'
            AND d.locationtype='2'))
    AND d.statspublic
    AND specieskey IS VALUED
    AND d.locationtype IS VALUED
    AND d.statslocation IS VALUED
    AND d.year IS VALUED
    AND d.month IS VALUED
GROUP BY d.year,
         d.month,
         specieskey,
         d.sourcecreated,
         d.locationtype,
         d.statslocation,
         d.clubplacekey
ORDER BY d.year DESC,
         d.month

If d.sourcecreated does not exist (on some old documents) then I would like to calculate the value. So if d.userkey has a value then I want to return ‘1’ for d.sourcecreated else ‘4’.
… and actually, if d.userkey exists I would like to add another condition (length of d.key ) to determine the right value :slight_smile:

I have tried to change it to:

SELECT d.year,
       d.month,
       specieskey,
       IFMISSINGORNULL(d.sourcecreated,(CASE WHEN d.userkey IS NOT VALUED THEN '4' ELSE (CASE WHEN LENGTH(`KEY`)=32 THEN '1' ELSE '2' END) END)) source,
       d.locationtype,
       d.statslocation,
       d.clubplacekey,
       (SUM(IFMISSINGORNULL(d.hoursfished,0))*60 + SUM(IFMISSINGORNULL(d.minutesfished,0))) minutesfished,
       COUNT(*) count,
       SUM(IFMISSINGORNULL(ARRAY_LENGTH(d.catchkeys),0)) catchcount,
       SUM(CASE WHEN ARRAY_LENGTH(d.catchkeys) = 0 THEN 1 ELSE 0 END) zerotrips
FROM data d
UNNEST targetspecies AS specieskey
WHERE d.type='FishingTrip'
    AND ((d.locationlevel2='91'
            AND d.locationtype='1')
        OR (d.locationlevel4='91'
            AND d.locationtype='2'))
    AND d.statspublic
    AND specieskey IS VALUED
    AND d.locationtype IS VALUED
    AND d.statslocation IS VALUED
    AND d.year IS VALUED
    AND d.month IS VALUED
GROUP BY d.year,
         d.month,
         specieskey,
         source,
         d.locationtype,
         d.statslocation,
         d.clubplacekey
ORDER BY d.year DESC,
         d.month

But I get this error:

    "code": 3080,
    "msg": "Ambiguous reference to field 'source' (near line 28, column 15).",

If I do a query without grouping the result then this line works as expected:

:
       IFMISSINGORNULL(d.sourcecreated,(CASE WHEN d.userkey IS NOT VALUED THEN '4' ELSE (CASE WHEN LENGTH(`KEY`)=32 THEN '1' ELSE '2' END) END)) source,
:

How can I get this to work with the grouped query?

2

Oh, thinking of some previous queries that I fiddled with I just tried to calculate the source separately like:

SELECT d.year,
       d.month,
       specieskey,
       source,
       d.locationtype,
       d.statslocation,
       d.clubplacekey,
       (SUM(IFMISSINGORNULL(d.hoursfished,0))*60 + SUM(IFMISSINGORNULL(d.minutesfished,0))) minutesfished,
       COUNT(*) count,
       SUM(IFMISSINGORNULL(ARRAY_LENGTH(d.catchkeys),0)) catchcount,
       SUM(CASE WHEN ARRAY_LENGTH(d.catchkeys) = 0 THEN 1 ELSE 0 END) zerotrips
FROM data d
UNNEST targetspecies AS specieskey
LET source = IFMISSINGORNULL(d.sourcecreated,(CASE WHEN d.userkey IS NOT VALUED THEN '4' ELSE (CASE WHEN LENGTH(d.`key`)=32 THEN '1' ELSE '2' END) END)) 
WHERE d.type='FishingTrip'
    AND ((d.locationlevel2='91'
            AND d.locationtype='1')
        OR (d.locationlevel4='91'
            AND d.locationtype='2'))
    AND d.statspublic
    AND specieskey IS VALUED
    AND d.locationtype IS VALUED
    AND d.statslocation IS VALUED
    AND d.year IS VALUED
    AND d.month IS VALUED
GROUP BY d.year,
         d.month,
         specieskey,
         source,
         d.locationtype,
         d.statslocation,
         d.clubplacekey
ORDER BY d.year DESC,
         d.month

And now I don’t get a syntax error! I will just need to verify that it actually does what I need - but it does look promising!

1 Like
3

When I saw your initial question I was going to suggest using ‘LET’, it should do what you need for this.

1 Like
4

Yep, it did! And I have verified that the result is what I expected :slightly_smiling_face:

5

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.