Re: SQL expression not ended, perhaps missing &name

[Denise White <dewhite@VICR.COM>]

Hi Kim,

As someone else mentioned (wording it a little differently), within a
BEGIN-SELECT, you need to indent all SQR code.  In other words, you should begin
the columns being selected, the FROM clause, and I think the WHERE clause in the
first column of your program, but all the code between the columns and the FROM
should be indented.  This is definitely the problem you are having, as the two
messages indicate the two BEGIN-SELECTs where the SQR code is not indented.

I didn't closely examine all your code, but I also see another problem.  In the
Select-Prod-Client procedure (after first indenting that if statement!), 'if
pc.gl_pay_type' should be 'if &pc.gl_pay_type'.  You need the ampersand (&) to
reference the selected column.  Actually, there is really no point in including
the aliases (&pc.gl_pay_type, &pc.position_nbr) next to the columns being
selected, as those are the exact same names you get anyway, without explicitly
declaring the aliases.

In addition, your date processing is not going to work.  You are moving date
columns to the string variables $action_dt and $max_jo_term, and then trying to
use these string variables as dates.  You could declare these variables as date
variables, but I am not sure exactly what format SQR will put them into - it may
not be the Oracle native format that you are using in "if $action_dt =>
'01-Jan-2002'".  I usually like to control the exact format myself for a compare
(in a compare, if you set up the string in the right format, you don't need to
declare the variable as date), so I would probably "select
to_char(job.action_dt,'yyyymmdd') &action_dt" and then compare &action_dt to
'20020101'.  Finding the difference between $action_dt and $max_jo_term would
probably require declaring two date variables and then doing the datediff SQR
date function.


HTH,
Denise White
Sr. Programmer/Analyst
Vicor

------------------------------

Date:    Mon, 24 Dec 2001 09:21:23 -0600
From:    Kim Little <Kim_Little@ARCNOW.COM>
Subject: Re: SQL expression not ended, perhaps missing &name

Thanks Steve, but that didn't work either, I still keep getting the error
in the following areas:

SQL expression not ended, perhaps missing &name.
from ps_bas_activity bas

and
SQL expression not ended, perhaps missing &name.
from ps_job_pc.

The problem is I did not write this someone else did and with no
documentation to follow up on, and this sqr has so many ineligible
variables. :-(






Stephen Keen <stephen_keen@SQR-SERVICES.COM>@list.iex.net> on 12/25/2001
08:57:30 AM

Please respond to sqr-users@list.iex.net

Sent by:  "Discussion of SQR, Brio Software's database reporting language"
      <SQR-USERS@list.iex.net>


To:   SQR-USERS@list.iex.net
cc:

Subject:  Re: SQL expression not ended, perhaps missing &name


Hey

I think the error is in the 'Begin-Procedure Select-Cust-Exists' area.
What
exactly are you selecting there?  From what I see an 'x'  Comment the
calling area and see if it works

Steve

Merry Christmas

-----Original Message-----
From: Discussion of SQR, Brio Software's database reporting language
[mailto:SQR-USERS@list.iex.net]On Behalf Of Kim Little
Sent: Monday, December 24, 2001 9:33 AM
To: SQR-USERS@list.iex.net
Subject: SQL expression not ended, perhaps missing &name
Importance: High


Can anyone help I am getting the following error, when trying to run this
SQR.


#Include 'setenv.sqc'

!**********************************
Begin-Program
!**********************************

  do Init-Report

  display 'Report Begin at: ' noline
  do display-time

  do Process-Main

  display 'Report Ended at: ' noline
  do display-time

  do Reset
  do StdAPI-Term

End-Program


!**********************************
Begin-Procedure Init-Report
!**********************************
  do Init-DateTime
  do Get-Current-DateTime
  do Init-Number
  do StdAPI-Init

  let $ReportID    = 'ARC_TCELG'
  do Get-Report-Language
  let $ReportTitle  = $ARC_TCELG-REPORTTITLE
  display $ReportID    NoLine
  display ': '         NoLine
  display $ReportTitle
  display ''

  if $prcs_process_instance = ''
    do Ask-Values
  else
    do Get-Values
  end-if

End-Procedure Init-Report


!**********************************
Begin-Procedure Process-Main
!**********************************

Begin-SELECT

bas.Emplid &bas.Emplid

 move &bas.Emplid to $Emplid1

 move 'N' to $SelectExists

do Select-Hire-Date

if $action_dt => '01-Jan-2002'
  do Select-Prod-Client
else
  do Select-Term-Row
  if ($action_dt - $max_jo_term) > 90
     do Select-Prod-Client
  else
     if $reg_temp1 = 'T'
        do Select-Prod-Client
     end-if
  end-if
end-if

from ps_bas_activity bas

end-select

end-procedure Process-Main


!**********************************
Begin-Procedure Select-Hire-Date
!**********************************

begin-select
job.action_dt

 move &job.action_dt to $action_dt

from ps_job job
where job.emplid = $Emplid1
  and job.empl_type not in ('E','S')
  and job.action = 'HIR'
end-select

end-procedure Select-Hire-Date


!**********************************
Begin-Procedure Select-Prod-Client
!**********************************

Begin-select

pc.gl_pay_type &pc.gl_pay_type
pc.position_nbr &pc.position_nbr

if pc.gl_pay_type between '1000' and '1099'
   do Update-Reg-Temp
else
   do Select-Client-ID
   if $SelectExists = 'Y'
      do Select-Cust-Eff-Status
      if $eff_status1 = 'A'
         do Update-Reg-Temp
      end-if
   end-if
end-if
from ps_job pc
where pc.emplid = $Emplid1
  and pc.effdt =
      (select max(pc_ed.effdt) from ps_job pc_ed
       where pc.emplid = pc_ed.emplid
         and pc.empl_rcd# = pc_ed.empl_rcd#)
  and pc.effseq =
      (select max(pc_es.effseq) from ps_job pc_es
       where pc.emplid = pc_es.emplid)

end-select

end-procedure Select-Prod-Client


!**********************************
Begin-Procedure Update-Reg-Temp
!**********************************

begin-sql

update ps_job rt
set rt.reg_temp = 'T'
where rt.emplid = $Emplid1
  and rt.effdt =
      (select max(rt_ed.effdt) from ps_job rt_ed
       where rt.emplid = rt_ed.emplid
         and rt.empl_rcd# = rt_ed.empl_rcd#)
  and rt.effseq =
      (select max(rt_es.effseq) from ps_job rt_es
       where rt.emplid = rt_es.emplid)

end-sql

end-procedure Update-Reg-Temp


!**********************************
Begin-Procedure Select-Client-ID
!**********************************

begin-select

client.ps_client_id &client.ps_client_id

  do Select-Cust-Exists
from client@asys client, job_order@asys jo
where client.client = jo.client
  and jo.job_order = &pc.position_nbr

end-select

end-procedure Select-Client-ID


!**********************************
Begin-Procedure Select-Cust-Exists
!**********************************

begin-select

'X'
   Move 'Y' to #SelectExists
from ps_arc_cust_vw cust_vw
where cust_vw.setid = 'SHARE'
  and cust_vw.cust_id = &client.ps_client_id

end-select

end-procedure Select-Cust-Exists


!**********************************
Begin-Procedure Select-Cust-Eff-Status
!**********************************

begin-select

noelig.eff_status

 move &noelig.eff_status to $eff_status1

from ps_arc_noelig_cust noelig
where noelig.setid = 'SHARE'
  and noelig.cust_id = &client.ps_client_id
  and noelig.EFFDT =
      (SELECT MAX(noelig_ED.EFFDT) FROM PS_ARC_NOELIG_CUST noelig_ed
       WHERE noelig.setid = noelig_ed.setid
         AND noelig.cust_id = noelig_ed.cust_id
         AND noelig_ED.EFFDT <= SYSDATE)

end-select

end-procedure Select-Cust-Eff-Status


!**********************************
Begin-Procedure Select-Term-Row
!**********************************

begin-select
max(jo_term.action_dt)  &max_jo_term

 move &max_jo_term to $max_jo_term

  do Select-Reg-Temp

from ps_job jo_term
where jo_term.emplid = $Emplid1
  and jo_term.action = 'TER'
end-select

end-procedure Select-Term-Row

!**********************************
Begin-Procedure Select-Reg-Temp
!**********************************

begin-select
jo_rt.reg_temp

 move &jo_rt.reg_temp to $reg_temp1

from ps_job jo_rt
where jo_rt.emplid = $Emplid1
  and jo_rt.action = 'TER'
  and jo_rt.action_dt = $max_jo_term
end-select

end-procedure Select-Reg-Temp


!***********************************
#include 'prcsapi.sqc'   !Update Process Request API
#include 'prcsdef.sqc'   !Update Process Request variable declaration
#Include 'curdttim.sqc'  ! Get-Current-DateTime procedure
#Include 'datetime.sqc'  ! Routines for date and time formatting
#Include 'datemath.sqc'  ! Routines for date and time formatting
#Include 'number.sqc'    ! Routines to format numbers
#Include 'stdapi.sqc'    ! Update Process API