| Dynamic Logic [message #677120] |
Tue, 20 August 2019 06:15  |
 |
vippysharma
Messages: 73
Registered: May 2013
Location: www
|
Member |
|
|
I have two strings i.e.
STR1 = 'Col1,Col2,Col3';
STR2 = 'Val1,Val2,Val3';
I want to create SQL using above strings in below format...
Select * into Some_Temp_Table from MyTable where Col1=Val1 and Col2=Val2 and Col3=Val3;
I am unable to put any logic here, what i thought first is to find out value of N from given string, means how many values we have in string and then generate this portion Var:= 'Col1=Val1 and Col2=Val2 and Col3=Val3' in some variable and then use that variable in Execute Immediate to get above SQL like
SQLis := 'Select * into Some_Temp_Table from MyTable where '||Var;
EXECUTE IMMEDIATE SQLis;
Any optimized way for above query/Problem ?
Var and SQLis are simple variable with Varchar2 datatype
Note: Strings can have N number of values like Col1,Col2,...,ColN but value of N will remains same in both strings (for ex, if STR1 has 3 values then STR2 should also have 3 values, as in above example)
|
|
|
|
|
|
|
|
| Re: Dynamic Logic [message #677123 is a reply to message #677122] |
Tue, 20 August 2019 07:17  |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
The easiest way would be to make a view then a standard query
CREATE TABLE MY_TABLE
(
STR1 VARCHAR2(500 BYTE),
STR2 VARCHAR2(500 BYTE)
);
Insert into MY_TABLE
(STR1, STR2)
Values
('Col1,Col2,Col3', 'Val1,Val2,Val3');
COMMIT;
CREATE OR REPLACE FORCE VIEW My_table_v
(
Col_1,
Col_2
)
BEQUEATH DEFINER
AS
SELECT REGEXP_SUBSTR (Str1,
'[^,]+',
1,
LEVEL) Col_1,
REGEXP_SUBSTR (Str2,
'[^,]+',
1,
LEVEL) Col_2
FROM My_table
CONNECT BY REGEXP_SUBSTR (Str1,
'[^,]+',
1,
LEVEL)
IS NOT NULL;
select * from my_table_v where col_1 = col_2;
[Updated on: Tue, 20 August 2019 07:19] Report message to a moderator |
|
|
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
