Jump to content
  • Revista PROGRAMAR: Já está disponível a edição #60 da revista programar. Faz já o download aqui!

vpess

Querie com muitas linhas

Recommended Posts

vpess

Boas,

Preciso da vossa ajuda.

Tenho uma querie com mais de 60 linhas e com union all , mas nao estou a conseguir faze-la, vai uma ajudinha???

Function funclst1()

conexao
servico.Text = 2
procura = Trim(CStr(servico.Text))

Dim LstDados As ListItem, Cont As Long, Qtd As Long, Mdb As String
ListView2.ListItems.Clear
With ListView2
    With .ColumnHeaders
            .Clear
            .Add , , "Ref.", 2000, lvwColumnLeft
            .Add , , "Quantidade", 1200, lvwColumnLeft
            .Add , , "Nº Serie", 1600, lvwColumnLeft
            
    End With

.View = lvwReport
End With

SQL = "select m1 as ref, mq1 as qtt from ot where servico='" + procura + "' and str(mq1) >0 " & _
" Union All " & _
" select m2 as ref, mq2 as qtt from ot where servico='" + procura + "' and str(mq2) >0 " & _
" Union All " & _
" select m3 as ref, mq3 as qtt from ot where servico='" + procura + "' and str(mq3) >0 " & _
" Union All " & _
" select m4 as ref, mq4 as qtt from ot where servico='" + procura + "' and str(mq4) >0 " & _
" Union All " & _
" select m5 as ref, mq5 as qtt from ot where servico='" + procura + "' and str(mq5) >0 " & _
" Union All " & _
" select m6 as ref, mq6 as qtt from ot where servico='" + procura + "' and str(mq6) >0 " & _
" Union All " & _
" select m7 as ref, mq7 as qtt from ot where servico='" + procura + "' and str(mq7) >0 " & _
" Union All " & _
" select m8 as ref, mq8 as qtt from ot where servico='" + procura + "' and str(mq8) >0 " & _
" Union All " & _
" select m9 as ref, mq9 as qtt from ot where servico='" + procura + "' and str(mq9) >0 " & _
" Union All " & _
" select m10 as ref, mq10 as qtt from ot where servico='" + procura + "' and str(mq10) >0 " & _
" Union All " & _
" select m11 as ref, mq11 as qtt from ot where servico='" + procura + "' and str(mq11) >0 " & _
" Union All " & _
" select m12 as ref, mq12 as qtt from ot where servico='" + procura + "' and str(mq12) >0 " & _
" Union All " & _
" select m13 as ref, mq13 as qtt from ot where servico='" + procura + "' and str(mq13) >0 "

SQL = SQL & _
" Union All " & _
" select m14 as ref, mq14 as qtt from ot where servico='" + procura + "' and str(mq14) >0 " & _
" Union All " & _
" select m15 as ref, mq15 as qtt from ot where servico='" + procura + "' and str(mq15) >0 " & _
" Union All " & _
" select m16 as ref, mq16 as qtt from ot where servico='" + procura + "' and str(mq16) >0 " & _
" Union All " & _
" select m17 as ref, mq17 as qtt from ot where servico='" + procura + "' and str(mq17) >0 " & _
" Union All " & _
" select m18 as ref, mq18 as qtt from ot where servico='" + procura + "' and str(mq18) >0 " & _
" Union All " & _
" select m19 as ref, mq19 as qtt from ot where servico='" + procura + "' and str(mq19) >0 " & _
" Union All " & _
" select m20 as ref, mq20 as qtt from ot where servico='" + procura + "' and str(mq20) >0 " & _
" Union All " & _
" select m21 as ref, mq21 as qtt from ot where servico='" + procura + "' and str(mq21) >0 " & _
" Union All " & _
" select m22 as ref, mq22 as qtt from ot where servico='" + procura + "' and str(mq22) >0 " & _
" Union All " & _
" select m23 as ref, mq23 as qtt from ot where servico='" + procura + "' and str(mq23) >0 " & _
" Union All " & _
" select m24 as ref, mq24 as qtt from ot where servico='" + procura + "' and str(mq24) >0 " & _
" Union All " & _
" select m25 as ref, mq25 as qtt from ot where servico='" + procura + "' and str(mq25) >0 "

SQL = SQL & _
" Union All " & _
" select m26 as ref, mq26 as qtt from ot where servico='" + procura + "' and str(mq26) >0 " & _
" Union All " & _
" select m27 as ref, mq27 as qtt from ot where servico='" + procura + "' and str(mq27) >0 " & _
" Union All " & _
" select m28 as ref, mq28 as qtt from ot where servico='" + procura + "' and str(mq28) >0 " & _
" Union All " & _
" select m29 as ref, mq29 as qtt from ot where servico='" + procura + "' and str(mq29) >0 " & _
" Union All " & _
" select m30 as ref, mq30 as qtt from ot where servico='" + procura + "' and str(mq30) >0 " & _
" Union All " & _
" select m31 as ref, mq31 as qtt from ot where servico='" + procura + "' and str(mq31) >0 " & _
" Union All " & _
" select m32 as ref, mq32 as qtt from ot where servico='" + procura + "' and str(mq32) >0 " & _
" Union All " & _
" select m33 as ref, mq33 as qtt from ot where servico='" + procura + "' and str(mq33) >0 " & _
" Union All " & _
" select m34 as ref, mq34 as qtt from ot where servico='" + procura + "' and str(mq34) >0 " & _
" Union All " & _
" select m35 as ref, mq35 as qtt from ot where servico='" + procura + "' and str(mq35) >0 " & _
" Union All " & _
" select m36 as ref, mq36 as qtt from ot where servico='" + procura + "' and str(mq36) >0 " & _
" Union All " & _
" select m37 as ref, mq37 as qtt from ot where servico='" + procura + "' and str(mq37) >0 "

SQL = SQL & _
" Union All " & _
" select m38 as ref, mq38 as qtt from ot where servico='" + procura + "' and str(mq38) >0 " & _
" Union All " & _
" select m39 as ref, mq39 as qtt from ot where servico='" + procura + "' and str(mq39) >0 " & _
" Union All " & _
" select m40 as ref, mq40 as qtt from ot where servico='" + procura + "' and str(mq40) >0 " & _
" Union All " & _
" select m41 as ref, mq41 as qtt from ot where servico='" + procura + "' and str(mq41) >0 " & _
" Union All " & _
" select m42 as ref, mq42 as qtt from ot where servico='" + procura + "' and str(mq42) >0 " & _
" Union All " & _
" select m43 as ref, mq43 as qtt from ot where servico='" + procura + "' and str(mq43) >0 " & _
" Union All " & _
" select m44 as ref, mq44 as qtt from ot where servico='" + procura + "' and str(mq44) >0 " & _
" Union All " & _
" select m45 as ref, mq45 as qtt from ot where servico='" + procura + "' and str(mq45) >0 "


rs.Open SQL, conn, 1, 3

ListView2.ListItems.Clear

'If rs.RecordCount = 0 Then Exit Sub

   While Not rs.EOF
      Set Lst = ListView2.ListItems.Add(, , rs(0))
         
      For i = 1 To 1
            Lst.SubItems(i) = rs(i)
      Next i
     rs.MoveNext
   Wend

End Function

Obrigado

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

By using this site you accept our Terms of Use and Privacy Policy. We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.