Extracting a table from SQL causes this error to appear: ERROR 180-322: Statement is not valid or it is used out of proper order

1
votes

Can you help me to find out where is the error in my code?

I'm trying to extract a data base from Oracle to SAS but every time I run this code, it doesn't appear. The output appears this error message:

ERROR 180-322: Statement is not valid or it is used out of proper order.

The log output is:

1          ;*';
            _
            180

But it doesn't make sense because I deleted all these characters, even on the comments. I have no idea what to do now.

The code is the following:

%include "/sasusers/&sysuserid/gedspla.sas";
libname Gui "/lidata_br/atuaria/dmatuarial/vida/GKK";

%let Data_cotacao = ('201804','201805','201806','201807','201808','201809',
                     '201810','201811','201812','2011901','201902','201903',
                     '201904  ','201905','201906','201907','201908','201909',
                     '201910','201911','201912','202001','202002','202003');



proc sql ;
connect to oracle(user=&d_usuario pass=&pass_dtm path='@dtm' preserve_comments);
    create table GKK.cotaVG (compress=yes reuse=yes) as
    select * from connection to oracle                              
(
select distinct coalesce(pa11.DHCT_NUM_CTC, pa12.DHCT_NUM_CTC)  DHCT_NUM_CTC,
              coalesce(pa11.DTON_COD_ATF, pa12.DTON_COD_ATF)  DTON_COD_ATF,
              a121.DTON_DES  DTON_DES,
              coalesce(pa11.DDTC_COD_ATF, pa12.DDTC_COD_ATF)  DDTC_COD_ATF,
              a120.DDTC_DES  DDTC_DES,
              coalesce(pa11.DDSC_COD_ATF, pa12.DDSC_COD_ATF)  DDSC_COD_ATF,
              a117.DDSC_DES  DDSC_DES,
              coalesce(pa11.DSDC_COD_ATF, pa12.DSDC_COD_ATF)  DSDC_COD_ATF,
              a118.DSDC_DES  DSDC_DES,
              coalesce(pa11.DHCT_NUM_PRA, pa12.DHCT_NUM_PRA)  DHCT_NUM_PRA,
              coalesce(pa11.DHCT_TSPR_COD, pa12.DHCT_TSPR_COD)  DHCT_TSPR_COD,
              a119.TSPR_DES  DHCT_TSPR_DES,
              coalesce(pa11.DCSN_COD_ATF, pa12.DCSN_COD_ATF)  DCSN_COD_ATF,
              a115.DCSN_COD_SOG  DCSN_COD_SOG,
              coalesce(pa11.DHCT_IND_ROB, pa12.DHCT_IND_ROB)  DHCT_IND_ROB,
              coalesce(pa11.DHCT_IND_ULT_CTC, pa12.DHCT_IND_ULT_CTC)  DHCT_IND_ULT_CTC,
              coalesce(pa11.DMID_COD_ATF, pa12.DMID_COD_ATF)  DMID_COD_ATF,
              a114.DMID_DES  DMID_DES,
              coalesce(pa11.DSIT_COD_ATF, pa12.DSIT_COD_ATF)  DSIT_COD_ATF,
              a17.DSIT_DES  DSIT_DES,
              coalesce(pa11.DMBE_APO_NUM, pa12.DMBE_APO_NUM)  DMBE_APO_NUM,
              coalesce(pa11.DHCT_CTRS_COD, pa12.DHCT_CTRS_COD)  DHCT_CTRS_COD,
              coalesce(pa11.DCEP_COD_ATF, pa12.DCEP_COD_ATF)  DCEP_COD_ATF,
              a16.DCEP_COD_CEP_REZ  DCEP_COD_CEP_REZ,
              coalesce(pa11.DCLD_COD_ATF, pa12.DCLD_COD_ATF)  DCLD_COD_ATF,
              a113.DCLD_DAT  DCLD_DAT,
              coalesce(pa11.DCLD_COD_ATF0, pa12.DCLD_COD_ATF0)  DCLD_COD_ATF0,
              a112.DCLD_DAT  DCLD_DAT0,
              coalesce(pa11.DCLD_COD_ATF1, pa12.DCLD_COD_ATF1)  DCLD_COD_ATF1,
              a111.DCLD_DAT  DCLD_DAT1,
              coalesce(pa11.DCLD_COD_ATF2, pa12.DCLD_COD_ATF2)  DCLD_COD_ATF2,
              a110.DCLD_DAT  DCLD_DAT2,
              coalesce(pa11.DCLD_COD_ATF3, pa12.DCLD_COD_ATF3)  DCLD_COD_ATF3,
              a19.DCLD_DAT  DCLD_DAT3,
              coalesce(pa11.DHCT_DCLD_DAT_CTC, pa12.DHCT_DCLD_DAT_CTC)  DHCT_DCLD_DAT_CTC,
              a14.DCLD_DAT  DCLD_DAT4,
              coalesce(pa11.DCLD_COD_ATF4, pa12.DCLD_COD_ATF4)  DCLD_COD_ATF4,
              a18.DCLD_DAT  DCLD_DAT5,
              coalesce(pa11.DPRD_COD_PRD, pa12.DPRD_COD_PRD)  DPRD_COD_PRD,
              a116.DPRD_NOM  DPRD_NOM,
              a15.DFFL_DFRG_COD_ATF  DFRG_COD_ATF,
              a123.DFRG_DES  DFRG_DES,
              a14.DCLD_DMAN_COD_ATF  DMAN_COD_ATF,
              a122.DMAN_DES  DMAN_DES,
              coalesce(pa11.DFFL_COD_ATF, pa12.DFFL_COD_ATF)  DFFL_COD_ATF,
              a15.DFFL_DES  DFFL_DES,
              pa11.WJXBFS1  WJXBFS1,
              pa12.WJXBFS1  WJXBFS2,
              pa11.WJXBFS2  WJXBFS3
from     (select    a11.DCTI_DTON_COD_ATF  DTON_COD_ATF,
              a11.DCTI_DDTC_COD_ATF  DDTC_COD_ATF,
              a12.DHCT_TSPR_COD  DHCT_TSPR_COD,
              a11.DCTI_DSDC_COD_ATF  DSDC_COD_ATF,
              a11.DCTI_DDSC_COD_ATF  DDSC_COD_ATF,
              a12.DHCT_IND_ROB  DHCT_IND_ROB,
              a12.DHCT_NUM_PRA  DHCT_NUM_PRA,
              a11.DCTI_DPRD_COD_PRD  DPRD_COD_PRD,
              a12.DHCT_NUM_CTC  DHCT_NUM_CTC,
              a12.DHCT_CTRS_COD  DHCT_CTRS_COD,
              a12.DHCT_DCSN_MCL  DCSN_COD_ATF,
              a12.DHCT_IND_ULT_CTC  DHCT_IND_ULT_CTC,
              a11.DCTI_DMID_COD_ATF  DMID_COD_ATF,
              a17.DFCL_DFFL_COD_ATF  DFFL_COD_ATF,
              a11.DCTI_DCLD_RFR_COD_ATF  DCLD_COD_ATF,
              a12.DHCT_DCLD_DAT_PRA  DCLD_COD_ATF0,
              a12.DHCT_DCLD_PRI_RCU  DCLD_COD_ATF1,
              a12.DHCT_DCLD_DAT_CTC  DHCT_DCLD_DAT_CTC,
              a11.DCTI_DCLD_INI_VGC_COD_ATF  DCLD_COD_ATF2,
              a11.DCTI_DCLD_FIM_VGC_COD_ATF  DCLD_COD_ATF3,
              a11.DCTI_DCLD_CRC_COD_ATF  DCLD_COD_ATF4,
              a12.DHCT_DSIT_COD_ATF  DSIT_COD_ATF,
              a11.DCTI_DCEP_COD_ATF  DCEP_COD_ATF,
              a12.DHCT_NUM_APO  DMBE_APO_NUM,
              sum(a11.DCTI_QTD_ITM)  WJXBFS1,
              sum(a11.DCTI_MRI_NGC_CTC)  WJXBFS2
from     DMD_ITM_CTC a11
              join       DMD_HIS_CTC a12
                on        (a11.DCTI_DHCT_COD_ATF = a12.DHCT_COD_ATF)
              join       DMD_HIS_CTC a13
                on        (a11.DCTI_DHCT_COD_ATF = a13.DHCT_COD_ATF and 
                          a12.DHCT_DCLD_DAT_CTC = a13.DHCT_DCLD_DAT_CTC and 
                          a12.DHCT_IND_ROB = a13.DHCT_IND_ROB)
              join       DMF_PRD          a14
                on        (a11.DCTI_DFPR_COD_ATF = a14.DFPR_COD_ATF)
              join       DMF_AGU_PRD              a15
                on        (a14.DFPR_DFAP_COD_ATF = a15.DFAP_COD_ATF)
              join       DMC_CRT          a16
                on        (a12.DHCT_DCRT_COD_CLI_OPN_CRT = a16.DCRT_COD_CLI_OPN_CRT)
              join       DMF_CEL           a17
                on        (a16.DCRT_DFCL_COD_ATF = a17.DFCL_COD_ATF)
where   (((a15.DFAP_DFCP_COD_ATF)
in          (select  c21.DFCP_COD_ATF
              from     DMF_CAT_PRD c21
              where   c21.DFCP_DFSP_COD_ATF in (2426, 2430, 7781, 2427)))
and TO_NUMBER(TO_CHAR(TO_DATE(a12.DHCT_DCLD_DAT_CTC, 'YYYYMMDD'), 'YYYYMM')) in (&Data_cotacao)
and a11.DCTI_DPRD_COD_PRD in ('93030', '93028', '77021', '77022', '77023', '77024', '77025', '77033', '77034', '77035', '77036', '77037', '77038', '77039', '77040', '77041', '77042')
and a12.DHCT_IND_ULT_CTC in ('S')
and a12.DHCT_DCRT_COD_CLI_OPN_CRT not in (99040067))
group by            a11.DCTI_DTON_COD_ATF,
              a11.DCTI_DDTC_COD_ATF,
              a12.DHCT_TSPR_COD,
              a11.DCTI_DSDC_COD_ATF,
              a11.DCTI_DDSC_COD_ATF,
              a12.DHCT_IND_ROB,
              a12.DHCT_NUM_PRA,
              a11.DCTI_DPRD_COD_PRD,
              a12.DHCT_NUM_CTC,
              a12.DHCT_CTRS_COD,
              a12.DHCT_DCSN_MCL,
              a12.DHCT_IND_ULT_CTC,
              a11.DCTI_DMID_COD_ATF,
              a17.DFCL_DFFL_COD_ATF,
              a11.DCTI_DCLD_RFR_COD_ATF,
              a12.DHCT_DCLD_DAT_PRA,
              a12.DHCT_DCLD_PRI_RCU,
              a12.DHCT_DCLD_DAT_CTC,
              a11.DCTI_DCLD_INI_VGC_COD_ATF,
              a11.DCTI_DCLD_FIM_VGC_COD_ATF,
              a11.DCTI_DCLD_CRC_COD_ATF,
              a12.DHCT_DSIT_COD_ATF,
              a11.DCTI_DCEP_COD_ATF,
              a12.DHCT_NUM_APO )     pa11
              full outer join    (select    a11.DCTI_DTON_COD_ATF  DTON_COD_ATF,
              a11.DCTI_DDTC_COD_ATF  DDTC_COD_ATF,
              a12.DHCT_TSPR_COD  DHCT_TSPR_COD,
              a11.DCTI_DSDC_COD_ATF  DSDC_COD_ATF,
              a11.DCTI_DDSC_COD_ATF  DDSC_COD_ATF,
              a12.DHCT_IND_ROB  DHCT_IND_ROB,
              a12.DHCT_NUM_PRA  DHCT_NUM_PRA,
              a11.DCTI_DPRD_COD_PRD  DPRD_COD_PRD,
              a12.DHCT_NUM_CTC  DHCT_NUM_CTC,
              a12.DHCT_CTRS_COD  DHCT_CTRS_COD,
              a12.DHCT_DCSN_MCL  DCSN_COD_ATF,
              a12.DHCT_IND_ULT_CTC  DHCT_IND_ULT_CTC,
              a11.DCTI_DMID_COD_ATF  DMID_COD_ATF,
              a17.DFCL_DFFL_COD_ATF  DFFL_COD_ATF,
              a11.DCTI_DCLD_RFR_COD_ATF  DCLD_COD_ATF,
              a12.DHCT_DCLD_DAT_PRA  DCLD_COD_ATF0,
              a12.DHCT_DCLD_PRI_RCU  DCLD_COD_ATF1,
              a12.DHCT_DCLD_DAT_CTC  DHCT_DCLD_DAT_CTC,
              a11.DCTI_DCLD_INI_VGC_COD_ATF  DCLD_COD_ATF2,
              a11.DCTI_DCLD_FIM_VGC_COD_ATF  DCLD_COD_ATF3,
              a11.DCTI_DCLD_CRC_COD_ATF  DCLD_COD_ATF4,
              a12.DHCT_DSIT_COD_ATF  DSIT_COD_ATF,
              a11.DCTI_DCEP_COD_ATF  DCEP_COD_ATF,
              a12.DHCT_NUM_APO  DMBE_APO_NUM,
              sum(a11.DCTI_QTD_ITM)  WJXBFS1
from     DMD_ITM_CTC a11
              join       DMD_HIS_CTC a12
                on        (a11.DCTI_DHCT_COD_ATF = a12.DHCT_COD_ATF)
              join       DMD_HIS_CTC a13
                on        (a11.DCTI_DHCT_COD_ATF = a13.DHCT_COD_ATF and 
              a12.DHCT_DCLD_DAT_CTC = a13.DHCT_DCLD_DAT_CTC and 
              a12.DHCT_IND_ROB = a13.DHCT_IND_ROB)
              join       DMF_PRD          a14
                on        (a11.DCTI_DFPR_COD_ATF = a14.DFPR_COD_ATF)
              join       DMF_AGU_PRD              a15
                on        (a14.DFPR_DFAP_COD_ATF = a15.DFAP_COD_ATF)
              join       DMC_CRT          a16
                on        (a12.DHCT_DCRT_COD_CLI_OPN_CRT = a16.DCRT_COD_CLI_OPN_CRT)
              join       DMF_CEL           a17
                on        (a16.DCRT_DFCL_COD_ATF = a17.DFCL_COD_ATF)
where   (((a15.DFAP_DFCP_COD_ATF)
in          (select  c21.DFCP_COD_ATF
             from     DMF_CAT_PRD c21
              where   c21.DFCP_DFSP_COD_ATF in (2426, 2430, 7781, 2427)))
and TO_NUMBER(TO_CHAR(TO_DATE(a12.DHCT_DCLD_DAT_CTC, 'YYYYMMDD'), 'YYYYMM')) in (&Data_cotacao)
and a11.DCTI_DPRD_COD_PRD in ('93030', '93028', '77021', '77022', '77023', '77024', '77025', '77033', '77034', '77035', '77036', '77037', '77038', '77039', '77040', '77041', '77042')
and a12.DHCT_IND_ULT_CTC in ('S')
and a12.DHCT_DCRT_COD_CLI_OPN_CRT not in (99040067)
and a11.DCTI_DDSC_COD_ATF in (3))
group by            a11.DCTI_DTON_COD_ATF,
              a11.DCTI_DDTC_COD_ATF,
              a12.DHCT_TSPR_COD,
              a11.DCTI_DSDC_COD_ATF,
              a11.DCTI_DDSC_COD_ATF,
              a12.DHCT_IND_ROB,
              a12.DHCT_NUM_PRA,
              a11.DCTI_DPRD_COD_PRD,
              a12.DHCT_NUM_CTC,
              a12.DHCT_CTRS_COD,
              a12.DHCT_DCSN_MCL,
              a12.DHCT_IND_ULT_CTC,
              a11.DCTI_DMID_COD_ATF,
              a17.DFCL_DFFL_COD_ATF,
              a11.DCTI_DCLD_RFR_COD_ATF,
              a12.DHCT_DCLD_DAT_PRA,
              a12.DHCT_DCLD_PRI_RCU,
              a12.DHCT_DCLD_DAT_CTC,
              a11.DCTI_DCLD_INI_VGC_COD_ATF,
              a11.DCTI_DCLD_FIM_VGC_COD_ATF,
              a11.DCTI_DCLD_CRC_COD_ATF,
              a12.DHCT_DSIT_COD_ATF,
              a11.DCTI_DCEP_COD_ATF,
              a12.DHCT_NUM_APO )   pa12
                on        (pa11.DCEP_COD_ATF = pa12.DCEP_COD_ATF and 
              pa11.DCLD_COD_ATF = pa12.DCLD_COD_ATF and 
              pa11.DCLD_COD_ATF0 = pa12.DCLD_COD_ATF0 and 
              pa11.DCLD_COD_ATF1 = pa12.DCLD_COD_ATF1 and 
              pa11.DCLD_COD_ATF2 = pa12.DCLD_COD_ATF2 and 
              pa11.DCLD_COD_ATF3 = pa12.DCLD_COD_ATF3 and 
              pa11.DCLD_COD_ATF4 = pa12.DCLD_COD_ATF4 and 
              pa11.DCSN_COD_ATF = pa12.DCSN_COD_ATF and 
              pa11.DDSC_COD_ATF = pa12.DDSC_COD_ATF and 
              pa11.DDTC_COD_ATF = pa12.DDTC_COD_ATF and 
              pa11.DFFL_COD_ATF = pa12.DFFL_COD_ATF and 
              pa11.DHCT_CTRS_COD = pa12.DHCT_CTRS_COD and 
              pa11.DHCT_DCLD_DAT_CTC = pa12.DHCT_DCLD_DAT_CTC and 
              pa11.DHCT_IND_ROB = pa12.DHCT_IND_ROB and 
              pa11.DHCT_IND_ULT_CTC = pa12.DHCT_IND_ULT_CTC and 
              pa11.DHCT_NUM_CTC = pa12.DHCT_NUM_CTC and 
              pa11.DHCT_NUM_PRA = pa12.DHCT_NUM_PRA and 
              pa11.DHCT_TSPR_COD = pa12.DHCT_TSPR_COD and 
              pa11.DMBE_APO_NUM = pa12.DMBE_APO_NUM and 
              pa11.DMID_COD_ATF = pa12.DMID_COD_ATF and 
              pa11.DPRD_COD_PRD = pa12.DPRD_COD_PRD and 
              pa11.DSDC_COD_ATF = pa12.DSDC_COD_ATF and 
              pa11.DSIT_COD_ATF = pa12.DSIT_COD_ATF and 
              pa11.DTON_COD_ATF = pa12.DTON_COD_ATF)
              join       DMC_CLD          a14
                on        (coalesce(pa11.DHCT_DCLD_DAT_CTC, pa12.DHCT_DCLD_DAT_CTC) = a14.DCLD_COD_ATF)
              join       DMF_FIL            a15
                on        (coalesce(pa11.DFFL_COD_ATF, pa12.DFFL_COD_ATF) = a15.DFFL_COD_ATF)
              join       DMM_CEP         a16
                on        (coalesce(pa11.DCEP_COD_ATF, pa12.DCEP_COD_ATF) = a16.DCEP_COD_ATF)
              join       DMD_STM_ORI a17
                on        (coalesce(pa11.DSIT_COD_ATF, pa12.DSIT_COD_ATF) = a17.DSIT_COD_ATF)
              join       DMC_CLD          a18
                on        (coalesce(pa11.DCLD_COD_ATF4, pa12.DCLD_COD_ATF4) = a18.DCLD_COD_ATF)
              join       DMC_CLD          a19
                on        (coalesce(pa11.DCLD_COD_ATF3, pa12.DCLD_COD_ATF3) = a19.DCLD_COD_ATF)
              join       DMC_CLD          a110
                on        (coalesce(pa11.DCLD_COD_ATF2, pa12.DCLD_COD_ATF2) = a110.DCLD_COD_ATF)
              join       DMC_CLD          a111
                on        (coalesce(pa11.DCLD_COD_ATF1, pa12.DCLD_COD_ATF1) = a111.DCLD_COD_ATF)
              join       DMC_CLD          a112
                on        (coalesce(pa11.DCLD_COD_ATF0, pa12.DCLD_COD_ATF0) = a112.DCLD_COD_ATF)
              join       DMC_CLD          a113
                on        (coalesce(pa11.DCLD_COD_ATF, pa12.DCLD_COD_ATF) = a113.DCLD_COD_ATF)
              join       DMM_IDD         a114
                on        (coalesce(pa11.DMID_COD_ATF, pa12.DMID_COD_ATF) = a114.DMID_COD_ATF)
              join       DMC_SIM_NAO              a115
                on        (coalesce(pa11.DCSN_COD_ATF, pa12.DCSN_COD_ATF) = a115.DCSN_COD_ATF)
              join       DMC_PRD          a116
                on        (coalesce(pa11.DPRD_COD_PRD, pa12.DPRD_COD_PRD) = a116.DPRD_COD_PRD)
              join       DMD_STS_CTC a117
                on        (coalesce(pa11.DDSC_COD_ATF, pa12.DDSC_COD_ATF) = a117.DDSC_COD_ATF)
              join       DMD_STS_DTL_CTC      a118
                on        (coalesce(pa11.DSDC_COD_ATF, pa12.DSDC_COD_ATF) = a118.DSDC_COD_ATF)
              join       (SELECT distinct DHCT_TSPR_COD TSPR_COD,
                                DHCT_TSPR_DES TSPR_DES
                                FROM DMD_HIS_CTC)  a119
                on        (coalesce(pa11.DHCT_TSPR_COD, pa12.DHCT_TSPR_COD) = a119.TSPR_COD)
              join       DMD_TIP_CTC  a120
                on        (coalesce(pa11.DDTC_COD_ATF, pa12.DDTC_COD_ATF) = a120.DDTC_COD_ATF)
              join       DMC_TIP_ORI_NGC       a121
                on        (coalesce(pa11.DTON_COD_ATF, pa12.DTON_COD_ATF) = a121.DTON_COD_ATF)
              join       DMC_MES_ANO             a122
                on        (a14.DCLD_DMAN_COD_ATF = a122.DMAN_COD_ATF)
              join       DMF_RGI           a123
                on        (a15.DFFL_DFRG_COD_ATF = a123.DFRG_COD_ATF)
;
disconnect from oracle;
run;
2
sqloraclesas
IIRC, you should end proc sql with quit;, not with run; - wildplasser
Post the log, where the error occurs usually helps to figure out the issue. - Reeza
Debugging mismatched quotes can be a pain. Start a new SAS session and try again. - Tom
@wildplasser thanks - Guilherme Kenji Kanashiro
@Reeza here it is: 1 ;*'; _ 180 ERROR 180-322: Statement is not valid or it is used out of proper order. 1 *"; _ 180 ERROR 180-322: Statement is not valid or it is used out of proper order. 1 */; __ 180 ERROR 180-322: Statement is not valid or it is used out of proper order. - Guilherme Kenji Kanashiro

2 Answers

1
votes

In your long query, you are attempting to bind a SAS macro variable to query:

and TO_NUMBER(TO_CHAR(TO_DATE(a12.DHCT_DCLD_DAT_CTC, 'YYYYMMDD'), 'YYYYMM')) in (&Data_cotacao)

However, you are double wrapping the parentheses and comparing a number (result of TO_NUMBER) to list of single quoted characters. Consequently, this interpolation of SAS variable and SQL statement would cause syntax errors with the connecting Oracle database engine.

Consider slightly adjusting by comparing character to character without repetitive wrapping parentheses:

and TO_CHAR(TO_DATE(a12.DHCT_DCLD_DAT_CTC, 'YYYYMMDD'), 'YYYYMM') in &Data_cotacao.

By the way there is room to cut down your very long query since the FULL JOIN derived tables are essentially identical with a difference of one sum aggregate (in first) and one where condition (in second). Specifically, consider using a common table expression (CTE) via the WITH syntax to hold one version of the subquery and re-use main outer query with an added JOIN condition:

proc sql ;
connect to oracle(user=&d_usuario pass=&pass_dtm path='@dtm' preserve_comments);
    create table GKK.cotaVG (compress=yes reuse=yes) as
    select * from connection to oracle                              
(
    WITH cte AS
      (<USE ENTIRE pal11 SUBQUERY>)

    SELECT <SAME COLUMNS AND COALESCE EXPRESSIONS>
    FROM cte AS pa11
    FULL OUTER JOIN cte AS pa12
      <SAME ON/AND CLAUSES WITH COALESCE EXPRESSIONS>
      AND pal12.DDSC_COD_ATF IN (3)                     

);
disconnect from oracle;
run;

Note: CTEs are only available in pass-through query with Oracle and not available in a SAS only proc sql;

0
votes

As an answer:

You should end proc sql with quit; , not with run;