[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index] [Date Index] [Thread Index]
[SQR-USERS Info] [SQRUG Home Page]

RE: [sqr-users] ERROR (SQR 1303 and 1304) and a Virtual Table

I am fairly confident that the date format is not the problem, but
rather somehow the syntax of the virtual table in the left outer join.
Therefore, I have restructured my program to execute a GetCustomerID
which does a call to GetCustomerDescr.  This is not ideal in the long
run, because I will not be able to sort by CustomerDescr but will get
this out to production for my users until I can resolve the left outer
join problem.  Thanks for your prompt response to my dilemma. 

-----Original Message-----
From: sqr-users-bounces+jhansen=spokanecounty.org@sqrug.org
[mailto:sqr-users-bounces+jhansen=spokanecounty.org@sqrug.org] On Behalf
Of Alexander, Steven
Sent: Tuesday, December 19, 2006 1:45 PM
To: 'This list is for discussion about the SQR database
reportinglanguagefrom Hyperion Solutions.'
Subject: RE: [sqr-users] ERROR (SQR 1303 and 1304) and a Virtual Table

I notice that the literal date that worked was in a different format
than the (string?) variable.  You might reformat $ThruDt or declare it
as a date variable.

-----Original Message-----
From: sqr-users-bounces+steven.alexander=sanjoseca.gov@sqrug.org
[mailto:sqr-users-bounces+steven.alexander=sanjoseca.gov@sqrug.org] On
Behalf Of Hansen, Janet
Sent: Tuesday, December 19, 2006 12:41 PM
To: This list is for discussion about the SQR database reporting
languagefrom Hyperion Solutions.
Subject: RE: [sqr-users] ERROR (SQR 1303 and 1304) and a Virtual Table

SQL SERVER
2006-12-31 00:00:00.000

Yes, this SQR has always worked in the past.  The only thing that has
changed is the addition of the left join to this Virtual Table, PERMIT. 

-----Original Message-----
From: sqr-users-bounces+jhansen=spokanecounty.org@sqrug.org
[mailto:sqr-users-bounces+jhansen=spokanecounty.org@sqrug.org] On Behalf
Of Alexander, Steven
Sent: Tuesday, December 19, 2006 12:01 PM
To: 'This list is for discussion about the SQR database
reportinglanguagefrom Hyperion Solutions.'
Subject: RE: [sqr-users] ERROR (SQR 1303 and 1304) and a Virtual Table

Which dbms is this?  I see that "let $ThruDT = &A.THRU_DATE" from the
run control record, but what is the exact value of "$ThruDT".  Is it in
"mm/dd/yyyy" format?  Is that the right format?


-----Original Message-----
From: sqr-users-bounces+steven.alexander=sanjoseca.gov@sqrug.org
[mailto:sqr-users-bounces+steven.alexander=sanjoseca.gov@sqrug.org] On
Behalf Of Hansen, Janet
Sent: Tuesday, December 19, 2006 10:21 AM
To: sqr-users@sqrug.org
Subject: [sqr-users] ERROR (SQR 1303 and 1304) and a Virtual Table

I am trying to run this SQR, however, am receiving the following error.
I have nailed it down to the exact line that is giving me trouble which
I have indicated by the {my problem is here!} marking.  If I hardcode
the date, the SQR completes successfully.  Note that I have commented
out all of the lines of code within the body of my select statement to
eliminate that as having any input into the problem.  Any suggestions
would be greatly appreciated, thanks.


        (SQR 1303) Error in SQL (perhaps missing &name after
expression):
        SELECT PR.CUST_ID, C.NAME1, PERMIT.DESCRSHORT, PR.PROJECT_ID,
PR.RESOURCE_TYPE, PR.ACCOUNTING_DT, PR.ACTIVITY_ID, PR.EMPLID,
PR.ANALYSIS_TYPE, PR.RESOURCE_CATEGORY, PR.RESOURCE_SUB_CAT,
PR.RESOURCE_QUANTITY, PR.RESOURCE_AMOUNT, PR.TRANS_DT, PR.FUND_CODE FROM
PS_PROJ_RESOURCE PR LEFT OUTER JOIN (SELECT TOP 100 PERCENT * FROM
PS_SPO_TL_CUSTOMER TLC WHERE TLC.EFF_STATUS = 'A' AND TLC.EFFDT =
(SELECT MAX(TLC1.EFFDT) FROM  PS_SPO_TL_CUSTOMER TLC1 WHERE TLC1.CUST_ID
= TLC.CUST_ID AND TLC1.EFFDT <= ?)) PERMIT ON PERMIT.CUST_ID =
PR.CUST_ID LEFT OUTER JOIN PS_CUSTOMER C ON PERMIT.DESCRSHORT =
C.CUST_ID WHERE  PR.BUSINESS_UNIT = 'PROJA' AND  PR.ANALYSIS_TYPE =
'PAY' AND  PR.CUST_ID > ' ' AND  PR.ACCOUNTING_DT BETWEEN ? AND ? AND
PR.FUND_CODE = ? AND (C.SETID = 'ARSHR' OR C.SETID IS NULL) AND
(C.CUST_STATUS = 'A' OR C.CUST_STATUS IS NULL) ORDER BY PR.CUST_ID,
PR.PROJECT_ID, PR.RESOURCE_TYPE, PR.ACCOUNTING_DT, PR.TRANS_DT 
        (SQR 1304) Check SELECT columns, expressions and 'where' clause
for syntax.

        SQR for PeopleSoft: Program Aborting.


My code is as follows:

#include 'setenv.sqc'    !Set environment
#Include 'setup32.sqc'   !Printer and page-size initialization

#define C-CUST    1
#define C-PROJ    1
#define C-CUSTDESCR 17
#define C-RCST   17
#define C-DUR    24
#define C-ACTV   37
#define C-EMPLID 54
#define C-NAME   67  
#define C-TRC    99
#define C-CAT    106
#define C-SCAT   113
#define C-QTY    120
#define C-GRATE  136
#define C-GROSS  150

begin-report
  do Init-Report
  do Process-Main
  do Reset
  do Stdapi-Term
end-report

begin-procedure Init-Report
  do Stdapi-Init
  move 'PCSP015' to $ReportID
  move 'Customer Detail'  to $ReportTitle
  do Init-DateTime
  do Init-Number
  do Get-Current-DateTime
  do Get-SPO-Values
end-procedure

begin-heading 7
#Include 'stdhdg01.sqc'
print 'From DUR '  (3, 1)
print $HdgFromDt   (3,11)
print ' Thru DUR ' (3, 22)
print $HdgThruDt   (3, 32)
PRINT 'FUND'       (4, 1)
print &A.FUND_CODE (4, 6)
PRINT 'Project ID' (6, {C-PROJ})
PRINT 'Type'       (6, {C-RCST})
PRINT 'Date'       (6, {C-DUR})
PRINT 'Activity ID' (6, {C-ACTV})
PRINT 'EMPLID'     (6, {C-EMPLID})
PRINT 'Name'       (6, {C-NAME})
PRINT 'TRC'        (6, {C-TRC})
PRINT 'CAT'         (6, {C-CAT})
PRINT 'SCAT'       (6, {C-SCAT})
PRINT '     Hours' (6, {C-QTY})
PRINT 'Actual Rate'       (6, {C-GRATE})
print 'Actual Gross'      (6, {C-GROSS})
print '-' (7, 1, 161) fill
end-heading

begin-procedure Get-SPO-Values
begin-Select
A.OPRID                    
A.RUN_CNTL_ID              
A.FROM_DATE                
A.THRU_DATE  
A.FUND_CODE              

    let $FromDT = &A.FROM_DATE
    let $ThruDT = &A.THRU_DATE
    let $FundCode = &A.FUND_CODE

FROM PS_SPO_RUN_CNTL_PC A
WHERE A.OPRID       = $PRCS_OPRID
  AND A.RUN_CNTL_ID = $PRCS_RUN_CNTL_ID
end-SELECT
do Format-DateTime($FromDT,$HdgFromDt,{DEFDATE}, '', '') do
Format-DateTime($ThruDT,$HdgThruDt,{DEFDATE}, '', '') end-procedure 


begin-procedure Process-Main
let #GrandHrs = 0
let #GrandGross = 0
let #CustHrs = 0
let #CustGross = 0
let #ProjHrs = 0
let #ProjGross = 0
let #RCSTHrs = 0
let #RCSTGross = 0
let $LastCust = ' '
let $LastCustDescr = ' '
let $LastProj = ' '
let $LastType = ' '
begin-Select
PR.CUST_ID
C.NAME1
PERMIT.DESCRSHORT
PR.PROJECT_ID
PR.RESOURCE_TYPE
PR.ACCOUNTING_DT
PR.ACTIVITY_ID
PR.EMPLID
PR.ANALYSIS_TYPE
PR.RESOURCE_CATEGORY
PR.RESOURCE_SUB_CAT
PR.RESOURCE_QUANTITY
PR.RESOURCE_AMOUNT
PR.TRANS_DT
PR.FUND_CODE


   !if $LastCust = ' '
   !      let $LastCust = &PR.CUST_ID
   !      let $LastCustDescr = &C.NAME1
   !      let $LastProj = &PR.PROJECT_ID
   !      let $LastType = &PR.RESOURCE_TYPE
   !      do Cust-Start
   !   end-if
   
   !   if &PR.CUST_ID <> $LastCust
   !      do Cust-End
   !      Position (+2)
   !      let $LastCust = &PR.CUST_ID
   !      let $LastCustDescr = &C.NAME1
   !      let $LastProj = &PR.PROJECT_ID
   !      let $LastType = &PR.RESOURCE_TYPE
   !      do Cust-Start
   !     end-if

   !     if &PR.PROJECT_ID <> $LastProj
   !        do Proj-End
   !        Position (+1)
   !        let $LastProj = &PR.PROJECT_ID
   !        let $LastType = &PR.RESOURCE_TYPE
   !        do Proj-Start
   !     end-if

   !     if &PR.RESOURCE_TYPE <> $LastType
   !        do RCST-End
   !        Position (+1)
   !        let $LastType = &PR.RESOURCE_TYPE
   !        do RCST-Start
   !     end-if
   
   !   if #Current-Line + 1 > 54
   !      new-Page
   !      do Cust-Start
   !   end-if
 
   !   let #TRCHours = 0
   !   let #ActRate = 0
   !   let #DistAmt = round(&PR.RESOURCE_AMOUNT, 2)
   !   let #TRCHours = &PR.RESOURCE_QUANTITY
   !   if #DistAmt <> 0 AND #TRCHours <> 0
   !      let #ActRate = #DistAmt / #TRCHours
   !   end-if
   !   let #TRCHours = &PR.RESOURCE_QUANTITY
   !   if &PR.RESOURCE_CATEGORY = 'FRING'
   !      let #TRCHours = 0
   !   end-if
   !   add #TRCHours to #RCSTHrs
   !   add #DistAmt to #RCSTGross
   !   !let $DURIn = &PR.ACCOUNTING_DT
   !   let $DURIn = &PR.TRANS_DT

   !   do Format-DateTime($DURIn,$DUROut,{DEFDATE}, '', '')
   !   print $DUROut (, {C-DUR})
   !   print &PR.ACTIVITY_ID (, {C-ACTV})
   !   print &PR.EMPLID (, {C-EMPLID})
   !   do GetEmployeeName
   !   print &PR.ANALYSIS_TYPE (, {C-TRC})
   !   print &PR.RESOURCE_CATEGORY (, {C-CAT})
   !   print &PR.RESOURCE_SUB_CAT (, {C-SCAT})
   !   print #TRCHours (, {C-QTY}) edit 999,999.99
   !   print #ActRate (, {C-GRATE}) edit 999.999999
   !   print #DistAmt (, {C-GROSS}) edit 9,999,999.99
   !   position (+1)


FROM PS_PROJ_RESOURCE PR
LEFT OUTER JOIN (SELECT TOP 100 PERCENT TLC.* 
           FROM PS_SPO_TL_CUSTOMER TLC 
           WHERE TLC.EFF_STATUS = 'A'
           AND TLC.EFFDT =      (SELECT MAX(TLC1.EFFDT)
                                 FROM  PS_SPO_TL_CUSTOMER TLC1
                                 WHERE TLC1.CUST_ID = TLC.CUST_ID 
                                 AND TLC1.EFFDT <= $ThruDT)) PERMIT ON
PERMIT.CUST_ID = PR.CUST_ID                {my problem is here!}
                         !AND EFFDT <= '12/31/2006')) PERMIT ON
PERMIT.CUST_ID = PR.CUST_ID LEFT OUTER JOIN PS_CUSTOMER C ON
PERMIT.DESCRSHORT = C.CUST_ID WHERE  PR.BUSINESS_UNIT = 'PROJA'
AND  PR.ANALYSIS_TYPE = 'PAY'
AND  PR.CUST_ID > ' '
AND  PR.ACCOUNTING_DT BETWEEN $FromDT AND $ThruDT !AND  PR.ACCOUNTING_DT
BETWEEN '01/01/2005' AND '12/31/2006'
AND  PR.FUND_CODE = $FundCode
!AND  PR.FUND_CODE = '110'
AND (C.SETID = 'ARSHR' OR C.SETID IS NULL)
AND (C.CUST_STATUS = 'A' OR C.CUST_STATUS IS NULL)              
ORDER BY PR.CUST_ID, PR.PROJECT_ID, PR.RESOURCE_TYPE, PR.ACCOUNTING_DT,
PR.TRANS_DT end-Select

do Cust-End
print '-'  (+2, {C-QTY},10) fill
print '-' (,{C-GROSS}, 12) fill
print 'Grand Total' (+1, 98) bold
print #GrandHrs (, {C-QTY}) edit 999,999.99 print #GrandGross (,
{C-GROSS}) edit 9,999,999.99 end-procedure

begin-procedure GetEmployeeName
begin-Select
PD.NAME
  print &PD.NAME (, {C-NAME})
FROM PS_PERSONAL_DATA PD
WHERE PD.EMPLID = &PR.EMPLID
end-Select
end-procedure


begin-procedure Cust-Start
   if #Current-line + 2 > 54
      new-page
   end-if
   Print $LastCust  (, {C-CUST})  bold
   Print $LastCustDescr (, {C-CUSTDESCR}) bold
   !Print $LastCName (, {C-RCST})  bold
   Print $LastProj (+1, {C-PROJ}) bold
   Print $LastType (, {C-RCST})   bold
end-procedure

begin-procedure Cust-End
  do Proj-End
  print '-'  (+1, {C-QTY},10) fill
  print '-' (,{C-GROSS}, 12) fill
  print $LastCust (+1, 98) bold
  Print $LastCustDescr (, 114) bold
  print #CustHrs (, {C-QTY}) edit 999,999.99
  print #CustGross (, {C-GROSS}) edit 9,999,999.99
  add #CustHrs to #GrandHrs
  add #CustGross to #GrandGross
  let #CustHrs = 0
  let #CustGross = 0
end-procedure

begin-procedure Proj-Start
   if #Current-line + 1 > 54
      do Cust-Start
   else
      Print $LastProj (+1, {C-PROJ}) bold
      Print $LastType (, {C-RCST})   bold
   end-if
end-procedure

begin-procedure Proj-End
  do RCST-End
  print '-'  (+1, {C-QTY},10) fill
  print '-' (,{C-GROSS}, 12) fill
  print $LastProj (+1, 98) bold
  print #ProjHrs (, {C-QTY}) edit 999,999.99
  print #ProjGross (, {C-GROSS}) edit 9,999,999.99
  add #ProjHrs to #CustHrs
  add #ProjGross to #CustGross
  let #ProjHrs = 0
  let #ProjGross = 0
end-procedure

begin-procedure RCST-Start
   if #Current-line + 1 > 54
      do Cust-Start
   else
      Print $LastType (+1, {C-RCST}) bold
   end-if
end-procedure

begin-procedure RCST-End
  print '-'  (, {C-QTY},10) fill
  print '-' (,{C-GROSS}, 12) fill
  print $LastType (+1, 98) bold
  print #RCSTHrs (, {C-QTY}) edit 999,999.99
  print #RCSTGross (, {C-GROSS}) edit 9,999,999.99
  add #RCSTHrs to #ProjHrs
  add #RCSTGross to #ProjGross
  let #RCSTHrs = 0
  let #RCSTGross = 0
end-procedure


#Include 'reset.sqc'     !Reset printer procedure
#Include 'curdttim.sqc'  !Get-Current-DateTime procedure #Include
'datetime.sqc'  !Routines for date and time formatting #Include
'datemath.sqc'  
#Include 'number.sqc'    !Routines to format numbers
#Include 'stdapi.sqc'    !Update Process API

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users