Baseado em exemplos do forum, montei esta rotina para testar SQL, mais nao consegui pegar os valores de uma terceira tabela.
FUNCTION PORTAL_array_muda() // modelo de filtro de array
LOCAL oQUERY
PRIVATE nCurrent, cDescricao, oELEMENTO, aVetorPai := {}, aVetorFilho := {}, oBrw1, oBrw2, oDlg_Busca
PRIVATE aTITULO:={}, zRec, xLOJA:=[01], zRec_02, aTIT_02:={}
PRIVATE cTXT:=DATE(), cTXT1:=DATE()+10, xQTDE:=0, DTFIM, DTINI, cARQ:=0
PRIVATE oDlg, aResult:= {}, oServer, TITULO:={}, xQTDEPENDENTE:=0, xVALOR:=0, xFRETE:=0
A_MsgGetFecha( [Cotações diaria], [Entre periodo a pesquisar ], @cTXT, @cTXT1 )
oServer := MySqlConnection( "127.0.0.1", "", "root", "123*123*" )
DTINI:= Transform( Dtos( cTXT ), "@R 9999-99-99" ) //'2020-05-06'
DTFIM:= Transform( Dtos( cTXT1 ), "@R 9999-99-99" ) //'2020-05-06'
oServer:Open()
oServer:Execute( "USE PORTAIS" )
oQuery := oServer:Execute( [SELECT * FROM entraportal WHERE dt_fecha BETWEEN ']+DTINI+[' AND ']+DTFIM+['] )
DO WHILE ! oQuery:Eof()
aAdd( aVetorPai, { oQuery:Fields("status" ):Value ,; // 1
oQuery:Fields("portal" ):Value ,; // 2
oQuery:Fields("idarquivo" ):Value ,; // 3
oQuery:Fields("dt_fecha" ):Value ,; // 4
oQuery:Fields("hr_fecha" ):Value ,; // 5
oQuery:Fields("identraportal" ):Value ,; // 6
oQuery:Fields("CodVend" ):Value ,; // 7
oQuery:Fields("uf" ):Value ,; // 8
oQuery:Fields("cliente" ):Value ,; // 9
oQuery:Fields("cnpj" ):Value ,; // 10
oQuery:Fields("filial" ):Value }) // 11
oQuery:MoveNext()
ENDDO
oQuery:Close()
IF LEN(aVETORPAI) > 0
INIT DIALOG oDlg_Busca TITLE "Pesquisar Produto - Filial: " +str(vLoja,4) AT 0,0 SIZE 1180,640 ;
FONT HFont():Add( 'tahoma',0,-13,400,,,) CLIPPER ;
STYLE WS_POPUP+WS_CAPTION+WS_SYSMENU+WS_MAXIMIZEBOX+WS_SIZEBOX+DS_CENTER
@ 13,070 BROWSE oBrw1 ARRAY SIZE 1160,310 STYLE WS_TABSTOP ;
ON POSCHANGE {|| cID := oBrw1:aarray[oBRW1:nCurrent, 06], ;
PORTAL_ARRAY_MUDA_onPosChange(@cID), ;
oBrw2:Refresh(), oDlg_Busca:Refresh() }
oBrw1:nHeadHeight := 25
oBrw1:aArray := aVetorPai
oBrw1:nColumns := 14
oBrw1:freeze := 1
oBrw1:lDispHead := .T.
oBrw1:lSep3d := .T.
oBrw1:lAdjRight := .T.
Hwg_CreateArList( oBrw1, aVETORPAI )
aTITULO:= { [Status], [Portal], [Nº Cotacao], [Fechamento], [Horario], [Id], [CodVe], [UF], [Cliente], [CNPJ], [Filial] }
FOR nI := 1 TO Len(oBrw1:aColumns)
oBrw1:aColumns[nI]:heading := aTITULO[nI]
IF (oBrw1:aColumns[nI]):Type == "C"
oBrw1:aColumns[nI]:nJusHead := DT_LEFT
ELSE
oBrw1:aColumns[nI]:nJusHead := DT_LEFT
ENDIF
IF (oBrw1:aColumns[nI]):Type == "C"
oBrw1:aColumns[nI]:nJusLin := DT_LEFT
oBrw1:aColumns[nI]:Picture := [@!]
ELSEIF (oBrw1:aColumns[nI]):Type == "N"
oBrw1:aColumns[nI]:nJusLin := DT_RIGHT
ENDIF
NEXT
FOR nG := 1 TO Len(oBrw1:aColumns)
IF (oBrw1:aColumns[nG]):Type == "N"
zREC:=oBrw1:aarray[oBrw1:nCurrent, nG]
IF VAL(SUBSTR(STR(zREC,12,2),11,2)) > 0
oBrw1:aColumns[nG]:Picture := [@E 999,999,999.99]
ELSE
oBrw1:aColumns[nG]:Picture := [@E 999,999,999]
ENDIF
ENDIF
NEXT
oBrw1:aColumns[2]:bColorBlock := ;
{|n| IF( (aVETORPAI[oBrw1:nCurrent][2]=[SINTESE] .OR. aVETORPAI[oBrw1:nCurrent][2]=[GTPLAN] ), {x_RED, x_WHITE, x_WHITE, x_GRAY}, ;
(IF( aVETORPAI[oBrw1:nCurrent][2]=[APOIO], {x_GREEN, x_WHITE, x_WHITE, x_GRAY}, ;
IF( aVETORPAI[oBrw1:nCurrent][2]=[BIONEXO], {x_DARKBLUE, x_WHITE, x_WHITE, x_GRAY}, ;
{x_BLUE, x_WHITE, x_WHITE, x_GRAY} ))))}
oBrw1:aColumns[01]:Length := 15
oBrw1:aColumns[02]:Length := 15
oBrw1:aColumns[02]:bHeadClick := {|| p_fobrowse2_1_onHeadClick( ) }
oBrw1:aColumns[04]:bHeadClick := {|| p_fobrowse2_2_onHeadClick( ) }
oBrw1:aColumns[03]:bHeadClick := {|| p_fobrowse2_3_onHeadClick( ) }
@ 13,390 BROWSE oBrw2 ARRAY SIZE 960,200 STYLE WS_TABSTOP AUTOEDIT
oBrw2:nHeadHeight := 24
oBrw2:nColumns := 5
oBrw2:freeze := 1
nCurrent := oBrw1:nCurrent
cARQ := aVETORPAI[nCurrent,6] //iPosicao é a coluna onde está a descrição
aGridFilho := {}
FOR EACH oElemento IN aVetorFilho
IF oElemento[6] = cARQ
aAdd( aGridFilho, { oElemento[1], oElemento[2], oElemento[3], oElemento[4], oElemento[5], oElemento[6], oElemento[7], oElemento[8],oElemento[9],oElemento[10] } )
ENDIF
NEXT
oBrw2:aArray := aGridFilho
IF LEN(aGridFilho) > 0
aTIT_02 := { [Codigo], [Descricao], [CodProd], [Seque], [Validade], [id], [Marca],[CNPJ] ,[PRECO],[CUSTO]}
Hwg_CreateArList( oBrw2, aGridFilho )
FOR nI := 1 TO Len(oBrw2:aColumns)
oBrw2:aColumns[nI]:heading := aTIT_02[nI]
IF (oBrw2:aColumns[nI]):Type == "C"
oBrw2:aColumns[nI]:nJusHead := DT_LEFT
ELSE
oBrw2:aColumns[nI]:nJusHead := DT_LEFT
ENDIF
IF (oBrw2:aColumns[nI]):Type == "C"
oBrw2:aColumns[nI]:nJusLin := DT_LEFT
oBrw2:aColumns[nI]:Picture := [@!]
ELSEIF (oBrw2:aColumns[nI]):Type == "N"
oBrw2:aColumns[nI]:nJusLin := DT_RIGHT
ENDIF
NEXT
FOR nG := 1 TO Len(oBrw2:aColumns)
IF (oBrw2:aColumns[nG]):Type == "N"
zREC_02:=oBrw2:aarray[oBrw2:nCurrent, nG]
IF VAL(SUBSTR(STR(zREC_02,12,2),11,2)) > 0
oBrw2:aColumns[nG]:Picture := [@E 999,999,999.99]
ELSE
oBrw2:aColumns[nG]:Picture := [@E 999,999,999]
ENDIF
ENDIF
NEXT
ENDIF
oBrw2:Refresh()
@ 980,600 BUTTONEX oButtonex1 CAPTION "&Abrir" SIZE 98,32 STYLE WS_TABSTOP ;
ON CLICK {|| .T. }
oButtonex1:Anchor := 12
@ 1080,600 BUTTONEX oButtonex2 CAPTION "Sai&r" SIZE 98,32 STYLE WS_TABSTOP ;
ON CLICK {|| DBCLOSEALL(), oDlg_Busca:CLOSE() }
oButtonex2:Anchor := 12
ACTIVATE DIALOG oDlg_Busca
ELSE
HWG_MSGINFO([Não existem dados para o periodo])
ENDIF
RETURN NIL
FUNCTION PORTAL_ARRAY_MUDA_ONPOSCHANGE(cARQ)
LOCAL oQUERY
aGridFilho := {} ; aVetorFilho:={}
oQuery := oServer:Execute( [SELECT * FROM WGG WHERE id_entraportal = ] + ALLTRIM(STR(cARQ,9)) )
DO WHILE ! oQuery:Eof()
aAdd( aVetorFilho, { oQuery:Fields("DESCRICAO" ):Value ,; // 1
oQuery:Fields("CODPROD" ):Value ,; // 2
oQuery:Fields("SEQUE" ):Value ,; // 3
oQuery:Fields("QTDE" ):Value ,; // 4
oQuery:Fields("UNIDA" ):Value ,; // 5
oQuery:Fields("ID_ENTRAPORTAL" ):Value ,; // 6 * CHAVE DE PESQUISA
oQuery:Fields("MARCA" ):Value ,; // 7
oQuery:Fields("PRECO" ):Value ,; // 8
oQuery:Fields("CUSTO" ):Value ,; // 9
oQuery:Fields("CNPJHOSP" ):Value }) // 10
oQuery:MoveNext()
ENDDO
oQuery:Close()
FOR EACH oElemento IN aVetorFilho
IF oElemento[6] = cARQ
aAdd( aGridFilho, { oElemento[1], oElemento[2], oElemento[3], oElemento[4], oElemento[5], oElemento[6], oElemento[7], oElemento[8], oElemento[9], oElemento[10] } )
ENDIF
NEXT
oBrw2:aArray := aGridFilho
IF LEN(aGridFilho) > 0
aTIT_02 := { [Descricao], [CodProd], [Seque], [Qtde], [Unidade], [id],[Marca],[Preco],[Custo],[CNPJ] }
Hwg_CreateArList( oBrw2, aGridFilho )
FOR nI := 1 TO Len(oBrw2:aColumns)
oBrw2:aColumns[nI]:heading := aTIT_02[nI]
IF (oBrw2:aColumns[nI]):Type == "C"
oBrw2:aColumns[nI]:nJusHead := DT_LEFT
ELSE
oBrw2:aColumns[nI]:nJusHead := DT_LEFT
ENDIF
IF (oBrw2:aColumns[nI]):Type == "C"
oBrw2:aColumns[nI]:nJusLin := DT_LEFT
oBrw2:aColumns[nI]:Picture := [@!]
ELSEIF (oBrw2:aColumns[nI]):Type == "N"
oBrw2:aColumns[nI]:nJusLin := DT_RIGHT
ENDIF
NEXT
FOR nG := 1 TO Len(oBrw2:aColumns)
IF (oBrw2:aColumns[nG]):Type == "N"
zREC_02:=oBrw2:aarray[oBrw2:nCurrent, nG]
IF VAL(SUBSTR(STR(zREC_02,12,2),11,2)) > 0
oBrw2:aColumns[nG]:Picture := [@E 999,999,999.99]
ELSE
oBrw2:aColumns[nG]:Picture := [@E 999,999,999]
ENDIF
ENDIF
NEXT
ENDIF
oBrw2:Refresh()
return nil
O que estou tentando fazer e pegar o PRECO da tabela BIODEPAES, quando houver e incluir no browse, so que o SELECT me retorna um unico item, que no caso e o unico que consta na tabela BIODEPAES.
Ja tentei de todas as maneira no Heid, mais nao consigo obter este retorno.
Observem que no pedido constam 4 itens, mais so consigo pegar 1, que e o que consta na tabela BIODEPAES.
Por favor, alguém poderia me ensinar como e que devo montar este select, para que eu consiga mesclar estas duas tabelas.
Grato.