 multiselect substitution variables
|
Joined: Sep 2015
Posts: 29
Member
|
OP
Member
Joined: Sep 2015
Posts: 29 |
I have Version 11.0.2.1766 In the manual (section 7.5) there is example : select * from emp
where deptno = &<name=''Department number''>
order by ename Just to mention that actually it must be like this (with single quotes, otherwise it wont work): select * from emp
where deptno = '&<name=''Department number''>'
order by ename So now my question. When I try to use multiselect substitution variables according to the manual it must be lie this: select * from emp
where deptno IN (&<name=''Department number''
list=''select deptno from dept order by dname''
multiselect=''yes''>)
order by ename But I dont get any values, obviously because it concatenates all entries and searches for them. How to pass the separate values so that they are recognized and eventually found in the db? P.S. And we might need to add the single quotes as well on top.
Last edited by kirilb; 01/09/18 11:29 AM.
|
|
|
Re: multiselect substitution variables
|
Joined: Sep 2003
Posts: 387 London, UK
Member
|
|
Member
Joined: Sep 2003
Posts: 387 London, UK |
Section 7.5 in my 11.1 manual has the example as select * from emp
where deptno=&deptno
order by ename Your example using quoted single quotes fails for me with "Substitution variable not properly terminated". The extended syntax with named attributes should use double-quotes, e.g: select * from emp
where deptno = &<name="Department number">
order by ename If you wanted to convert the variable value into a quoted string, you would add type=string. (You could also use type="string" but you only need to quote multi-word strings.) The following works for me: with emp (empno, ename, deptno) as
( select 1, 'FORD', 10 from dual union all
select 2, 'ROBERTSON', 20 from dual union all
select 3, 'KING', 10 from dual )
select * from emp
where deptno in (&<name="Department number" list="select 10 from dual union all select 20 from dual" multiselect=yes>)
order by ename;This lets me select 10 and 20, and generates this (from v$sql): with emp (empno, ename, deptno) as
( select 1, 'FORD', 10 from dual union all
select 2, 'ROBERTSON', 20 from dual union all
select 3, 'KING', 10 from dual )
select * from emp
where deptno in (10, 20)
order by ename
|
|
|
Re: multiselect substitution variables
|
Joined: Sep 2015
Posts: 29
Member
|
|
OP
Member
Joined: Sep 2015
Posts: 29 |
ok, your example based on the dual table worked also in my environment. But I see a difference to the manual, in your example multiselect=yes is without quotes. Anyway, thanks so far. When I use your example and just replacing with my attributes/table in a very simplistic query I still get a error message (ORA-00907: missing right parenthesis): SELECT *
FROM MY_TABLE A
WHERE MY_ATTRIBUTE = (&< NAME = "my_attribute"
LIST = "select distinct my_attribute from my_table"
MULTISELECT = YES >)
|
|
|
Re: multiselect substitution variables
|
Joined: Sep 2015
Posts: 29
Member
|
|
OP
Member
Joined: Sep 2015
Posts: 29 |
Ok, I am now a step further. Because my entries in MY_ATTRIBUTE are with blanks inside like this
This is an entry 1,
This is an entry 2
and so on and thought it could be related to this. Then I put single quotes in the popup of the substitution variables like this
'This is an entry 1', 'This is an entry 2' and bingo, it worked!
So how can I amend the query to handle such entries with blanks?
|
|
|
Re: multiselect substitution variables
|
Joined: Mar 2007
Posts: 210 Rostock, Germany
Member
|
|
Member
Joined: Mar 2007
Posts: 210 Rostock, Germany |
Hi, the tags for values of substitution variables must have nothing or doublequotes instead of two single quotes. right: MULTISELECT = YES MULTISELECT = "YES" wrong: MULTISELECT = ''YES'' Here is on example from my butterfly-collection ;-). I use this here to collapse or expand the amount of colums in an select-clause. There are Doublequotes to start and end aliases (with special chars) and to surround tags like list, and with the double Doublequotes you can mask the comma in one list-item
WITH journal AS
(SELECT 4711 jour_lfd_nr
,300 jour_value
,100 jour_value_w1
,195 jour_value_w2
,'S' jour_d_k
FROM dual
UNION
SELECT 4712 jour_lfd_nr
,150 jour_value
,50 jour_value_w1
,97 jour_value_w2
,'S' jour_d_k
FROM dual)
SELECT jo.jour_lfd_nr "Jo Id"
,jour_d_k "D/K"
,jo.jour_value_w1 "val"
-- NoFormat Start
&< name = "show W1/W2"
hint = "Show Cols W1/W2?"
list=" "" ,jo.jour_value_w1 as ""val w1"" ,jo.jour_value_w2 AS ""val w2"" "",yes
,"" "", no
"
default = "yes"
description = "yes"
restricted = "yes"
>
-- NoFormat End
FROM journal jo
ORDER BY to_number(jo.jour_lfd_nr);
Last edited by Theod; 01/10/18 10:42 AM.
There is no shortcut for experience(d).
PL/SQL Developer Version 10+11+12, Query Reporter Version 3.2.0.300
Oracle 11g,12c
Windows Server 2003/2008
Windows 10 Professionel 64 bit
|
|
|
Re: multiselect substitution variables
|
Joined: Sep 2003
Posts: 387 London, UK
Member
|
|
Member
Joined: Sep 2003
Posts: 387 London, UK |
[quote=kirilb]I see a difference to the manual, in your example multiselect=yes is without quotes.[/quote]
The doublequotes are only needed for multi-word expressions, so you need to quote something like "One Two Three" but it's optional for "One".
[quote=kirilb]Then I put single quotes in the popup of the substitution variables like this
'This is an entry 1', 'This is an entry 2' and bingo, it worked![/quote]
The substitution system should be able to handle all quoting without you having to add anything yourself. Set the type as string and it'll add the single quotes for you.
|
|
|
Re: multiselect substitution variables
|
Joined: Sep 2015
Posts: 29
Member
|
|
OP
Member
Joined: Sep 2015
Posts: 29 |
type = "string" was the hint which enabled my multiselection. Many thanks!
|
|
|
Re: multiselect substitution variables
|
Joined: Sep 2015
Posts: 29
Member
|
|
OP
Member
Joined: Sep 2015
Posts: 29 |
But then, next question. How to pass the entries from my_attribute from rows to columns in a pivot: WTIH FIRST_STEP AS
(SELECT *
FROM MY_TABLE A
WHERE MY_ATTRIBUTE IN '(&< NAME = "my_attribute"
TYPE = "string"
LIST = "select distinct my_attribute from my_table"
MULTISELECT = YES >)'
)
SELECT * FROM FIRST_STEP
PIVOT (MIN(MY_COUNTER) AS MY_COUNTER FOR(MY_ATTRIBUTE) IN('&FIRST_ENTRY_FROM_my_attribute' AS
"&FIRST_ENTRY_FROM_my_attribute",
'&SECOND_ENTRY_FROM_my_attribute' AS
"&SECOND_ENTRY_FROM_my_attribute" and so on ....
))
|
|
|
Re: multiselect substitution variables
|
Joined: Sep 2003
Posts: 387 London, UK
Member
|
|
Member
Joined: Sep 2003
Posts: 387 London, UK |
Not sure how close this is to what you want, but the nearest I can see is this case from the manual: [quote]The items in a list can sometimes depend on the value of another variable. Imagine that you want to create a report that shows all columns of a specific table. A table is identified by the owner and the name. For the owner you can use a list of all users in the database. For the table name you can query the all_tables view for the selected owner:[/quote] select * from all_tab_columns
where owner =
&<name="Owner" type="string" list="select username from all_users order by username">
and table_name =
&<name="Table" type="string" list="select table_name from all_tables where owner = :owner order by table_name">
order by column_id[quote]As you can see, the select statement for the second (table) list refers to the first variable through the :owner bind variable. Whenever the value of the owner is changed, the table list will be populated. Note that the name of the bind variable cannot be longer than 30 characters, and cannot contain spaces or other special characters. Spaces in the variable name will be converted to underscores. If the variable name had been Owner of the table, then the bind variable would have been :owner_of_the_table. Also note that if the value of a variable is empty, the dependent list will also be empty. No query will be performed in this situation.[/quote] In the sample query, the second variable expression includes where owner = :owner, which refers back to the &owner variable (I would have wrapped the text so you could see it more easily but unfortunately that breaks the forum syntax highlighting.)
|
|
|
|
|
