0

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 ?

Community
  • 1
  • 1
gstackoverflow
  • 36,709
  • 117
  • 359
  • 710
  • 2
    Provide sample data, desired results, and an explanation of what the code is supposed to be doing. – Gordon Linoff Jun 18 '20 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. – The Impaler Jun 18 '20 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. – gstackoverflow Jun 18 '20 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. – Andrew Jun 18 '20 at 16:56
  • @Andrew I will be able to test on my own. I am asking about idea – gstackoverflow Jun 18 '20 at 16:57
  • @The Impaler approximate query would be enough for me – gstackoverflow Jun 18 '20 at 17:14
  • @gstackoverflow: with over 25K rep, you probably have read this before, but I think this really makes the point : [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) – GMB Jun 18 '20 at 17:15
  • @GMB sure I know it. But I am here because I am really need help - I am stuck for almost the whole day and I can't uncover all details because of NDA. If could help me - please do it - otherwise I will countinue suffer. I can't generate fake data too because I don't have it. I just have a query – gstackoverflow Jun 18 '20 at 17:18
  • You don't need to provide the actual details, just something that reproduces the issue. Couldn't you create a couple of demo tables and some sample data that reproduce the technical issue? I'm not going to spend my spare time writing my own test case for something that might miss the actual problem. – William Robertson Jun 20 '20 at 21:36

1 Answers1

1

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.

The Impaler
  • 45,731
  • 9
  • 39
  • 76