Questions about null fields.

[timothyjlaurent]

Hi I love this function although I had a difficult time getting it working for some tables initially.

I had to make the following change to the program to get it working :

            #fieldType = ("numeric(%i, %i)" %(numericFieldWidth, columnScale))
            fieldType = ("double precision")

I am wondering now how I can get NULL values to show up?

    # This trick makes possible to get rid of the null values in the pivoted tables.
    # TODO: Consider to make it optional, with a parameter for it.

    replaceZeros = ['coalesce("{0}",0) as "{0}"'.format(destColumn["columns"]) for destColumn in destColumns]
    replaze0 = ','.join(replaceZeros)

What should I do here to make null values show up instead of '0'

Thanks for the great function and any help.

Next project for me is to pivot varchar fields.

[timothyjlaurent]

Ok I was able to get this working.

replaceZeros = '"{0}" as "{0}"'.format(destColumn["columns"]) for destColumn in destColumns]

[jtornero]

@timothyjlaurent Thank you for your interest, Timothy. Unfortunately, I am not able right now to spend much time on this, but I hope in some weeks I'll be able to play... Anyway, for the first part: in principle the function is supposed to manage both numeric as well as double data. Don't know which and where were the errors you get. It would be interesting to know, providing that the intention behind

fieldType = ("numeric(%i, %i)" %(numericFieldWidth, columnScale))

is to return column values with the same data type. Of course, using double instead of numeric, you will get rid of errors, but that is not the idea. Maybe it is a good idea for you to show the errors and a sample of the data, so we could se how to manage that case or maybe make a general fix. I remember that getting the type of field from the database was not trivial and, in some cases, could mislead you.

About the second part, that's just matter of adding another parameter to the function and process it in a proper way, something like (providing we set a new function parameter showZeros)

if showZeros:
    replaceZeros = ['coalesce("{0}",0) as "{0}"'.format(destColumn["columns"]) for destColumn indestColumns]
    replaze0 = ','.join(replaceZeros)
else:
    replaceZeros = '"{0}" as "{0}"'.format(destColumn["columns"]) for destColumn in destColumns]

This would be, of course, nice. I programmed the function to show zeros just because if you want to calculate on the new table/view, presence of nulls cold make bad things happen (i.e. 1+2+null=null). Also, if you export your table/view to csv to use it in a spreadsheet the effect could be the same.

Another question is... maybe too many parameters?

Thank you vvery much for your interest, best regards,

Jorge Tornero