Solved

Lobby Data Source Designer: Column As not allowed

  • 21 September 2020
  • 10 replies
  • 279 views

Userlevel 4
Badge +10
  • Sidekick (Customer)
  • 119 replies

Trying to sort a column by order process and not alphabetically for a Lobby and it doesn’t like the as Status format.

The similar quick report works fine.

Is this not possible in a Lobby?

 

case when state = 'Blocked' then '1 - Blocked' when state = 'Planned' then '2 - Planned' when state = 'Released'  then '3 - Released' when state = 'Reserved'  then '4 - Reserved' when state = 'Picked'  then '5 - Picked' when state = 'Partially  Delivered'  then '6 - Partially Delivered' when state = 'Delivered'  then '7 - Delivered' end as Status

 

Quick report has desired seqeunce:

 

Lobby is currently alphabetically:

 

icon

Best answer by SANJ 21 September 2020, 18:37

View original

10 replies

Userlevel 7
Badge +24

you should not need “as status” here

just put the rest of the text (up to “END”) into the column, and then name it.

Userlevel 4
Badge +10

thx for your reply. I tried that but then the Order by doesn’t work and I get an error:

 

 

Userlevel 1
Badge +6

Hi Bob,

No need to use case statement in the state column to display but use order by as below,

in the order by clause,
decode(state,'Blocked',1,'Planned',2,'Released',3,'Reserved',4,'Picked',5,'Partially  Delivered',6,'Delivered',7)

Thanks,
Sanjay

Userlevel 4
Badge +10

Thx for the idea. However that code merely replaces the text for a number; I need for it to be sorted on my case results text; not the DB status value- see my 1st print screen in my original post. Blocked, Planned, Released, etc...

 

 

Userlevel 7
Badge +18

ORDER BY columns don’t get AS aliases in SQL. However, if we’re working in pure SQL without a middle application trying to interpret it, you can do an ORDER BY on the alias itself: ORDER BY status

Userlevel 1
Badge +6

Hi,

If you want to display the column value as same as your quick report then you can use case statement and don't use “as status” suggested by Paul and use the order by value as

decode(state,'Blocked',1,'Planned',2,'Released',3,'Reserved',4,'Picked',5,'Partially  Delivered',6,'Delivered',7) in order by clause

 

Userlevel 4
Badge +10

thx for the explanation. So this can’t be done in a Lobby, correct?

 

 

 

Userlevel 4
Badge +10

 

Userlevel 1
Badge +6

Hi,

 

This can be done in Lobby.

Please use the case statement in the columns section and the order by decode expression which I have mentioned above in the order by clause section in lobby or also you can use the same case statement in order by clause.

Userlevel 4
Badge +10

ok, thx. it works! I misunderstood your solution- once I put the decode) code in the Order by it worked.

 

 

Reply