I have a problem loading a field using a function in ADVPL

Question

I have a problem loading a field using a function in ADVPL

Navigation

#1 by (3 votes)

1

I need to load the CK_XLOTE field by entering the product code in the CK_PRODUTO field, I am generating a function to perform this procedure, but it is not returning the batch in the field. Can anyone help me.

Follow the code

#INCLUDE 'PROTHEUS.CH'
#INCLUDE 'PARMTYPE.CH'

User Function TESTE()

//MsgAlert(M->CK_PRODUTO)

Local cQuery        := ""
Local cLote         := ""

cQuery := " SELECT  TOP 1 "
cQuery += "         SB8.B8_LOTECTL, "
cQuery += "         SB8.B8_DTVALID "
cQuery += " FROM    " + RetSQLName("SB8") + "   AS SB8 "
cQuery += " WHERE   SB8.B8_FILIAL               = '" + xFilial("SB8") + "' "
cQuery += " AND     SB8.B8_LOCAL                = '01' "
cQuery += " AND     SB8.B8_DTVALID              > GETDATE() "
cQuery += " AND     SB8.B8_SALDO                > SB8.B8_EMPENHO "
cQuery += " AND     SB8.B8_PRODUTO              = '" + M->CK_PRODUTO + "' "
cQuery += " AND     SB8.D_E_L_E_T_              = '' "
cQuery += " ORDER BY SB8.B8_DTVALID "

If SELECT("SQL") > 0    
    dbSelectArea("SQL")     
    dbCloseArea()       
EndIf 

cQuery := ChangeQuery(cQuery)                                                              
dbUseArea(.T., 'TOPCONN', TCGENQRY(,,cQuery),"SQL", .F., .T.)       
dbSelectArea("SQL")

If SQL->(!EOF())

    cLote := SQL->B8_LOTECTL

EndIf

return(cLote)

Follow the trigger configuration

sql-server advpl

asked by anonymous 16.08.2018 / 19:58

1 answer

Query on all tables in the database Difficulty removing item from inverted list in Vue

score 3 · Answer 1

Query is probably not bringing any records, due to the setting of one or two conditions in Query:

cQuery += " AND     SB8.D_E_L_E_T_ = '' "

When comparing the field SB8.D_E_L_E_T_, compare it with a string with a whitespace (' ') instead of an empty string ('') . The database understands that an empty string with no spaces represents the value NULL - and no record will meet this condition.

cQuery += " AND     SB8.B8_DTVALID              > GETDATE() "

When comparing the B8_VALID field in Query, remember that a DATA field in AdvPL is written to the database as a varchar (8), in the format YYYYMMDD. So make sure that this comparison between the date in character format and the GetDate () function actually accepts the current format, or if it is not necessary to do a CAST () from the B8_VALID field or even a GETDATE () CAST () to return a string in the format suitable for comparison.