RE: Best method using SQL to combine date fields (day, month, year)

Hi Dan,

I don't your query would return the correct results... I think, to do
this correctly, you need three test cases...

1> year(date) > x.yyyy
2> year(date) = x.yyyy and month(date) > x.mm
3> year(date) = x.yyyy and month(date) = x.mm and day(date) >= x.dd

Eric DeLong


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dan Kimmel
Sent: Monday, October 20, 2008 3:27 PM
To: Midrange Systems Technical Discussion
Subject: RE: Best method using SQL to combine date fields (day, month,
year)

If all you want to do is find the records greater than or equal to a
date, you could do this:

select * from somelib.somefile x where
year(date)>= x.yyyy or (year(date)= x.yyyy and month(date)>= x.mm) or
(year(date)=x.yyyy and month(date)=x.mm and day(date)>= x.dd;

Then SQL could do the whole thing with an index scan if you had an index
over yyyy, mm, dd. If it was me, I'd build the sql statement in my
program and replace the year, month, day functions with constants.


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Bradley V. Stone
Sent: Monday, October 20, 2008 2:11 PM
To: Midrange Systems Technical Discussion
Subject: RE: Best method using SQL to combine date fields (day, month,
year)

See, what I was thinking I could do was

date >= ((yyyy * 10000) + (mm * 100) + dd)

Probably not, though.

Bradley V. Stone
BVSTools - www.bvstools.com
eRPG SDK - www.erpgsdk.com

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of rob@xxxxxxxxx
Sent: Monday, October 20, 2008 2:03 PM
To: Midrange Systems Technical Discussion
Subject: Re: Best method using SQL to combine date fields (day, month,
year)


H'mm I wonder if instead of a trigger you created an entirely new PF.

And have a LF on it named the same as the old PF. But, I don't know
if DDS has the flexibility that DDL has in creating derived fields.
For example how would you do the CREATE VIEW following in DDS?

CREATE TABLE myTable
(RealDateField date,
...)

create view qtemp/myview as (select
year(RealDateField) as yyyy,
month(RealDateField) as mm,
day(RealDateField) as dd
from qtemp/mytable )

SELECT * FROM MYVIEW
....+....1....+....2....+....3....+....4....+.
YYYY MM DD
2,008 10 20
******** End of data ********


Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Charles Wilt" <charles.wilt@xxxxxxxxx> Sent by:
midrange-l-bounces@xxxxxxxxxxxx
10/20/2008 02:53 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
"Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx> cc

Subject
Re: Best method using SQL to combine date fields (day, month, year)






Then unless you are on 6.1, you are SOL.

You're going to have to do it the hard way...

select * from mytable
where (myYYYY > :startYear
or ( myYYYY = :startYear
and myMM > :startMonth )
or ( myYYYY = :startYear
and myMM = :startMonth
and myDay >= :startDay )
and (myYYYY < :endYear
or ( myYYYY = :endYear
and myMM < :endMonth )
or ( myYYYY = :endYear
and myMM = :endMonth
and myDay <= :endDay )

What fun!

You could try creating a new keyed logical that combines the fields
and is keyed on the combined field. I don't know if the CQE or SQE
will make use of it. Perhaps the CQE if you specify the logical in
the query.... maybe Elvis or Birgitta would know.


Another option that's more work up front but better in the long run.
Create a new file with an actual date field. Change the current
physical to a logical over the new file. Add a *BEFORE UPDATE/INSERT
trigger to the file to keep the date field in sync with the component
fields. All of which should be doable without having to recompile any

applications. You could even try without the triggers, simply have
the logical define the component fields using SST. But IIRC that
logical would be read only.

Good luck!

Charles


On Mon, Oct 20, 2008 at 2:19 PM, Bradley V. Stone
<bvstone@xxxxxxxxxxxx>
wrote:

Yes, an index would be needed.

Bradley V. Stone
BVSTools - www.bvstools.com
eRPG SDK - www.erpgsdk.com

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Charles Wilt
Sent: Monday, October 20, 2008 11:35 AM
To: Midrange Systems Technical Discussion
Subject: Re: Best method using SQL to combine date fields (day,
month,
year)


Create a UDF (download Alan's(?) iDate UDF)...
Create a view that uses the UDF...

Now the problem, if you're intending to use this date as a
selection criteria, an index will not be used even if one exists
over the three columns. (Unless you're at v6r1, then you can build

an index with the calculated value)

Do you need the index for performance?

Charles

On Mon, Oct 20, 2008 at 12:17 PM, Bradley V. Stone
<bvstone@xxxxxxxxxxxx> wrote:

Ok, building some dynamic SQL statements.

Got to a file that has date separated into year, month and day

fields. Ugh.

So, wondering the best/easiest way to combine these so I can

use them in an

SQL statement? Hopefully some magical SQL command that I can

use inline?

Thanks!

Bradley V. Stone
BVSTools - www.bvstools.com
eRPG SDK - www.erpgsdk.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)

mailing list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.