-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathSuccess_View_Creation (1).txt
1556 lines (1458 loc) · 114 KB
/
Success_View_Creation (1).txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
drop view procedures.CL_ICD_PX;
create view procedures.CL_ICD_PX AS
select
cast(ICD_PX_ID as VARCHAR(18) )as ICD_PX_ID ,
cast(ICD_PX_NAME as VARCHAR(254) )as ICD_PX_NAME ,
cast(HCD_REC_STATE_C as DOUBLE )as HCD_REC_STATE_C ,
cast(PROCEDURE_NAME as VARCHAR(254) )as PROCEDURE_NAME ,
cast(PROC_MASTER_NM as VARCHAR(60) )as PROC_MASTER_NM ,
cast(SHORT_PROC_NAME as VARCHAR(60) )as SHORT_PROC_NAME ,
cast(BILL_DESC as VARCHAR(254) )as BILL_DESC ,
cast(CM_PHY_OWNER_ID as VARCHAR(25) )as CM_PHY_OWNER_ID ,
cast(CM_LOG_OWNER_ID as VARCHAR(25) )as CM_LOG_OWNER_ID ,
cast(CODE_SET_C as DOUBLE )as CODE_SET_C ,
cast(REFERENCE_CODE as VARCHAR(20) )as REFERENCE_CODE ,
cast(PX_CODE as VARCHAR(20) )as PX_CODE ,
cast(REF_CODE as VARCHAR(20) )as REF_CODE ,
cast(REF_BILL_CODE as VARCHAR(254) )as REF_BILL_CODE ,
cast(REF_BILL_CODE_SET_C as DOUBLE )as REF_BILL_CODE_SET_C ,
cast(INSTANT_OF_UPDATE_DTTM as TIMESTAMP )as INSTANT_OF_UPDATE_DTTM from staging_clarity.CL_ICD_PX;
drop view insurance.CLARITY_EPM_2;
create view insurance.CLARITY_EPM_2 AS select
cast(PAYOR_ID as DOUBLE ) as PAYOR_ID ,
cast(CM_PHY_OWNER_ID as VARCHAR(25) ) as CM_PHY_OWNER_ID ,
cast(CM_LOG_OWNER_ID as VARCHAR(25) ) as CM_LOG_OWNER_ID ,
cast(PROD_TYPE_C as VARCHAR(66) ) as PROD_TYPE_C ,
cast(INC_BAD_DEBT_YN as VARCHAR(1) ) as INC_BAD_DEBT_YN ,
cast(PRE_AUTH_PHONE_NUM as VARCHAR(40) ) as PRE_AUTH_PHONE_NUM ,
cast(RPT_GRP_ELEVEN_C as VARCHAR(66) ) as RPT_GRP_ELEVEN_C ,
cast(RPT_GRP_THIRTEEN_C as VARCHAR(66) ) as RPT_GRP_THIRTEEN_C ,
cast(RPT_GRP_FOURTEEN_C as VARCHAR(66) ) as RPT_GRP_FOURTEEN_C ,
cast(RPT_GRP_FIFTEEN_C as VARCHAR(66) ) as RPT_GRP_FIFTEEN_C ,
cast(RPT_GRP_SIXTEEN_C as VARCHAR(66) ) as RPT_GRP_SIXTEEN_C ,
cast(RPT_GRP_SEVENTEEN_C as VARCHAR(66) ) as RPT_GRP_SEVENTEEN_C ,
cast(RPT_GRP_EIGHTEEN_C as VARCHAR(66) ) as RPT_GRP_EIGHTEEN_C ,
cast(RPT_GRP_TWENTY_C as VARCHAR(66) ) as RPT_GRP_TWENTY_C ,
cast(PMT_TYPOLOGY_C as DOUBLE ) as PMT_TYPOLOGY_C ,
cast(PAYOR_APPEAL_WINDOW as DOUBLE ) as PAYOR_APPEAL_WINDOW ,
cast(SEC_PAYOR_WINDOW as DOUBLE ) as SEC_PAYOR_WINDOW ,
cast(RPT_GRP_TWELVE_C as VARCHAR(66) ) as RPT_GRP_TWELVE_C ,
cast(RPT_GRP_NINETEEN_C as VARCHAR(66) ) as RPT_GRP_NINETEEN_C ,
cast(OVRIDE_LAB_CHRG_YN as VARCHAR(1) ) as OVRIDE_LAB_CHRG_YN ,
cast(CASE_INS_WO_CODE_ID as DOUBLE ) as CASE_INS_WO_CODE_ID ,
cast(CASE_DB_ADJ_CODE_ID as DOUBLE ) as CASE_DB_ADJ_CODE_ID ,
cast(CHK_XOVERREMIT_YN as VARCHAR(1) ) as CHK_XOVERREMIT_YN ,
cast(UDS_TYPE_C as DOUBLE ) as UDS_TYPE_C ,
cast(USE_ELCT_VERIF_YN as VARCHAR(1) ) as USE_ELCT_VERIF_YN ,
cast(ALT_CLM_COUNTRY_C as VARCHAR(66) ) as ALT_CLM_COUNTRY_C ,
cast(CONTRACT_NUM as VARCHAR(30) ) as CONTRACT_NUM ,
cast(PBP_NUM as VARCHAR(30) ) as PBP_NUM ,
cast(PART_D_RX_GRP as VARCHAR(30) ) as PART_D_RX_GRP ,
cast(ALWAYS_WO_PYR_YN as VARCHAR(1) ) as ALWAYS_WO_PYR_YN ,
cast(CONTRACTED_YN as VARCHAR(1) ) as CONTRACTED_YN from staging_clarity.CLARITY_EPM_2;
drop view insurance.CLARITY_EPP;
create view insurance.CLARITY_EPP AS select
cast(BENEFIT_PLAN_ID as DOUBLE ) BENEFIT_PLAN_ID ,
cast(BENEFIT_PLAN_NAME as VARCHAR(200) ) BENEFIT_PLAN_NAME ,
cast(PRODUCT_TYPE as VARCHAR(254) ) PRODUCT_TYPE ,
cast(RPT_GRP_ONE as VARCHAR(80) ) RPT_GRP_ONE ,
cast(RPT_GRP_TWO as VARCHAR(80) ) RPT_GRP_TWO ,
cast(RPT_GRP_THREE as VARCHAR(80) ) RPT_GRP_THREE ,
cast(RPT_GRP_FOUR as VARCHAR(80) ) RPT_GRP_FOUR ,
cast(RPT_GRP_FIVE as VARCHAR(80) ) RPT_GRP_FIVE ,
cast(RPT_GRP_SIX as VARCHAR(66) ) RPT_GRP_SIX ,
cast(RPT_GRP_SEVEN as VARCHAR(66) ) RPT_GRP_SEVEN ,
cast(RPT_GRP_EIGHT as VARCHAR(66) ) RPT_GRP_EIGHT ,
cast(RPT_GRP_NINE as VARCHAR(66) ) RPT_GRP_NINE ,
cast(RPT_GRP_TEN as VARCHAR(66) ) RPT_GRP_TEN ,
cast(BEN_BKT_OPT_C as DOUBLE ) BEN_BKT_OPT_C ,
cast(IN_OUT_NET_D_C as DOUBLE ) IN_OUT_NET_D_C ,
cast(PB_GL_SEG as VARCHAR(12) ) PB_GL_SEG ,
cast(AP_CLM_GL_SEG as VARCHAR(12) ) AP_CLM_GL_SEG ,
cast(CAP_AP_GL_SEG as VARCHAR(12) ) CAP_AP_GL_SEG ,
cast(CAP_RR_GL_SEG as VARCHAR(12) ) CAP_RR_GL_SEG ,
cast(LIFEMAX_LIMIT_IN as DOUBLE ) LIFEMAX_LIMIT_IN ,
cast(LIFEMAX_LIMIT_OUT as DOUBLE ) LIFEMAX_LIMIT_OUT ,
cast(LIFEMAX_LIMIT_ALL as DOUBLE ) LIFEMAX_LIMIT_ALL ,
cast(LIFEMAX_DOLLAR_IN as DOUBLE ) LIFEMAX_DOLLAR_IN ,
cast(LIFEMAX_DOLLAR_OUT as DOUBLE ) LIFEMAX_DOLLAR_OUT ,
cast(LIFEMAX_DOLLAR_ALL as DOUBLE ) LIFEMAX_DOLLAR_ALL ,
cast(LIFEMAX_PER_IN as DOUBLE ) LIFEMAX_PER_IN ,
cast(LIFEMAX_PER_OUT as DOUBLE ) LIFEMAX_PER_OUT ,
cast(LIFEMAX_PER_ALL as DOUBLE ) LIFEMAX_PER_ALL ,
cast(PLAN_TYPE_C as DOUBLE ) PLAN_TYPE_C ,
cast(CVG_TYPE_C as DOUBLE ) CVG_TYPE_C ,
cast(PLAN_BILL_TYPE_C as DOUBLE ) PLAN_BILL_TYPE_C ,
cast(IS_DED_TO_MOOP_C as DOUBLE ) IS_DED_TO_MOOP_C ,
cast(ALL_ENC_TO_MOOP_C as DOUBLE ) ALL_ENC_TO_MOOP_C ,
cast(CARRYOVER_CLASS_C as DOUBLE ) CARRYOVER_CLASS_C ,
cast(PB_SAVINGS_PRCNTG as DOUBLE ) PB_SAVINGS_PRCNTG ,
cast(PPO_ADDRESS as VARCHAR(255) ) PPO_ADDRESS ,
cast(PPO_CITY as VARCHAR(40) ) PPO_CITY ,
cast(PPO_STATE_C as VARCHAR(66) ) PPO_STATE_C ,
cast(PPO_ZIP_CODE as VARCHAR(50) ) PPO_ZIP_CODE ,
cast(PPO_PHONE_number as VARCHAR(50) ) PPO_PHONE_number ,
cast(PAYOR_ID as DOUBLE ) PAYOR_ID ,
cast(BP_ADDR_LINE1 as VARCHAR(80) ) BP_ADDR_LINE1 ,
cast(BP_ADDR_LINE2 as VARCHAR(80) ) BP_ADDR_LINE2 ,
cast(BP_CITY as VARCHAR(50) ) BP_CITY ,
cast(BP_STATE_C as VARCHAR(66) ) BP_STATE_C ,
cast(BP_ZIP as VARCHAR(50) ) BP_ZIP ,
cast(BP_PHONE as VARCHAR(50) ) BP_PHONE ,
cast(SHORT_NAME as VARCHAR(254) ) SHORT_NAME ,
cast(CM_PHY_OWNER_ID as VARCHAR(25) ) CM_PHY_OWNER_ID ,
cast(CM_LOG_OWNER_ID as VARCHAR(25) ) CM_LOG_OWNER_ID ,
cast(RECORD_STAT_EPP_C as DOUBLE ) RECORD_STAT_EPP_C ,
cast(IS_GEN_EOB_YN as VARCHAR(254) ) IS_GEN_EOB_YN ,
cast(BP_FAX as VARCHAR(50) ) BP_FAX ,
cast(MSP_TYPE_C as DOUBLE ) MSP_TYPE_C ,
cast(ALT_CLM_CITY as VARCHAR(50) ) ALT_CLM_CITY ,
cast(ALT_CLM_STATE_C as VARCHAR(66) ) ALT_CLM_STATE_C ,
cast(ALT_CLM_ZIP_CODE as VARCHAR(50) ) ALT_CLM_ZIP_CODE ,
cast(OVERRIDE_PAYOR_YN as DOUBLE ) OVERRIDE_PAYOR_YN ,
cast(BP_COUNTY_C as VARCHAR(66) ) BP_COUNTY_C ,
cast(BP_COUNTRY_C as VARCHAR(66) ) BP_COUNTRY_C ,
cast(BP_HOUSE_number as VARCHAR(20) ) BP_HOUSE_number ,
cast(BP_DISTRICT_C as DOUBLE ) BP_DISTRICT_C ,
cast(ALT_ID_RC_BY_PLN as VARCHAR(254) ) ALT_ID_RC_BY_PLN ,
cast(EPP_PLAN_GENERIC_YN as VARCHAR(1) ) EPP_PLAN_GENERIC_YN ,
cast(CARRIER_ID as VARCHAR(18) ) CARRIER_ID ,
cast(ALLOW_PCP_EDITIN_YN as VARCHAR(1) ) ALLOW_PCP_EDITIN_YN ,
cast(ECD_TABLE_DEF_COPAY as DOUBLE ) ECD_TABLE_DEF_COPAY ,
cast(DEFAULT_COPAY as DOUBLE ) DEFAULT_COPAY ,
cast(PLAN_NAME_OPT_YN as VARCHAR(1) ) PLAN_NAME_OPT_YN ,
cast(CLAIM_MAIL_CODE_C as DOUBLE ) CLAIM_MAIL_CODE_C ,
cast(PLAN_CODE_ON_PAPER as VARCHAR(254) ) PLAN_CODE_ON_PAPER ,
cast(PLAN_CODE_ON_ELEC as VARCHAR(254) ) PLAN_CODE_ON_ELEC ,
cast(PAYOR_ORG_ID as VARCHAR(254) ) PAYOR_ORG_ID ,
cast(MEDIGAP_NUM as VARCHAR(254) ) MEDIGAP_NUM ,
cast(MEDIGAP_PLAN_YN as VARCHAR(1) ) MEDIGAP_PLAN_YN ,
cast(XOVER_APP_C as VARCHAR(66) ) XOVER_APP_C ,
cast(SAVE_FULL_GP_RES_YN as VARCHAR(1) ) SAVE_FULL_GP_RES_YN ,
cast(APC_GRP_DISP_PP_ID as DOUBLE ) APC_GRP_DISP_PP_ID ,
cast(ANES_PRICING_ID as DOUBLE ) ANES_PRICING_ID ,
cast(SHOW_ASA_CODE_YN as VARCHAR(1) ) SHOW_ASA_CODE_YN ,
cast(ANES_UN_MIN_YN as VARCHAR(1) ) ANES_UN_MIN_YN ,
cast(ANES_P_MODS_YN as VARCHAR(1) ) ANES_P_MODS_YN ,
cast(VOIDS_DMD_RPRT_YN as VARCHAR(1) ) VOIDS_DMD_RPRT_YN ,
cast(PAPER_CLAIM_YN as VARCHAR(1) ) PAPER_CLAIM_YN ,
cast(CLM_FIN_CL_C as VARCHAR(66) ) CLM_FIN_CL_C ,
cast(ALT_CLM_PHONE as VARCHAR(15) ) ALT_CLM_PHONE ,
cast(EXCLD_PB_FO_YN as VARCHAR(1) ) EXCLD_PB_FO_YN ,
cast(CLAIM_DEF_PLAN_ID as DOUBLE ) CLAIM_DEF_PLAN_ID ,
cast(OK_RESUB_ZERO_YN as VARCHAR(1) ) OK_RESUB_ZERO_YN ,
cast(OK_SUBMT_ZERO_IN_YN as VARCHAR(1) ) OK_SUBMT_ZERO_IN_YN ,
cast(PAID_DEMAND_CLM_YN as VARCHAR(1) ) PAID_DEMAND_CLM_YN ,
cast(CLM_GRP_PTER_ID as DOUBLE ) CLM_GRP_PTER_ID ,
cast(RESUB_ZERO_CMG_ID as VARCHAR(200) ) RESUB_ZERO_CMG_ID ,
cast(SUBMIT_ZERO_INS_ID as VARCHAR(200) ) SUBMIT_ZERO_INS_ID ,
cast(DMD_ZERO_CMG_ID as VARCHAR(200) ) DMD_ZERO_CMG_ID ,
cast(PAPER_CRD_YN as VARCHAR(1) ) PAPER_CRD_YN ,
cast(ELCT_CRD_YN as VARCHAR(1) ) ELCT_CRD_YN ,
cast(PRNT_TAXO_YN as VARCHAR(1) ) PRNT_TAXO_YN ,
cast(PROD_PRT_B_CLM_YN as VARCHAR(1) ) PROD_PRT_B_CLM_YN ,
cast(SUPP_PMT_PAYOR_ID as DOUBLE ) SUPP_PMT_PAYOR_ID ,
cast(SUPP_PMT_PLAN_ID as DOUBLE ) SUPP_PMT_PLAN_ID ,
cast(MAX_SVC_LINES_CMS as DOUBLE ) MAX_SVC_LINES_CMS ,
cast(ADJ_GRPR_SEC_CLM_C as DOUBLE ) ADJ_GRPR_SEC_CLM_C ,
cast(ADJUSTMENT_CODE as VARCHAR(254) ) ADJUSTMENT_CODE ,
cast(DEFAULT_CHG_TBL_ID as VARCHAR(18) ) DEFAULT_CHG_TBL_ID ,
cast(CHG_TBL_LPP_ID as DOUBLE ) CHG_TBL_LPP_ID ,
cast(ADJUD_CHARGE_VIA_C as DOUBLE ) ADJUD_CHARGE_VIA_C ,
cast(COVERED_PERCENTAGE as DOUBLE ) COVERED_PERCENTAGE ,
cast(ADJUD_PP_ID as DOUBLE ) ADJUD_PP_ID ,
cast(COPY_IME_LINES_YN as VARCHAR(1) ) COPY_IME_LINES_YN ,
cast(USE_IN_SHARED_CV_YN as VARCHAR(1) ) USE_IN_SHARED_CV_YN ,
cast(ADMSN_BILL_YN as VARCHAR(1) ) ADMSN_BILL_YN ,
cast(INTERF_CVGCREATE_ID as DOUBLE ) INTERF_CVGCREATE_ID ,
cast(PREVENT_DUP_CVG_C as DOUBLE ) PREVENT_DUP_CVG_C from staging_clarity.CLARITY_EPP;
drop view providers.CLARITY_SER;
create view providers.CLARITY_SER AS select
cast(PROV_ID as VARCHAR(18) ) as PROV_ID ,
cast(PROV_NAME as VARCHAR(254) ) as PROV_NAME ,
cast(PROV_TYPE as VARCHAR(254) ) as PROV_TYPE ,
cast(PROV_ABBR as VARCHAR(254) ) as PROV_ABBR ,
cast(GL_PREFIX as VARCHAR(15) ) as GL_PREFIX ,
cast(RPT_GRP_ONE as VARCHAR(80) ) as RPT_GRP_ONE ,
cast(RPT_GRP_TWO as VARCHAR(80) ) as RPT_GRP_TWO ,
cast(RPT_GRP_THREE as VARCHAR(80) ) as RPT_GRP_THREE ,
cast(RPT_GRP_FOUR as VARCHAR(80) ) as RPT_GRP_FOUR ,
cast(RPT_GRP_FIVE as VARCHAR(80) ) as RPT_GRP_FIVE ,
cast(RPT_GRP_SIX as VARCHAR(66) ) as RPT_GRP_SIX ,
cast(RPT_GRP_SEVEN as VARCHAR(66) ) as RPT_GRP_SEVEN ,
cast(RPT_GRP_EIGHT as VARCHAR(66) ) as RPT_GRP_EIGHT ,
cast(RPT_GRP_NINE as VARCHAR(66) ) as RPT_GRP_NINE ,
cast(RPT_GRP_TEN as VARCHAR(66) ) as RPT_GRP_TEN ,
cast(IS_RESIDENT as VARCHAR(3) ) as IS_RESIDENT ,
cast(USER_ID as VARCHAR(18) ) as USER_ID ,
cast(EPIC_PROV_ID as VARCHAR(18) ) as EPIC_PROV_ID ,
cast(REFERRAL_SRCE_TYPE as VARCHAR(66) ) as REFERRAL_SRCE_TYPE ,
cast(IS_VERIFIED_YN as VARCHAR(1) ) as IS_VERIFIED_YN ,
cast(SER_REF_SRCE_ID as VARCHAR(18) ) as SER_REF_SRCE_ID ,
cast(UPIN as VARCHAR(30) ) as UPIN ,
cast(SSN as VARCHAR(192) ) as SSN ,
cast(EMP_STATUS as VARCHAR(40) ) as EMP_STATUS ,
cast(STAFF_RESOURCE as VARCHAR(20) ) as STAFF_RESOURCE ,
cast(CLINICIAN_TITLE as VARCHAR(100) ) as CLINICIAN_TITLE ,
cast(EXTERNAL_NAME as VARCHAR(80) ) as EXTERNAL_NAME ,
cast(ACTIVE_STATUS as VARCHAR(20) ) as ACTIVE_STATUS ,
cast(REFERRAL_SOURCE_TYPE as VARCHAR(66) ) as REFERRAL_SOURCE_TYPE ,
cast(RECORD_TYPE as DOUBLE ) as RECORD_TYPE ,
cast(BILL_PROV_YN as VARCHAR(1) ) as BILL_PROV_YN ,
cast(BILL_UNDER_PROV_ID as VARCHAR(18) ) as BILL_UNDER_PROV_ID ,
cast(SUP_PROV_ID as VARCHAR(18) ) as SUP_PROV_ID ,
cast(COUNTY_C as VARCHAR(66) ) as COUNTY_C ,
cast(COUNTRY_C as VARCHAR(66) ) as COUNTRY_C ,
cast(OFFICE_PHONE_NUM as VARCHAR(50) ) as OFFICE_PHONE_NUM ,
cast(OFFICE_FAX_NUM as VARCHAR(25) ) as OFFICE_FAX_NUM ,
cast(EMAIL as VARCHAR(128) ) as EMAIL ,
cast(DEA_number as VARCHAR(30) ) as DEA_number ,
cast(SEX as VARCHAR(66) ) as SEX ,
cast(BIRTH_DATE as TIMESTAMP ) as BIRTH_DATE ,
cast(MEDICARE_PROV_ID as VARCHAR(12) ) as MEDICARE_PROV_ID ,
cast(MEDICAID_PROV_ID as VARCHAR(12) ) as MEDICAID_PROV_ID ,
cast(IS_PRIV_REVOKED as VARCHAR(1) ) as IS_PRIV_REVOKED ,
cast(NURSE_EMP_ID as VARCHAR(18) ) as NURSE_EMP_ID ,
cast(EPICCARE_PROV_YN as VARCHAR(1) ) as EPICCARE_PROV_YN ,
cast(MEDS_AUTH_PROV_YN as VARCHAR(1) ) as MEDS_AUTH_PROV_YN ,
cast(ORDS_AUTH_PROV_YN as VARCHAR(1) ) as ORDS_AUTH_PROV_YN ,
cast(TRANS_INTF_USER_YN as VARCHAR(1) ) as TRANS_INTF_USER_YN ,
cast(PEER_REV_LAST_DATE as TIMESTAMP ) as PEER_REV_LAST_DATE ,
cast(TAKING_NEW_PAT_YN as VARCHAR(1) ) as TAKING_NEW_PAT_YN ,
cast(TAKING_WALKINS_YN as VARCHAR(1) ) as TAKING_WALKINS_YN ,
cast(LAST_RECOMMENDED_DATE as TIMESTAMP ) as LAST_RECOMMENDED_DATE ,
cast(BASE_COST as DOUBLE ) as BASE_COST ,
cast(SURG_REC_POOL_YN as VARCHAR(1) ) as SURG_REC_POOL_YN ,
cast(INSTRUMENT_TYPE_C as VARCHAR(66) ) as INSTRUMENT_TYPE_C ,
cast(EQUIP_SERVICE_DATE as TIMESTAMP ) as EQUIP_SERVICE_DATE ,
cast(EQUIP_LASTSVC_DATE as TIMESTAMP ) as EQUIP_LASTSVC_DATE ,
cast(CLM_POS_REQD_YN as VARCHAR(1) ) as CLM_POS_REQD_YN ,
cast(DEFAULT_POS_CLM_YN as VARCHAR(1) ) as DEFAULT_POS_CLM_YN ,
cast(MODALITY_TYPE_C as DOUBLE ) as MODALITY_TYPE_C ,
cast(MODALITY_YN as VARCHAR(1) ) as MODALITY_YN ,
cast(SUPERV_POOL_ID as VARCHAR(18) ) as SUPERV_POOL_ID ,
cast(SUPERV_POOL_NAME as VARCHAR(254) ) as SUPERV_POOL_NAME ,
cast(FLASH_CARD_PRT_ROU as VARCHAR(255) ) as FLASH_CARD_PRT_ROU ,
cast(CTRL_SHEET_PRT_ROU as VARCHAR(255) ) as CTRL_SHEET_PRT_ROU ,
cast(PIN_ID as VARCHAR(18) ) as PIN_ID ,
cast(PROV_ATTR_ID as VARCHAR(18) ) as PROV_ATTR_ID ,
cast(ATTND_PRIM_PAGER as VARCHAR(50) ) as ATTND_PRIM_PAGER ,
cast(OO_OFFICE_FROM_DTE as TIMESTAMP ) as OO_OFFICE_FROM_DTE ,
cast(OO_OFFICE_TO_DTE as TIMESTAMP ) as OO_OFFICE_TO_DTE ,
cast(DEF_DEPARTMENT_ID as DOUBLE ) as DEF_DEPARTMENT_ID ,
cast(CM_PHY_OWNER_ID as VARCHAR(25) ) as CM_PHY_OWNER_ID ,
cast(CM_LOG_OWNER_ID as VARCHAR(25) ) as CM_LOG_OWNER_ID ,
cast(RPT_GRP_ELEVEN_C as VARCHAR(66) ) as RPT_GRP_ELEVEN_C ,
cast(RPT_GRP_TWELVE_C as VARCHAR(66) ) as RPT_GRP_TWELVE_C ,
cast(RPT_GRP_THIRTEEN_C as VARCHAR(66) ) as RPT_GRP_THIRTEEN_C ,
cast(RPT_GRP_FOURTEEN_C as VARCHAR(66) ) as RPT_GRP_FOURTEEN_C ,
cast(RPT_GRP_FIFTEEN_C as VARCHAR(66) ) as RPT_GRP_FIFTEEN_C ,
cast(RPT_GRP_SIXTEEN_C as VARCHAR(66) ) as RPT_GRP_SIXTEEN_C ,
cast(RPT_GRP_SEVNTEEN_C as VARCHAR(66) ) as RPT_GRP_SEVNTEEN_C ,
cast(RPT_GRP_EIGHTEEN_C as VARCHAR(66) ) as RPT_GRP_EIGHTEEN_C ,
cast(RPT_GRP_NINETEEN_C as VARCHAR(66) ) as RPT_GRP_NINETEEN_C ,
cast(RPT_GRP_TWENTY_C as VARCHAR(66) ) as RPT_GRP_TWENTY_C ,
cast(HOSPITALIST_YN as VARCHAR(254) ) as HOSPITALIST_YN ,
cast(DEF_DIVISION_C as DOUBLE ) as DEF_DIVISION_C ,
cast(DEF_PROVIDER_YN as VARCHAR(254) ) as DEF_PROVIDER_YN ,
cast(PROV_REC_STATE_C as DOUBLE ) as PROV_REC_STATE_C ,
cast(PROV_START_DATE as TIMESTAMP ) as PROV_START_DATE ,
cast(PRACTICE_NAME_C as DOUBLE ) as PRACTICE_NAME_C ,
cast(SURG_SCHED_OUT_YN as VARCHAR(1) ) as SURG_SCHED_OUT_YN ,
cast(SURG_EQP_SVCDAT_YN as VARCHAR(1) ) as SURG_EQP_SVCDAT_YN ,
cast(SURG_COST_TBL_ID as DOUBLE ) as SURG_COST_TBL_ID ,
cast(TEAM_LEADER_ID as VARCHAR(18) ) as TEAM_LEADER_ID ,
cast(TEAM_C as DOUBLE ) as TEAM_C ,
cast(SUP_PROV_YN as VARCHAR(1) ) as SUP_PROV_YN ,
cast(EMPLOYED_CRNA_YN as VARCHAR(1) ) as EMPLOYED_CRNA_YN ,
cast(IS_INTERPRETER_YN as VARCHAR(1) ) as IS_INTERPRETER_YN ,
cast(DOCTORS_DEGREE as VARCHAR(254) ) as DOCTORS_DEGREE ,
cast(REVENUE_DEPT_ID as DOUBLE ) as REVENUE_DEPT_ID ,
cast(ENC_PROV_YN as VARCHAR(1) ) as ENC_PROV_YN ,
cast(PHARMACIST_YN as VARCHAR(1) ) as PHARMACIST_YN ,
cast(LAB_FAX_number as VARCHAR(254) ) as LAB_FAX_number ,
cast(RES_POOL_TYPE_C as DOUBLE ) as RES_POOL_TYPE_C ,
cast(OR_VLD_DT_OFST as DOUBLE ) as OR_VLD_DT_OFST ,
cast(OR_CHARGE_CODE_ID as DOUBLE ) as OR_CHARGE_CODE_ID ,
cast(DBC_EXT_POS_ID as DOUBLE ) as DBC_EXT_POS_ID ,
cast(OP_ORD_PROV_YN as VARCHAR(1) ) as OP_ORD_PROV_YN ,
cast(IS_SUP_PROV_REQ_C as DOUBLE ) as IS_SUP_PROV_REQ_C ,
cast(PROV_PHOTO as VARCHAR(120) ) as PROV_PHOTO ,
cast(USE_DEPT_VT_LIM_YN as VARCHAR(1) ) as USE_DEPT_VT_LIM_YN ,
cast(VERIFYING_PERSON_ID as VARCHAR(18) ) as VERIFYING_PERSON_ID ,
cast(DIRECTORY_INFO as VARCHAR(254) ) as DIRECTORY_INFO ,
cast(EDI_CLM_ACTIVE_YN as VARCHAR(1) ) as EDI_CLM_ACTIVE_YN ,
cast(PROV_CLM_PROC_STA_C as DOUBLE ) as PROV_CLM_PROC_STA_C ,
cast(PAYEE_NUM_DEFAULT as VARCHAR(254) ) as PAYEE_NUM_DEFAULT ,
cast(SER_CLM_ID as VARCHAR(254) ) as SER_CLM_ID ,
cast(MCD_PROF_CD_C as DOUBLE ) as MCD_PROF_CD_C ,
cast(PROVIDER_TYPE_C as VARCHAR(66) ) as PROVIDER_TYPE_C ,
cast(EPRESCRIBING_YN as VARCHAR(1) ) as EPRESCRIBING_YN ,
cast(EP_FLAG_YN as VARCHAR(1) ) as EP_FLAG_YN ,
cast(SEX_C as VARCHAR(66) ) as SEX_C ,
cast(STAFF_RESOURCE_C as DOUBLE ) as STAFF_RESOURCE_C ,
cast(ACTIVE_STATUS_C as DOUBLE ) as ACTIVE_STATUS_C ,
cast(REFERRAL_SOURCE_TYPE_C as VARCHAR(66) ) as REFERRAL_SOURCE_TYPE_C ,
cast(REFERRAL_SRCE_TYPE_C as VARCHAR(66) ) as REFERRAL_SRCE_TYPE_C from staging_clarity.CLARITY_SER;
drop view providers.CLARITY_SER_SPEC;
create view providers.CLARITY_SER_SPEC AS select
cast(PROV_ID as VARCHAR(18) ) as PROV_ID ,
cast(LINE as DOUBLE ) as LINE ,
cast(SPECIALTY_C as VARCHAR(66) ) as SPECIALTY_C ,
cast(CM_PHY_OWNER_ID as VARCHAR(25) ) as CM_PHY_OWNER_ID ,
cast(CM_LOG_OWNER_ID as VARCHAR(25) ) as CM_LOG_OWNER_ID from staging_clarity.CLARITY_SER_SPEC;
drop view patients.ETHNIC_BACKGROUND;
create view patients.ETHNIC_BACKGROUND AS select
cast(PAT_ID as VARCHAR(18) ) as PAT_ID ,
cast(LINE as DOUBLE ) as LINE ,
cast(CM_PHY_OWNER_ID as VARCHAR(25) ) as CM_PHY_OWNER_ID ,
cast(CM_LOG_OWNER_ID as VARCHAR(25) ) as CM_LOG_OWNER_ID ,
cast(ETHNIC_BKGRND_C as DOUBLE ) as ETHNIC_BKGRND_C from staging_clarity.ETHNIC_BACKGROUND;
drop view insurance.HSP_ACCT_CVG_LIST;
create view insurance.HSP_ACCT_CVG_LIST AS select
cast(HSP_ACCOUNT_ID as DOUBLE ) as HSP_ACCOUNT_ID ,
cast(LINE as DOUBLE ) as LINE ,
cast(COVERAGE_ID as DOUBLE ) as COVERAGE_ID ,
cast(CM_PHY_OWNER_ID as VARCHAR(25) ) as CM_PHY_OWNER_ID ,
cast(CM_LOG_OWNER_ID as VARCHAR(25) ) as CM_LOG_OWNER_ID from staging_clarity.HSP_ACCT_CVG_LIST;
drop view diagnoses.HSP_ACCT_DX_LIST;
create view diagnoses.HSP_ACCT_DX_LIST AS select
cast(HSP_ACCOUNT_ID as DOUBLE ) as HSP_ACCOUNT_ID ,
cast(LINE as DOUBLE ) as LINE ,
cast(DX_ID as DOUBLE ) as DX_ID ,
cast(CM_PHY_OWNER_ID as VARCHAR(25) ) as CM_PHY_OWNER_ID ,
cast(CM_LOG_OWNER_ID as VARCHAR(25) ) as CM_LOG_OWNER_ID ,
cast(DX_POA_YNU as VARCHAR(254) ) as DX_POA_YNU ,
cast(DX_AFFECTS_DRG_YN as VARCHAR(254) ) as DX_AFFECTS_DRG_YN ,
cast(DX_COMORBIDITY_YN as VARCHAR(254) ) as DX_COMORBIDITY_YN ,
cast(FINAL_DX_SOI_C as DOUBLE ) as FINAL_DX_SOI_C ,
cast(FINAL_DX_ROM_C as DOUBLE ) as FINAL_DX_ROM_C ,
cast(FINAL_DX_EXCLD_YN as VARCHAR(1) ) as FINAL_DX_EXCLD_YN ,
cast(FNL_DX_AFCT_SOI_YN as VARCHAR(1) ) as FNL_DX_AFCT_SOI_YN ,
cast(FNL_DX_AFCT_ROM_YN as VARCHAR(1) ) as FNL_DX_AFCT_ROM_YN ,
cast(FINAL_DX_POA_C as DOUBLE ) as FINAL_DX_POA_C ,
cast(DX_COMORBIDITY_C as DOUBLE ) as DX_COMORBIDITY_C ,
cast(DX_HAC_YN as VARCHAR(1) ) as DX_HAC_YN ,
cast(DX_TYPE_C as DOUBLE ) as DX_TYPE_C ,
cast(DX_START_DT as TIMESTAMP ) as DX_START_DT ,
cast(DX_END_DT as TIMESTAMP ) as DX_END_DT from staging_clarity.HSP_ACCT_DX_LIST;
drop view procedures.HSP_ACCT_PX_LIST;
create view procedures.HSP_ACCT_PX_LIST AS select
cast(HSP_ACCOUNT_ID as DOUBLE ) as HSP_ACCOUNT_ID ,
cast(LINE as DOUBLE ) as LINE ,
cast(FINAL_ICD_PX_ID as VARCHAR(18) ) as FINAL_ICD_PX_ID ,
cast(PROC_DATE as TIMESTAMP ) as PROC_DATE ,
cast(PROC_PERF_PROV_ID as VARCHAR(18) ) as PROC_PERF_PROV_ID ,
cast(PROC_EVENT_number as DOUBLE ) as PROC_EVENT_number ,
cast(CM_PHY_OWNER_ID as VARCHAR(25) ) as CM_PHY_OWNER_ID ,
cast(CM_LOG_OWNER_ID as VARCHAR(25) ) as CM_LOG_OWNER_ID ,
cast(PX_AFCT_ROM_YN as VARCHAR(1) ) as PX_AFCT_ROM_YN ,
cast(PX_EXCLD_RPT_YN as VARCHAR(1) ) as PX_EXCLD_RPT_YN ,
cast(PX_AFCT_SOI_YN as VARCHAR(1) ) as PX_AFCT_SOI_YN from staging_clarity.HSP_ACCT_PX_LIST;
drop view patients.PATIENT_RACE;
create view patients.PATIENT_RACE AS select
cast(PAT_ID as VARCHAR(18) ) as PAT_ID ,
cast(LINE as DOUBLE ) as LINE ,
cast(PATIENT_RACE_C as DOUBLE ) as PATIENT_RACE_C ,
cast(CM_PHY_OWNER_ID as VARCHAR(25) ) as CM_PHY_OWNER_ID ,
cast(CM_LOG_OWNER_ID as VARCHAR(25) ) as CM_LOG_OWNER_ID from staging_clarity.PATIENT_RACE;
drop view clarity_reference.ZC_ACCT_CLASS_HA;
create view clarity_reference.ZC_ACCT_CLASS_HA AS select
cast(ACCT_CLASS_HA_C as VARCHAR(66) ) as ACCT_CLASS_HA_C ,
cast(NAME as VARCHAR(254) ) as NAME ,
cast(TITLE as VARCHAR(254) ) as TITLE ,
cast(ABBR as VARCHAR(254) ) as ABBR ,
cast(INTERNAL_ID as VARCHAR(66) ) as INTERNAL_ID from staging_clarity.ZC_ACCT_CLASS_HA;
drop view clarity_reference.ZC_ETHNIC_BKGRND;
create view clarity_reference.ZC_ETHNIC_BKGRND AS select
cast(ETHNIC_BKGRND_C as DOUBLE ) as ETHNIC_BKGRND_C ,
cast(NAME as VARCHAR(254) ) as NAME ,
cast(TITLE as VARCHAR(254) ) as TITLE ,
cast(ABBR as VARCHAR(254) ) as ABBR ,
cast(INTERNAL_ID as DOUBLE ) as INTERNAL_ID from staging_clarity.ZC_ETHNIC_BKGRND;
create view clarity_reference.ZC_ETHNIC_GROUP AS select
cast(ETHNIC_GROUP_C as DOUBLE ) as ETHNIC_GROUP_C ,
cast(NAME as VARCHAR(254) ) as NAME ,
cast(TITLE as VARCHAR(254) ) as TITLE ,
cast(ABBR as VARCHAR(254) ) as ABBR ,
cast(INTERNAL_ID as DOUBLE ) as INTERNAL_ID from staging_clarity.ZC_ETHNIC_GROUP;
create view clarity_reference.ZC_FINANCIAL_CLASS AS select
cast(FINANCIAL_CLASS as VARCHAR(66) ) as FINANCIAL_CLASS ,
cast(NAME as VARCHAR(254) ) as NAME ,
cast(TITLE as VARCHAR(254) ) as TITLE ,
cast(ABBR as VARCHAR(254) ) as ABBR ,
cast(INTERNAL_ID as VARCHAR(66) ) as INTERNAL_ID from staging_clarity.ZC_FINANCIAL_CLASS;
drop view clarity_reference.ZC_HOSP_ADMSN_TYPE;
create view clarity_reference.ZC_HOSP_ADMSN_TYPE AS select
cast(HOSP_ADMSN_TYPE_C as VARCHAR(254) ) as HOSP_ADMSN_TYPE_C ,
cast(NAME as VARCHAR(254) ) as NAME ,
cast(TITLE as VARCHAR(254) ) as TITLE ,
cast(ABBR as VARCHAR(254) ) as ABBR ,
cast(INTERNAL_ID as VARCHAR(254) ) as INTERNAL_ID from staging_clarity.ZC_HOSP_ADMSN_TYPE;
create view clarity_reference.ZC_MC_ADM_SOURCE AS select
cast (ADMISSION_SOURCE_C as VARCHAR(66) ) as ADMISSION_SOURCE_C ,
cast (NAME as VARCHAR(254) ) as NAME ,
cast (TITLE as VARCHAR(254) ) as TITLE ,
cast (ABBR as VARCHAR(254) ) as ABBR ,
cast (INTERNAL_ID as VARCHAR(66) ) as INTERNAL_ID from staging_clarity.ZC_MC_ADM_SOURCE;
drop view clarity_reference.ZC_PAT_SERVICE;
create view clarity_reference.ZC_PAT_SERVICE AS select
cast(HOSP_SERV_C as VARCHAR(66) ) as HOSP_SERV_C ,
cast(NAME as VARCHAR(254) ) as NAME ,
cast(TITLE as VARCHAR(254) ) as TITLE ,
cast(ABBR as VARCHAR(254) ) as ABBR ,
cast(INTERNAL_ID as VARCHAR(66) ) as INTERNAL_ID from staging_clarity.ZC_PAT_SERVICE;
drop view clarity_reference.ZC_MC_PAT_STATUS;
create view clarity_reference.ZC_MC_PAT_STATUS AS select
cast(PAT_STATUS_C as VARCHAR(66) ) as PAT_STATUS_C ,
cast(NAME as VARCHAR(254) ) as NAME ,
cast(TITLE as VARCHAR(254) ) as TITLE ,
cast(ABBR as VARCHAR(254) ) as ABBR ,
cast(INTERNAL_ID as VARCHAR(66) ) as INTERNAL_ID from staging_clarity.ZC_MC_PAT_STATUS;
drop view clarity_reference.ZC_PATIENT_RACE;
create view clarity_reference.ZC_PATIENT_RACE AS select
cast(PATIENT_RACE_C as DOUBLE ) as PATIENT_RACE_C ,
cast(NAME as VARCHAR(254) ) as NAME ,
cast(TITLE as VARCHAR(254) ) as TITLE ,
cast(ABBR as VARCHAR(254) ) as ABBR ,
cast(INTERNAL_ID as DOUBLE ) as INTERNAL_ID from staging_clarity.ZC_PATIENT_RACE;
create view clarity_reference.ZC_SEX AS select
cast(RCPT_MEM_SEX_C as VARCHAR(66) ) as RCPT_MEM_SEX_C ,
cast(NAME as VARCHAR(254) ) as NAME ,
cast(TITLE as VARCHAR(254) ) as TITLE ,
cast(ABBR as VARCHAR(254) ) as ABBR ,
cast(INTERNAL_ID as VARCHAR(66) ) as INTERNAL_ID from staging_clarity.ZC_SEX;
drop view clarity_reference.ZC_SPECIALTY;
create view clarity_reference.ZC_SPECIALTY AS select
cast(SPECIALTY_C as VARCHAR(66) ) as SPECIALTY_C ,
cast(NAME as VARCHAR(254) ) as NAME ,
cast(TITLE as VARCHAR(254) ) as TITLE ,
cast(ABBR as VARCHAR(254) ) as ABBR ,
cast(INTERNAL_ID as VARCHAR(66) ) as INTERNAL_ID from staging_clarity.ZC_SPECIALTY;
drop view clarity_reference.ZC_ACCT_BASECLS_HA;
create view clarity_reference.ZC_ACCT_BASECLS_HA AS select
cast(ACCT_BASECLS_HA_C as DOUBLE ) as ACCT_BASECLS_HA_C ,
cast(NAME as VARCHAR(254) ) as NAME ,
cast(TITLE as VARCHAR(254) ) as TITLE ,
cast(ABBR as VARCHAR(254) ) as ABBR ,
cast(INTERNAL_ID as DOUBLE ) as INTERNAL_ID from staging_clarity.ZC_ACCT_BASECLS_HA;
drop view clarity_reference.ZC_DX_POA;
create view clarity_reference.ZC_DX_POA AS select
cast(DX_POA_C as DOUBLE ) as DX_POA_C ,
cast(NAME as VARCHAR(254) ) as NAME ,
cast(TITLE as VARCHAR(254) ) as TITLE ,
cast(ABBR as VARCHAR(254) ) as ABBR ,
cast(INTERNAL_ID as DOUBLE ) as INTERNAL_ID from staging_clarity.ZC_DX_POA;
drop view insurance.COVERAGE;
create view insurance.COVERAGE AS select
cast(COVERAGE_ID as DOUBLE ) as COVERAGE_ID ,
cast(COVERAGE_TYPE_C as DOUBLE ) as COVERAGE_TYPE_C ,
cast(COVERAGE_STATUS_C as DOUBLE ) as COVERAGE_STATUS_C ,
cast(CARRIER_ID as VARCHAR(18) ) as CARRIER_ID ,
cast(PAYOR_ID as DOUBLE ) as PAYOR_ID ,
cast(PLAN_ID as DOUBLE ) as PLAN_ID ,
cast(PLAN_GRP_ID as VARCHAR(20) ) as PLAN_GRP_ID ,
cast(SUBSCR_NUM as VARCHAR(50) ) as SUBSCR_NUM ,
cast(ACCT_ID as DOUBLE ) as ACCT_ID ,
cast(SUBSCR_NAME as VARCHAR(160) ) as SUBSCR_NAME ,
cast(COBRA_STATUS_YN as VARCHAR(1) ) as COBRA_STATUS_YN ,
cast(COBRA_DATE as TIMESTAMP ) as COBRA_DATE ,
cast(LATE_ENROLL_YN as VARCHAR(1) ) as LATE_ENROLL_YN ,
cast(STUDENT_REVIEW_DT as TIMESTAMP ) as STUDENT_REVIEW_DT ,
cast(EMPLOYMENT_DATE as TIMESTAMP ) as EMPLOYMENT_DATE ,
cast(APPLICATION_DATE as TIMESTAMP ) as APPLICATION_DATE ,
cast(EPIC_CVG_ID as DOUBLE ) as EPIC_CVG_ID ,
cast(PB_ACCT_ID as VARCHAR(18) ) as PB_ACCT_ID ,
cast(SUBSCR_BIRTHDATE as TIMESTAMP ) as SUBSCR_BIRTHDATE ,
cast(SUBSCR_SEX_C as VARCHAR(66) ) as SUBSCR_SEX_C ,
cast(SUBSCR_ADDR1 as VARCHAR(300) ) as SUBSCR_ADDR1 ,
cast(SUBSCR_ADDR2 as VARCHAR(300) ) as SUBSCR_ADDR2 ,
cast(SUBSCR_CITY as VARCHAR(40) ) as SUBSCR_CITY ,
cast(SUBSCR_STATE_C as VARCHAR(66) ) as SUBSCR_STATE_C ,
cast(SUBSCR_COUNTRY_C as VARCHAR(66) ) as SUBSCR_COUNTRY_C ,
cast(SUBSCR_ZIP as VARCHAR(50) ) as SUBSCR_ZIP ,
cast(SUBSCR_PHONE as VARCHAR(50) ) as SUBSCR_PHONE ,
cast(SUBSCRIBER_FAX as VARCHAR(25) ) as SUBSCRIBER_FAX ,
cast(SUBSCR_WORK_PHONE as VARCHAR(50) ) as SUBSCR_WORK_PHONE ,
cast(CVG_EFF_DT as TIMESTAMP ) as CVG_EFF_DT ,
cast(CVG_TERM_DT as TIMESTAMP ) as CVG_TERM_DT ,
cast(SUBSCR_COUNTY_C as VARCHAR(66) ) as SUBSCR_COUNTY_C ,
cast(CASEHEAD_number as VARCHAR(40) ) as CASEHEAD_number ,
cast(CASEHEAD_NAME as VARCHAR(40) ) as CASEHEAD_NAME ,
cast(DT_LAST_PRO_RATED as TIMESTAMP ) as DT_LAST_PRO_RATED ,
cast(TNSFRD_COVERAGE_ID as DOUBLE ) as TNSFRD_COVERAGE_ID ,
cast(UPDATE_DATE as TIMESTAMP ) as UPDATE_DATE ,
cast(SUBSC_RACE_C as DOUBLE ) as SUBSC_RACE_C ,
cast(SUB_MARITAL_STS_C as DOUBLE ) as SUB_MARITAL_STS_C ,
cast(IS_SUB_US_CITZN_YN as VARCHAR(1) ) as IS_SUB_US_CITZN_YN ,
cast(CVG_REG_STATUS_C as VARCHAR(66) ) as CVG_REG_STATUS_C ,
cast(LAST_DATE_VERIFIED as TIMESTAMP ) as LAST_DATE_VERIFIED ,
cast(NEXT_REVIEW_DATE as TIMESTAMP ) as NEXT_REVIEW_DATE ,
cast(VERIFY_USER_ID as VARCHAR(18) ) as VERIFY_USER_ID ,
cast(VERIFY_SOURCE_C as DOUBLE ) as VERIFY_SOURCE_C ,
cast(SUBSCR_EMPLOYER_ID as VARCHAR(254) ) as SUBSCR_EMPLOYER_ID ,
cast(GROUP_NAME as VARCHAR(254) ) as GROUP_NAME ,
cast(CVG_ADDR1 as VARCHAR(300) ) as CVG_ADDR1 ,
cast(CVG_ADDR2 as VARCHAR(300) ) as CVG_ADDR2 ,
cast(CVG_CITY as VARCHAR(40) ) as CVG_CITY ,
cast(STATE_C as VARCHAR(66) ) as STATE_C ,
cast(CVG_ZIP as VARCHAR(50) ) as CVG_ZIP ,
cast(CVG_PHONE1 as VARCHAR(50) ) as CVG_PHONE1 ,
cast(SUBSCR_SSN as VARCHAR(192) ) as SUBSCR_SSN ,
cast(SUBSCR_EEP_ADDR_1 as VARCHAR(300) ) as SUBSCR_EEP_ADDR_1 ,
cast(SUBSCR_EEP_ADDR_2 as VARCHAR(300) ) as SUBSCR_EEP_ADDR_2 ,
cast(SUBSCR_EEP_CITY as VARCHAR(40) ) as SUBSCR_EEP_CITY ,
cast(SUBSCR_EEP_STE_C as VARCHAR(66) ) as SUBSCR_EEP_STE_C ,
cast(SUBSCR_EEP_ZIP as VARCHAR(50) ) as SUBSCR_EEP_ZIP ,
cast(SUBSCR_EEP_PHONE as VARCHAR(50) ) as SUBSCR_EEP_PHONE ,
cast(SUBSCR_EMP_STAT_C as DOUBLE ) as SUBSCR_EMP_STAT_C ,
cast(GROUP_NUM as VARCHAR(254) ) as GROUP_NUM ,
cast(CLAIM_MAIL_CODE_C as DOUBLE ) as CLAIM_MAIL_CODE_C ,
cast(CM_PHY_OWNER_ID as VARCHAR(25) ) as CM_PHY_OWNER_ID ,
cast(CM_LOG_OWNER_ID as VARCHAR(25) ) as CM_LOG_OWNER_ID ,
cast(WEB_APN_ID as VARCHAR(18) ) as WEB_APN_ID ,
cast(WC_EMPLOYER_ID as VARCHAR(254) ) as WC_EMPLOYER_ID ,
cast(WC_DATE_OF_INJURY as TIMESTAMP ) as WC_DATE_OF_INJURY ,
cast(IS_SIG_ON_FILE_YN as VARCHAR(1) ) as IS_SIG_ON_FILE_YN ,
cast(ENROLL_REASON_C as DOUBLE ) as ENROLL_REASON_C ,
cast(CVG_TERM_REASON_C as DOUBLE ) as CVG_TERM_REASON_C ,
cast(SUBSCR_EMPR_ID_CMT as VARCHAR(254) ) as SUBSCR_EMPR_ID_CMT ,
cast(PAT_REC_OF_SUBS_ID as VARCHAR(18) ) as PAT_REC_OF_SUBS_ID ,
cast(ECD_TABLE_DEF_COPAY as DOUBLE ) as ECD_TABLE_DEF_COPAY ,
cast(COINSURANCE_OVR as DOUBLE ) as COINSURANCE_OVR ,
cast(SUBSCR_HOUSE_NUM as VARCHAR(20) ) as SUBSCR_HOUSE_NUM ,
cast(MEDC_COVERED_LEFT as DOUBLE ) as MEDC_COVERED_LEFT ,
cast(MEDC_COINS_LEFT as DOUBLE ) as MEDC_COINS_LEFT ,
cast(MEDC_RESERVE_LEFT as DOUBLE ) as MEDC_RESERVE_LEFT ,
cast(CCS_PAT_ID as VARCHAR(254) ) as CCS_PAT_ID ,
cast(CCS_DX as VARCHAR(254) ) as CCS_DX ,
cast(CCS_CC_NAME as VARCHAR(254) ) as CCS_CC_NAME ,
cast(CCS_COOR_PHONE as VARCHAR(254) ) as CCS_COOR_PHONE ,
cast(CCS_COUNTY_PHONE as VARCHAR(254) ) as CCS_COUNTY_PHONE ,
cast(CVG_COUNTY_C as VARCHAR(66) ) as CVG_COUNTY_C ,
cast(CVG_COUNTRY_C as VARCHAR(66) ) as CVG_COUNTRY_C ,
cast(CVG_HOUSE_NUM as VARCHAR(20) ) as CVG_HOUSE_NUM ,
cast(CVG_DISTRICT_C as DOUBLE ) as CVG_DISTRICT_C ,
cast(SUBSCR_EEP_CNTY_C as VARCHAR(66) ) as SUBSCR_EEP_CNTY_C ,
cast(SUBSCR_EEP_HOUSE_N as VARCHAR(20) ) as SUBSCR_EEP_HOUSE_N ,
cast(SUBSCR_EEP_DIST_C as DOUBLE ) as SUBSCR_EEP_DIST_C ,
cast(SUBSCR_DISTRICT_C as DOUBLE ) as SUBSCR_DISTRICT_C ,
cast(EFF_HOSP_CVG_DT as TIMESTAMP ) as EFF_HOSP_CVG_DT ,
cast(EFF_PROV_CVG_DT as TIMESTAMP ) as EFF_PROV_CVG_DT ,
cast(MEDICARE_CVG_TYPE_C as DOUBLE ) as MEDICARE_CVG_TYPE_C ,
cast(MEDICARE_SUBSCR_ID as VARCHAR(254) ) as MEDICARE_SUBSCR_ID ,
cast(RQG_REL_TO_SUB_C as DOUBLE ) as RQG_REL_TO_SUB_C ,
cast(Q4CO_BUCKETS_EXC_YN as VARCHAR(1) ) as Q4CO_BUCKETS_EXC_YN ,
cast(SUBSC_REL_TO_GUAR_C as DOUBLE ) as SUBSC_REL_TO_GUAR_C ,
cast(SUBSCR_EMPR_CNTRY_C as VARCHAR(66) ) as SUBSCR_EMPR_CNTRY_C ,
cast(MED_SEC_TYPE_C as VARCHAR(66) ) as MED_SEC_TYPE_C ,
cast(CHDP_COUNTY_C as VARCHAR(66) ) as CHDP_COUNTY_C ,
cast(CHDP_AID_CODE as VARCHAR(254) ) as CHDP_AID_CODE ,
cast(CVG_CARD_ISSUE_DT as TIMESTAMP ) as CVG_CARD_ISSUE_DT ,
cast(CVG_DEDUCTIBLE_YN as VARCHAR(1) ) as CVG_DEDUCTIBLE_YN ,
cast(FIRST_SPEC_AID_CODE as VARCHAR(254) ) as FIRST_SPEC_AID_CODE ,
cast(SEC_SPEC_AID_CODE as VARCHAR(254) ) as SEC_SPEC_AID_CODE ,
cast(THRD_SPEC_AID_CODE as VARCHAR(254) ) as THRD_SPEC_AID_CODE ,
cast(EVC_NUM as VARCHAR(254) ) as EVC_NUM ,
cast(COUNTY_CODE_C as DOUBLE ) as COUNTY_CODE_C ,
cast(CVG_VERIFICATION_ID as DOUBLE ) as CVG_VERIFICATION_ID ,
cast(EXT_ROUTING_NUM_C as VARCHAR(66) ) as EXT_ROUTING_NUM_C ,
cast(CONF_NAM_OF_ASSC_PT as VARCHAR(192) ) as CONF_NAM_OF_ASSC_PT ,
cast(OWN_BUS_SEG_EAF_ID as DOUBLE ) as OWN_BUS_SEG_EAF_ID from staging_clarity.COVERAGE;
// changed PRIM_ENC_DATE_REAL to DOUBLE instead of FLOAT(126)
drop view patients.HSP_ACCOUNT;
create view patients.HSP_ACCOUNT AS select
cast(HSP_ACCOUNT_ID as DOUBLE ) as HSP_ACCOUNT_ID ,
cast(HSP_ACCOUNT_NAME as VARCHAR(254) ) as HSP_ACCOUNT_NAME ,
cast(ACCT_BASECLS_HA_C as DOUBLE ) as ACCT_BASECLS_HA_C ,
cast(ACCT_BILLED_DATE as TIMESTAMP ) as ACCT_BILLED_DATE ,
cast(ACCT_CLASS_HA_C as VARCHAR(66) ) as ACCT_CLASS_HA_C ,
cast(ACCT_CLOSE_DATE as TIMESTAMP ) as ACCT_CLOSE_DATE ,
cast(ACCT_FIN_CLASS_C as VARCHAR(66) ) as ACCT_FIN_CLASS_C ,
cast(ACCT_NOTIFICTN_DT as TIMESTAMP ) as ACCT_NOTIFICTN_DT ,
cast(ACCT_SLFPYST_HA_C as DOUBLE ) as ACCT_SLFPYST_HA_C ,
cast(ACCT_BILLSTS_HA_C as DOUBLE ) as ACCT_BILLSTS_HA_C ,
cast(ACCT_TYPE_HA_C as VARCHAR(66) ) as ACCT_TYPE_HA_C ,
cast(ACCT_ZERO_BAL_DT as TIMESTAMP ) as ACCT_ZERO_BAL_DT ,
cast(ASSOC_AUTHCERT_ID as DOUBLE ) as ASSOC_AUTHCERT_ID ,
cast(ADM_DATE_TIME as TIMESTAMP ) as ADM_DATE_TIME ,
cast(ADM_DEPARMENT_ID as DOUBLE ) as ADM_DEPARMENT_ID ,
cast(ADM_LOC_ID as DOUBLE ) as ADM_LOC_ID ,
cast(ADM_PRIORITY as VARCHAR(25) ) as ADM_PRIORITY ,
cast(ADM_PROV_ID as VARCHAR(18) ) as ADM_PROV_ID ,
cast(ARCHIVED_DATE as TIMESTAMP ) as ARCHIVED_DATE ,
cast(ARCHIVE_ID as VARCHAR(254) ) as ARCHIVE_ID ,
cast(ATTENDING_PROV_ID as VARCHAR(18) ) as ATTENDING_PROV_ID ,
cast(AUTOPSY_DONE_YN as VARCHAR(1) ) as AUTOPSY_DONE_YN ,
cast(AUTOPSY_PROV_ID as VARCHAR(18) ) as AUTOPSY_PROV_ID ,
cast(BAD_DEBT_AGENCY_ID as DOUBLE ) as BAD_DEBT_AGENCY_ID ,
cast(BAD_DEBT_BUCKET_ID as DOUBLE ) as BAD_DEBT_BUCKET_ID ,
cast(BILL_NOTE_EXP_DATE as TIMESTAMP ) as BILL_NOTE_EXP_DATE ,
cast(CLAIM_ID as DOUBLE ) as CLAIM_ID ,
cast(CLAIM_USER_CHNG_YN as VARCHAR(1) ) as CLAIM_USER_CHNG_YN ,
cast(CODE_BLUE_YNU as VARCHAR(1) ) as CODE_BLUE_YNU ,
cast(COLL_GRPR_HA_C as DOUBLE ) as COLL_GRPR_HA_C ,
cast(COMBINE_ACCT_ID as DOUBLE ) as COMBINE_ACCT_ID ,
cast(COMBINE_COMMENT as VARCHAR(255) ) as COMBINE_COMMENT ,
cast(COMBINE_DATE_TIME as TIMESTAMP ) as COMBINE_DATE_TIME ,
cast(COMBINE_USER_ID as VARCHAR(18) ) as COMBINE_USER_ID ,
cast(COMPLETION_DT_TM as TIMESTAMP ) as COMPLETION_DT_TM ,
cast(COMPLETN_STS_HA_C as DOUBLE ) as COMPLETN_STS_HA_C ,
cast(CORONER_CASE_YN as VARCHAR(1) ) as CORONER_CASE_YN ,
cast(COVERAGE_ID as DOUBLE ) as COVERAGE_ID ,
cast(CPT_CD_NEW_INFO_YN as VARCHAR(1) ) as CPT_CD_NEW_INFO_YN ,
cast(CVG_LIST_SELECT_YN as VARCHAR(1) ) as CVG_LIST_SELECT_YN ,
cast(DISCH_DATE_TIME as TIMESTAMP ) as DISCH_DATE_TIME ,
cast(DISCH_DEPT_ID as DOUBLE ) as DISCH_DEPT_ID ,
cast(DISCH_DESTIN_HA_C as VARCHAR(66) ) as DISCH_DESTIN_HA_C ,
cast(DISCH_LOC_ID as DOUBLE ) as DISCH_LOC_ID ,
cast(DISCH_TO as VARCHAR(25) ) as DISCH_TO ,
cast(DRG_EXPECTED_REIMB as DOUBLE ) as DRG_EXPECTED_REIMB ,
cast(ER_ADMIT_DATE_TIME as TIMESTAMP ) as ER_ADMIT_DATE_TIME ,
cast(ER_ADMIT_SRC_HA_C as DOUBLE ) as ER_ADMIT_SRC_HA_C ,
cast(ER_ADMIT_TYP_HA_C as DOUBLE ) as ER_ADMIT_TYP_HA_C ,
cast(ER_DSCHG_DATE_TIME as TIMESTAMP ) as ER_DSCHG_DATE_TIME ,
cast(ER_PAT_STS_HA_C as VARCHAR(66) ) as ER_PAT_STS_HA_C ,
cast(EXPIRATION_UNIT_ID as DOUBLE ) as EXPIRATION_UNIT_ID ,
cast(EXPIRD_IN_HOUSE_YN as VARCHAR(1) ) as EXPIRD_IN_HOUSE_YN ,
cast(FINAL_DRG_ID as VARCHAR(18) ) as FINAL_DRG_ID ,
cast(FOLLOW_UP as VARCHAR(25) ) as FOLLOW_UP ,
cast(FRST_DET_BILL_DATE as TIMESTAMP ) as FRST_DET_BILL_DATE ,
cast(FRST_DMND_STMT_DT as TIMESTAMP ) as FRST_DMND_STMT_DT ,
cast(FRST_STMT_DATE as TIMESTAMP ) as FRST_STMT_DATE ,
cast(GUAR_ADDR_1 as VARCHAR(300) ) as GUAR_ADDR_1 ,
cast(GUAR_ADDR_2 as VARCHAR(300) ) as GUAR_ADDR_2 ,
cast(GUAR_CITY as VARCHAR(80) ) as GUAR_CITY ,
cast(GUAR_COUNTRY_C as VARCHAR(66) ) as GUAR_COUNTRY_C ,
cast(GUAR_COUNTY_C as VARCHAR(66) ) as GUAR_COUNTY_C ,
cast(GUAR_DOB as TIMESTAMP ) as GUAR_DOB ,
cast(GUAR_HM_PHONE as VARCHAR(50) ) as GUAR_HM_PHONE ,
cast(GUARANTOR_ID as DOUBLE ) as GUARANTOR_ID ,
cast(GUAR_NAME as VARCHAR(254) ) as GUAR_NAME ,
cast(GUAR_SEX_C as VARCHAR(66) ) as GUAR_SEX_C ,
cast(GUAR_SSN as VARCHAR(25) ) as GUAR_SSN ,
cast(GUAR_STATE_C as VARCHAR(66) ) as GUAR_STATE_C ,
cast(GUAR_WK_PHONE as VARCHAR(50) ) as GUAR_WK_PHONE ,
cast(GUAR_ZIP as VARCHAR(50) ) as GUAR_ZIP ,
cast(HIGH_RISK_YNU as VARCHAR(1) ) as HIGH_RISK_YNU ,
cast(HOSPICE_INDICATOR as VARCHAR(25) ) as HOSPICE_INDICATOR ,
cast(INST_OF_UPDATE as TIMESTAMP ) as INST_OF_UPDATE ,
cast(INSTRUCT_GIVEN_YNU as VARCHAR(1) ) as INSTRUCT_GIVEN_YNU ,
cast(IS_ACTIVE_YN as VARCHAR(1) ) as IS_ACTIVE_YN ,
cast(IS_CALLED_911_YNU as VARCHAR(1) ) as IS_CALLED_911_YNU ,
cast(IS_COPY_FLAG as VARCHAR(1) ) as IS_COPY_FLAG ,
cast(IS_CR_BAL_FLAG as VARCHAR(1) ) as IS_CR_BAL_FLAG ,
cast(IS_INSTI_YN as VARCHAR(1) ) as IS_INSTI_YN ,
cast(IS_LATE_CHG_FLAG as VARCHAR(1) ) as IS_LATE_CHG_FLAG ,
cast(IS_PMTPLAN_AMT_DUE as VARCHAR(1) ) as IS_PMTPLAN_AMT_DUE ,
cast(LAST_DET_BILL_DATE as TIMESTAMP ) as LAST_DET_BILL_DATE ,
cast(LAST_DMND_STMT_DT as TIMESTAMP ) as LAST_DMND_STMT_DT ,
cast(LAST_INTRM_BILL_DT as TIMESTAMP ) as LAST_INTRM_BILL_DT ,
cast(LAST_STMT_DATE as TIMESTAMP ) as LAST_STMT_DATE ,
cast(LOC_ID as DOUBLE ) as LOC_ID ,
cast(MEANS_OF_ARRV_C as VARCHAR(66) ) as MEANS_OF_ARRV_C ,
cast(NEXT_STMT_DATE as TIMESTAMP ) as NEXT_STMT_DATE ,
cast(NUM_OF_DET_BILLS as DOUBLE ) as NUM_OF_DET_BILLS ,
cast(NUM_OF_DMND_STMTS as DOUBLE ) as NUM_OF_DMND_STMTS ,
cast(NUM_OF_STMTS_SENT as DOUBLE ) as NUM_OF_STMTS_SENT ,
cast(PAT_ADDR_1 as VARCHAR(300) ) as PAT_ADDR_1 ,
cast(PAT_ADDR_2 as VARCHAR(300) ) as PAT_ADDR_2 ,
cast(PAT_CITY as VARCHAR(80) ) as PAT_CITY ,
cast(PAT_COUNTRY_C as VARCHAR(66) ) as PAT_COUNTRY_C ,
cast(PAT_COUNTY_C as VARCHAR(66) ) as PAT_COUNTY_C ,
cast(PAT_DOB as TIMESTAMP ) as PAT_DOB ,
cast(PAT_HOME_PHONE as VARCHAR(50) ) as PAT_HOME_PHONE ,
cast(PAT_ID as VARCHAR(18) ) as PAT_ID ,
cast(PAT_NAME as VARCHAR(254) ) as PAT_NAME ,
cast(PAT_SEX_C as VARCHAR(66) ) as PAT_SEX_C ,
cast(PAT_SSN as VARCHAR(25) ) as PAT_SSN ,
cast(PAT_STATE_C as VARCHAR(66) ) as PAT_STATE_C ,
cast(PAT_WRK_PHN as VARCHAR(50) ) as PAT_WRK_PHN ,
cast(PAT_ZIP as VARCHAR(50) ) as PAT_ZIP ,
cast(PLAN_DONE_YNU as VARCHAR(1) ) as PLAN_DONE_YNU ,
cast(POLICE_INVOLVD_YNU as VARCHAR(1) ) as POLICE_INVOLVD_YNU ,
cast(POST_ADM_EXP_HA_C as DOUBLE ) as POST_ADM_EXP_HA_C ,
cast(POST_OP_EXP_HA_C as DOUBLE ) as POST_OP_EXP_HA_C ,
cast(PREBILL_BUCKET_ID as DOUBLE ) as PREBILL_BUCKET_ID ,
cast(PRIM_SVC_HA_C as VARCHAR(66) ) as PRIM_SVC_HA_C ,
cast(PRIOR_ADMISSION as VARCHAR(25) ) as PRIOR_ADMISSION ,
cast(PSYCH_CASE_YNU as VARCHAR(1) ) as PSYCH_CASE_YNU ,
cast(READMISSION_INDIC as VARCHAR(25) ) as READMISSION_INDIC ,
cast(RECORD_CREATE_DATE as TIMESTAMP ) as RECORD_CREATE_DATE ,
cast(RECUR_PARENT_ID as DOUBLE ) as RECUR_PARENT_ID ,
cast(RECUR_STS_HA_C as DOUBLE ) as RECUR_STS_HA_C ,
cast(REFERRING_PROV_ID as VARCHAR(18) ) as REFERRING_PROV_ID ,
cast(REHAB_INDICATOR as VARCHAR(25) ) as REHAB_INDICATOR ,
cast(SCNDRY_SVC_HA_C as VARCHAR(66) ) as SCNDRY_SVC_HA_C ,
cast(SELF_PAY_BUCKET_ID as DOUBLE ) as SELF_PAY_BUCKET_ID ,
cast(SERV_AREA_ID as DOUBLE ) as SERV_AREA_ID ,
cast(TAKE_HOME_DRUG_YNU as VARCHAR(1) ) as TAKE_HOME_DRUG_YNU ,
cast(TOT_ACCT_BAL as DOUBLE ) as TOT_ACCT_BAL ,
cast(TOT_ADJ as DOUBLE ) as TOT_ADJ ,
cast(TOT_CHGS as DOUBLE ) as TOT_CHGS ,
cast(TOT_PMTS as DOUBLE ) as TOT_PMTS ,
cast(TRANSFER_FROM as VARCHAR(25) ) as TRANSFER_FROM ,
cast(TRANSFER_SRC_HA_C as DOUBLE ) as TRANSFER_SRC_HA_C ,
cast(TREATMENT_AUTH_NUM as VARCHAR(254) ) as TREATMENT_AUTH_NUM ,
cast(UB92_COINS_DAYS as DOUBLE ) as UB92_COINS_DAYS ,
cast(UB92_COVERED_DAYS as DOUBLE ) as UB92_COVERED_DAYS ,
cast(UB92_LIFETIME_DAYS as DOUBLE ) as UB92_LIFETIME_DAYS ,
cast(UB92_NONCOVRD_DAYS as DOUBLE ) as UB92_NONCOVRD_DAYS ,
cast(UB92_TOB_OVERRIDE as VARCHAR(254) ) as UB92_TOB_OVERRIDE ,
cast(UNDISTRB_BUCKET_ID as DOUBLE ) as UNDISTRB_BUCKET_ID ,
cast(PATIENT_STATUS_C as VARCHAR(66) ) as PATIENT_STATUS_C ,
cast(ADMISSION_SOURCE_C as VARCHAR(66) ) as ADMISSION_SOURCE_C ,
cast(ADMISSION_TYPE_C as DOUBLE ) as ADMISSION_TYPE_C ,
cast(PRIMARY_PAYOR_ID as DOUBLE ) as PRIMARY_PAYOR_ID ,
cast(PRIMARY_PLAN_ID as DOUBLE ) as PRIMARY_PLAN_ID ,
cast(PATIENT_MRN as VARCHAR(18) ) as PATIENT_MRN ,
cast(NUM_OF_CHARGES as DOUBLE ) as NUM_OF_CHARGES ,
cast(SIGN_ON_FILE_C as DOUBLE ) as SIGN_ON_FILE_C ,
cast(SIGN_ON_FILE_DATE as TIMESTAMP ) as SIGN_ON_FILE_DATE ,
cast(EXTRACT_DATETIME as TIMESTAMP ) as EXTRACT_DATETIME ,
cast(PRIM_CONTACT_OVRD as VARCHAR(254) ) as PRIM_CONTACT_OVRD ,
cast(CODING_STATUS_C as DOUBLE ) as CODING_STATUS_C ,
cast(CODING_STS_USER_ID as VARCHAR(18) ) as CODING_STS_USER_ID ,
cast(CODING_DATETIME as TIMESTAMP ) as CODING_DATETIME ,
cast(ABSTRACT_USER_ID as VARCHAR(18) ) as ABSTRACT_USER_ID ,
cast(OLD_RECUR_PRNT_ID as DOUBLE ) as OLD_RECUR_PRNT_ID ,
cast(OLD_RECUR_STS_C as DOUBLE ) as OLD_RECUR_STS_C ,
cast(NEEDS_REPOST_YN as VARCHAR(1) ) as NEEDS_REPOST_YN ,
cast(CASE_MIX_GRP_CODE as VARCHAR(20) ) as CASE_MIX_GRP_CODE ,
cast(LAST_CMG_CODE as VARCHAR(20) ) as LAST_CMG_CODE ,
cast(LAST_INT_CVG_ID as DOUBLE ) as LAST_INT_CVG_ID ,
cast(BIRTH_WEIGHT as DOUBLE ) as BIRTH_WEIGHT ,
cast(GESTATIONAL_AGE as VARCHAR(20) ) as GESTATIONAL_AGE ,
cast(DISCHARGE_WEIGHT as DOUBLE ) as DISCHARGE_WEIGHT ,
cast(ORGAN_DONOR_YN as VARCHAR(1) ) as ORGAN_DONOR_YN ,
cast(PREMATURE_BABY_YN as VARCHAR(1) ) as PREMATURE_BABY_YN ,
cast(CODER_INITIALS as VARCHAR(20) ) as CODER_INITIALS ,
cast(DNB_DATE as TIMESTAMP ) as DNB_DATE ,
cast(ADMIT_CATEGORY_C as DOUBLE ) as ADMIT_CATEGORY_C ,
cast(PRIM_ENC_CSN_ID as DOUBLE ) as PRIM_ENC_CSN_ID ,
cast(PRIM_ENC_DATE_REAL as DOUBLE ) as PRIM_ENC_DATE_REAL ,
cast(IS_HOSPITALIST_YN as VARCHAR(1) ) as IS_HOSPITALIST_YN ,
cast(ADMIT_DX_TEXT as VARCHAR(500) ) as ADMIT_DX_TEXT ,
cast(MOM_HSP_ACCT_ID as DOUBLE ) as MOM_HSP_ACCT_ID ,
cast(MOM_PATIENT_ID as VARCHAR(18) ) as MOM_PATIENT_ID ,
cast(FIRST_BILLED_DATE as TIMESTAMP ) as FIRST_BILLED_DATE ,
cast(INIT_CODING_DATE as TIMESTAMP ) as INIT_CODING_DATE ,
cast(LAST_CODING_DATE as TIMESTAMP ) as LAST_CODING_DATE ,
cast(EXP_TOTAL_CHG_AMT as DOUBLE ) as EXP_TOTAL_CHG_AMT ,
cast(EXP_TOTAL_CHG_CMT as VARCHAR(255) ) as EXP_TOTAL_CHG_CMT ,
cast(EXP_PAT_LIAB_CMT as VARCHAR(255) ) as EXP_PAT_LIAB_CMT ,
cast(PRORATED_PAT_LIAB as DOUBLE ) as PRORATED_PAT_LIAB ,
cast(PRORATED_PAT_BAL as DOUBLE ) as PRORATED_PAT_BAL ,
cast(EXP_NONCVD_CHG_AMT as DOUBLE ) as EXP_NONCVD_CHG_AMT ,
cast(BILL_DRG_IDTYPE_ID as DOUBLE ) as BILL_DRG_IDTYPE_ID ,
cast(BILL_DRG_MDC_VAL as VARCHAR(20) ) as BILL_DRG_MDC_VAL ,
cast(BILL_DRG_WEIGHT as VARCHAR(254) ) as BILL_DRG_WEIGHT ,
cast(BILL_DRG_PS as DOUBLE ) as BILL_DRG_PS ,
cast(BILL_DRG_ROM as DOUBLE ) as BILL_DRG_ROM ,
cast(BILL_DRG_SHORT_LOS as VARCHAR(20) ) as BILL_DRG_SHORT_LOS ,
cast(BILL_DRG_LONG_LOS as VARCHAR(20) ) as BILL_DRG_LONG_LOS ,
cast(BILL_DRG_AMLOS as VARCHAR(20) ) as BILL_DRG_AMLOS ,
cast(BILL_DRG_GMLOS as VARCHAR(20) ) as BILL_DRG_GMLOS ,
cast(BASE_INV_NUM as VARCHAR(12) ) as BASE_INV_NUM ,
cast(INV_NUM_SEQ_CTR as DOUBLE ) as INV_NUM_SEQ_CTR ,
cast(RESEARCH_ID as VARCHAR(18) ) as RESEARCH_ID ,
cast(SPECIALTY_SVC_C as DOUBLE ) as SPECIALTY_SVC_C ,
cast(XFER_TO_NURSE_C as DOUBLE ) as XFER_TO_NURSE_C ,
cast(XFER_TO_ACUTE_C as DOUBLE ) as XFER_TO_ACUTE_C ,
cast(DEATH_TYPE_C as DOUBLE ) as DEATH_TYPE_C ,
cast(APGAR_1_MIN as DOUBLE ) as APGAR_1_MIN ,
cast(APGAR_5_MIN as DOUBLE ) as APGAR_5_MIN ,
cast(GRAVIDA as DOUBLE ) as GRAVIDA ,
cast(PARA as DOUBLE ) as PARA ,
cast(BIRTH_CERT_SENT_YN as VARCHAR(1) ) as BIRTH_CERT_SENT_YN ,
cast(FAILED_VBAC_YN as VARCHAR(1) ) as FAILED_VBAC_YN ,
cast(DELIVERY_DATE_TIME as TIMESTAMP ) as DELIVERY_DATE_TIME ,
cast(PRENATAL_PROV_ID as VARCHAR(18) ) as PRENATAL_PROV_ID ,
cast(DELIVER_PROV_ID as VARCHAR(18) ) as DELIVER_PROV_ID ,
cast(HOLD_STATUS_C as DOUBLE ) as HOLD_STATUS_C ,
cast(GEST_AGE_BABY as DOUBLE ) as GEST_AGE_BABY ,
cast(CM_PHY_OWNER_ID as VARCHAR(25) ) as CM_PHY_OWNER_ID ,
cast(CM_LOG_OWNER_ID as VARCHAR(25) ) as CM_LOG_OWNER_ID ,
cast(HOM_CLARITY_FLG_YN as VARCHAR(254) ) as HOM_CLARITY_FLG_YN ,
cast(ACCT_FOLLOWUP_DT as TIMESTAMP ) as ACCT_FOLLOWUP_DT from staging_clarity.HSP_ACCOUNT;
drop view patients.PATIENT;
create view patients.PATIENT AS select
cast(PAT_ID as VARCHAR(18) ) as PAT_ID ,
cast(PAT_NAME as VARCHAR(240) ) as PAT_NAME ,
cast(ADD_LINE_1 as VARCHAR(50) ) as ADD_LINE_1 ,
cast(ADD_LINE_2 as VARCHAR(50) ) as ADD_LINE_2 ,
cast(CITY as VARCHAR(50) ) as CITY ,
cast(STATE_C as VARCHAR(66) ) as STATE_C ,
cast(COUNTY_C as VARCHAR(66) ) as COUNTY_C ,
cast(COUNTRY_C as VARCHAR(66) ) as COUNTRY_C ,
cast(ZIP as VARCHAR(60) ) as ZIP ,
cast(HOME_PHONE as VARCHAR(192) ) as HOME_PHONE ,
cast(WORK_PHONE as VARCHAR(50) ) as WORK_PHONE ,
cast(EMAIL_ADDRESS as VARCHAR(255) ) as EMAIL_ADDRESS ,
cast(RESTRICTED_YN as VARCHAR(1) ) as RESTRICTED_YN ,
cast(PAT_STATUS as VARCHAR(10) ) as PAT_STATUS ,
cast(BIRTH_DATE as TIMESTAMP ) as BIRTH_DATE ,
cast(SEX as VARCHAR(1) ) as SEX ,
cast(ETHNIC_GROUP_C as DOUBLE ) as ETHNIC_GROUP_C ,
cast(MARITAL_STATUS_C as DOUBLE ) as MARITAL_STATUS_C ,
cast(RELIGION_C as DOUBLE ) as RELIGION_C ,
cast(LANGUAGE_C as VARCHAR(66) ) as LANGUAGE_C ,
cast(SSN as VARCHAR(192) ) as SSN ,
cast(REG_DATE as TIMESTAMP ) as REG_DATE ,
cast(REG_STATUS_C as VARCHAR(66) ) as REG_STATUS_C ,
cast(EPICCARE_PAT_YN as VARCHAR(1) ) as EPICCARE_PAT_YN ,
cast(MEDICARE_NUM as VARCHAR(254) ) as MEDICARE_NUM ,
cast(MEDICAID_NUM as VARCHAR(20) ) as MEDICAID_NUM ,
cast(POWER_OF_ATTRNY_YN as VARCHAR(1) ) as POWER_OF_ATTRNY_YN ,
cast(POWER_OF_ATTRNY_DT as TIMESTAMP ) as POWER_OF_ATTRNY_DT ,
cast(ADV_DIRECTIVE_YN as VARCHAR(1) ) as ADV_DIRECTIVE_YN ,
cast(ADV_DIRECTIVE_DATE as TIMESTAMP ) as ADV_DIRECTIVE_DATE ,
cast(DEF_FIN_CLASS_C as VARCHAR(66) ) as DEF_FIN_CLASS_C ,
cast(FIN_STATUS_C as DOUBLE ) as FIN_STATUS_C ,
cast(CUR_PCP_PROV_ID as VARCHAR(18) ) as CUR_PCP_PROV_ID ,
cast(CLAIM_ALERT_C as DOUBLE ) as CLAIM_ALERT_C ,
cast(CUR_PRIM_LOC_ID as DOUBLE ) as CUR_PRIM_LOC_ID ,
cast(LEGAL_STATUS_C as DOUBLE ) as LEGAL_STATUS_C ,
cast(VETERAN_STATUS_C as VARCHAR(66) ) as VETERAN_STATUS_C ,
cast(MOTHER_PAT_ID as VARCHAR(18) ) as MOTHER_PAT_ID ,
cast(FATHER_PAT_ID as VARCHAR(18) ) as FATHER_PAT_ID ,
cast(BIRTH_LOC_ID as DOUBLE ) as BIRTH_LOC_ID ,
cast(BIRTH_STATUS_C as DOUBLE ) as BIRTH_STATUS_C ,
cast(BIRTH_WRIST_BAND as VARCHAR(25) ) as BIRTH_WRIST_BAND ,
cast(PED_BIRTH_LEN as VARCHAR(20) ) as PED_BIRTH_LEN ,
cast(PED_BIRTH_WT as VARCHAR(12) ) as PED_BIRTH_WT ,
cast(PED_APGAR_ONE as VARCHAR(255) ) as PED_APGAR_ONE ,
cast(PED_APGAR_TWO as VARCHAR(255) ) as PED_APGAR_TWO ,
cast(PED_APGAR_TEN as VARCHAR(255) ) as PED_APGAR_TEN ,
cast(PED_COMMENT as VARCHAR(508) ) as PED_COMMENT ,
cast(PED_MULT_BIRTH_ORD as DOUBLE ) as PED_MULT_BIRTH_ORD ,
cast(PED_MULT_BIRTH_TOT as DOUBLE ) as PED_MULT_BIRTH_TOT ,
cast(EPIC_PAT_ID as VARCHAR(18) ) as EPIC_PAT_ID ,
cast(REC_CREATE_DATE as TIMESTAMP ) as REC_CREATE_DATE ,
cast(CREATE_USER_ID as VARCHAR(18) ) as CREATE_USER_ID ,
cast(PRIM_CVG_ID as DOUBLE ) as PRIM_CVG_ID ,
cast(PRIM_EPM_ID as DOUBLE ) as PRIM_EPM_ID ,
cast(PRIM_EPP_ID as DOUBLE ) as PRIM_EPP_ID ,
cast(PRIM_FC as VARCHAR(66) ) as PRIM_FC ,
cast(PERM_CHART_LOC_C as DOUBLE ) as PERM_CHART_LOC_C ,
cast(CUR_CHART_LOC_C as DOUBLE ) as CUR_CHART_LOC_C ,
cast(PAT_MRN_ID as VARCHAR(102) ) as PAT_MRN_ID ,
cast(DEATH_DATE as TIMESTAMP ) as DEATH_DATE ,
cast(UPDATE_DATE as TIMESTAMP ) as UPDATE_DATE ,
cast(EOB_FLAG_YN as VARCHAR(1) ) as EOB_FLAG_YN ,
cast(REC_CREATE_PAT_ID as VARCHAR(18) ) as REC_CREATE_PAT_ID ,
cast(ORGAN_DONOR_YN as VARCHAR(1) ) as ORGAN_DONOR_YN ,
cast(TMP_ADDR_LINE_1 as VARCHAR(50) ) as TMP_ADDR_LINE_1 ,
cast(TMP_ADDR_LINE_2 as VARCHAR(50) ) as TMP_ADDR_LINE_2 ,
cast(TMP_CITY as VARCHAR(40) ) as TMP_CITY ,
cast(TMP_STATE_C as VARCHAR(66) ) as TMP_STATE_C ,
cast(TMP_COUNTRY_C as VARCHAR(66) ) as TMP_COUNTRY_C ,
cast(TMP_ZIP as VARCHAR(50) ) as TMP_ZIP ,
cast(TMP_HOME_PHONE as VARCHAR(50) ) as TMP_HOME_PHONE ,
cast(TMP_COUNTY_C as VARCHAR(66) ) as TMP_COUNTY_C ,
cast(TMP_ADDR_START_DT as TIMESTAMP ) as TMP_ADDR_START_DT ,
cast(TMP_ADDR_END_DT as TIMESTAMP ) as TMP_ADDR_END_DT ,
cast(TMP_CARE_OF_PERSON as VARCHAR(100) ) as TMP_CARE_OF_PERSON ,
cast(IS_MAIL_BLOCKED_YN as VARCHAR(1) ) as IS_MAIL_BLOCKED_YN ,
cast(IS_PHONE_REMNDR_YN as VARCHAR(1) ) as IS_PHONE_REMNDR_YN ,
cast(CASE_SPVSR_USER_ID as VARCHAR(18) ) as CASE_SPVSR_USER_ID ,
cast(PAT_LAST_NAME as VARCHAR(240) ) as PAT_LAST_NAME ,
cast(PAT_FIRST_NAME as VARCHAR(240) ) as PAT_FIRST_NAME ,
cast(PAT_MIDDLE_NAME as VARCHAR(508) ) as PAT_MIDDLE_NAME ,
cast(PAT_TITLE_C as DOUBLE ) as PAT_TITLE_C ,
cast(PAT_NAME_SUFFIX_C as DOUBLE ) as PAT_NAME_SUFFIX_C ,
cast(SPECIAL_STATUS_C as DOUBLE ) as SPECIAL_STATUS_C ,
cast(LANG_CARE_C as VARCHAR(66) ) as LANG_CARE_C ,
cast(LANG_WRIT_C as VARCHAR(66) ) as LANG_WRIT_C ,
cast(PROXY_PAT_YN as VARCHAR(1) ) as PROXY_PAT_YN ,
cast(PROXY_NAME as VARCHAR(50) ) as PROXY_NAME ,
cast(PROXY_PHONE as VARCHAR(50) ) as PROXY_PHONE ,
cast(PROXY_PACK_YN as VARCHAR(1) ) as PROXY_PACK_YN ,
cast(EMPLOYER_ID as VARCHAR(254) ) as EMPLOYER_ID ,
cast(EMPY_STATUS_C as DOUBLE ) as EMPY_STATUS_C ,
cast(CM_PHY_OWNER_ID as VARCHAR(25) ) as CM_PHY_OWNER_ID ,
cast(CM_LOG_OWNER_ID as VARCHAR(25) ) as CM_LOG_OWNER_ID ,
cast(ALRGY_UPD_DATE as TIMESTAMP ) as ALRGY_UPD_DATE ,
cast(ALRGY_UPD_USER_ID as VARCHAR(18) ) as ALRGY_UPD_USER_ID ,
cast(GUARDIAN_NAME as VARCHAR(254) ) as GUARDIAN_NAME ,
cast(PREF_CLIN_ZIP as VARCHAR(254) ) as PREF_CLIN_ZIP ,
cast(PREF_PCP_SEX_C as VARCHAR(66) ) as PREF_PCP_SEX_C ,
cast(PREF_PCP_SPEC_C as VARCHAR(66) ) as PREF_PCP_SPEC_C ,
cast(PREF_PCP_LANG_C as VARCHAR(66) ) as PREF_PCP_LANG_C ,
cast(COUNTRY_OF_ORIG_C as VARCHAR(66) ) as COUNTRY_OF_ORIG_C ,
cast(COMM_PREF_LTR_C as DOUBLE ) as COMM_PREF_LTR_C ,
cast(PED_BIRTH_HEAD_CIR as VARCHAR(254) ) as PED_BIRTH_HEAD_CIR ,
cast(PED_DISCHRG_WT as VARCHAR(254) ) as PED_DISCHRG_WT ,
cast(PED_CESAREAN_YN as VARCHAR(1) ) as PED_CESAREAN_YN ,
cast(PED_GEST_AGE as VARCHAR(254) ) as PED_GEST_AGE ,
cast(PED_NOUR_METH_C as DOUBLE ) as PED_NOUR_METH_C ,
cast(PED_DELIVR_METH_C as VARCHAR(66) ) as PED_DELIVR_METH_C ,
cast(PED_MULTI_BIRTH_YN as VARCHAR(1) ) as PED_MULTI_BIRTH_YN ,
cast(EDD_DT as TIMESTAMP ) as EDD_DT ,
cast(EDD_ENTERED_DT as TIMESTAMP ) as EDD_ENTERED_DT ,
cast(EDD_CMT as VARCHAR(1000) ) as EDD_CMT ,
cast(INTRPTR_NEEDED_YN as VARCHAR(1) ) as INTRPTR_NEEDED_YN ,
cast(PCP_DON_CHART_YN as VARCHAR(1) ) as PCP_DON_CHART_YN ,
cast(PAT_HAS_IOL_YN as VARCHAR(1) ) as PAT_HAS_IOL_YN ,
cast(SPECIES_C as VARCHAR(66) ) as SPECIES_C ,
cast(PED_BIRTH_LABOR as VARCHAR(254) ) as PED_BIRTH_LABOR ,
cast(PED_HOSP_DAYS as VARCHAR(254) ) as PED_HOSP_DAYS ,
cast(PED_HOSP_NAME as VARCHAR(254) ) as PED_HOSP_NAME ,
cast(PED_HOSP_LOCATION as VARCHAR(254) ) as PED_HOSP_LOCATION ,
cast(MEDS_LAST_REV_TM as TIMESTAMP ) as MEDS_LAST_REV_TM ,
cast(MEDS_LST_REV_USR_ID as VARCHAR(18) ) as MEDS_LST_REV_USR_ID ,
cast(SELF_VERIF_DATE as TIMESTAMP ) as SELF_VERIF_DATE ,
cast(SELF_VERIF_STATU_YN as VARCHAR(1) ) as SELF_VERIF_STATU_YN ,
cast(SELF_EC_VERIF_DATE as TIMESTAMP ) as SELF_EC_VERIF_DATE ,
cast(SELF_EC_VERIF_ST_YN as VARCHAR(1) ) as SELF_EC_VERIF_ST_YN ,
cast(LAST_MYC_ASKED_DATE as TIMESTAMP ) as LAST_MYC_ASKED_DATE ,
cast(EMPR_ID_CMT as VARCHAR(254) ) as EMPR_ID_CMT ,
cast(HOUSE_NUM as VARCHAR(254) ) as HOUSE_NUM ,
cast(DISTRICT_C as DOUBLE ) as DISTRICT_C ,
cast(PAT_STATUS_C as VARCHAR(66) ) as PAT_STATUS_C ,
cast(MEDS_LAST_REV_CSN as DOUBLE ) as MEDS_LAST_REV_CSN ,
cast(SEX_C as VARCHAR(66) ) as SEX_C ,
cast(RECORD_STATE_C as DOUBLE ) as RECORD_STATE_C from staging_clarity.PATIENT;
//Changed FLOAT(126) to DOUBLE for PAT_ENC_DATE_REAL
drop view encounters.PAT_ENC_HSP;
create view encounters.PAT_ENC_HSP AS select
cast(PAT_ID as VARCHAR(18) ) as PAT_ID ,
cast(PAT_ENC_DATE_REAL as DOUBLE ) as PAT_ENC_DATE_REAL ,
cast(PAT_ENC_CSN_ID as DOUBLE ) as PAT_ENC_CSN_ID ,
cast(ADT_CONTACT as DOUBLE ) as ADT_CONTACT ,
cast(ADT_INITIAL as VARCHAR(12) ) as ADT_INITIAL ,
cast(ADT_PAT_CLASS_C as VARCHAR(66) ) as ADT_PAT_CLASS_C ,
cast(ADT_BILLING_TYPE_C as DOUBLE ) as ADT_BILLING_TYPE_C ,
cast(ADT_PATIENT_STAT_C as DOUBLE ) as ADT_PATIENT_STAT_C ,
cast(LEVEL_OF_CARE_C as VARCHAR(66) ) as LEVEL_OF_CARE_C ,
cast(PENDING_DISCH_TIME as TIMESTAMP ) as PENDING_DISCH_TIME ,
cast(DISCH_CODE_C as DOUBLE ) as DISCH_CODE_C ,
cast(ADT_ATHCRT_STAT_C as DOUBLE ) as ADT_ATHCRT_STAT_C ,
cast(ADT_LAST_RVW_DT as TIMESTAMP ) as ADT_LAST_RVW_DT ,
cast(ADT_NEXT_RVW_DT as TIMESTAMP ) as ADT_NEXT_RVW_DT ,
cast(PREADM_UNDO_RSN_C as DOUBLE ) as PREADM_UNDO_RSN_C ,
cast(EXP_ADMISSION_TIME as TIMESTAMP ) as EXP_ADMISSION_TIME ,
cast(EXP_LEN_OF_STAY as DOUBLE ) as EXP_LEN_OF_STAY ,
cast(EXP_DISCHARGE_DATE as TIMESTAMP ) as EXP_DISCHARGE_DATE ,
cast(ADMIT_CATEGORY_C as DOUBLE ) as ADMIT_CATEGORY_C ,
cast(ADMIT_SOURCE_C as VARCHAR(254) ) as ADMIT_SOURCE_C ,
cast(TYPE_OF_ROOM_C as VARCHAR(66) ) as TYPE_OF_ROOM_C ,