1

Topic: The request does not work

Hello!
There is a SQL query which preempts a part of the data from OLAP.
[spoiler SQL-]

WITH Profit AS (
SELECT
CAST (F1. "[Shops]. [The TT Code]. [The TT Code]. [MEMBER_CAPTION]" AS VARCHAR (20)) AS [the TT Code];
CAST (F1. "[Production]. [Production Code]. [Production Code]. [MEMBER_CAPTION]" AS VARCHAR (20)) AS [production Code];
CONVERT (nvarchar (10), F1. "[Time]. [Date]. [Date]. [MEMBER_CAPTION]", 104) AS [Date];
F1. "[Measures]. [Shipments ]" AS [Shipments ];
F1. "[Measures]. [Resets ]" AS [Resets ]
FROM OPENROWSET (
' MSOLAP ';
' Provider=MSOLAP.3; Integrated Security=SSPI; Persist Security Info=True; Initial Catalog=profit; Data Source=xxx.yyy.ru; MDX Compatibility=1; Safety Options=2; MDX Missing Member Mode=Error ';
' SELECT
{[Measures]. [Shipments ];
[Measures]. [Resets ]}
ON 0;
NON EMPTY
[Shops]. [The TT Code]. [The TT Code] *
[Production]. [Production Code]. [Production Code] *
[Time]. [Date]. [Date]
ON 1
FROM
(SELECT
([Time]. [Date] .& [2017-06-01T00:00:00]: [Time]. [Date] .& [2017-08-10T00:00:00],
{[Shops]. [The Client]. [Klient1]},
{[Cities]. [City] .& [1];
[Cities]. [City] .& [3]}
) ON 0
FROM PROFIT)'
) AS F1
)
;
Block AS (
SELECT
CustItemBlocking. ITEMID AS [the nomenclature code];
CustItemBlocking. CUSTACCOUNTSP AS [the TT code],
CustItemBlocking. BLOCKED AS [lock]
FROM
Server1.DataBase1.dbo. CustItemBlocking CustItemBlocking
)
SELECT
Profit. [The TT code];
Profit. [Production code];
Profit. [Date];
Block. [Lock]
FROM
Profit
LEFT JOIN
Block
ON
>>> Profit. [The TT Code] =Block. [The TT Code] <<<
AND
>>> Profit. [Production Code] = block. [The TT Code] <<<

[/spoiler]
Produces such error "it was not possible to resolve the conflict of parameters of sorting between" Cyrillic_General_CI_AS "and" Cyrillic_General_CI_AI "in operation equal to." .
The error quits from for the selected lines.
Why writes about sorting if in request misses Order By ? How it is necessary to write request correctly?

2

Re: The request does not work

ferzmikk;
There, where the such writes, it is possible to try to write explicitly
COLLATE CYRILLIC_GENERAL_CI_AS

3

Re: The request does not work

xenix wrote:

there where the such writes, it is possible to try to write explicitly
COLLATE CYRILLIC_GENERAL_CI_AS

In what place in the given request it is necessary to write or it as separate request?

4

Re: The request does not work

ferzmikk;
Data types in request it is not visible, therefore I would make somehow so

FROM
Profit
LEFT JOIN
Block
ON
Profit. [The TT code] COLLATE CYRILLIC_GENERAL_CI_AS=Block. [The TT Code] COLLATE CYRILLIC_GENERAL_CI_AS
AND
Profit. [Production code] COLLATE CYRILLIC_GENERAL_CI_AS = block. [The TT Code] COLLATE CYRILLIC_GENERAL_CI_AS

5

Re: The request does not work

xenix wrote:

data types in request it is not visible

Fields CustItemBlocking. [The nomenclature Code] and CustItemBlocking. [The TT Code] have type nvarchar (20). And CustItemBlocking. [Lock] - int.

6

Re: The request does not work

xenix wrote:

ferzmikk;
Data types in request it is not visible, therefore I would make somehow so

FROM
Profit
LEFT JOIN
Block
ON
Profit. [The TT code] COLLATE CYRILLIC_GENERAL_CI_AS=Block. [The TT Code] COLLATE CYRILLIC_GENERAL_CI_AS
AND
Profit. [Production code] COLLATE CYRILLIC_GENERAL_CI_AS = block. [The TT Code] COLLATE CYRILLIC_GENERAL_CI_AS

The request earned!

7

Re: The request does not work

xenix;
Thanks!

8

Re: The request does not work

ferzmikk;
And it is better to change Collation in the initial data on which is under construction OLAP.

9

Re: The request does not work

bideveloper wrote:

and it is better to change Collation in the initial data on which is under construction OLAP.

You mean in the MDX-inquiry or in the environment of SQL Server Business Intelligence Development Studio?
It allows to increase speed of outswapping considerably?

10

Re: The request does not work

ferzmikk wrote:

you mean in the MDX-inquiry or in the environment of SQL Server Business Intelligence Development Studio?
It allows to increase speed of outswapping considerably?

In basis MSSQL or in storage MSOLAP
It is bad, when different , and yes, it can accelerate requests, and generally will be problems less.

ferzmikk wrote:

ON
Profit. [The TT code] =Block. [The TT Code]
AND
Profit. [Production code] = block. [The TT Code]

Generally the strange conditions, it turns out, in PROFIT a field [Shops]. [The TT Code]. [The TT Code] always equally [Production]. [Production Code]. [Production Code]

11

Re: The request does not work

alexeyvg wrote:

it is passed...

ON
Profit. [The TT code] =Block. [The TT Code]
AND
Profit. [Production code] = block. [>>> the nomenclature Code <<<]