CODEWITHSUNDEEP
sqldb2ibm-midrange
Jae Carr
Jae Carr

Reputation: 1225

Division throwing "expression in SELECT list no valid."

In our database we have some tables that use a decimal date format along these lines: yyyymmdd, all as one big decimal (ie, today would be 20130225). I'm trying to create an SQL query that pulls things back in monthly buckets. From my research it seems the best approach is to just divide the number by 100 to cut off the days on the far right (20130225/100 equals 201302 if the remainder is removed.)

I can't seem to figure out how to make it function. I started by using something like (INVOICE_DATE/100) As "INVOICE_MONTH" in both my Select and Group by. When that didn't work I reduced the Group by to "INVOICE_MONTH" and then to (INVOICE_DATE/100), neither of which worked. So I reduced the Select and Group by to just INVOICE_DATE/100 which still doesn't seem to be working.

Here's my code at current (with a couple small redactions)

Select  Count(COMPETITOR_NAME) as "STORE COUNT",
    COMPETITOR_NAME,
    INVOICE_DATE/100,
    CUSTOMER_NUMBER,   
    OVERRIDE_TYPE,
    Sum (QTY_SOLD) as "Quantity Sold",
    Sum (EXT_ORIGINAL_PRICE) as "Sum Original Price", 
    Sum (EXT_OVERRIDE_PRICE) as "Sum Override Price", 
    Sum (EXT_VARIANCE) As "Sum Variance",
    Sum (INTERNAL_COST) as "Internal Cost"
From    DA*****.DW*******.PRCOVRWK
Where   INVOICE_DATE/100 between 201002 and 201302
Group By    ROLLUP(COMPETITOR_NAME,
    INVOICE_DATE/100),
    OVERRIDE_TYPE,
    CUSTOMER_NUMBER
Order By    OVERRIDE_TYPE,
    COMPETITOR_NAME,
    INVOICE_DATE,
    CUSTOMER_NUMBER

I can't help but wonder if my Where statement is causing problems as well. At first I was just using Where INVOICE_DATE between 20100225 and 20130225, but I thought perhaps it needed to use the same thing as was in the Select statement? (Yeah, I'm kinda stretching at this point.) I've been doing my best to follow the documentation on IBMs website, but I must be reading it wrong as it doesn't seem to function as expected.

At any rate. The basic need is to be able to pull the information back in monthly buckets. So, however that might be accomplished is acceptable if you know another approach. My hope is to be able to just use decimal division to pull up the year/date combo and using that in the Group By to get the results I need.

PS- We are using v5,1 of DB2 for i.

Upvotes: 1

Views: 854

Answers (5)

Jae Carr
Jae Carr

Reputation: 1225

The answer turned out to be fairly simple, actually. The problem was that I didn't have the division properly listed in the Order By. It doesn't seem like that should have mattered, but once I did that the whole thing worked.

I did end up with a decimal answer. I decided to fix that by using rounding though, which seems to have gone well. Here's the code I ended up with for the curious:

Select  Count(COMPETITOR_NAME) as "STORE COUNT",
    COMPETITOR_NAME,
    ROUND(INVOICE_DATE/100, 0) As "Month_Date",
    CUSTOMER_NUMBER,   
    OVERRIDE_TYPE,
    Sum (QTY_SOLD) as "Quantity Sold",
    Sum (EXT_ORIGINAL_PRICE) as "Sum Original Price", 
    Sum (EXT_OVERRIDE_PRICE) as "Sum Override Price", 
    Sum (EXT_VARIANCE) As "Sum Variance",
    Sum (INTERNAL_COST) as "Internal Cost"
From    DA******.DW*******.PRCOVRWK
Where   INVOICE_DATE > 20100201
Group By    COMPETITOR_NAME,
    ROUND(INVOICE_DATE/100, 0),
    OVERRIDE_TYPE,
    CUSTOMER_NUMBER
Order By    COMPETITOR_NAME,
    ROUND(INVOICE_DATE/100, 0),
    SUM (EXT_VARIANCE),
    CUSTOMER_NUMBER

So, yeah, silly as it might seem, DB2 V5,1 just really wanted me to make the statement match between the Select,Group By and Order By. Well, nearly the same, you can use As in the Select but not anywhere else.

Hope this helps anyone else who seems to be having a similar problem.

Upvotes: 0

John Y
John Y

Reputation: 14559

Shot in the dark: Have you tried using INT to force the data to be an integer? As in INT(INVOICE_DATE/100)?

Other "spellings" of this might be INTEGER(INVOICE_DATE/100) or CAST(INVOICE_DATE/100 AS INTEGER).

Upvotes: 1

Buck Calabro
Buck Calabro

Reputation: 7633

Try to convert the decimal to a date.

select digits(date), 
  year(date(
    substr(digits(date),1,4) concat 
    ''-'' concat 
    substr(digits(date),5,2) concat
    ''-'' concat substr(digits(date),7,2))) as year,
  month(date(
    substr(digits(date),1,4) concat 
    ''-'' concat 
    substr(digits(date),5,2) concat
    ''-'' concat substr(digits(date),7,2))) as month
from ....

The key here is to convert it to character, then substring and insert the proper date separator character.

Then do it all over again for the GROUP BY. It will be the most hideous SQL statement ever, but it'll be all client side.

Upvotes: 1

Buck Calabro
Buck Calabro

Reputation: 7633

Decimal dates make our life more difficult than they need to be. May I suggest using Alan Campin's IDate functions? They'll let you easily convert those decimals into SQL date data types. Once you have that, you can extract out the year and month and do your GROUP BY on those.

Are you really on V5R1? I didn't think ROLLUP worked that early...

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I think the issue is the syntax for the rollup. Check the documentation . . . The expression group by rollup works only with all the columns. So, try this:

Group By ROLLUP (COMPETITOR_NAME,
    INVOICE_DATE/100,
    OVERRIDE_TYPE,
    CUSTOMER_NUMBER)

Or use grouping sets for different combinations.

Also, I suggest that you explicitly use integer division. So, replace \ with idiv:

invoice_date idiv 100

Upvotes: 0

Related Questions