0

I need help with a view.

I have this one employee showing up twice right now because he has two work locations listed.

How can I put something into the view so it selects one location instead of both?

Example of data

EMPLID  Location
120032  NYC
120032  LAX
120021  NYC
120034  CHI

I want 120032 to only show his NYC when the view is executed.

Is putting something in the field formula better in app designer?

helloworld21285
  • 1
  • 1
  • 1
    How you view looks like? The actual code of the view would be helpful. Also, What drives the choice between NYC and LAX? and provide the sample data so we can help. Have you tried any other codes to fix this problem? – Kelevra Nov 12 '19 at 16:39
  • i just need the logic to do the IF or CASE statement help. – helloworld21285 Nov 12 '19 at 17:02
  • 1
    do you care which location is selected for the view? – JoshGivens Nov 12 '19 at 17:05
  • What tables do you use? What data is in those tables? What determines which city to show. How's your select looks like in the view? I can't give you much help If I don't know those things. – Kelevra Nov 12 '19 at 17:05
  • table is PS_EMPLOYEE_TBL field is LOCATION – helloworld21285 Nov 12 '19 at 17:45

1 Answers1

2

For this example you can use aggregation:

select emplid, max(location) as location
from t
group by emplid;

This works on any database I am familiar with. Here is a db<>fiddle using MySQL.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Did you copy the sql verbatim? If so, you'll need to use your actual table name, not `t`. So `select EMPLID, max(LOCATION) as location from PS_EMPLOYEE_TBL group by EMPLID;`. – clav Nov 12 '19 at 21:11
  • @Helloworld21285 what exactly didn't work, Did you get any error messages? Or it didn't resolve the problem? – Kelevra Nov 12 '19 at 21:54