Query Optimization: Getting Customer IDs without JOIN and Handling UNNEST

SQL++
,
1

SELECT A.RteId,B.LocNm
FROM Test A
UNNEST SrvLoc B
WHERE A.$Type = “Route” and B.LocTyp=“DC”
and A.RteId IN (SELECT RAW b.RteID
FROM Test a
UNNEST CustRel b
WHERE a.$Type = “Customer”
and a.custid IN [“2003”,“2004”]
and b.cd=“01”)

I have this above query and is returning results for me.
My requirement is I need to get all the customerid IN (Select…) .Is there a way we can do it without Join .?
If Join is the only way then How wll i handle the UNNEST in the above query
SQL++ n1ql query

Please help

2

Can you explain the need in a bit more detail? In your example query it looks like you already have the customer IDs as they are a parameter on the predicate. What customer ID are you trying to get and what document does it live on?

3

This is from 1 document called Route
SELECT A.RteId,B.LocNm
FROM Test A
UNNEST SrvLoc B
WHERE A.$Type = “Route” and B.LocTyp=“DC”
and A.RteId IN

and this is from second documnet called customer
SELECT RAW b.RteID
FROM Test a
UNNEST CustRel b
WHERE a.$Type = “Customer”
and a.custid IN [“2003”,“2004”]
and b.cd=“01”

now if i do A.Rteid IN ( SELECT FROM CUSTOMER DOCUMENT)

I get RteId and LocNm from the above … How can i get customerid as well here .

4 
WITH routeIds AS (SELECT b.RteID, a.custid
                  FROM Test a
                  UNNEST a.CustRel b
                  WHERE a.$Type = "Customer"
                        AND a.custid IN ["2003","2004"]
                        AND b.cd = "01")
SELECT A.RteId, B.LocNm, r.custid
FROM Test A
UNNEST A.SrvLoc B
JOIN routeIds AS r ON A.RteId = r.RteID
WHERE A.$Type = "Route"
      AND B.LocTyp = "DC";

Different representation LocNms AS ARRAY

WITH routeIds AS (SELECT b.RteID, a.custid
                  FROM Test a
                  UNNEST a.CustRel b
                  WHERE a.$Type = "Customer"
                        AND a.custid IN ["2003","2004"]
                        AND b.cd = "01")
SELECT A.RteId, LocNms, r.custid
FROM Test A
JOIN routeIds AS r ON A.RteId = r.RteID
LET LocNms = ARRAY v.LocNm FOR v IN A.SrvLoc WHEN v.LocTyp = "DC" END
WHERE A.$Type = "Route"  AND ARRAY_LENGTH(LocNms) > 0 ;
5

Thanks . This will be the final one .
Is there a way i can merge this two . The select condition varies here and I have to see if locationnm exist either in Route or RLocationList. Please let me know

WITH routeIds AS (SELECT b.RteID, A.CustId
                  FROM `Test1` A
                  UNNEST CustR b
                  WHERE A.`$Type` = "Customer"
                  and A.CustId IN ["960131",
"960316",
"964289",
"965368",
"972178",
"976802",
"988458",
"808871",
"809130",
"814458",
"815015",
"815211",
"816319"] 
AND  b.Cdv = "01")

SELECT A.RteId, B.LocNm, r.CustId
FROM `Test1` A
UNNEST A.LocList B
JOIN routeIds AS r ON A.RteId = r.RteID
WHERE A.`$Type` = "Route"
      AND  B.LocTypCdv = "TT";  
      
WITH routeIds AS (SELECT b.RteID, A.CustId
                  FROM `Test1` A
                  UNNEST CustR b
                  WHERE A.`$Type` = "Customer"
                  and A.CustId IN ["960131",
"960316",
"964289",
"965368",
"972178",
"976802",
"988458",
"808871",
"809130",
"814458",
"815015",
"815211",
"816319"] 
 AND  b.Cdv = "01")
SELECT A.RteId, B.LocNm, r.CustId
FROM `Test1` A
UNNEST LocList B
JOIN routeIds AS r ON A.RteId = r.RteID
WHERE A.`$Type` = "RLocationList"
      AND  (B.LocTypCdv = "TT" or B.LocTypCdv = "TT1")
6 
WITH routeIds AS (SELECT b.RteID, A.CustId
                  FROM Test1 A
                  UNNEST CustR b
                  WHERE A.`$Type` = "Customer"
                  AND A.CustId IN ["960131", "960316", "964289", "965368", "972178", "976802", "988458",
                                   "808871", "809130", "814458", "815015", "815211", "816319"]
                  AND b.Cdv = "01")
SELECT A.RteId, B.LocNm, r.CustId
FROM Test1 A
UNNEST LocList B
JOIN routeIds AS r ON A.RteId = r.RteID
WHERE (A.`$Type` = "RLocationList" AND B.LocTypCdv IN ["TT","TT1"])
      OR ( A.`$Type` = "Route" AND B.LocTypCdv IN ["TT"])
7

This will not work for my requirement . I need two unnest and If i just add 2 unnest then it is not working . Here I ahev 2 unnest Sloc AND Loclist and I need to have both in tthe SQL . Please let me know

WITH routeIds AS (SELECT b.RteID, A.GtmuCustId
                 FROM `Test` A
                  UNNEST Cs b
                  WHERE A.`$Type` = "Customer"
                  AND A.GtmuCustId IN ["860131",
"860316",
"864289",
"865368",
"872178",
"876802",
"888458",
"908871",
"909130",
"914458",
"915015",
"915211",
"916319",
"941294",
"941454",
"2825140",
"2825314",
"955478",
"955487"
] 
AND b.Cdv = "01")
SELECT A.RteId, B.LocNm, r.GtmuCustId
 FROM `Test` A
UNNEST A.SLoc AS B
JOIN routeIds AS r ON A.RteId = r.RteID
WHERE (A.`$Type` = "Route" AND B.LocTypCdv !="05");

WITH routeIds AS (SELECT b.RteID, A.GtmuCustId
                 FROM `Test` A
                  UNNEST CustRteRltnshps b
                  WHERE A.`$Type` = "Customer"
                  AND A.GtmuCustId IN ["860131",
"860316",
"864289",
"865368",
"872178",
"876802",
"888458",
"908871",
"909130",
"914458",
"915015",
"915211",
"916319",
"941294",
"941454",
"2825140",
"2825314",
"955478",
"955487"
] 
AND b.Cdv = "01")
SELECT A.RteId, B.LocNm, r.GtmuCustId
 FROM `Test` A
UNNEST A.LocList   AS B
JOIN routeIds AS r ON A.RteId = r.RteID
WHERE (A.`$Type` = "RouteLocationList" AND (B.LocTypCdv ="DC" or B.LocTypCdv ="PL" ));
8

I am not sure what exactly your question. You keep changing. If you need combine both queries you could do by using UNION ALL

9

Sorry about that . was fine tunning thus have to change few times . I think I am ok with what I have . Thanks for the help .

10

You could use concat both arrays

UNNEST ARRAY_CONCAT(LocList, Sloc)