Querie com muitas linhas

[vpess 0]

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