Solved

Lobby Data Source using Case statement

Userlevel 4

+8

I have following SQL statement which gives me the right result:

SELECT CASE WHEN PART_NO LIKE '1%' THEN 'A' WHEN PART_NO LIKE '2%' THEN 'B' WHEN PART_NO LIKE '3%' THEN 'C' END PART_TYPE,

MAX(PART_NO) PART_NO FROM PART_CATALOG WHERE (PART_NO LIKE '1%' OR PART_NO LIKE '2%' OR PART_NO LIKE '3%')

GROUP BY CASE WHEN PART_NO LIKE '1%' THEN 'A' WHEN PART_NO LIKE '2%' THEN 'B' WHEN PART_NO LIKE '3%' THEN 'C' END

How would this convert to definition of a Lobby Data Source (with view Part_Catalog) regarding Condition and Group By?

icon

Best answer by anmise 3 May 2020, 12:25

Userlevel 7

I have following SQL statement which gives me the right result:

SELECT CASE WHEN PART_NO LIKE '1%' THEN 'A' WHEN PART_NO LIKE '2%' THEN 'B' WHEN PART_NO LIKE '3%' THEN 'C' END PART_TYPE,

MAX(PART_NO) PART_NO FROM PART_CATALOG WHERE (PART_NO LIKE '1%' OR PART_NO LIKE '2%' OR PART_NO LIKE '3%')

GROUP BY CASE WHEN PART_NO LIKE '1%' THEN 'A' WHEN PART_NO LIKE '2%' THEN 'B' WHEN PART_NO LIKE '3%' THEN 'C' END

How would this convert to definition of a Lobby Data Source (with view Part_Catalog) regarding Condition and Group By?

View: PART_CATALOG

Condition: Your where clause

Columns: CASE WHEN PART_NO LIKE '1%' THEN 'A' WHEN PART_NO LIKE '2%' THEN 'B' WHEN PART_NO LIKE '3%' THEN 'C' END and MAX(PART_NO)

Group by: CASE WHEN PART_NO LIKE '1%' THEN 'A' WHEN PART_NO LIKE '2%' THEN 'B' WHEN PART_NO LIKE '3%' THEN 'C' END

Userlevel 4

+8

Ah, so repeat the Case statement in the Column definition. It works! Thanks alot :grinning:

Sign up