I have a requirement in Oracle SQL that specific values should be repeated 5 times. So, I have written the below query and getting the result as expected.
SELECT Item_Name || RANGES AS Item_Number
FROM
(
SELECT DISTINCT 'Price Line ' || column1 || '-' AS Item_Name, level+0 RANGES
FROM
(
SELECT DISTINCT column1 from tbl where column1 in
(
'ABC',
'BCD'
)
) connect by level <= 5
) order by Item_Number
OUTPUT:
Price Line ABC-1
Price Line ABC-2
Price Line ABC-3
Price Line ABC-4
Price Line ABC-5
Price Line BCD-1
Price Line BCD-2
Price Line BCD-3
Price Line BCD-4
Price Line BCD-5
But when I add more than 10 values like 'DEF', 'EFG',.....'XYZ', the query keeps executing for hours without any result.
Any help or suggestion on this would be appreciated.
Regards,
Make sure the CONNECT BY is not operating on your table rows. For example, use a common table expression (i.e., a WITH clause) to create a row source with 5 rows using CONNECT BY and then CROSS JOIN that row source to your table. Here is an example.
CREATE TABLE ITEMS ( item_number VARCHAR2(30) );
INSERT INTO ITEMS VALUES ('ABC');
INSERT INTO ITEMS VALUES ('BCD');
INSERT INTO ITEMS VALUES ('CDE');
INSERT INTO ITEMS VALUES ('DEF');
INSERT INTO ITEMS VALUES ('EFG');
INSERT INTO ITEMS VALUES ('FGH');
INSERT INTO ITEMS VALUES ('GHI');
INSERT INTO ITEMS VALUES ('HIJ');
INSERT INTO ITEMS VALUES ('IJK');
INSERT INTO ITEMS VALUES ('JKL');
COMMIT;
WITH rowgen AS (
SELECT rownum rn FROM dual CONNECT BY rownum <= 5 )
SELECT item_number || '-' || rn
FROM items
CROSS JOIN rowgen
ORDER BY item_number, rn;
+----------------------+ | ITEM_NUMBER||'-'||RN | +----------------------+ | ABC-1 | | ABC-2 | | ABC-3 | | ABC-4 | | ABC-5 | | BCD-1 | | BCD-2 | | BCD-3 | | BCD-4 | | BCD-5 | | CDE-1 | | CDE-2 | | CDE-3 | | CDE-4 | | CDE-5 | | ... | +----------------------+