link server with case expression

anybody know how many case expression can be in linked server query?
when i have more then 10 "case" expression witjh linked server, sql query
give this message :
Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 170, Level 15, State 1, Line 1
Line 8: Incorrect syntax near 'Qry1068'.
Server: Msg 125, Level 15, State 1, Line 1
Case expressions may only be nested to level 10.
any microsoft document or page show the detail about this?
Thanks in advance
SoonyuPlease post your query
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"soonyu" wrote:

> anybody know how many case expression can be in linked server query?
> when i have more then 10 "case" expression witjh linked server, sql query
> give this message :
> Server: Msg 8180, Level 16, State 1, Line 1
> Statement(s) could not be prepared.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 8: Incorrect syntax near 'Qry1068'.
> Server: Msg 125, Level 15, State 1, Line 1
> Case expressions may only be nested to level 10.
> any microsoft document or page show the detail about this?
> Thanks in advance
> Soonyu|||l 2000 sp3 sit in win2003
when we run the quere in server A.. it look okay with case expression more
then 10
but when we run query in server B with linkserver to server A
sql server give this message
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Qry1068'.
Msg 125, Level 15, State 4, Line 1
Case expressions may only be nested to level 10.
select distinct rp.invoice_id,
CASE
WHEN (rp.product_group_code = 2 and c.catelog_id = 1) then 'PG'
WHEN (rp.product_group_code = 2 and c.catelog_id = 3) then 'JB'
WHEN (rp.product_group_code = 9 and c.catelog_id = 1) then 'KL'
WHEN (rp.product_group_code = 9 and c.catelog_id = 2) then 'SG'
WHEN (rp.product_group_code = 9 and c.catelog_id = 3) then 'JB'
WHEN (rp.product_group_code = 8 and c.catelog_id = 1) then 'GEORGETOWN'
WHEN (rp.product_group_code = 3 and c.catelog_id = 2) then 'GENTING'
WHEN (rp.product_group_code not in (2, 3, 8, 9) and c.catelog_id = 1) then
'PERAK'
WHEN (rp.product_group_code not in (2, 3, 8, 9) and c.catelog_id = 2) then
'N9'
WHEN (I.product_code not in (2, 3, 8, 9) and c.catelog_id = 3) then 'KEDAH'
WHEN (I.product_code not in (2, 3, 8, 9) and c.catelog_id = 3) then 'PERIS'
ELSE ''
END
from a.db1.dbo.invoice rp, a.db1.dbo.contract c
WHERE rp.id=c.id
if i take out of one of the case condition, no error and result come out
I just wonder whether this is sql2000 sp3 bug or limitation using case
expression over sql linked server
Regards
"Wayne Snyder" wrote:
> Please post your query
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "soonyu" wrote:
>

Line location as expression

Is it possible to move lines based on the values from dataset(trying to draw graph kinda thing by joining points using lines) ?Let me know if I am not clear here.

Thanks

Yes, it is possible if I understand you correctly and if you are willing to do some custom rendering. Check out CustomReportItem and look at the Polygons sample in the RS sample stuff.

If you go this route, take it from me you will find out it's not all about 1's and 0's <rofl>.

But, yes, you are really not being clear <g>. So maybe I've misunderstood...

>L<

sql

Line feed formating

I have put a chr(10) line feed in a expression and it displays just fine in the preview in VS. When I deploy it to the web server it ignores it.

How do I get the line feed to continue to the web server?

="Displays Job's that do not have serial numbers assigned to them form the pervious 30 days." & Chr(10) &
"Products for: " & Parameters!ProdClass.Label & ", Reporting dates: " & Format(DateAdd("d", -30, Now), "d") & " - " & Today

Try this instead:

="Displays Job's that do not have serial numbers assigned to them form the pervious 30 days." & VbCrLf &
"Products for: " & Parameters!ProdClass.Label & ", Reporting dates: " & Format(DateAdd("d", -30, Now), "d") & " - " & Today

-- Robert

|||That worked great Thanks!

Limitations of MeasureExpression

I have a many to many dimensional problem that I am trying to solve.

Am I right in thinking that MeasureExpression is limited to a simple expression such as [value]*[percantage] and cannot cope with [value]*[percentage1]*[percentage2]?

What I am trying to achieve is similar to the example under the heading Measure Expressions and Many-to-Many Dimensions in the article http://msdn2.microsoft.com/en-US/library/ms345139.aspx. In this example there is a many to many relationship between Organisation and Store Sales; and the weighted [Amount] is calculated in the MeasureExpression as [Amount]*[Ownership].

They key difference in my scenario is that instead of a single many to many relationship between Organisation and Store Sales I have several relationships so I would need to have a calculation such as [Amount]*[Ownership]*[Sales Director Percentage].

Is there a way that MeasureExpression could support more complex calculations - or is there a better way to achieve this?

Thanks

Yes, the measure expressions are limited to A*B or A/B. You should check out Marco Russo's paper on many-to-many dimensions and see if there is anything in there you can use: http://www.sqlbi.eu/Default.aspx?tabid=80|||

Thanks for your input Michael. In fact I had come across that document the previous day, but it doesn't cover my particular scenario. The tricky bit is always that fact that there is a weighting to be applied to each of the many-to-many dimensions.

In the end I decided to handle this in the ETL by breaking down the fact table into constituent parts representing each unique combination and have SSAS aggregate only those needed for each calculation. It seems to me that SSAS is very good at aggregation, but not so good at apportioning values.

We live and learn ...

|||

What about this case:

create intermediate measure as AB = A * B and set its visible property to false

and than create your target measure as MyMeasure = AB * C

|||

I guess that would work in theory, but since I actually need A * B * C * D * E I would need three invisible measures and one visible one which seems a little clunky.

Having talked this through with a couple of other people I think the solution of breaking this down in the ETL process and having SSAS simply perform the aggregation seems to be the best approach.

Thanks.

|||

Hi,

you can try and use Calculated Measures (calculations).

The expressions here can take multiple measures, and also infact invoke .Net assemblies/ stored procedures.

Apart from that , you can use MDX and its array of functions.

check the MSDN site on Calculated Measures.

HTH

Regards