1

Now I have following working query for Oracle:

 select * from (
          select orgId, oNdId, stamp, op,
                 lgin, qwe, rty,
                 tusid, tnid, teid, 
                 thid, tehid, trid,
                 name1, name2,
                 xtrdta, rownum as rnum from 
          (
            select a.*
            from tblADT a 
            where a.orgId=? and  EXISTS(
            SELECT oNdId, prmsn FROM (
                SELECT oNdId, rp.prmsn FROM tblOND
                LEFT JOIN tblRoleprmsn rp ON rp.roleId=? AND rp.prmsn='vors'
                START WITH oNdId IN (
                    SELECT oNdId FROM tblrnpmsn rnp
                    WHERE rnp.roleId=?
                    AND rnp.prmsn=?
                )
            CONNECT BY PRIOR oNdId = parentId
            )
            WHERE oNdId = a.oNdId OR 1 = (
                CASE WHEN prmsn IS NOT NULL THEN
                    CASE WHEN a.oNdId IS NULL THEN 1 ELSE 0 END
                END
            )
        )               
             AND op IN (?)
            order by stamp desc
          ) WHERE rownum < (? + ? + 1)
        ) WHERE rnum >= (? + 1)

For now I am trying to implement analog for PostreSQl. Based on my investigation I could use recursive CTE.

But I am not successful. The eaxamples I found all without where clause so it is not so easy.

Could you please help me with that ?

Improve this question
9
  • 2
    Provide sample data, desired results, and an explanation of what the code is supposed to be doing. Commented Jun 18, 2020 at 16:36
  • 1
    It's a trivial CTE. With a few rows of sample data we should be able to write query for you. Commented Jun 18, 2020 at 16:42
  • @The Impaler I am really sorry but why do I need to provide sample data ? There is a table 'tblOND' which has 2 columns 'oNdId' and 'parentId' it is a hierarchy here. Commented Jun 18, 2020 at 16:51
  • 3
    Because without data and desired results, we can only write a query, not execute it, test it, or ensure it does what it's supposed to. Commented Jun 18, 2020 at 16:56
  • @Andrew I will be able to test on my own. I am asking about idea Commented Jun 18, 2020 at 16:57

1 Answer 1

Reset to default
2

The Oracle query seems to have a few extra quirks and conditions I'm not able to understand. It's probably related to the specific use case.

In the absence of sample data I'll show you the simple case. You say:

There is a table 'tblOND' which has 2 columns 'oNdId' and 'parentId' it is a hierarchy here

Here's a query that would get all the children of nodes, according to an initial filtering predicate:

create table tblond (
  ondid int primary key not null, 
  parentid int foreign key references tblond (ondid)
);

with recursive
n as (
  select ondid, parentid, 1 as lvl
  from tblond
  where <search_predicate> -- initial nodes
 union all
  select t.ondid, t.parentid, n.lvl + 1
  from n
  join tblond t on t.parentid = n.ondid -- #1
)
select * from n

Recursive CTEs are not limited to hierarchies, but to any kind of graph. As long as you are able to depict the relationship to "walk" to the next nodes (#1) you can keep adding rows.

Also the example shows a "made up" column lvl; you can produce as many columns as you need/want.

The section before the UNION ALL is the "anchor" query that is run only once. After the UNION ALL is the "iterative" query that is run iteratively until it does not return any more rows.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

where can I paste WHERE oNdId = a.oNdId OR 1 = ( CASE WHEN prmsn IS NOT NULL THEN CASE WHEN a.oNdId IS NULL THEN 1 ELSE 0 END END )
If that's the predicate to "walk" to the next nodes of the graph, add it in #1.
I tried it but it filter all rows from result
If you produce a db-fiddle.com we can help you debugging it. With your reputation, you know the drill.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.