New answers tagged

0

With this modified query with recursive rt1 as( select id,hid,parent_hid||'->'||hid as str from rt2 where rt2.parent_hid=1 union select t.id,t.hid,s.str||'->'||t.hid as str from rt2 t inner join rt1 s on (s.hid=t.parent_hid) ), rt2 as ( select id, hid, parent_hid, hierarchy from hierarchy_link h left join ...


6

If we slice your query, the first cte (rt2) returns this: id hid parent_hid hierarchy 11 2 1 2 22 3 2 3 44 5 3 5 77 8 5 8 170 11 8 11 190 13 11 13 240 18 15 18 255 20 18 20 The first SELECT ...


2

Looking into my crystal ball, your query might work faster by orders of magnitude like this: SELECT i.*, rr.urllist FROM vw_image i LEFT JOIN LATERAL ( SELECT string_agg(r.url, ',') AS urllist FROM resourceonpage rop JOIN resource r ON r.id = rop.pageid WHERE rop.siteid = 2294 -- or: = i.siteid AND r.resourceid = i.resourceid ...


3

There's no point in using UPDATE to rows INSERTed in the same statement, as the INSERT could set the missing column in the first place: insert into t1(id, name, str2) values (38, 'www', 'aaa'); Besides the pointlessness, it doesn't work, as shown in the question. The reason is mentioned in the doc in 7.8.2. Data-Modifying Statements in WITH: All the ...


0

Another option is to use a variable: DECLARE @HasRows bit = 0; WITH foo as ( ... ) SELECT TOP(1) @HasRows = 1 FROM foo; IF @HasRows BEGIN PRINT 'True'; END



Top 50 recent answers are included