|
-
Sep 17th, 2010, 02:11 PM
#1
Thread Starter
Fanatic Member
 [RESOLVED] Byref Value
HI
I am passing a variable Byref, but when return It is not changed
Code:
Private Sub VerFiltroData(ByRef SSql As String)
Dim AnosInicio As String
Dim lSql As String
Dim i As Integer
'-----------------------------------------'
' Veririca Quais Anos Estão clicados '
'-----------------------------------------'
For i = 0 To lbxAnoInicio.ListCount - 1
If lbxAnoInicio.Selected(i) Then
If AnosInicio <> "" Then AnosInicio = AnosInicio + ", "
AnosInicio = AnosInicio + lbxAnoInicio.List(i)
End If
Next i
'----------------------------'
' Montaquery com anos
'----------------------------'
lSql = ""
If AnosInicio <> "" And AnosInicio <> "<Todas>" Then
lSql = lSql & " and to_number(to_char(A.DATA_INICIO,'YYYY')) in (" & AnosInicio & ")" & vbCrLf
End If
SSql = Replace(SSql, "STR_ANO_INICIO", lSql)
End Sub
STR_ANO_INICIO is constant inside query,when return procedure It already is with STR_ANO_INICIO"
-
Sep 17th, 2010, 02:22 PM
#2
Re: Byref Value
Few things.
1) Ensure SSql passed to VerFiltroData does contain "STR_ANO_INICIO"
2) Case-sensitivity? Is "STR_ANO_INICIO" in SSql upper-case?
3) How are you calling VerFiltroData, can you show us an example
-
Sep 17th, 2010, 02:27 PM
#3
Thread Starter
Fanatic Member
Re: Byref Value
Thank you
Code:
Public Sub CarregaDados_Estoque(Optional pFiltro As String)
Dim i, n As Integer
Dim AnosInicio As String
On Error Resume Next
rsEvento.Close
Set rsEvento = Nothing
On Error GoTo 0
Screen.MousePointer = vbHourglass
If isOracle Then
SSql = " SELECT ANO_INCLUSAO, " & vbCrLf
SSql = SSql & " DEPARTAMENTO, " & vbCrLf
SSql = SSql & " MARCA, " & vbCrLf
SSql = SSql & " CD_GRUPO, " & vbCrLf
SSql = SSql & " DS_GRUPO, " & vbCrLf
SSql = SSql & " ID_ITENS_ORCADOS, " & vbCrLf
SSql = SSql & " DS_ITEM_ORCADO, " & vbCrLf
SSql = SSql & " ESTOQUE_1, " & vbCrLf
SSql = SSql & " ESTOQUE_2, " & vbCrLf
SSql = SSql & " ESTOQUE_TOTAL, " & vbCrLf
SSql = SSql & " sum(NVL(case " & vbCrLf
SSql = SSql & " when MES = '01' THEN " & vbCrLf
SSql = SSql & " NVL(QT_ORCADO, 0) " & vbCrLf
SSql = SSql & " end,0)) JAN_ORCADO, " & vbCrLf
SSql = SSql & " sum(NVL(case " & vbCrLf
SSql = SSql & " when MES = '01' THEN " & vbCrLf
SSql = SSql & " NVL(QT_REALIZADO, 0) " & vbCrLf
SSql = SSql & " end,0)) JAN_REALIZADO, " & vbCrLf
SSql = SSql & " sum(NVL(case " & vbCrLf
SSql = SSql & " when MES = '02' THEN " & vbCrLf
SSql = SSql & " NVL(QT_ORCADO, 0) " & vbCrLf
SSql = SSql & " end,0)) FEV_ORCADO, " & vbCrLf
SSql = SSql & " sum(NVL(case " & vbCrLf
SSql = SSql & " when MES = '02' THEN " & vbCrLf
SSql = SSql & " NVL(QT_REALIZADO, 0) " & vbCrLf
SSql = SSql & " end,0)) FEV_REALIZADO, " & vbCrLf
SSql = SSql & " sum(NVL(case " & vbCrLf
SSql = SSql & " when MES = '03' THEN " & vbCrLf
SSql = SSql & " NVL(QT_ORCADO, 0) " & vbCrLf
SSql = SSql & " end,0)) MAR_ORCADO, " & vbCrLf
SSql = SSql & " sum(NVL(case " & vbCrLf
SSql = SSql & " when MES = '03' THEN " & vbCrLf
SSql = SSql & " NVL(QT_REALIZADO, 0) " & vbCrLf
SSql = SSql & " end,0)) MAR_REALIZADO, " & vbCrLf
SSql = SSql & " sum(NVL(case " & vbCrLf
SSql = SSql & " when MES = '04' THEN " & vbCrLf
SSql = SSql & " NVL(QT_ORCADO, 0) " & vbCrLf
SSql = SSql & " end,0)) ABR_ORCADO, " & vbCrLf
SSql = SSql & " sum(NVL(case " & vbCrLf
SSql = SSql & " when MES = '04' THEN " & vbCrLf
SSql = SSql & " NVL(QT_REALIZADO, 0) " & vbCrLf
SSql = SSql & " end,0)) ABR_REALIZADO, " & vbCrLf
SSql = SSql & " sum(NVL(case " & vbCrLf
SSql = SSql & " when MES = '05' THEN " & vbCrLf
SSql = SSql & " NVL(QT_ORCADO, 0) " & vbCrLf
SSql = SSql & " end,0)) MAI_ORCADO, " & vbCrLf
SSql = SSql & " sum(NVL(case " & vbCrLf
SSql = SSql & " when MES = '05' THEN " & vbCrLf
SSql = SSql & " NVL(QT_REALIZADO, 0) " & vbCrLf
SSql = SSql & " end,0)) MAI_REALIZADO, " & vbCrLf
SSql = SSql & " sum(NVL(case " & vbCrLf
SSql = SSql & " when MES = '06' THEN " & vbCrLf
SSql = SSql & " NVL(QT_ORCADO, 0) " & vbCrLf
SSql = SSql & " end,0)) JUN_ORCADO, " & vbCrLf
SSql = SSql & " sum(NVL(case " & vbCrLf
SSql = SSql & " when MES = '06' THEN " & vbCrLf
SSql = SSql & " NVL(QT_REALIZADO, 0) " & vbCrLf
SSql = SSql & " end,0)) JUN_REALIZADO, " & vbCrLf
SSql = SSql & " sum(NVL(case " & vbCrLf
SSql = SSql & " when MES = '07' THEN " & vbCrLf
SSql = SSql & " NVL(QT_ORCADO, 0) " & vbCrLf
SSql = SSql & " end,0)) JUL_ORCADO, " & vbCrLf
SSql = SSql & " sum(NVL(case " & vbCrLf
SSql = SSql & " when MES = '07' THEN " & vbCrLf
SSql = SSql & " NVL(QT_REALIZADO, 0) " & vbCrLf
SSql = SSql & " end,0)) JUL_REALIZADO, " & vbCrLf
SSql = SSql & " sum(NVL(case " & vbCrLf
SSql = SSql & " when MES = '08' THEN " & vbCrLf
SSql = SSql & " NVL(QT_ORCADO, 0) " & vbCrLf
SSql = SSql & " end,0)) AGO_ORCADO, " & vbCrLf
SSql = SSql & " sum(NVL(case " & vbCrLf
SSql = SSql & " when MES = '08' THEN " & vbCrLf
SSql = SSql & " NVL(QT_REALIZADO, 0) " & vbCrLf
SSql = SSql & " end,0)) AGO_REALIZADO, " & vbCrLf
SSql = SSql & " sum(NVL(case " & vbCrLf
SSql = SSql & " when MES = '09' THEN " & vbCrLf
SSql = SSql & " NVL(QT_ORCADO, 0) " & vbCrLf
SSql = SSql & " end,0)) SET_ORCADO, " & vbCrLf
SSql = SSql & " sum(NVL(case " & vbCrLf
SSql = SSql & " when MES = '09' THEN " & vbCrLf
SSql = SSql & " NVL(QT_REALIZADO, 0) " & vbCrLf
SSql = SSql & " end,0)) SET_REALIZADO, " & vbCrLf
SSql = SSql & " sum(NVL(case " & vbCrLf
SSql = SSql & " when MES = '10' THEN " & vbCrLf
SSql = SSql & " NVL(QT_ORCADO, 0) " & vbCrLf
SSql = SSql & " end,0)) OUT_ORCADO, " & vbCrLf
SSql = SSql & " sum(NVL(case " & vbCrLf
SSql = SSql & " when MES = '10' THEN " & vbCrLf
SSql = SSql & " NVL(QT_REALIZADO, 0) " & vbCrLf
SSql = SSql & " end,0)) OUT_REALIZADO, " & vbCrLf
SSql = SSql & " sum(NVL(case " & vbCrLf
SSql = SSql & " when MES = '11' THEN " & vbCrLf
SSql = SSql & " NVL(QT_ORCADO, 0) " & vbCrLf
SSql = SSql & " end,0)) NOV_ORCADO, " & vbCrLf
SSql = SSql & " sum(NVL(case " & vbCrLf
SSql = SSql & " when MES = '11' THEN " & vbCrLf
SSql = SSql & " NVL(QT_REALIZADO, 0) " & vbCrLf
SSql = SSql & " end,0)) NOV_REALIZADO, " & vbCrLf
SSql = SSql & " sum(NVL(case " & vbCrLf
SSql = SSql & " when MES = '12' THEN " & vbCrLf
SSql = SSql & " NVL(QT_ORCADO, 0) " & vbCrLf
SSql = SSql & " end,0)) DEZ_ORCADO, " & vbCrLf
SSql = SSql & " sum(NVL(case " & vbCrLf
SSql = SSql & " when MES = '12' THEN " & vbCrLf
SSql = SSql & " NVL(QT_REALIZADO, 0) " & vbCrLf
SSql = SSql & " end,0)) DEZ_REALIZADO " & vbCrLf
SSql = SSql & "FROM ( " & vbCrLf
SSql = SSql & "select to_char(A.DT_INCLUSAO, 'YYYY') ANO_INCLUSAO, " & vbCrLf
SSql = SSql & " (select Z.T$DESC " & vbCrLf
SSql = SSql & " from TRITON.TTDCMS950501 Z " & vbCrLf
SSql = SSql & " where substr(A.CD_ESTRUTURA, 1, 2) = Z.T$DEPT " & vbCrLf
SSql = SSql & " and rownum = 1 " & vbCrLf
SSql = SSql & " and substr(A.CD_ESTRUTURA, 1, 2) <> '**') DEPARTAMENTO, " & vbCrLf
SSql = SSql & " E.DS_MARCA MARCA, " & vbCrLf
SSql = SSql & " T3.CD_GRUPO, " & vbCrLf
SSql = SSql & " T3.DS_GRUPO, " & vbCrLf
SSql = SSql & " T2.ID_ITENS_ORCADOS, " & vbCrLf
SSql = SSql & " T2.DS_ITEM_ORCADO, " & vbCrLf
SSql = SSql & " 0 estoque_1, " & vbCrLf
SSql = SSql & " 0 estoque_2, " & vbCrLf
SSql = SSql & " 0 estoque_total, " & vbCrLf
SSql = SSql & " to_char(A.DT_INCLUSAO, 'MM') MES, " & vbCrLf
SSql = SSql & " T1.QT_ORCADO QT_ORCADO, " & vbCrLf
SSql = SSql & " T1.QT_REALIZADO QT_REALIZADO " & vbCrLf
SSql = SSql & " from SCETB_EVENTO A " & vbCrLf
SSql = SSql & " LEFT OUTER JOIN SCETB_AUX_MARCA E " & vbCrLf
SSql = SSql & " ON (A.CD_MARCA = E.CD_MARCA) " & vbCrLf
SSql = SSql & " INNER JOIN SCETB_ITENS_EVENTO T1 " & vbCrLf
SSql = SSql & " ON (A.CD_EVENTO = T1.CD_EVENTO) " & vbCrLf
SSql = SSql & " INNER JOIN SCETB_ITENS_ORCADOS T2 " & vbCrLf
SSql = SSql & " ON (T1.CD_ITEM_ORCADO = T2.ID_ITENS_ORCADOS) " & vbCrLf
SSql = SSql & " INNER JOIN SCETB_GRUPO_ITENS T3 " & vbCrLf
SSql = SSql & " ON (T2.CD_GRUPO = T3.CD_GRUPO) " & vbCrLf
SSql = SSql & " WHERE 1=1 " & vbCrLf
SSql = SSql & " STR_ANO_INICIO " & vbCrLf '----Ano de Inicio
SSql = SSql & ") " & vbCrLf
SSql = SSql & " GROUP BY ANO_INCLUSAO, " & vbCrLf
SSql = SSql & " DEPARTAMENTO, " & vbCrLf
SSql = SSql & " MARCA, " & vbCrLf
SSql = SSql & " CD_GRUPO, " & vbCrLf
SSql = SSql & " DS_GRUPO, " & vbCrLf
SSql = SSql & " ID_ITENS_ORCADOS, " & vbCrLf
SSql = SSql & " DS_ITEM_ORCADO " & vbCrLf
SSql = SSql & " ORDER BY 1,2,4 " & vbCrLf
Else
'SQL SERVER
End If
VerFiltroData (SSql)
Set rsEvento = New ADODB.Recordset
rsEvento.CursorLocation = adUseClient
rsEvento.Open SSql, cn, adOpenForwardOnly, adLockOptimistic
Screen.MousePointer = vbDefault
End Sub
-
Sep 17th, 2010, 02:32 PM
#4
Re: Byref Value
This is the problem: VerFiltroData (SSql)
Change to: Call VerFiltroData(SSql) or this: VerFiltroData SSql
When calling subroutines in the way you did, you are actually passing a copy of SSql because VB is treating (SSql) as an expression, not a reference.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote
