Multi-value parameter cannot be passed to SQL where clause (Oracle db)

Greetings!!

I have a problem with SSRS 2005 (simple rdl) on Oracle10g environment.

PROBLEM Definition

We have multi-valued list box, the selected values of which are being passed as parameters to Oracle 10g (back-end) from the report. I want to be able to pass these values as "WHERE......IN" clause in the main query in SSRS dataset window responsible for loading report. However, when I do that it throws error. I have tried all possible combinations i.e using IN operator with '@.', ':' , '&'. Now when I did the same with SQL server as back end; it worked. Can you please help with the exact syntax of executing this successfully?

Sample Query used in sample SSRS data-set when SQL server was at back end:

SELECT PKEY, PRODUCT, TIME, BRANCH, AGEGROUP, REVENUE, PROFIT, COST

FROM TEST_SSRS WHERE (PRODUCT IN (@.p))

The report worked fine in this case when the user selected certain values in multi-select list.

Thank you,

Karthik

Oracle can not support the query parameter with a name as your case "@.p". You may change your query into:

SELECT PKEY, PRODUCT, TIME, BRANCH, AGEGROUP, REVENUE, PROFIT, COST

FROM TEST_SSRS WHERE (PRODUCT IN ?)

Then set the parameter ? to one of your report parameter.

It should be work.

|||

Multi-value query parameter rewrite for Oracle only works with the "Oracle" data extension (i.e. data source type = "Oracle").
Note: Oracle uses ":" to mark named parameter - therefore the following query syntax should work:

SELECT PKEY, PRODUCT, TIME, BRANCH, AGEGROUP, REVENUE, PROFIT, COST
FROM TEST_SSRS WHERE PRODUCT IN (:p)

-- Robert

|||Excellent, the : method worked for me. However it only worked in the data view, and not the preview window. In both instances I am asked for the paramters which I specify. In the data view I have to type in the date value as a paramter but in the preview view I can select a date form the calendar. Basically I am getting results in the data view but nothing is retuned in the preview view, no error messages occur, just no data. Can anyone help?

Multi-value parameter cannot be passed to SQL where clause (Oracle db)

Greetings!!

I have a problem with SSRS 2005 (simple rdl) on Oracle10g environment.

PROBLEM Definition

We have multi-valued list box, the selected values of which are being passed as parameters to Oracle 10g (back-end) from the report. I want to be able to pass these values as "WHERE......IN" clause in the main query in SSRS dataset window responsible for loading report. However, when I do that it throws error. I have tried all possible combinations i.e using IN operator with '@.', ':' , '&'. Now when I did the same with SQL server as back end; it worked. Can you please help with the exact syntax of executing this successfully?

Sample Query used in sample SSRS data-set when SQL server was at back end:

SELECT PKEY, PRODUCT, TIME, BRANCH, AGEGROUP, REVENUE, PROFIT, COST

FROM TEST_SSRS WHERE (PRODUCT IN (@.p))

The report worked fine in this case when the user selected certain values in multi-select list.

Thank you,

Karthik

Oracle can not support the query parameter with a name as your case "@.p". You may change your query into:

SELECT PKEY, PRODUCT, TIME, BRANCH, AGEGROUP, REVENUE, PROFIT, COST

FROM TEST_SSRS WHERE (PRODUCT IN ?)

Then set the parameter ? to one of your report parameter.

It should be work.

|||

Multi-value query parameter rewrite for Oracle only works with the "Oracle" data extension (i.e. data source type = "Oracle").
Note: Oracle uses ":" to mark named parameter - therefore the following query syntax should work:

SELECT PKEY, PRODUCT, TIME, BRANCH, AGEGROUP, REVENUE, PROFIT, COST
FROM TEST_SSRS WHERE PRODUCT IN (:p)

-- Robert

|||Excellent, the : method worked for me. However it only worked in the data view, and not the preview window. In both instances I am asked for the paramters which I specify. In the data view I have to type in the date value as a paramter but in the preview view I can select a date form the calendar. Basically I am getting results in the data view but nothing is retuned in the preview view, no error messages occur, just no data. Can anyone help?

Multiple Where Clause on One Report

Hello People, Please help. I have a basic report with a parameter in the 'Where" clause called (@.Stat) from the statement below:

" WHERE contractinfo.termdate >= GETDATE()
AND provider.status= 'Active' AND provider.credentialstatus = (@.Stat)"

This variable has one of two values: 'A' or 'B' that the user selects, how do I set it up so that if user selects say 'A' then the Where clause would go to one set of constraints ie

"WHERE contract.description NOT LIKE 'NON%' "

But if the user selects 'B' then the Where clause would go to a different set of constraint ie

"WHERE contract.description LIKE 'NON%' "

Thanks

You might be able to change your WHERE clause from this:

Code Snippet

WHERE contractinfo.termdate >= GETDATE()

AND provider.status= 'Active' AND provider.credentialstatus = (@.Stat)

to this:

Code Snippet

WHERE contractinfo.termdate >= GETDATE()

AND provider.status= 'Active'

AND ( @.stat = 'A' AND contact.description not like 'NON%' OR

@.stat = 'B' AND contact.description like 'NON%'

)

|||Thanks for the help Kent that worked great, the only thing I did different for my report was leaving the original (@.Stat) parameter in also otherwise it would not have filtered it by the "A or B" condition and then adding your script suggestion.. Thanks again

Multiple where clause help please

Hello,
What is wrong with this query? There are no messages other than (0 row(s)
affected).
The criteria 5 and 16 does exist.
UPDATE articles
SET DEPART_REF = '19'
where ARTICLE_PARENT_ID = '5' or ARTICLE_PARENT_ID = '16'
Thanks in advance from a sql newbie
JakeNevermind, I am asking for the wrong fields in the where clause!
"GitarJake" <spamaintme@.oz.net> wrote in message
news:SXEXd.58461$SF.35123@.lakeread08...
> Hello,
> What is wrong with this query? There are no messages other than (0 row(s)
> affected).
> The criteria 5 and 16 does exist.
> UPDATE articles
> SET DEPART_REF = '19'
> where ARTICLE_PARENT_ID = '5' or ARTICLE_PARENT_ID = '16'
> Thanks in advance from a sql newbie
> Jake
>

Multiple where clause help please

Hello,
What is wrong with this query? There are no messages other than (0 row(s)
affected).
The criteria 5 and 16 does exist.
UPDATE articles
SET DEPART_REF = '19'
where ARTICLE_PARENT_ID = '5' or ARTICLE_PARENT_ID = '16'
Thanks in advance from a sql newbie
Jake
Nevermind, I am asking for the wrong fields in the where clause!
"GitarJake" <spamaintme@.oz.net> wrote in message
news:SXEXd.58461$SF.35123@.lakeread08...
> Hello,
> What is wrong with this query? There are no messages other than (0 row(s)
> affected).
> The criteria 5 and 16 does exist.
> UPDATE articles
> SET DEPART_REF = '19'
> where ARTICLE_PARENT_ID = '5' or ARTICLE_PARENT_ID = '16'
> Thanks in advance from a sql newbie
> Jake
>
sql

Multiple where clause help please

Hello,
What is wrong with this query? There are no messages other than (0 row(s)
affected).
The criteria 5 and 16 does exist.
UPDATE articles
SET DEPART_REF = '19'
where ARTICLE_PARENT_ID = '5' or ARTICLE_PARENT_ID = '16'
Thanks in advance from a sql newbie
JakeNevermind, I am asking for the wrong fields in the where clause!
"GitarJake" <spamaintme@.oz.net> wrote in message
news:SXEXd.58461$SF.35123@.lakeread08...
> Hello,
> What is wrong with this query? There are no messages other than (0 row(s)
> affected).
> The criteria 5 and 16 does exist.
> UPDATE articles
> SET DEPART_REF = '19'
> where ARTICLE_PARENT_ID = '5' or ARTICLE_PARENT_ID = '16'
> Thanks in advance from a sql newbie
> Jake
>

multiple values for single column in where clause

how does one specify multiple values for a single column in a where clause?

example:

SELECT fname, lname
FROM tblContacts
WHERE (state = 'MI','CA','AZ','TN','NJ')

if my memory serves me there is an IN() value list operator but I can't remember the syntax :confused:USE pubs
SELECT pub_name
FROM publishers
WHERE pub_id in ('1389', '0736')

See BOL for more.|||yup, thanks dudesql

multiple table joins, does the "ON" clause determine which two tab

How do you know which table is the intermediate or translation table in a
query using multiple joins.
For example:
SELECT T1.ID
FROM T1 LEFT OUTER JOIN T2 on T1.ID = T2.ID LEFT OUTER JOIN T3 ON T1.ID =
T3.ID
WHERE (T2.ID IS NULL) AND (T3.ID IS NULL)
T1
--
A
B
C
D
E
T2
--
A
X
B
Y
T3
--
B
H
R
J
K
X
E
Results of the query is:
C
D
In the above query is T2 the intermediate table or is it T1'
In both "ON" clauses T1 is used. This confuses me as to which table is the
intermediate.
Is there a way to use parenthesis to clarify the precedence or anything?
Thanks,
GeoffSELECT T1.ID
FROM T1
LEFT OUTER JOIN T2
on T1.ID = T2.ID
LEFT OUTER JOIN T3
ON T1.ID = T3.ID
WHERE (T2.ID IS NULL) AND (T3.ID IS NULL)
There is no "intermediate" table as I would think about that in an n:m
realtionsship. As it seems, the tabels all have the same primary keys which
sounds that they dont build up a many2many relationship. (Perhaps if you
would post up your real example and some DDL that would clarify the
situation a bit)

> Is there a way to use parenthesis to clarify the precedence or anything?
I always put tabs between the expression to chop up the important parts of
th query. (see above)
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Geoff" <Geoff@.discussions.microsoft.com> schrieb im Newsbeitrag
news:3D111A57-3DB2-4202-A6BD-F1530880A665@.microsoft.com...
> How do you know which table is the intermediate or translation table in a
> query using multiple joins.
> For example:
> SELECT T1.ID
> FROM T1 LEFT OUTER JOIN T2 on T1.ID = T2.ID LEFT OUTER JOIN T3 ON T1.ID =
> T3.ID
> WHERE (T2.ID IS NULL) AND (T3.ID IS NULL)
> T1
> --
> A
> B
> C
> D
> E
> T2
> --
> A
> X
> B
> Y
> T3
> --
> B
> H
> R
> J
> K
> X
> E
>
> Results of the query is:
> C
> D
>
> In the above query is T2 the intermediate table or is it T1'
> In both "ON" clauses T1 is used. This confuses me as to which table is the
> intermediate.
> Is there a way to use parenthesis to clarify the precedence or anything?
> Thanks,
> Geoff
>

multiple table joins, does the "ON" clause determine which two

Thanks for the response.
Those are the "real" tables. I was doing an exercise in trying to figure out
as many ways as I could to do the "minus" operation on three tables.
I am trying to go to the next level of DB programming, that is move up from
basic SQL queries. I was reading about "translation" or "intermediate" table
s
when you do joins and I didn't really understand when such a table comes int
o
existance.
Here is what I was reading:
http://msdn.microsoft.com/library/d...r />
_0hwz.asp
"The middle table of the join (the titleauthor table) can be called the
translation table or intermediate table, because titleauthor is an
intermediate point of connection between the other tables involved in the
join.
When there is more than one join operator in the same statement, either to
join more than two tables or to join more than two pairs of columns, the joi
n
expressions can be connected with AND or with OR."
"Jens Sü?meyer" wrote:

> SELECT T1.ID
> FROM T1
> LEFT OUTER JOIN T2
> on T1.ID = T2.ID
> LEFT OUTER JOIN T3
> ON T1.ID = T3.ID
> WHERE (T2.ID IS NULL) AND (T3.ID IS NULL)
> There is no "intermediate" table as I would think about that in an n:m
> realtionsship. As it seems, the tabels all have the same primary keys whic
h
> sounds that they don′t build up a many2many relationship. (Perhaps if you
> would post up your real example and some DDL that would clarify the
> situation a bit)
>
> I always put tabs between the expression to chop up the important parts of
> th query. (see above)
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Geoff" <Geoff@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:3D111A57-3DB2-4202-A6BD-F1530880A665@.microsoft.com...
>
>But taht depends on your design wheter you have a 1-n or n-m relationship.
1-n One Customer can have multiple orders.
n-m One Author can write multiple book, but one book can be written by
multiple authors, therefore you need this intermediate table where you make
the connection between the values:
AUTHORS
========
AuthId AuthorName
1 Jens Smeyer
2 Peter Gedoens
BOOKS
======
BookId BookName
1 YourExampleBook
2 SQL Server the curse of relationals
3 Oracle sucks
AuthorBooks
=========
AuthorId BookId
1 1
2 1
1 2
1 3
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Geoff" <Geoff@.discussions.microsoft.com> schrieb im Newsbeitrag
news:EEA9BA2B-F086-4F4C-8A5B-C7788F033D9D@.microsoft.com...
> Thanks for the response.
> Those are the "real" tables. I was doing an exercise in trying to figure
> out
> as many ways as I could to do the "minus" operation on three tables.
> I am trying to go to the next level of DB programming, that is move up
> from
> basic SQL queries. I was reading about "translation" or "intermediate"
> tables
> when you do joins and I didn't really understand when such a table comes
> into
> existance.
> Here is what I was reading:
> http://msdn.microsoft.com/library/d.../>
09_0hwz.asp
> "The middle table of the join (the titleauthor table) can be called the
> translation table or intermediate table, because titleauthor is an
> intermediate point of connection between the other tables involved in the
> join.
> When there is more than one join operator in the same statement, either to
> join more than two tables or to join more than two pairs of columns, the
> join
> expressions can be connected with AND or with OR."
>
>
>
> "Jens Smeyer" wrote:
>

multiple subcubes in one MDX query

Is it possible to use more than one subcube in one MDX statement? Putting a subcube in the FROM clause is great, but sometimes you need more than one.

One subcube in the from clause is nice, but sometimes you need to use one subcube to limit the totals for one column, but then you need a different subcube to limit the totals of another column. For instance, if users can dynamically pick a list of stores and you want the total sales for the stores they pick, a subcube in the from clause will do that. But then if you want a second column to show the totals of peer stores (which are not in the subcube), then you're in trouble.

I think I know the answer to this question already, but I thought I'd ask just to make sure.

The silence is deafening ;-)

In SQL you can use multiple subqueries such as:

select column1
,column2 = (select x from y where a=b)
,column3 = (select x from z where c=d)
from aaa

In MDX, I think you can only have a subcube in the FROM clause, so you can't do more than one subcube per query. Is that correct?

|||

No - you can only use subcubes in the from clause, if you have more than one, they have to be nested - effectively narrowing the scope, which would not help in your example.

In the example you have given I would consider approaching it from the other way. Create a subcube in the from clause that picks all the peer stores, then use a calculated measure or a tuple to pick out the total for the particular store that the user selected.