11
Views
4
Comments
Solved
I have problems with Order By in SQL
Question
Application Type
Mobile
Service Studio Version
11.10.8 (Build 37885)

Good afternoon,


I am trying to do a dynamic Order By, I have verified the query and I cannot find the fault.

But for some reason it is not ordering me with the CASE commands.

Has anyone tried to do a dynamic order by or find an error in my query?


SELECT 
     {SaleDetail}.[ProductId] AS 'ProductId', SUM({SaleDetail}.[Count]), SUM({SaleDetail}.[Gain]), SUM({SaleDetail}.[Cost]), {Product}.[Name]
From
    {Sale} INNER JOIN {SaleDetail}
            ON {Sale}.[Id] = {SaleDetail}.[SaleId]
        INNER JOIN {Product}
            On {SaleDetail}.[ProductId] = {Product}.[Id]
WHERE
        {Sale}.[Date] >= (CASE WHEN @DateTimeIni = '1900-01-01 00:00:00' THEN {Sale}.[Date] ELSE @DateTimeIni END) AND 
        {Sale}.[Date] <= (CASE WHEN @DateTimeEnd = '1900-01-01 00:00:00' THEN {Sale}.[Date] ELSE @DateTimeEnd END) AND
        -- Obligatorio
        {Sale}.[Is_Active] = 1 AND
        {SaleDetail}.[Is_Active] = 1
        
GROUP BY {SaleDetail}.[ProductId], {Product}.[Name]

ORDER BY (CASE @OrderByQuantityGainSale 
                WHEN 'Quantity' THEN 2
                WHEN 'Gain' THEN 3
                WHEN 'Sale' THEN 4
                ELSE 1
            END) DESC


By default it should be

Order by 1 Desc



Rank: #55
Solution

Hi Cristian,


1. Set your attribute @OrderQuantityGainSale to have the property Expand Inline set to True and Data Type to Text.

2. Change your order by syntax to be ORDER BY @OrderQuantityGainSale DESC

3. Fill this parameter value with the following:

If(<Variable> = "Quantity", "1", If(<Variable>= "Gain", "3", If(<Variable>= "Sale", "4", "1")))

Replace your <Variable> for the name of your variable, like in my example the variable is SQLFilter.

4. In order to Test successfully the AdvancedSQL in Service Studio make sure you fill in the example one of the following values: 1,2,3 or 4.  


Regards,
João

HELP ! :c

Rank: #55
Solution

Hi Cristian,


1. Set your attribute @OrderQuantityGainSale to have the property Expand Inline set to True and Data Type to Text.

2. Change your order by syntax to be ORDER BY @OrderQuantityGainSale DESC

3. Fill this parameter value with the following:

If(<Variable> = "Quantity", "1", If(<Variable>= "Gain", "3", If(<Variable>= "Sale", "4", "1")))

Replace your <Variable> for the name of your variable, like in my example the variable is SQLFilter.

4. In order to Test successfully the AdvancedSQL in Service Studio make sure you fill in the example one of the following values: 1,2,3 or 4.  


Regards,
João

Hello João Marques,

Thanks for your help and your answer.

I tried your example, in different ways and it throws me error.


----------------------------------------------------------------------------------
----------------------------------------------------------------------------------


Rank: #287

Hi Cristian 

As João mentioned in his reply, You have to set Expand Inline property to True.

Kind Regards