4GL embedded Sql query presenting error on script run.

Question

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

Fullscreen 6253.ZSSMO.txt Download

#################################################################################################################
# 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

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

jcarraway · Accepted 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. Fullscreen ZSSOM.txt Download ################################################################################################################# # 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)="user@domain.com" HEADER="[Nightly Report] SCA Suspicious Order Monitoring" ISSUERMAIL="DONOTREPLY@scapharma.com" 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 #################################################################################################################

Sage X3

4GL embedded Sql query presenting error on script run.

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING