claim_value_wide_view
Materialized: false
Definition
WITH codes AS (
SELECT ct.claim_id,
ct.value_code1,
ct.value_code2,
ct.value_code3,
ct.value_code4,
ct.value_code5,
ct.value_code6,
ct.value_code7,
ct.value_code8,
ct.value_code9,
ct.value_code10,
ct.value_code11,
ct.value_code12,
ct.value_code13,
ct.value_code14,
ct.value_code15,
ct.value_code16,
ct.value_code17,
ct.value_code18,
ct.value_code19,
ct.value_code20,
ct.value_code21,
ct.value_code22,
ct.value_code23,
ct.value_code24,
ct.value_code25,
ct.value_code26,
ct.value_code27,
ct.value_code28,
ct.value_code29,
ct.value_code30
FROM public.crosstab('SELECT claim_id, claim_value_index, value_code
FROM claims.claim_value
ORDER BY claim_id, claim_value_index, value_code'::text) ct(claim_id bigint, value_code1 text, value_code2 text, value_code3 text, value_code4 text, value_code5 text, value_code6 text, value_code7 text, value_code8 text, value_code9 text, value_code10 text, value_code11 text, value_code12 text, value_code13 text, value_code14 text, value_code15 text, value_code16 text, value_code17 text, value_code18 text, value_code19 text, value_code20 text, value_code21 text, value_code22 text, value_code23 text, value_code24 text, value_code25 text, value_code26 text, value_code27 text, value_code28 text, value_code29 text, value_code30 text)
), amounts AS (
SELECT ct.claim_id,
ct.value_amount1,
ct.value_amount2,
ct.value_amount3,
ct.value_amount4,
ct.value_amount5,
ct.value_amount6,
ct.value_amount7,
ct.value_amount8,
ct.value_amount9,
ct.value_amount10,
ct.value_amount11,
ct.value_amount12,
ct.value_amount13,
ct.value_amount14,
ct.value_amount15,
ct.value_amount16,
ct.value_amount17,
ct.value_amount18,
ct.value_amount19,
ct.value_amount20,
ct.value_amount21,
ct.value_amount22,
ct.value_amount23,
ct.value_amount24,
ct.value_amount25,
ct.value_amount26,
ct.value_amount27,
ct.value_amount28,
ct.value_amount29,
ct.value_amount30
FROM public.crosstab('SELECT claim_id, claim_value_index, value_amount
FROM claims.claim_value
ORDER BY claim_id, claim_value_index, value_amount'::text) ct(claim_id bigint, value_amount1 text, value_amount2 text, value_amount3 text, value_amount4 text, value_amount5 text, value_amount6 text, value_amount7 text, value_amount8 text, value_amount9 text, value_amount10 text, value_amount11 text, value_amount12 text, value_amount13 text, value_amount14 text, value_amount15 text, value_amount16 text, value_amount17 text, value_amount18 text, value_amount19 text, value_amount20 text, value_amount21 text, value_amount22 text, value_amount23 text, value_amount24 text, value_amount25 text, value_amount26 text, value_amount27 text, value_amount28 text, value_amount29 text, value_amount30 text)
), descriptions AS (
SELECT ct.claim_id,
ct.value_description1,
ct.value_description2,
ct.value_description3,
ct.value_description4,
ct.value_description5,
ct.value_description6,
ct.value_description7,
ct.value_description8,
ct.value_description9,
ct.value_description10,
ct.value_description11,
ct.value_description12,
ct.value_description13,
ct.value_description14,
ct.value_description15,
ct.value_description16,
ct.value_description17,
ct.value_description18,
ct.value_description19,
ct.value_description20,
ct.value_description21,
ct.value_description22,
ct.value_description23,
ct.value_description24,
ct.value_description25,
ct.value_description26,
ct.value_description27,
ct.value_description28,
ct.value_description29,
ct.value_description30
FROM public.crosstab('SELECT claim_id, claim_value_index, title
FROM claims.claim_value v
LEFT OUTER JOIN claims.nubc_value_lookup n ON v.value_code = n.value_code
ORDER BY claim_id, claim_value_index, title'::text) ct(claim_id bigint, value_description1 text, value_description2 text, value_description3 text, value_description4 text, value_description5 text, value_description6 text, value_description7 text, value_description8 text, value_description9 text, value_description10 text, value_description11 text, value_description12 text, value_description13 text, value_description14 text, value_description15 text, value_description16 text, value_description17 text, value_description18 text, value_description19 text, value_description20 text, value_description21 text, value_description22 text, value_description23 text, value_description24 text, value_description25 text, value_description26 text, value_description27 text, value_description28 text, value_description29 text, value_description30 text)
)
SELECT codes.claim_id,
codes.value_code1,
codes.value_code2,
codes.value_code3,
codes.value_code4,
codes.value_code5,
codes.value_code6,
codes.value_code7,
codes.value_code8,
codes.value_code9,
codes.value_code10,
codes.value_code11,
codes.value_code12,
codes.value_code13,
codes.value_code14,
codes.value_code15,
codes.value_code16,
codes.value_code17,
codes.value_code18,
codes.value_code19,
codes.value_code20,
codes.value_code21,
codes.value_code22,
codes.value_code23,
codes.value_code24,
codes.value_code25,
codes.value_code26,
codes.value_code27,
codes.value_code28,
codes.value_code29,
codes.value_code30,
amounts.value_amount1,
amounts.value_amount2,
amounts.value_amount3,
amounts.value_amount4,
amounts.value_amount5,
amounts.value_amount6,
amounts.value_amount7,
amounts.value_amount8,
amounts.value_amount9,
amounts.value_amount10,
amounts.value_amount11,
amounts.value_amount12,
amounts.value_amount13,
amounts.value_amount14,
amounts.value_amount15,
amounts.value_amount16,
amounts.value_amount17,
amounts.value_amount18,
amounts.value_amount19,
amounts.value_amount20,
amounts.value_amount21,
amounts.value_amount22,
amounts.value_amount23,
amounts.value_amount24,
amounts.value_amount25,
amounts.value_amount26,
amounts.value_amount27,
amounts.value_amount28,
amounts.value_amount29,
amounts.value_amount30,
descriptions.value_description1,
descriptions.value_description2,
descriptions.value_description3,
descriptions.value_description4,
descriptions.value_description5,
descriptions.value_description6,
descriptions.value_description7,
descriptions.value_description8,
descriptions.value_description9,
descriptions.value_description10,
descriptions.value_description11,
descriptions.value_description12,
descriptions.value_description13,
descriptions.value_description14,
descriptions.value_description15,
descriptions.value_description16,
descriptions.value_description17,
descriptions.value_description18,
descriptions.value_description19,
descriptions.value_description20,
descriptions.value_description21,
descriptions.value_description22,
descriptions.value_description23,
descriptions.value_description24,
descriptions.value_description25,
descriptions.value_description26,
descriptions.value_description27,
descriptions.value_description28,
descriptions.value_description29,
descriptions.value_description30
FROM codes
LEFT JOIN amounts ON amounts.claim_id = codes.claim_id
LEFT JOIN descriptions ON descriptions.claim_id = amounts.claim_id;
| Column | Data Type | Indexed |
|---|---|---|
claim_id | bigint | No |
value_code1 | text | No |
value_code2 | text | No |
value_code3 | text | No |
value_code4 | text | No |
value_code5 | text | No |
value_code6 | text | No |
value_code7 | text | No |
value_code8 | text | No |
value_code9 | text | No |
value_code10 | text | No |
value_code11 | text | No |
value_code12 | text | No |
value_code13 | text | No |
value_code14 | text | No |
value_code15 | text | No |
value_code16 | text | No |
value_code17 | text | No |
value_code18 | text | No |
value_code19 | text | No |
value_code20 | text | No |
value_code21 | text | No |
value_code22 | text | No |
value_code23 | text | No |
value_code24 | text | No |
value_code25 | text | No |
value_code26 | text | No |
value_code27 | text | No |
value_code28 | text | No |
value_code29 | text | No |
value_code30 | text | No |
value_amount1 | text | No |
value_amount2 | text | No |
value_amount3 | text | No |
value_amount4 | text | No |
value_amount5 | text | No |
value_amount6 | text | No |
value_amount7 | text | No |
value_amount8 | text | No |
value_amount9 | text | No |
value_amount10 | text | No |
value_amount11 | text | No |
value_amount12 | text | No |
value_amount13 | text | No |
value_amount14 | text | No |
value_amount15 | text | No |
value_amount16 | text | No |
value_amount17 | text | No |
value_amount18 | text | No |
value_amount19 | text | No |
value_amount20 | text | No |
value_amount21 | text | No |
value_amount22 | text | No |
value_amount23 | text | No |
value_amount24 | text | No |
value_amount25 | text | No |
value_amount26 | text | No |
value_amount27 | text | No |
value_amount28 | text | No |
value_amount29 | text | No |
value_amount30 | text | No |
value_description1 | text | No |
value_description2 | text | No |
value_description3 | text | No |
value_description4 | text | No |
value_description5 | text | No |
value_description6 | text | No |
value_description7 | text | No |
value_description8 | text | No |
value_description9 | text | No |
value_description10 | text | No |
value_description11 | text | No |
value_description12 | text | No |
value_description13 | text | No |
value_description14 | text | No |
value_description15 | text | No |
value_description16 | text | No |
value_description17 | text | No |
value_description18 | text | No |
value_description19 | text | No |
value_description20 | text | No |
value_description21 | text | No |
value_description22 | text | No |
value_description23 | text | No |
value_description24 | text | No |
value_description25 | text | No |
value_description26 | text | No |
value_description27 | text | No |
value_description28 | text | No |
value_description29 | text | No |
value_description30 | text | No |