Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Null Values in Concats



Permlink Replies: 1 - Last Post: Dec 21, 2016 12:53 PM Last Post By: quinn wildman Threads: [ Previous | Next ]
Josh Blevins

Posts: 1
Registered: 12/9/14
Null Values in Concats
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 15, 2016 10:29 AM
I have built this query and on the concat when null values are found that row or no rows are found.

{SELECT R.RESPONSE_ID,US.US_ID, R.RESPONSE_PICKUP_TIME, DC_NAME, Q.STATION_NAME, RT.*, U.UNIT_NUMBER, R.RESPONSE_D_FACILITY,
F.FACILITY_NAME ||' -- '||
R.RESPONSE_PU_HOUSE_NUMBER || ' ' ||
S.STREETS_NAME ||' '||
ST.STREET_TYPE_DESC ||' '||
C.CITY_NAME ||' '||

R.RESPONSE_PU_ZIP
AS ADDRESS FROM RESPONSES R
INNER JOIN STATIONS Q ON R.RESPONSE_PRIMARY_STATION = Q.STATION_ID
INNER JOIN FACILITIES F ON R.RESPONSE_PU_FACILITY = F.FACILITY_ID
INNER JOIN DISPATCH_COMPLAINT DC ON R.RESPONSE_DISPATCH_COMPLAINT = DC.DC_ID
INNER JOIN STREETS S ON R.RESPONSE_PU_STREET = S.STREETS_ID
INNER JOIN STREET_TYPE ST ON R.RESPONSE_PU_STREET_TYPE = ST.STREET_TYPE_ID
INNER JOIN CITIES C ON R.RESPONSE_PU_CITY = C.CITY_ID
INNER JOIN RESPONSES_TIMES RT ON R.RESPONSE_ID = RT.RT_RESPONSE_ID
INNER JOIN UNIT_SCHEDULE US ON RT.RT_UNIT = US.US_ID
INNER JOIN UNITS U ON US.US_UNIT = U.UNIT_ID

WHERE RESPONSE_STATUS > 1 AND RESPONSE_STATUS <= 8}
quinn wildman

Posts: 856
Registered: 12/2/99
Re: Null Values in Concats
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 21, 2016 12:53 PM   in response to: Josh Blevins in response to: Josh Blevins
Is your problem that no rows are selected, or that your concatenation is results in NULL? If it's no rows, then your problem is in your where clause. If it is that your concatenation results in NULL (which is what I think), it is because NULL || anything results in NULL. You could wrap each potentially NULL field with COALESCE like: COALESCE(field, '')

Josh Blevins wrote:
I have built this query and on the concat when null values are found that row or no rows are found.

{SELECT R.RESPONSE_ID,US.US_ID, R.RESPONSE_PICKUP_TIME, DC_NAME, Q.STATION_NAME, RT.*, U.UNIT_NUMBER, R.RESPONSE_D_FACILITY,
F.FACILITY_NAME ||' -- '||
R.RESPONSE_PU_HOUSE_NUMBER || ' ' ||
S.STREETS_NAME ||' '||
ST.STREET_TYPE_DESC ||' '||
C.CITY_NAME ||' '||

R.RESPONSE_PU_ZIP
AS ADDRESS FROM RESPONSES R
INNER JOIN STATIONS Q ON R.RESPONSE_PRIMARY_STATION = Q.STATION_ID
INNER JOIN FACILITIES F ON R.RESPONSE_PU_FACILITY = F.FACILITY_ID
INNER JOIN DISPATCH_COMPLAINT DC ON R.RESPONSE_DISPATCH_COMPLAINT = DC.DC_ID
INNER JOIN STREETS S ON R.RESPONSE_PU_STREET = S.STREETS_ID
INNER JOIN STREET_TYPE ST ON R.RESPONSE_PU_STREET_TYPE = ST.STREET_TYPE_ID
INNER JOIN CITIES C ON R.RESPONSE_PU_CITY = C.CITY_ID
INNER JOIN RESPONSES_TIMES RT ON R.RESPONSE_ID = RT.RT_RESPONSE_ID
INNER JOIN UNIT_SCHEDULE US ON RT.RT_UNIT = US.US_ID
INNER JOIN UNITS U ON US.US_UNIT = U.UNIT_ID

WHERE RESPONSE_STATUS > 1 AND RESPONSE_STATUS <= 8}
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02