Community

Create Database View

332
1
08-30-2023 06:16 AM
ammsgis
by
New Contributor III
‎08-30-2023 06:16 AM

Hey guys! I created that sql expression below in Create Database View to simplify the users queries and it worked well, but I've acrossed with some issues.

When I made join i lost the fields subtype_codes. Then, it remains just the associated default value.

Is there a way to conserve subtype_code after doing that join tables?

SELECT adhp.name_txt, adhp.type_code, adhp.military_code, adhpsurfacearea.subtypecode, adhpsurfacearea.globalid, adhpsurfacearea.length_val, surfacecharacteristic.composition_code FROM adhp JOIN adhpsurfacearea ON adhp.gfid = adhpsurfacearea.adhp_id JOIN surfacecharacteristic ON surfacecharacteristic.gfid = adhpsurfacearea.surfacecharacteristic_id;

In this case, the field is subtype_code from adhpsurfacearea.

Thanks in advance.

 

Tags (3)
0 Kudos
1 Reply
DougGreen
by
Occasional Contributor II
‎08-30-2023 09:23 AM

It seems like you could join to the lookup table and display the values instead of the codes. It would follow this pattern:

SELECT
    adhp.name_txt,
    adhp.type_code,
    adhp.military_code,
    --adhpsurfacearea.subtypecode,
    LU.subtypevalue,
    adhpsurfacearea.globalid,
    adhpsurfacearea.length_val,
    surfacecharacteristic.composition_code
FROM
    adhp
JOIN adhpsurfacearea ON adhp.gfid = adhpsurfacearea.adhp_id
JOIN surfacecharacteristic ON surfacecharacteristic.gfid = adhpsurfacearea.surfacecharacteristic_id
JOIN [SUBTYPECODELOOKUPTABLENAME] LU ON LU.subtypecode = adhpsurfacearea.subtypecode

 

0 Kudos