Fixed query issue

[Former Member]

Hi,

I have 2 fixed queries and not returning any values when I execute them though the data exists in tables.

1. SELECT '[Param.2]' AS name,

ShiftDefinition.idShiftDefinition AS idShiftDefinition

FROM ShiftDefinition

INNER JOIN ShiftDefinition_lang

ON ShiftDefinition_lang.idFactory = ShiftDefinition.idFactory

AND ShiftDefinition_lang.idShiftDefinition = ShiftDefinition.idShiftDefinition

WHERE ShiftDefinition.idFactory = '[Param.1]'

order by ShiftDefinition.timeBegin asc

2. SELECT Team.idTeam, Team.teamName

FROM Team

WHERE (Team.idFactory = '[Param.1]')

ORDER BY Team.teamOrder ASC

Can someone help guide why I am not able to retrieve any result after execution.

Thanks,

Raveen

[Former Member · Accepted solution]

Hi Raveen,

What is the datatype of Team.idFactory? If you are using the '\[Param.X\]' syntax, you may not need the quotes, which are for string datatypes. When testing, do you have a default parameter set on the Parameters screen? Does the query work if you substitute \[Param.X\]' with an actual value?

Kind Regards,

Diana Hoppe

[Former Member · Accepted solution]

Hi Diana,

Tha datatype of Team.ifFactory is int and I am passing the Parameter as 6 which is one value in the table for idFactory and I can retrieve the values but if I wont pass the value in the Parameters screen then I nothing is getting retrieved.

Thanks,

Raveen,

[Former Member · Accepted solution]

Raveen,

Are you leaving the quotes off of \[Param.1\] and just putting the number 6 in the Parameter screen?

SELECT Team.idTeam, Team.teamName

FROM Team

WHERE Team.idFactory = \[Param.1\]

ORDER BY Team.teamOrder ASC

- Diana

[Former Member · Accepted solution]

Hi Diana,

I have used the query you suggested and it works only when i pass the Parameter value as 6, if I dont pass anything its throwing an error as "Incorrect syntax near the keyword "ORDER".

Thanks,

Raveen

[Former Member · Accepted solution]

Hi Raveen,

If you want to test the query in the Workbench, you would need to have a default parameter set on the Parameters screen when using \[Param.X], otherwise the query will fail. This parameter can be overwritten at runtime, for example, you link in a new value from a web page or BLS transaction.

- Diana

[Former Member · Accepted solution]

Thanks Diana, would you help me to solve the First query as well, I tried the same way as I did for 2nd query but no luck.

Regards,

Raveen

[Former Member · Accepted solution]

Hi Raveen,

Looking at the query, I assume that Param.2 is a placeholder for a column name. Generally, whenever you would use quotes when hardcoding the entire query, you would need to use the quotes with the placholder ('\[Param.1\]'). Since a column name is not usually quoted, you don't need the quotes in this case, even though the column name is a string.

SELECT \[Param.2\] AS name,

ShiftDefinition.idShiftDefinition AS idShiftDefinition

FROM ShiftDefinition

INNER JOIN ShiftDefinition_lang

ON ShiftDefinition_lang.idFactory = ShiftDefinition.idFactory

AND ShiftDefinition_lang.idShiftDefinition = ShiftDefinition.idShiftDefinition

WHERE ShiftDefinition.idFactory = \[Param.1\]

order by ShiftDefinition.timeBegin asc

Let me know if that does the trick

- Diana

[Former Member · Accepted solution]

Thanks a lot Diana, Query is working .