All Forums Database
ggraju 1 post Joined 11/14
06 Nov 2014
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
 
 

dnoeth 4628 posts Joined 11/04
07 Nov 2014

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

Dieter

You must sign in to leave a comment.