multiselect substitution variables

[kirilb]

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.

 

[William Robertson]

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

 

[kirilb]

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 >)

 

[kirilb]

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?

 

[Theod]

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);

 

[William Robertson]

I see a difference to the manual, in your example multiselect=yes is without quotes.

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".

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!

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.

 

[kirilb]

type = "string" was the hint which enabled my multiselection. Many thanks!

 

[kirilb]

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 ....
                                                  ))

 

[William Robertson]

Not sure how close this is to what you want, but the nearest I can see is this case from the manual:

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:

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

As you can see, the select statement for the second (table) list refers to the first variable through the wner 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 wner_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.

In the sample query, the second variable expression includes where owner = wner, 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.)