Split List with more than a thousand lines - . NET

Asked

Viewed 193 times

0

I’m getting an error while performing this select 

sb = New StringBuilder
sb.Append(" SELECT  ")
sb.Append(" SE.NU_SERIE_NF, ")
sb.Append(" SE.NU_NF, ")
sb.Append(" SE.DH_ENTREGA, ")
sb.Append(" SE.DH_IMPORTACAO, ")
sb.Append(" SE.ARMAZEM, ")
sb.Append(" SE.TRANSPORTADORA ")
sb.Append(" FROM ")
sb.Append(" SEP_ENTREGA SE ")
sb.Append(" WHERE ")
sb.Append(" SE.NU_NF IN (" & nuNota.ToString() & ") AND ")
sb.Append(" SE.NU_SERIE_NF IN (" & nuSerieNota.Distinct().ToList().FirstOrDefault().ToString() & ") ")
dtEmail = ListarDados(sb.ToString, conexao)

Error happens on this line sb.Append(" SE.NU_NF IN (" & nuNota.ToString() & ") AND ") result having a thousand lines or more, thing that select in Oracle does not accept.

Code populating the variable nuNota:

'Monta lista com os Números de Nota
 Dim nuNota As String = ""
 Dim nuSerieNota As String = ""
 For i As Integer = 0 To listaNotaDHSolColeta.Count - 1
          nuNota += listaNotaDHSolColeta.Item(i).nuNota.ToString()
          nuSerieNota += listaNotaDHSolColeta.Item(i).nuSerieNota.ToString()
          If i < listaNotaDHSolColeta.Count - 1 Then
             nuNota += ","
             nuSerieNota += ","
          End If
 Next

I searched the net, but I could not solve this problem.

My question is, how can I divide this list into 2 parts, so I can search in select ?

.net vb.net oracle list

  • In Vb I don’t know if there is much, but something like a Count() would have the total size of the list just divided by 2 and then do the data search up to that point something like the . Take() of the c#.

  • This is a question only about Oracle. It has no relation to .NET. I recommend editing the question in order to get a more direct help.

  • I disagree, the doubt involves both sides. Since the division I am not able to do is directly in the code.

  • 1

    You tried to exchange the IN list for the SELECT (if any) that generates it ? Something like .... select .... from .... Where ... field in (select field from .....)

1 answer

1

In principle, it seems to me that the solution indicated by Motta would be the most appropriate (in the case of the elements in listaNotaDHSolColeta are also coming from a database query).

In any case, if your intention is to perform the query in stages, each of them with a group of "n" note numbers, your starting point is to divide your array into groups of "n" elements. You can use the function below to do this (as I don’t know the type of listaNotaDHSolColeta, did the generic function, but could also have used the type Object):

Function Pacotes(Of T)(lista As IEnumerable(Of T), elementos As Integer) As T()()
    Dim dicItens As New Dictionary(Of Integer, T), dicGrupos As New Dictionary(Of Integer, List(Of T)), iGrupo As Integer
    For Each item As T In lista
        dicItens(dicItens.Count) = item
    Next
    For Each kvpItem As KeyValuePair(Of Integer, T) In dicItens
        iGrupo = kvpItem.Key \ elementos
        If Not dicGrupos.ContainsKey(iGrupo) Then
            dicGrupos(iGrupo) = New List(Of T)
        End If
        dicGrupos(iGrupo).Add(kvpItem.Value)
    Next
    Return dicGrupos.Select(Function(kvp) kvp.Value.ToArray).ToArray
End Function

You can use it like this:

Dim gruposNotaDHSolColeta = Pacotes(listaNotaDHSolColeta, 1000)

Browser other questions tagged .net vb.net oracle list

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