Didn't get expected result using LET

SQL++
1

Hi,

I am trying to combine 2 select statements using LET function, but it is printing null o/p. Do my query is correct?, can someone please help to combine below specified queries.

SELECT a.vlrnum,
a.nwid,
a.is_vlr,
a.hnwid,
a.zcode,
a.cosid,
a.trallowed,
a.use_random_code,
a.rej_code AS noderejcode
c.name
FROM config a config c
INNER JOIN config b ON a.nwid = b.nwid
AND a.vlrnum = b.vlrnum
WHERE a.__t=‘ntr-vlr-details’
AND b.__t=‘ntr-vlrm’
AND c.__t=‘ntr-reject-codes’

SELECT c.name AS name
FROM config c
WHERE c.__t=‘ntr-reject-codes’
AND c.code IN (
SELECT noderejcode
FROM config a
UNNEST SPLIT(a.rej_code,",") AS noderejcode
WHERE a.__t=‘ntr-vlr-details’)

Thanks,
Vikas

2 
SELECT d.*
FROM (
      WITH noderejcode AS ( SELECT DISTINCT nrc.name, nrc.code
                         FROM config AS nrc
                         WHERE nrc.__t = "ntr-reject-codes")
      SELECT a.vlrnum,
             a.nwid,
             a.is_vlr,
             a.hnwid,
             a.zcode,
             a.cosid,
             a.trallowed,
             a.use_random_code,
             a.rej_code AS noderejcode,
             b.name
             FROM config AS a
             UNNEST noderejcode AS b
             WHERE a.__t = "ntr-vlr-details"
             AND b.code IN SPLIT(a.rej_code,",")) AS d
INNER JOIN config AS c ON d.nwid = c.nwid AND d.vlrnum = c.vlrnum
WHERE c.__t = "ntr-vlrm";
3

With this query I am facing syntax problem.

4

Comma is missing in projection and try again

5

I can able to resolve the syntax issue by keeping ‘as’. But the query output is empty even we have proper data set.

SELECT d.*
FROM (
WITH noderejcode as ( SELECT DISTINCT nrc.name, nrc.code
FROM config AS nrc
WHERE nrc.__t = “ntr-reject-codes”)
SELECT a.vlrnum,
a.nwid,
a.is_vlr,
a.hnwid,
a.zcode,
a.cosid,
a.trallowed,
a.use_random_code,
a.rej_code AS rejcode,
b.name
FROM config AS a
UNNEST noderejcode AS b
WHERE a.__t = “ntr-vlr-details”
AND b.code IN SPLIT(a.rej_code,“,”)) AS d
INNER JOIN config AS c ON d.nwid = c.nwid AND d.vlrnum = c.vlrnum
WHERE c.__t = “ntr-vlrm”;

6

You need to figure what is wrong. By running subquery first and see if that give results. What is wrong.