Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic Logic (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production)
Dynamic Logic [message #677120] Tue, 20 August 2019 06:15 Go to next message
vippysharma
Messages: 42
Registered: May 2013
Location: New Delhi
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 #677121 is a reply to message #677120] Tue, 20 August 2019 06:27 Go to previous messageGo to next message
EdStevens
Messages: 1116
Registered: September 2013
Senior Member
Your data model is so seriously, fatally flawed in so many ways I don't even know where to begin.
Google "Data Normalization" and "Third Normal Form".
Correct your data model and your problem will likely disappear.
Re: Dynamic Logic [message #677122 is a reply to message #677121] Tue, 20 August 2019 06:38 Go to previous messageGo to next message
vippysharma
Messages: 42
Registered: May 2013
Location: New Delhi
Member
This is nothing to do with Data Normalization or Data Modeling in my requirement here or what i want to achieve here, because i don't want to save any data set in my DB (even what i am saving in temp table i.e. Some_Temp_table is for view purpose only and dropped after use). I just need one optimized logic to create above SQL using some variable String(s) values, that's it.
Re: Dynamic Logic [message #677123 is a reply to message #677122] Tue, 20 August 2019 07:17 Go to previous messageGo to next message
Bill B
Messages: 1889
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

Re: Dynamic Logic [message #677134 is a reply to message #677122] Wed, 21 August 2019 06:31 Go to previous messageGo to next message
EdStevens
Messages: 1116
Registered: September 2013
Senior Member
vippysharma wrote on Tue, 20 August 2019 06:38
This is nothing to do with Data Normalization or Data Modeling in my requirement here or what i want to achieve here, because i don't want to save any data set in my DB (even what i am saving in temp table i.e. Some_Temp_table is for view purpose only and dropped after use). I just need one optimized logic to create above SQL using some variable String(s) values, that's it.
So where do your strings come from?
Strings ARE data, and the format of those strings, as well as the relations of the data in one string to the data in another string, IS an aspect of data modeling regardless of whether or not the end result goes into a permanent table or not.

This is starting to show symptoms of the 'X-Y Problem'.
Re: Dynamic Logic [message #677154 is a reply to message #677134] Fri, 23 August 2019 01:59 Go to previous message
vippysharma
Messages: 42
Registered: May 2013
Location: New Delhi
Member
Thanks Bill, got some idea to tart with... Smile

@EdStevens... Agree with your thought but in my problem nothing to do with any DB relations or logical core values implementations, i was just looking for one simple way to take care my anonymous problem logically. Never mind, thank you for your output too,
Previous Topic: Bursting Program is not being Submitted
Next Topic: Compare two tables to find new/modified/removed records
Goto Forum:
  


Current Time: Fri Sep 20 19:26:03 CDT 2019