Hi guys,

I was wondering if you could help me insert the order by syntax to the query but I am getting an error:

SyntaxEditor Code Snippet

SELECT {InnectoClientRole}.[Id], 
{InnectoClientRole}.[RoleTitle], 
{InnectoClientRole}.[Score],
(case when {InnectoClientRole}.[Score] <= 1.4 then 'Core'
when {InnectoClientRole}.[Score] <= 2.4 then 'Implement'
when {InnectoClientRole}.[Score] <= 3.4 then 'Guide'
when {InnectoClientRole}.[Score] <= 4.4 then 'Translate'
when {InnectoClientRole}.[Score] <= 5.4 then 'Lead'
else 'Global'end),

(select {RoleLevel}.[LevelName]
from {RoleLevel} 
inner join {InnectoClientRoleFactor} on {InnectoClientRoleFactor}.[RoleLevelID] = {RoleLevel}.[Id]
inner join {RoleFactor} on {RoleFactor}.[Id] = {InnectoClientRoleFactor}.[RoleFactorId]
where {InnectoClientRoleFactor}.[InnectoClientRoleId] = {InnectoClientRole}.[Id]
and {RoleFactor}.[DisplayOrder] = 1),
(select {RoleLevel}.[LevelName]
from {RoleLevel}
inner join {InnectoClientRoleFactor} on {InnectoClientRoleFactor}.[RoleLevelID] = {RoleLevel}.[Id]
inner join {RoleFactor} on {RoleFactor}.[Id] = {InnectoClientRoleFactor}.[RoleFactorId]
where {InnectoClientRoleFactor}.[InnectoClientRoleId] = {InnectoClientRole}.[Id]
and {RoleFactor}.[DisplayOrder] = 2),
(select {RoleLevel}.[LevelName]
from {RoleLevel}
inner join {InnectoClientRoleFactor} on {InnectoClientRoleFactor}.[RoleLevelID] = {RoleLevel}.[Id]
inner join {RoleFactor} on {RoleFactor}.[Id] = {InnectoClientRoleFactor}.[RoleFactorId]
where {InnectoClientRoleFactor}.[InnectoClientRoleId] = {InnectoClientRole}.[Id]
and {RoleFactor}.[DisplayOrder] = 3),
(select {RoleLevel}.[LevelName]
from {RoleLevel}
inner join {InnectoClientRoleFactor} on {InnectoClientRoleFactor}.[RoleLevelID] = {RoleLevel}.[Id]
inner join {RoleFactor} on {RoleFactor}.[Id] = {InnectoClientRoleFactor}.[RoleFactorId]
where {InnectoClientRoleFactor}.[InnectoClientRoleId] = {InnectoClientRole}.[Id]
and {RoleFactor}.[DisplayOrder] = 4),
(select {RoleLevel}.[LevelName]
from {RoleLevel}
inner join {InnectoClientRoleFactor} on {InnectoClientRoleFactor}.[RoleLevelID] = {RoleLevel}.[Id]
inner join {RoleFactor} on {RoleFactor}.[Id] = {InnectoClientRoleFactor}.[RoleFactorId]
where {InnectoClientRoleFactor}.[InnectoClientRoleId] = {InnectoClientRole}.[Id]
and {RoleFactor}.[DisplayOrder] = 5),

(select {OrgLevelItem}.[Name]
from {OrgLevelItem}
inner join {RoleOrgLevelItem} on {RoleOrgLevelItem}.[OrgLevelItemID] = {OrgLevelItem}.[Id]
inner join {OrgLevel} on {OrgLevel}.[Id] = {OrgLevelItem}.[OrgLevelID]
where {OrgLevel}.[Order] = 1
and {RoleOrgLevelItem}.[InnectoClientRoleID] = {InnectoClientRole}.[Id]),
(select {OrgLevelItem}.[Name]
from {OrgLevelItem}
inner join {RoleOrgLevelItem} on {RoleOrgLevelItem}.[OrgLevelItemID] = {OrgLevelItem}.[Id]
inner join {OrgLevel} on {OrgLevel}.[Id] = {OrgLevelItem}.[OrgLevelID]
where {OrgLevel}.[Order] = 2
and {RoleOrgLevelItem}.[InnectoClientRoleID] = {InnectoClientRole}.[Id]),
(select {OrgLevelItem}.[Name]
from {OrgLevelItem}
inner join {RoleOrgLevelItem} on {RoleOrgLevelItem}.[OrgLevelItemID] = {OrgLevelItem}.[Id]
inner join {OrgLevel} on {OrgLevel}.[Id] = {OrgLevelItem}.[OrgLevelID]
where {OrgLevel}.[Order] = 3
and {RoleOrgLevelItem}.[InnectoClientRoleID] = {InnectoClientRole}.[Id]),
(select {OrgLevelItem}.[Name]
from {OrgLevelItem}
inner join {RoleOrgLevelItem} on {RoleOrgLevelItem}.[OrgLevelItemID] = {OrgLevelItem}.[Id]
inner join {OrgLevel} on {OrgLevel}.[Id] = {OrgLevelItem}.[OrgLevelID]
where {OrgLevel}.[Order] = 4
and {RoleOrgLevelItem}.[InnectoClientRoleID] = {InnectoClientRole}.[Id]),
(select {OrgLevelItem}.[Name]
from {OrgLevelItem}
inner join {RoleOrgLevelItem} on {RoleOrgLevelItem}.[OrgLevelItemID] = {OrgLevelItem}.[Id]
inner join {OrgLevel} on {OrgLevel}.[Id] = {OrgLevelItem}.[OrgLevelID]
where {OrgLevel}.[Order] = 5
and {RoleOrgLevelItem}.[InnectoClientRoleID] = {InnectoClientRole}.[Id]),

UserCreate.[Name],
{InnectoClientRole}.[isAccepted],
{InnectoClientRole}.[TotalIncumbentsCount],
'',
{InnectoClientRole}.[Comments],
{InnectoClientRole}.[isDuplicate],
{InnectoClientRoleStatus}.[Label],
UserUpdate.[Name],
{InnectoClientRole}.[CreatedDateTime],
{InnectoClientRole}.[ModifiedDateTime]
from {InnectoClientRole}
left join {User} UserCreate on UserCreate.[Id] = {InnectoClientRole}.[CreatedByUserID]
left join {User} UserUpdate on UserUpdate.[Id] = {InnectoClientRole}.[CreatedByUserID]
left join {InnectoClientRoleStatus} on {InnectoClientRoleStatus}.[Id] = {InnectoClientRole}.[InnectoClientRoleStatusId]
where ((({InnectoClientRole}.[RoleTitle] like '%' + @SearchKeyword + '%'
or UserCreate.[Name] like '%' + @SearchKeyword + '%'
or UserUpdate.[Name] like '%' + @SearchKeyword + '%'))
and ({InnectoClientRole}.[InnectoClientRoleStatusId] = @Status or @Status ='')
and (CAST(@Level AS INT) = Round({InnectoClientRole}.[Score],0) or @Level ='')
and ({InnectoClientRole}.[InnectoClientEvaluationID] = @SessionEvaluationID))
ORDER BY @OrderBy

I apologise the long the sql but it is returning and doing what I need. It's just the last part where I am trying to add the order by that is breaking. I need it, to sort the list the table record that feeds from this.

The query runs fine without the order by but I need it in there.

Kind regards,

Salim

Hello Salim,

Since the SQL is too long I cannot reproduce the problem at my end, would you mind sharing what text you are passing into @OrderBy parameter?

Thanks,

Junaid


Hi Salim,

When you are testing the query what value are you assigning to OrderBy?

Thanks for the speedy response guys,

Not passing anything atm.

Hi Salim, 

That's the reason why you are getting that error, cause it tries to order and doesn't have any value assigned. You have 2 solutions here:

1 -  If you want to do always an order to your query you just need to pass {Table}.[Attribute] instead of empty text

2 - If you just need to order in some cases you can pass the command ORDER BY inside your value Order By. It becomes something like this:
@OrderBy (you remove the command ORDER BY), and put it inside the text "ORDER BY {Table}.[Attribute]". If you don't want to order anything you pass an empty text.



Hope it helps,

João Campos

João Campos wrote:

Hi Salim, 

That's the reason why you are getting that error, cause it tries to order and doesn't have any value assigned. You have 2 solutions here:

1 -  If you want to do always an order to your query you just need to pass {Table}.[Attribute] instead of empty text

2 - If you just need to order in some cases you can pass the command ORDER BY inside your value Order By. It becomes something like this:
@OrderBy (you remove the command ORDER BY), and put it inside the text "ORDER BY {Table}.[Attribute]". If you don't want to order anything you pass an empty text.



Hope it helps,

João Campos

Hi Joao,


The query is now working with no errors and ordering thanks.

However when actually accessing the page it is now an internal error:



I followed the syntax you suggested:




Any idea why?


Kind regards,

Salim

Hi,

You need to add "ORDER BY " plus what you already using in that OrderBy input parameter.

Regards,

Marcelo

Solution

Salim Chafaqui wrote:

João Campos wrote:

Hi Salim, 

That's the reason why you are getting that error, cause it tries to order and doesn't have any value assigned. You have 2 solutions here:

1 -  If you want to do always an order to your query you just need to pass {Table}.[Attribute] instead of empty text

2 - If you just need to order in some cases you can pass the command ORDER BY inside your value Order By. It becomes something like this:
@OrderBy (you remove the command ORDER BY), and put it inside the text "ORDER BY {Table}.[Attribute]". If you don't want to order anything you pass an empty text.



Hope it helps,

João Campos

Hi Joao,


The query is now working with no errors and ordering thanks.

However when actually accessing the page it is now an internal error:



I followed the syntax you suggested:




Any idea why?


Kind regards,

Salim

Hi Salim, 

You can use one of the solutions i gave before. Either you use this one printed and you put ORDER BY 

before List_Sort_Column or you put ORDER BY before @OrderBy in the query, but in the second case you can no longer have empty value to order


Personally, if you always want to have one type of order i would use the second solution ORDER BY @OrderBy in the query and keep the input value as the one you have.

But if you can have empty order just put the ORDER BY command before List_Sort_Column


Hope it helps,

João Campos

Solution

Marcelo Ferreira wrote:

Hi,

You need to add "ORDER BY " plus what you already using in that OrderBy input parameter.

Regards,

Marcelo

Hi Marcelo,


Thanks for replying.

The internal error has gone however the sorting doesn't seem to work and the query is saying that the order by parameter is not being used.

I am trying to use the parameter to orderby and not explicitly order by because I want to then feed the parameter to sort the table columns.