CODEWITHSUNDEEP
sqldb2
OVO
OVO

Reputation: 123

How do I avoid redundancy in query on DB2?

I am trying to return only one row per driver on my query.

Currently I am linking my driver table's column Terminal_number to my site table's column First_field_insert, but within the first_field_insert there are some values that are the same! As a result it will produce duplicate returns!

Ideally I would like to only return one row per driver.

This is what I have:

SELECT D.DRIVER_ID, D.NAME, D.USER9 AS PHONE, 
       CASE D.TERMINAL_NUMBER WHEN '0' THEN FETCH FIRST ROW ONLY END,
       D.DRIVER_TYPE, S.FAX_PHONE_NUMBER
FROM DRIVER D,
     SITE S
WHERE ACTIVE_IN_DISP = 'True' 
  AND TERMINAL_NUMBER = FIRST_FIELD_INSERT 
  AND FAX_PHONE_NUMBER = :FAX_PHONE_NUMBER
ORDER BY DRIVER_ID, TERMINAL_NUMBER
WITH UR

Upvotes: 0

Views: 65

Answers (2)

Alexander Pranko
Alexander Pranko

Reputation: 1959

I like the direction which @Oliver H suggested. Another possibility here is to preselect necessary records in SITE table before joining it with DRIVER table but it certainly not so optimal from performance perspective.

SELECT D.DRIVER_ID, D.NAME, D.USER9 AS PHONE, 
       CASE D.TERMINAL_NUMBER WHEN '0' THEN FETCH FIRST ROW ONLY END,
       D.DRIVER_TYPE, S.FAX_PHONE_NUMBER
FROM DRIVER D,
     (select distinct driver_id, first_field_insert, first_field_column, fax_phone_number
        from SITE) S
WHERE ACTIVE_IN_DISP = 'True' 
  AND TERMINAL_NUMBER = FIRST_FIELD_INSERT 
  AND FAX_PHONE_NUMBER = :FAX_PHONE_NUMBER
ORDER BY DRIVER_ID, TERMINAL_NUMBER
WITH UR

Upvotes: 0

OVO
OVO

Reputation: 123

(SELECT FAX_PHONE_NUMBER FROM SITE WHERE FIRST_FIELD_INSERT = CAST(TERMINAL_NUMBER AS VARCHAR(2)) AND FAX_PHONE_NUMBER = :FAX_PHONE_NUMBER FETCH FIRST 1 ROW ONLY) AS REGION

Upvotes: 1

Related Questions