please help me in fixing this error

Database
N/A

please help me in fixing this error

Hi,

I am facing the syntax error: expected something between the word 'create_ts' and ')' while trying to run the below piece of code

select distinct  a.cm15 , a.create_ts , case when  a.count_ip > 1 then cast ( a.count_ip as char ( 1 )) else trim ( b.ip_addr ) end as    ip_addr

     from    (

          select    a.cm15 , a.create_ts , count ( a.ip_addr ) as  count_ip

          from    (

               select distinct  a.cm15 , ( b.create_ts (format 'yyyy-mm-dd' )(char(20))) as  create_ts  , b.ip_addr

               from    myca_enroll   a

                join   myca_logon_activity b

                     on b.user_private_guid = a.user_private_guid

               join vt_cm_remittance c on a.cm11=c.cm11

               ) a

         group by   a.cm15 , a.create_ts 

          ) a

      join   (

          select distinct  a.cm15 , ( b.create_ts (format 'yyyy-mm-dd' )(char(20))) as  create_ts  , b.ip_addr

          from    myca_enroll   a

           join   myca_logon_activity b

                on b.user_private_guid = a.user_private_guid

          join vt_cm_remittance c on a.cm11=c.cm11

          ) b

           on b.cm15 = a.cm15

           and b.create_ts = a.create_ts 

     )     d

      on d.cm15 = a.cm15

      and d.create_ts = ( a.pay_cptr_dt (format 'yyyy-mm-dd' )(char(20)))

 join   (

     select    rec_src_cd , cm15 , pay_cptr_dt , sum ( amt ) as  sum_amt

     from    vt_cm_remittance

     group by   rec_src_cd , cm15 , pay_cptr_dt

          )     e

      on e.cm15 = a.cm15

      and e.pay_cptr_dt = a.pay_cptr_dt

      and e.rec_src_cd = a.rec_src_cd

Can someone help me to figure out what am I missing here?

Thanks in advance,

ggraju

1 REPLY
Junior Contributor

Re: please help me in fixing this error

The round brackets are not balanced:

           and b.create_ts = a.create_ts 
) d

There's no opening bracket/Select for d

And there's no need to cast create_ts as a char, cast(create_ts as date) also strips off the time portion