0
I’m performing a query on a table where the structure is :
Documento Produto
123 Camisa Posh rosa
127 Calça Handara 36
127 Meia Barby
158 Calça Handara 38
129 Blusa Yoll M
129 Blusa Yoll azul P
129 Short preto 36
In Python I created a course for this query, because my intention and save the results in a string separated by ,
, but in that way:
Camisa Posh rosa,
Calça Handara 36, Meia Barby ,
Calça Handara 38,
Blusa Yoll M, Blusa Yoll azul P, Short preto 36
In this case he would take the products that are on the same coupon and write on a single line, but my problem is that he is doubling the amount of registration for these documents:
Camisa Posh rosa,
Calça Handara 36, Meia Barby ,
Calça Handara 36, Meia Barby ,
Calça Handara 38,
Blusa Yoll M, Blusa Yoll azul P, Short preto 36
Blusa Yoll M, Blusa Yoll azul P, Short preto 36
Blusa Yoll M, Blusa Yoll azul P, Short preto 36
I believe I have to create a FOR to check the coupons and not repeat the data, however I am not able to play this logic in the code. Follows the code
import os
os.chdir("C:\\oracle\\product\\10.2.0\\client")
import cx_Oracle
con = cx_Oracle.connect('venda/[email protected]:1521/local')
curVendas = con.cursor()
curVendaProd = con.cursor()
base = ''
curVendas.execute('select * from base_venda')
for vendas in curVendas:
print(vendas)
quantidade = curVendaProd.execute('select produto from base_venda where documento = ' + str(vendas[0]))
i = 1
for produtos in curVendaProd:
print(produtos)
if (i == quantidade) :
base = base + produtos[0]
else:
base = base + produtos[0] + ','
i += 1
base = base + '\n'
arquivo = open("base_import.csv", "w")
arquivo.write(base)
arquivo.close()
You don’t need to make the logic in Python, because you can already get the desired result directly from the database by doing the
GROUP BY
by the document number and concatenating the products.– Woss
Possible duplicate of Multiple results in a single record - Oracle
– Woss
Anderson thanks for the interaction really using the concatena LISTAGG as I want but returns this error due to having many record.ora-01489 result of string concatenation is Too long
– Andressa M
Did the
group by
?– Woss
Yes! If I use a record quantity constraint works normally, however if form on top of base it returns this error. I’m researching that there is a xmlagg function for base with many record, I’m seeing how apply.
– Andressa M
The
group by
should already solve the problem of repeated records in the result. If you are still repeating, you must be doing something wrong.– Woss
Actually it solved, I have no more record repeated, I just can’t see all the data in the query because it presents the error I told you, actually the problem is not in the script you sent but in my base have many records.
– Andressa M