YTD Criteria

[beckyh]

I would like to write a SQL statement using DB2 SQL syntax. More specifically I need to date to be based on Year To Date criteria. Any ideas where I begin? My date field is alphanumeric. eek!

 

[MarcLodge]

Becky,
Could you be a little more specific? Please let us know what criteria you are passing to your SQL, and what response you are expecting. Don't worry about alphanumeric etc., but just let us know what it is you are trying to achieve.

Marc

 

[beckyh]

No records are being returned.
Here is my SQL

SELECT BPKQCD as Unit, BPKRCD as Nature, BSBEVA as Budget, BPBAVA/100 as Actual, BPGDNB, (BSBEVA-(BPBAVA/100))as Variance, AHADNA
FROM YABPREP LEFT OUTER JOIN YABSREP
ON BPKQCD = BSJ6CD AND BPKRCD = BSJ7CD and BPGDNB=BSNBCD
INNER JOIN YAAHREP ON BPKRCD = AHAFCD
GROUP BY BPKQCD, BPKRCD, BPGDNB, BPBAVA, bsbeva, AHADNA
HAVING (((YABPREP.BPKQCD)='6121') AND ((YABPREP.BPGDNB)like '%05'))


UNION (SELECT BSJ6CD, BSJ7CD, BSBEVA as Budget, BPBAVA/100 as Actual, BSNBCD, (BSBEVA-(BPBAVA/100))as Variance, AHADNA
FROM YABPREP RIGHT OUTER JOIN YABSREP
ON BPKQCD = BSJ6CD AND BPKRCD = BSJ7CD and BPGDNB=BSNBCD
INNER JOIN YAAHREP ON BSJ7CD = AHAFCD
GROUP BY BSJ6CD, BSJ7CD, BSNBCD, BSBEVA,BPBAVA, AHADNA
HAVING (((YABSREP.BSJ6CD)='6121') AND ((YABSREP.BSNBCD)like '%05'))

 

[gregsimpson]

beckyh,

which bit exactly in this SQL is the date. Why is your date field no defined as a date, within db2. Also are you aware you can cast from 1 datatype to another. so you could cast your char field as a date.

Does that help?

Cheers
Greg

 

[beckyh]

Date fields are BPGDNB and BSNBCD.

These are IFM files. Yuck! It was written in England and is NOT user friendly for programmers.

Cast....hum.....that's a good thought. I will look into that.

 

[beckyh]

I am trying to figure out the CAST function. I get the following error:

IBM][iSeries Access ODBC Driver][DB2 UDB]SQ20226 - The maximum number of stacked diagnostics areas has been exceeded.


SQL = "SELECT BPKQCD as Unit, BPKRCD as Nature, BSBEVA as Budget, BPBAVA/100 as Actual, CAST(BPGDNB AS DATE), (BSBEVA-(BPBAVA/100))as Variance, AHADNA FROM YABPREP LEFT OUTER JOIN YABSREP ON BPKQCD = BSJ6CD AND BPKRCD = BSJ7CD and BPGDNB=BSNBCD INNER JOIN YAAHREP ON BPKRCD = AHAFCD GROUP BY BPKQCD, BPKRCD, BPGDNB, BPBAVA, bsbeva, AHADNA HAVING (YABPREP.BPGDNB ='APR05') UNION SELECT BSJ6CD, BSJ7CD, BSBEVA, BPBAVA/100, CAST(BSNBCD AS DATE), (BSBEVA-(BPBAVA/100)), AHADNA FROM YABPREP RIGHT OUTER JOIN YABSREP ON BPKQCD = BSJ6CD AND BPKRCD = BSJ7CD and BPGDNB=BSNBCD INNER JOIN YAAHREP ON BSJ7CD = AHAFCD GROUP BY BSJ6CD, BSJ7CD, BSNBCD, BSBEVA,BPBAVA, AHADNA HAVING (YABSREP.BSNBCD ='APR05')"

 

[blom0344]

becky,

There is something inherently wrong with your SQL. Group by and Having clauses are mandatory when performing aggregates (group by part) and setting conditions on the aggregate itself (having part).

The SQL should be rewritten with simple 'where' clauses to start with , cause you are not performing aggregation at all..

If your date fields are formatted as a valid date format you may as well try to use the DATE function instead of CASTing one type to another...

Ties Blom
Information analyst

 

[beckyh]

I would tend to disagree. This forum doesn't give enough background info as to what I am trying to accomplish, as well as all the SQL changes I have made already to my code. I do need to GROUP BY my recordset, as well as set conditions on that aggregate.

The date fields are alphanumeric, looking like this APR05. I am trying to write a query that will give me YTD records. I am having trouble writing criteria.

Does that help clear up?

 

[BrianTyler]

I agree with Ties, I think he means:

SELECT
BPKQCD as Unit
,BPKRCD as Nature
,BSBEVA as Budget
,BPBAVA/100 as Actual
,BPGDNB
,BSBEVA-(BPBAVA/100))as Variance
,AHADNA
FROM
YABPREP LEFT OUTER JOIN YABSREP ON BPKQCD = BSJ6CD AND BPKRCD = BSJ7CD and BPGDNB=BSNBCD
INNER JOIN YAAHREP ON BPKRCD = AHAFCD
where
(((YABPREP.BPKQCD)='6121') AND ((YABPREP.BPGDNB)like '%05' ))

UNION

(SELECT
BSJ6CD
,BSJ7CD
,BSBEVA as Budget
,BPBAVA/100 as Actual
,BSNBCD
,(BSBEVA-(BPBAVA/100))as Variance
,AHADNA
FROM
YABPREP RIGHT OUTER JOIN YABSREP ON BPKQCD = BSJ6CD AND BPKRCD = BSJ7CD and BPGDNB=BSNBCD
INNER JOIN YAAHREP ON BSJ7CD = AHAFCD
where
(((YABSREP.BSJ6CD)='6121') AND ((YABSREP.BSNBCD)like '%05'))

You could also try using some OR operators to avoid the UNION, though this could have an effect on optimization.

Brian

 

[blom0344]

Thanks Brian, I was a bit lazy not rewriting it myself


Ties Blom
Information analyst

 

[beckyh]

The above SQL is not working, I had to remove some parantheses.

I have tried using WHERE as well as HAVING. Once I get the SQL to compile, neither keyword will return any records. Something is wrong with the criteria.

 

[blom0344]

Uuh, are you working on an AS400? In my experience most character based fields are then of the 'Char' type with fixed length types. This would indicate the use of TRIM functions within conditions...

Ties Blom
Information analyst

 

[beckyh]

I am sending this SQL to our ISeries via an ODBC connection through client Access.

 

[beckyh]

I got it!! I added another % sign after my 5 in the criteria.

 

[gregsimpson]

Bad choice of terminology from me I guess earlier on. I was trying to point you towards the date function, which it would appear you don't need anyway, as you're basically only interested in particular characters in a aplhanumeric string, as far as I can tell, which just happen to be your systems representation of a date.

Cheers
Greg