36
Views
5
Comments
Strange error advance query sql
Application Type
Traditional Web

Hi All,


I got error in advance query, but i dont know where location in this error

i use Service Studio P11


this is my sql query:

SELECT {TABLE1}.[FIELD1], COUNT({TABLE2}.[FIELD1]), ROUND(

    (IF(SUM({TABLE3}.[FIELD1]) IS NULL, 0, SUM({TABLE3}.[FIELD1])) + IF(SUM({TABLE4}.[FIELD1]) IS NULL, 0, SUM({TABLE4}.[FIELD1]))

    )/1000000

    , 2

    ),0,0

FROM {TABLE2} 

    LEFT JOIN {TABLE5} ON {TABLE2}.[Id] = {TABLE5}.[Id]

    INNER JOIN {TABLE6} on ({TABLE6}.[Id] = {TABLE2}.[FIELD2])

    LEFT JOIN {TABLE1} ON {TABLE5}.[FIELD1] = {TABLE1}.[Id]

    INNER JOIN {TABLE7} ON {TABLE2}.[Id] = {TABLE7}.[FIELD1]

    INNER JOIN {TABLE8} ON {TABLE7}.[Id] = {TABLE8}.[Id]

    LEFT JOIN {TABLE3} ON {TABLE3}.[Id] = {TABLE2}.[Id]

    LEFT JOIN {TABLE4} on {TABLE2}.[FIELD1] = {TABLE4}.[FIELD1]

WHERE

    ({TABLE8}.[FIELD1] = @param)

    AND {TABLE6}.[Name] = 'String'

GROUP BY {TABLE1}.[FIELD1]

UNION ALL

SELECT {TABLE1}.[FIELD1], COUNT({TABLE2}.[FIELD1]), ROUND(

    (

        IF(SUM({TABLE3}.[FIELD1]) IS NULL, 0, SUM({TABLE3}.[FIELD1]))

        + IF(SUM({TABLE4}.[FIELD1]) IS NULL, 0, SUM({TABLE4}.[FIELD1]))

    )/1000000

    , 2

),0,0

FROM {TABLE2} 

    LEFT JOIN {TABLE5} ON {TABLE2}.[Id] = {TABLE5}.[Id]

    INNER JOIN {TABLE6} on ({TABLE6}.[Id] = {TABLE2}.[FIELD2])

    LEFT JOIN {TABLE1} ON {TABLE5}.[FIELD1] = {TABLE1}.[Id]

    INNER JOIN {TABLE7} ON {TABLE2}.[Id] = {TABLE7}.[FIELD1]

    INNER JOIN {TABLE9} ON {TABLE7}.[Id] = {TABLE9}.[Id]

    LEFT JOIN {TABLE3} ON {TABLE3}.[Id] = {TABLE2}.[Id]

    LEFT JOIN {TABLE4} on {TABLE2}.[FIELD1] = {TABLE4}.[FIELD1]

WHERE

    ({TABLE9}.[FIELD1] = @Param)

    AND {TABLE6}.[Name] = 'String'

GROUP BY {TABLE1}.[FIELD1]

ORDER BY FIELD1


And i attach for error information

Hope you guys know what i mean

Thank you All.

Screenshot20201210091442.png

Champion

Hi Adam

If you are working in none personal environment, I suggest you copy the executed SQL  to SQL tool (eg: Sql Server Management Studio). Then you get more detailed information. Also you can remove some lines to specify which line is the reason for the error.

And just hand's up. You can use IsNull instead of If.

Regards

Hi Adam, 

What kind of database are you using ?

I believe that "IF(condition, value_if_true, value_if_false) " syntax is not a valid sql / oracle syntax unless you are using MySQL but currently OutSystems only supports Oracle / SQL Server databases.


Instead, use the CASE WHEN syntax. Please use this link as a reference

Hello Adam,

You can not use a IF Statement with a SELECT Statement, you have to use CASE WHEN instead of IF into the query. Also, try to look into the service center for errors for this query.

Thanks & Regards,

Sachin

Hi Adam,

If you are having database access please try to execute same query in database editor it will be helpful you to identify the issue.


Regards

Shashikant Shukla

Hi Kane,

See this component with several samples of advanced SQL.

https://www.outsystems.com/forge/component-overview/10200/sql-advanced-query-samples-for-dummies


Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.