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