4GL embedded Sql query presenting error on script run.

SOLVED

Let me start by saying: Everything is DONE, except the sql query fails. This is the last piece holding up deployment.

I am trying to embed a sql query in a 4gl script to pull specific data into a variable set, then email the results on a nightly scheduled task.

I am not using Sdata or 4gl links/reads because i need a count of rows returned in a nested query matching criteria and can run this query in the sql query tool in Sage to return what I need.

The script compiles without error and is syntactically sound, but throws an "expression of non-boolean type specified in a context where a condition is expected" error at runtime.

I am attaching the full script for review, the following is where the error occurs:

Local Char ZSOMFREQ(255)
Local Integer ZCUSTFREQ(1..)
Local Char ZITEMFREQ(25)(1..)
Local Integer ZCOUNTFREQ(1..)
Local Integer I
Global Integer S1

ZSOMFREQ="select custfreq, itemfreq, COUNT(itemfreq) as countfreq "
& + "from( "
& + "select SOQ.BPCORD_0 as custfreq, SOQ.ITMREF_0 as itemfreq, SOQ.ORDDAT_0, SOQ.DEMDLVDAT_0, SOH.SOHTYP_0 from SORDERQ as SOQ "
& + "join SORDER as SOH on SOH.SOHNUM_0 = SOQ.SOHNUM_0 "
& + "where SOH.SOHTYP_0 in('BDO','WEB') and SOQ.ORDDAT_0 >= DATEADD(DAY, -30, GETDATE()) "
& + ") as ssom "
& + "group by custfreq, itemfreq "
& + "having count(itemfreq) >= 10 "
& + "order by custfreq, itemfreq;"

For (Integer CUSTFREQ, Char ITEMFREQ(25), Integer COUNTFREQ) From "5" Sql ZSOMFREQ As [ZSF]
Read [ZSF] First
If fstat = 4
S1 = 1
Else
While fstat < 4
ZCUSTFREQ(I) = [F:ZSF]CUSTFREQ : ZITEMFREQ(I) = [F:ZSF]ITEMFREQ : ZCOUNTFREQ(I) = [F:ZSF]COUNTFREQ
Append BODY , '|'+ZCUSTFREQ(I)+'||'+ZITEMFREQ(I)+'||'+ZCOUNTFREQ(I)+'|'+chr$(10)
I += 1
Read [ZSF] Next
Wend
Endif
Next
Return

#################################################################################################################
# Program Name: ZSSMO
# Description : ZSSMO - Nightly SCA Suspicious Order Monitoring Process
# Author      : SCA ZJCARRAWAY
# Date        : 05-MAR-2019
# Type        : Modification
#################################################################################################################
# * Revision Log *
#-----------------------------------------------------------------------------------------------------------------
# Rev      Issue Number      Date               User       Description
#-----------------------------------------------------------------------------------------------------------------
# 000       ZJCARRAWAY      05-MAR-2019      ZJCARRAWAY   Initial release
#################################################################################################################

Gosub EXEC
End

$EXEC

 If !clalev([F:ZSOH])  : Local File SORDER [F:ZSOH]  : Endif
 If !clalev([F:ZSOQ])  : Local File SORDERQ  [F:ZSOQ]  : Endif

 Local Integer  STA
 Local Integer  TRACE
 Local Char      FIC_TRACE(250)
 Local Char      ISSUERMAIL(250)
 Local Char      A_USER(250)(1..)
 Local Char      CC_USER(250)(1..)
 Local Char      HEADER(250)
 Local Clbfile   BODY(0)
 Local Char      ATTACHMENTS(250)(1..)

 A_USER(1)="[email protected]"

 HEADER="[Nightly Report] SCA Suspicious Order Monitoring"
 ISSUERMAIL="[email protected]"
 TRACE = 2

 Append BODY , 'Nightly Suspicious Order Frequency Report:'+chr$(10)+chr$(10)
 Append BODY , '|Customer||Product||Order Count (Past Month)|'+chr$(10)
 Append BODY , '---------------------------------------------'+chr$(10)
 Gosub $ZSOMFREQ

 Append BODY, chr$(10)+chr$(10)+chr$(10)+chr$(10)

 Append BODY , 'Nightly Suspicious Order Pattern Report:'+chr$(10)+chr$(10)
 Append BODY , '|Customer||Product||Order Count (Past Week)|'+chr$(10)
 Append BODY , '---------------------------------------------'+chr$(10)
 Gosub $ZSOMPAT

 If (S1 + S2) <> '2'
  STA =func ASYRMAIL.ASEND_MAIL(GACTX,ISSUERMAIL,A_USER,CC_USER,HEADER,BODY,ATTACHMENTS,TRACE)
 Endif

Return
#################################################################################################################

#################################################################################################################
$ZSOMFREQ

Local Char ZSOMFREQ(255)
Local Integer ZCUSTFREQ(1..)
Local Char ZITEMFREQ(25)(1..)
Local Integer ZCOUNTFREQ(1..)
Local Integer I
Global Integer S1

ZSOMFREQ="select custfreq, itemfreq, COUNT(itemfreq) as countfreq "
& + "from( "
& + "select SOQ.BPCORD_0 as custfreq, SOQ.ITMREF_0 as itemfreq, SOQ.ORDDAT_0, SOQ.DEMDLVDAT_0, SOH.SOHTYP_0 from SORDERQ as SOQ "
& + "join SORDER as SOH on SOH.SOHNUM_0 = SOQ.SOHNUM_0 "
& + "where SOH.SOHTYP_0 in('BDO','WEB') and SOQ.ORDDAT_0 >= DATEADD(DAY, -30, GETDATE()) "
& + ") as ssom "
& + "group by custfreq, itemfreq "
& + "having count(itemfreq) >= 10 "
& + "order by custfreq, itemfreq;"

For (Integer CUSTFREQ, Char ITEMFREQ(25), Integer COUNTFREQ) From "5" Sql ZSOMFREQ As [ZSF]
  Read [ZSF] First
  If fstat = 4
    S1 = 1
  Else
    While fstat < 4
    ZCUSTFREQ(I) = [F:ZSF]CUSTFREQ : ZITEMFREQ(I) = [F:ZSF]ITEMFREQ : ZCOUNTFREQ(I) = [F:ZSF]COUNTFREQ
    Append BODY , '|'+ZCUSTFREQ(I)+'||'+ZITEMFREQ(I)+'||'+ZCOUNTFREQ(I)+'|'+chr$(10)
    I += 1
    Read [ZSF] Next
    Wend
  Endif
Next
Return
#################################################################################################################

#################################################################################################################
$ZSOMPAT

Local Char ZSOMPAT(255)
Local Integer ZCUSTPAT(1..)
Local Char ZITEMPAT(25)(1..)
Local Integer ZCOUNTPAT(1..)
Local Integer J
Global Integer S2

ZSOMPAT="select custpat, itempat, COUNT(itempat) as countpat "
& + "from( "
& + "select SOQ.BPCORD_0 as custpat, SOQ.ITMREF_0 as itempat, SOQ.ORDDAT_0, SOQ.DEMDLVDAT_0, SOH.SOHTYP_0 from SORDERQ as SOQ "
& + "join SORDER as SOH on SOH.SOHNUM_0 = SOQ.SOHNUM_0 "
& + "where SOH.SOHTYP_0 in('BDO','WEB') and SOQ.ORDDAT_0 >= DATEADD(DAY, -7, GETDATE()) "
& + ") as ssom "
& + "group by custpat, itempat "
& + "having count(itempat) >= 3 "
& + "order by custpat, itempat;"

For (Integer CUSTPAT, Char ITEMPAT(25), Integer COUNTPAT) From "5" Sql ZSOMPAT As [ZSP]
  Read [ZSP] First
  If fstat = 4
    S2 = 1
  Else
    While fstat < 4
    ZCUSTPAT(J) = [F:ZSP]CUSTPAT : ZITEMPAT(J) = [F:ZSP]ITEMPAT : ZCOUNTPAT(J) = [F:ZSP]COUNTPAT
    Append BODY , '|'+ZCUSTPAT(J)+'||'+ZITEMPAT(J)+'||'+ZCOUNTPAT(J)+'|'+chr$(10)
    J += 1
    Read [ZSP] Next
    Wend
  Endif
Next
Return

#################################################################################################################

Parents
  • +1
    verified answer

    I was able to bypass the embedded sql issue by creating two custom views and reading them from the script.

    I'm uploading the corrected script for anyone else who faces similar issues in the future. We have tested the scheduled task and committed our working solution.

    If anyone would still like to provide input on properly utilizing 4gl embedded sql queries feel free.

    #################################################################################################################
    # Program Name: ZSSOM
    # Description : ZSSOM - Nightly SCA Suspicious Order Monitoring Process
    # Author      : SCA ZJCARRAWAY
    # Date        : 05-MAR-2019
    # Type        : Modification
    #################################################################################################################
    # * Revision Log *
    #-----------------------------------------------------------------------------------------------------------------
    # Rev      Issue Number      Date               User       Description
    #-----------------------------------------------------------------------------------------------------------------
    # 000       ZJCARRAWAY      05-MAR-2019      ZJCARRAWAY   Initial release
    #################################################################################################################
    
    Gosub EXEC
    End
    
    $ACTION
    Case ACTION
      When  "EXEC"  : Gosub EXEC
      When Default
    Endcase
    Return
    
    $EXEC
    
     If !clalev([F:ZSOH])  : Local File SORDER [F:ZSOH]  : Endif
     If !clalev([F:ZSOQ])  : Local File SORDERQ  [F:ZSOQ]  : Endif
     If !clalev([F:ZSP])  : Local File ZSOMPAT [F:ZSP] : Endif
     If !clalev([F:ZSF])  : Local File ZSOMFREQ [F:ZSF] : Endif
    
     Local Integer  STA
     Local Integer  TRACE
     Local Char      FIC_TRACE(250)
     Local Char      ISSUERMAIL(250)
     Local Char      A_USER(250)(1..)
     Local Char      CC_USER(250)(1..)
     Local Char      HEADER(250)
     Local Clbfile   BODY(0)
     Local Char      ATTACHMENTS(250)(1..)
     Global Integer I
    
    A_USER(1)="[email protected]"
    
     HEADER="[Nightly Report] SCA Suspicious Order Monitoring"
     ISSUERMAIL="[email protected]"
     TRACE = 2
    
     Gosub $ZSOMFREQ : Gosub $ZSOMPAT
    
     If I <> 2
      STA =func ASYRMAIL.ASEND_MAIL(GACTX,ISSUERMAIL,A_USER,CC_USER,HEADER,BODY,ATTACHMENTS,TRACE)
     Endif
    
    Return
    #################################################################################################################
    
    #################################################################################################################
    $ZSOMFREQ
    
    Local Char ZSOMFREQ(255)
    Local Char ZCUSTFREQ(10)
    Local Char ZITEMFREQ(10)
    Local Integer ZCOUNTFREQ
    
    #ZSOMFREQ="select custfreq, itemfreq, COUNT(itemfreq) as countfreq "
    #& + "from( "
    #& + "select SOQ.BPCORD_0 as custfreq, SOQ.ITMREF_0 as itemfreq, SOQ.ORDDAT_0, SOQ.DEMDLVDAT_0, SOH.SOHTYP_0 from SORDERQ as SOQ "
    #& + "join SORDER as SOH on SOH.SOHNUM_0 = SOQ.SOHNUM_0 "
    #& + "where SOH.SOHTYP_0 in('BDO','WEB') and SOQ.ORDDAT_0 >= DATEADD(DAY, -30, GETDATE()) "
    #& + ") as ssom "
    #& + "group by custfreq, itemfreq "
    #& + "having count(itemfreq) >= 10 "
    #& + "order by custfreq, itemfreq;"
    #
    #For (Integer CUSTFREQ, Char ITEMFREQ(25), Integer COUNTFREQ) From "5" Sql ZSOMFREQ As [ZSF]
      Read [F:ZSF]ZSF0 First
      If fstat = 4
        I += 1
      Else
        Append BODY , 'Nightly Suspicious Order Frequency Report:'+chr$(10)+chr$(10)
        Append BODY , ' Customer:  Product:  Order Count (Past Month):'+chr$(10)
        Append BODY , '-----------------------------------------------------------------'+chr$(10)
        While fstat < 4
          ZCUSTFREQ = [F:ZSF]CUSTFREQ : ZITEMFREQ = [F:ZSF]ITEMFREQ : ZCOUNTFREQ = [F:ZSF]COUNTFREQ
          Append BODY , '   '+ZCUSTFREQ+'        '+ZITEMFREQ+'          '+num$(ZCOUNTFREQ)+' '+chr$(10)
          Read [F:ZSF] Next
        Wend
        Append BODY, chr$(10)+chr$(10)+chr$(10)
      Endif
    #Next
    Return
    #################################################################################################################
    
    #################################################################################################################
    $ZSOMPAT
    
    Local Char ZSOMPAT(255)
    Local Char ZCUSTPAT(10)
    Local Char ZITEMPAT(10)
    Local Integer ZCOUNTPAT
    
    #ZSOMPAT="select custpat, itempat, COUNT(itempat) as countpat "
    #& + "from( "
    #& + "select SOQ.BPCORD_0 as custpat, SOQ.ITMREF_0 as itempat, SOQ.ORDDAT_0, SOQ.DEMDLVDAT_0, SOH.SOHTYP_0 from SORDERQ as SOQ "
    #& + "join SORDER as SOH on SOH.SOHNUM_0 = SOQ.SOHNUM_0 "
    #& + "where SOH.SOHTYP_0 in('BDO','WEB') and SOQ.ORDDAT_0 >= DATEADD(DAY, -7, GETDATE()) "
    #& + ") as ssom "
    #& + "group by custpat, itempat "
    #& + "having count(itempat) >= 3 "
    #& + "order by custpat, itempat;"
    #
    #For (Integer CUSTPAT, Char ITEMPAT(25), Integer COUNTPAT) From "5" Sql ZSOMPAT As [ZSP]
      Read [F:ZSP]ZSP0 First
      If fstat = 4
        I += 1
      Else
        Append BODY , 'Nightly Suspicious Order Pattern Report:'+chr$(10)+chr$(10)
        Append BODY , ' Customer:  Product:  Order Count (Past Week):'+chr$(10)
        Append BODY , '----------------------------------------------------------------'+chr$(10)
        While fstat < 4
          ZCUSTPAT = [F:ZSP]CUSTPAT : ZITEMPAT = [F:ZSP]ITEMPAT : ZCOUNTPAT = [F:ZSP]COUNTPAT
          Append BODY , '   '+ZCUSTPAT+'        '+ZITEMPAT+'          '+num$(ZCOUNTPAT)+' '+chr$(10)
          Read [F:ZSP] Next
        Wend
      Endif
    #Next
    Return
    
    #################################################################################################################
    

  • 0 in reply to jcarraway

    I believe there are 2 issues in your original attempt. The first is that you defined your ZSOMFREQ query variable as 255 characters. Your actual query text was much longer than that so it was getting truncated. SQL didn't understand the truncated portion and was returning the SQL error.

    Try this as your variable def: Local Clbfile ZSOMFREQ(4)

    The second issue is that your FOR loop specified that the CUSTFREQ field is an integer instead of the char string it really is.

    Try: For (char CUSTFREQ(GLONBPC), Char ITEMFREQ(25), Integer COUNTFREQ)

    Tested with this source...

    Local Clbfile ZSOMFREQ(4)

    Local Char ZCUSTFREQ(10)

    Local Char ZITEMFREQ(10)

    Local Integer ZCOUNTFREQ

    Local Clbfile BODY(0)

     

    ZSOMFREQ="select custfreq, itemfreq, COUNT(itemfreq) as countfreq "

    & + "from( "

    & + "select SOQ.BPCORD_0 as custfreq, SOQ.ITMREF_0 as itemfreq, SOQ.ORDDAT_0, SOQ.DEMDLVDAT_0, SOH.SOHTYP_0 from SORDERQ as SOQ "

    & + "join SORDER as SOH on SOH.SOHNUM_0 = SOQ.SOHNUM_0 "

    & + "where SOH.SOHTYP_0 in('BDO','WEB') and SOQ.ORDDAT_0 >= DATEADD(DAY, -30, GETDATE()) "

    & + ") as ssom "

    & + "group by custfreq, itemfreq "

    & + "having count(itemfreq) >= 10 "

    & + "order by custfreq, itemfreq;"

     

    Append BODY , 'Nightly Suspicious Order Frequency Report:'+chr$(10)+chr$(10)

    Append BODY , ' Customer: Product: Order Count (Past Month):'+chr$(10)

    Append BODY , '-----------------------------------------------------------------'+chr$(10)

     

    For (char CUSTFREQ(GLONBPC), Char ITEMFREQ(25), Integer COUNTFREQ) From "5" Sql ZSOMFREQ As [ZSF]

    ZCUSTFREQ = [F:ZSF]CUSTFREQ : ZITEMFREQ = [F:ZSF]ITEMFREQ : ZCOUNTFREQ = [F:ZSF]COUNTFREQ

    Append BODY , ' '+ZCUSTFREQ+' '+ZITEMFREQ+' '+num$(ZCOUNTFREQ)+' '+chr$(10)

    Next

     

    Append BODY, chr$(10)+chr$(10)+chr$(10)

    Infbox BODY

  • 0 in reply to Rod Quenneville

    Thanks, I'll review this when I get time and let you know how it goes.

Reply Children
No Data