-
Notifications
You must be signed in to change notification settings - Fork 1
/
query-entry.sql
19 lines (19 loc) · 1.1 KB
/
query-entry.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT
en.Z_PK AS ID,
(en.ZGREGORIANYEAR || '-' || en.ZGREGORIANMONTH || '-' || en.ZGREGORIANDAY) AS CREATION_DATE,
(jr.ZNAME || '-' || en.Z_PK) AS FILE_NAME,
jr.ZNAME AS DIR,
CASE WHEN lc.ZALTITUDE IS NULL THEN 0 ELSE lc.ZALTITUDE END AS ALTITUDE,
CASE WHEN lc.ZLONGITUDE IS NULL THEN 0 ELSE lc.ZLONGITUDE END AS LONGITUDE,
CASE WHEN lc.ZADMINISTRATIVEAREA IS NULL THEN 0 ELSE lc.ZADMINISTRATIVEAREA END AS STATE,
CASE WHEN lc.ZLOCALITYNAME IS NULL THEN 0 ELSE lc.ZLOCALITYNAME END AS CITY,
CASE WHEN lc.ZADDRESS IS NULL THEN 0 ELSE lc.ZADDRESS END AS ADDRESS,
CASE WHEN lc.ZPLACENAME IS NULL THEN 0 ELSE lc.ZPLACENAME END AS PLACE_NAME,
CASE WHEN we.ZCONDITIONSDESCRIPTION IS NULL THEN 0 ELSE we.ZCONDITIONSDESCRIPTION END AS CONDITIONSDESCRIPTION,
CASE WHEN we.ZWEATHERCODE IS NULL THEN 0 ELSE we.ZWEATHERCODE END AS WEATHER_CODE,
CASE WHEN en.ZMARKDOWNTEXT IS NULL THEN NULL ELSE en.ZMARKDOWNTEXT END AS MD
FROM ZENTRY en
LEFT JOIN ZJOURNAL jr on en.ZJOURNAL = jr.Z_PK
LEFT JOIN ZLOCATION lc on en.ZLOCATION = lc.Z_PK
LEFT JOIN ZWEATHER we on en.ZWEATHER = we.Z_PK
-- LIMIT 10;