CODEWITHSUNDEEP
sqlpostgresql
lakshmi sowmya
lakshmi sowmya

Reputation: 15

Migration of CONNECT BY LEVEL from ORACLE to POSTGRES

I Need inputs on Migration of CONNECT BY LEVEL from Oracle to Postgres.

Source Code from Oracle:

SELECT LEVEL,AST_TXRBATCHID_SEQ.NEXTVAL  AS BATCH
from DUAL
CONNECT BY LEVEL <= '2';

Upvotes: 0

Views: 542

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247270

A simple solution would be:

SELECT 1 AS level, nextval('ast_txrbatchid_seq') AS batch
UNION
SELECT 2, nextval('ast_txrbatchid_seq');

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

Use generate_series and NEXTVAL() function.

SQL Fiddle

PostgreSQL 9.6 Schema Setup:

create sequence AST_TXRBATCHID_SEQ start with 1 increment by 1;

Query 1:

SELECT id,NEXTVAL('AST_TXRBATCHID_SEQ') AS BATCH
from generate_series(1,2) as id

Results:

| id | batch |
|----|-------|
|  1 |     2 |
|  2 |     3 |

Upvotes: 1

Related Questions