So I have this strange problem, that I don't understand.
I have a query with a number of columns. Most importantly:
a column with (order)lines, ranging form 000 to 900 as text. (these say somehting about the subtype of the order)
a column with articletype, as text
a column with orderquantity, as a number
a column with price, as a decimal number
As I need an orderprice, I made a conditional column Orderprice: Orderquantity x price
I also want a total for a specific artikel, so I made another conditional column Specificsales: IF Articletype = "text" then Orderprice
This runs peachy... I get the total sales price and the price for my specific article. But here is where it gets interesting:
I have left the data type for Orderprice and Specificsales standard/undefinded. When I now change the data type for Orderprice to fixed decimal then Specificsales goes to null in all fields (but Orderprice remains OK). If, however, I chose decimal, then I have no issues.
The data type for Specificsales seems irrelevant to this issue. I can set it to fixed decimal or decimal but the result seems depended on the setting in Orderprice. So even though Orderprice shows the correct value in both settings, Specificsales only shows the correct value (regardless of it’s own type) when Orderprice is not changed or set to decimal. The order in which I do the type change or add the extra column doesn’t seem to have an effect.
But it gets even more interesting 😉
For another selection, I want to use the lines for a specific group of subtypes. Specifically, I want the price for every line below 800. However, since this is text, I need to change this into a number. So I do a type change on this column, and then.....
My Specificsales goes to null in every field?! It does not use the lines colum; the Orderprice still runs smoothly. What gives?
If I change the lines column back to text, my Specificsales runs fine again.
If I duplicate the lines column, rename it and change that to numbers, Specificsales goes to null again.
Neither articletype, orderquantity or price has any dependency on the lines column.
Baffled and slightly frustrated at this point. Any suggestions on what I'm missing?
