261 questions
0
votes
0
answers
44
views
How to migrate Oracle's CONNECT_BY_ISLEAF, CONNECT_BY_ROOT, and CONNECT BY PRIOR to SQL Server? [duplicate]
I have the following query that I am trying to translate:
SELECT
sub.COLUMN_1,
sub.COLUMN_2,
sub.COLUMN_3,
CONNECT_BY_ROOT sub.COLUMN_4,
CONNECT_BY_ROOT sub.COLUMN_5,
...
- 131
2
votes
2
answers
85
views
How to deal with a NULL in Connect By Level without the query going on forever?
I have a leases table, with start and end date of each lease. Some leases don't have an end date, and the field is NULL.
I want to generate a list of the payment dates/amounts over the duration of the ...
0
votes
1
answer
67
views
Oracle help using Connect By Root to return assembly top level parent
I'm trying to use CONNECT BY ROOT to do a recursive query that returns the top-level parent of an assembly part. My query so far is only able to return the parent one level above. I created a test ...
- 11
1
vote
2
answers
117
views
Connect by prior - is this an Oracle bug?
So either this is an Oracle bug, or I'm being a bit slow today.
This SQL executes fine:
WITH car_paint_options AS (
SELECT 'Escort' car_model, 'red,blue' paint_opts FROM dual UNION
SELECT 'Puma'...
- 3,625
0
votes
0
answers
219
views
Using an oracle sequence directly in the CONNECT BY clause
I have below query to use the sequence from CONNECT BY clause and insert into the table
INSERT INTO DF_SUBJECTS (SUBJECT_ID)
SELECT 'CUST' || LPAD(your_sequence.NEXTVAL, 3, '0'), ROWNUM
FROM dual
...
- 505
1
vote
1
answer
123
views
Snowflake: PRIOR keyword is missing in Connect By statement
I am trying to implement a stored procedure to return the result set of a CONNECT BY query in Snowflake
create or replace procedure test(email varchar(100))
RETURNS TABLE (email_address varchar(100))
...
- 33
0
votes
1
answer
129
views
Oracle SQL Connect By Level - literal does not match format string
I want to generate dates between 2 dates coming from parameters in oracle fusion using the connect by level statement
SELECT papf.person_number emp_id,
(SELECT to_date(:p_from_date,'dd-mm-yyyy') + ...
2
votes
1
answer
141
views
Formatting a hierarchical query in a nested tree syntax (Oracle)
I am working with data that is represented using the following basic syntax:
a→b→c
EDIT
This is used to describe a spatiotemporal relation of alteration assemblages around an ore deposit. An ...
- 47
3
votes
1
answer
170
views
Why does deterministic function return unexpected numbers in CONNECT BY LEVEL query?
Test #1:
I have a user-defined function and a CONNECT BY LEVEL query:
with function custom_function(p_id in number) return number
is
begin
return p_id;
end;
select
custom_function(level)...
- 366
-1
votes
1
answer
86
views
I need to convert this query from Oracle to SQL Server
SELECT
ffl2.fee_record_code,
(SELECT max(fee_record_code)
FROM fees_list ffl3
START WITH ffl3.fee_record_code = Nvl(ffl2.fes_associated_record, ffl2.fee_record_code)
CONNECT BY ...
0
votes
1
answer
139
views
Expanding Oracle rows with comma-delimited values into multiple rows
I have a table in Oracle like the following:
KEY,VALS
k1,"a,b"
I need it to look like:
KEY,VAL
k1,a
k1,b
I did this with CONNECT BY and LEVEL, following an example:
with t as (
select '...
- 4,109
1
vote
5
answers
22k
views
Oracle - using connect by prior in Hierarchical Queries and put them into rows
I am new in Oracle, I am going to use connect by prior to implement the flat table instead of Hierarchical one. but I am a little bit confuse. My table is like this:
empTabl:
empID
empName
managerID
...
- 55
1
vote
2
answers
232
views
Oracle - Connect By Prior
I know I have to use CONNECT BY PRIOR in this query, but I'm not sure how to implement it.
We have customers who purchase monthly subscriptions, and those get auto-renewed each month. We have a log ...
- 853
0
votes
1
answer
628
views
Use connect by by in REGEXP_SUBSTR without breaking result to multiple rows
SELECT CHR(91)||'a-zA-Z0-9._%-'||CHR(93)||'+'|| listagg(REGEXP_SUBSTR('[email protected], [email protected]', '@'||CHR(91)||'^,'||CHR(93)||'+', 1, LEVEL), ', ') within group (order by level) as domain
FROM ...
- 505
1
vote
1
answer
92
views
Is there any to add one loop row in connect by oracle with nocycle?
Just like Oracle continues to follow a path beyond a cyclical loop when the cycle occurs at the top node (root node connected right back to root node), is there any way to do the same with in between ...
1
vote
1
answer
307
views
Extract a sub-tree from a hierarchy tree based on a leaf in Oracle
I have a table users representing a hierarchical tree like this:
Column
Type
Comment
user_id
integer
sequence
user_type
integer
1 for group of users 2 for normal user
group_id
integer
Reference to a ...
- 127
2
votes
1
answer
469
views
Oracle SQL: to count the records based on fixed time frame (say 15 or 30 minutes)
I have a table similar to
Start time | End Time | User |
09/02/2021 03:01:13 | 09/02/2021 03:45:15 | ABC |
09/02/2021 03:15:20 | 09/02/2021 05:03:...
- 21
0
votes
1
answer
868
views
Oracle SQL - Generate rows based on quantity column
We use pooled positions that have a max headcount assigned and I need to build a report that creates a line for each head, including the details of the incumbent if there is one or a NULL line where ...
- 13
0
votes
2
answers
111
views
Oracle 12c Recursive query for Product , Category relationship
I have data like below.
If i search Product "P1" then
I need all the category which has the product "P1"
I need all the products that are related to the category from step 1
...
- 23
0
votes
1
answer
463
views
connect-by-prior: using PRIOR to drill 2-levels up
I have the following connect-by-prior SQL which essentially starts at the leaf node and works its way up the tree to the parent "tree-trunc" (level-1):
with my_tree as (
select 'level 4.1'...
- 3,625
0
votes
0
answers
640
views
syntax error at or near "START" in Postgres despite the fact that EXTENSION tablefunc is created [duplicate]
I have a query like this(which works in Oracle properly):
...
LEFT JOIN myTable ON ....
START WITH orgNodeId IN (
SELECT someColumn FROM anothertable
...
- 36.9k
0
votes
2
answers
244
views
SQL Recursive Queries , problems with understanding "prior" expression on connect by clause
I created a table like the following , to understand recursive queries:
For this I made an insert, to cause a loop to the query and now my table looks like the following (Athens to Vienna connection ...
- 109
1
vote
1
answer
1k
views
CONNECT BY NOCYCLE generating millions of child nodes
I have a database where from_node and to_node are the columns. Trying to find all the reachable nodes from from_node to to_node. There are cycles. Few from_node using connect by nocycle, is generating ...
- 11
1
vote
0
answers
149
views
Find root parent from child in oracle sql
I have to find the top parent in a hierarchy where I provide the child.
the top most parent will be when the id = ref
table example:
+--------------------+
| id parent_id ref |
+--------------------...
0
votes
1
answer
324
views
Oracle hierarchial queries
I am trying to pull out all the hierarchy values for a particular segment in Oracle Apps(referring tables applsys.fnd_flex_Value_norm_hierarchy & applsys.fnd_flex_values). I want a tree like ...
