Home » SQL & PL/SQL » SQL & PL/SQL » Need help to build dynamic query (Oracle 11G)
Need help to build dynamic query [message #650025] Wed, 13 April 2016 00:02 Go to next message
irish
Messages: 5
Registered: April 2016
Junior Member
Hi Good Morning

Need help to build dynamic where query.

CREATE TABLE PT_PAR
(
QUEUE_ID NUMBER(9) NOT NULL,
SEQ_NO NUMBER(5) NOT NULL,
VAL VARCHAR2(250 BYTE) NOT NULL,
PT_ID NUMBER(4),
PTDESC VARCHAR2(60 BYTE)
)


INSERT INTO PT_PAR VALUES (1000,1,49,1,'VAR'); ---pg_id single
INSERT INTO PT_PAR VALUES (1000,1,'00012',1,'VAR'); --lbl
INSERT INTO PT_PAR VALUES (1000,1,'90,100,101,102',1,'VAR');---time_idd
INSERT INTO PT_PAR VALUES (1000,1,'00013',1,'VAR'); ---lbl
INSERT INTO PT_PAR VALUES (1000,1,'90,98,97',1,'VAR'); ----time_id
INSERT INTO PT_PAR VALUES (1000,1,'00014',1,'VAR'); --lbl
INSERT INTO PT_PAR VALUES (1000,1,'90,98,99',1,'VAR'); --time_id
INSERT INTO PT_PAR VALUES (2000,1,49,1,'VAR'); ---pg_id single
INSERT INTO PT_PAR VALUES (2000,1,'00012',1,'VAR'); --lbl

I have table like above and parameters will come automatically through different process.

The input parameter is queue id. based on this we need to frame query. for ex the input is 1000
then need to build where condition like below using PT_PAR table until the row ends. and need to pass this where to another view (VAR_RPT_V)

WHERE pg_id=49
and( lbl='00012'
and time_id in (90,100,101,102))
or (lbl='00013'
and time_id in (90,98,97))
or (lbl='00014'
and time_id in (90,98,99))

Pass the dynamic where to another view
SELECT * FROM VAR_RPT_V
WHERE pg_id=49
and( lbl='00012'
and time_id in (90,100,101,102))
or (lbl='00013'
and time_id in (90,98,97))
or (lbl='00014'
and time_id in (90,98,99))

Re: Need help to build dynamic query [message #650040 is a reply to message #650025] Wed, 13 April 2016 03:37 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's not at all obvious how you tell from the data which rows should be used to restrict which columns or how you tell when to use an OR rather than an AND.
You need explain the rows that transform that data into that where clause.
Previous Topic: Stored Procedure: XML versus Array for multi-table insert
Next Topic: Data count for Likert Scale
Goto Forum:
  


Current Time: Fri Mar 29 02:59:26 CDT 2024