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

Asked 5 years, 10 months ago

Viewed 245 times

1

I need to load the field CK_XLOTE typing the code of the product in the field CK_PRODUTO, I am generating a function to perform this procedure, but it is not returning the lot in the field. Can someone 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 setting

1

A hint, do not use table alias starting with S, mainly with 3 letters.. As it is used by the standard tables, try using a Getnextalias() to determine the alias and see if it works...

– thur

2018/08/16 at 22:23
Vlw thanks for the help.

– Junior Guerreiro

2018/08/18 at 14:55
Thanks for your help. Now you can take me a doubt,this my function and to load a field that the query is bringing, now my doubt is, if in this same query, I bring two fields as a result, for me to fill two fields in the system and need to create two functions one for each trigger of the specific field against domain.

– Junior Guerreiro

2018/08/18 at 14:58

1 answer

Browser other questions tagged sql-server advpl

You are not signed in. Login or sign up in order to post.

by siga0984 • **649** points · Answer 1 · 2018-09-23T03:24:14+00:00

Query is probably not bringing any record, due to mounting one or two conditions in the Query:

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

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

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

When comparing the B8_VALID field in the Query, remember that a DATA field in Advpl is written to the database as a varchar(8), in YYYYMMDD format. 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 make a CAST() from the B8_VALID field or even a CAST() from GETDATE() to return a string in the appropriate format for comparison.