SQL - Extract decimal portion of a number...

Got a really early start today arriving on site at 6:30am. My current task is to output sales transaction history to three XML documents. The history is a compilation of daily sales from 300 retail stores that are stored on iSeries in a DB2 table.

After some discussion with the IT Manager it was decided that we would add records to a new file during nightly processing. This file will mimic a file currently in the process except for added status and date/time stamp fields for use in the MidRetail processing.

I first have to determine how I can identify sales based on three categories; regular, promo and markdown. The file does not contain proper coding to identify these types of sales, if it did this would be to easy.

One clue I am given is that markdown’s all end with 99 cents and there is a discount code but not consistent. The first question is how do I get at the number to the right of the decimal place. Should be easy I should be able to do this mathematically but do not know the formula. I want to be able to do this with SQL and did not take long to find answers like Floor() and PARSENAME but not exactly what I am looking for. I went over all the BIF’s again and did not see any silver bullets. Thought this was going to be easy, not!

Then I found an obscure post on a forum about CAST function. It was not exactly what I needed but showed promise. After tinkering around with the function taking care of the positive/negative potential issue I was able to come up with the following, vnew is the selling price and vold is the orginal price:

abs(cast(vnew as int)- vnew)   

Now armed with something that will work I created these three statements:

//Regular
Select vstr, vdte, vcls, vven, vsty, vclr, vsiz, vqty, vold, vnew,abs(cast(vnew as int)- vnew) as cents from rbryant/midsalespf
where abs(cast(vnew as int)- vnew) <> .99
order by vstr, vdte, vcls, vven, vsty, vclr;

//Promo
Select vstr, vdte, vcls, vven, vsty, vclr, vsiz, vqty, vold, vnew,abs(cast(vnew as int)- vnew) as cents from rbryant/midsalespf
where  vnew <> vold
order by vstr, vdte, vcls, vven, vsty, vclr;

//Markdown
Select vstr, vdte, vcls, vven, vsty, vclr, vsiz, vqty, vold, vnew,abs(cast(vnew as int)- vnew) as cents from rbryant/midsalespf
where abs(cast(vnew as int)- vnew) = .99
order by vstr, vdte, vcls, vven, vsty, vclr;

I add sum(vqty) and Group by to the equation I have my daily sales summed to store, date, class, vendor, style and color. I add size and I’ll have my summary to the size. I am sure the weekly summary to the color level will be easy.

After analyzing the data a few different ways and counting up records to make sure nothing missed I took my results to the IT Manager. Looks good and we can move forward.

Tomorrow I look into how to add these statements in my RPGLE program to create XML documents.  

~Richard