Re: [U2] Searching for 'Bad' Dates

2013-01-30 Thread Wols Lists
On 29/01/13 23:36, Wjhonson wrote:
 Just use the NUM function in an I-Descript.
 Any external format, has to have at least one non-numeric in it.
 
I was thinking that. Just set field 2 to

NUM( @RECORD( x))

where x is your field number (provided it's not multi-value of course)
and then select for your i-desc is 0

Cheers,
Wol
___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users


Re: [U2] Searching for 'Bad' Dates

2013-01-29 Thread Greg Coelho
All,

Can anyone help me with a query to find instances where an external date 
has been written to a date field?  I would like to use ...WITH  
MPAR.STATUS.DATE LIKE ..
./... (the dates would be in the fomat: '12/16/12', but this field is set 
with external formatting so I end up bringing back everything.  Is there a 
way I can 'turn off' formatting for this field within the query?

Thanks,

Greg
___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users


Re: [U2] Searching for 'Bad' Dates

2013-01-29 Thread Lunt, Bruce
Use a dict item for the field that does not have the date conversion.
Usually F1 or F2 or whatever are just raw formatted and will work.

Bruce 

-Original Message-
From: u2-users-boun...@listserver.u2ug.org
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Greg Coelho
Sent: Tuesday, January 29, 2013 2:38 PM
To: u2-users@listserver.u2ug.org
Subject: Re: [U2] Searching for 'Bad' Dates

All,

Can anyone help me with a query to find instances where an external date
has been written to a date field?  I would like to use ...WITH
MPAR.STATUS.DATE LIKE ..
./... (the dates would be in the fomat: '12/16/12', but this field is
set with external formatting so I end up bringing back everything.  Is
there a way I can 'turn off' formatting for this field within the query?

Thanks,

Greg
___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users
___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users


Re: [U2] Searching for 'Bad' Dates

2013-01-29 Thread Israel, John R.
Write a new dict w/o the conversion code and select on it.

John Israel

Sent from my iPhone

On Jan 29, 2013, at 5:38 PM, Greg Coelho coe...@american.edu wrote:

 All,
 
 Can anyone help me with a query to find instances where an external date 
 has been written to a date field?  I would like to use ...WITH  
 MPAR.STATUS.DATE LIKE ..
 ./... (the dates would be in the fomat: '12/16/12', but this field is set 
 with external formatting so I end up bringing back everything.  Is there a 
 way I can 'turn off' formatting for this field within the query?
 
 Thanks,
 
 Greg
 ___
 U2-Users mailing list
 U2-Users@listserver.u2ug.org
 http://listserver.u2ug.org/mailman/listinfo/u2-users
___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users


Re: [U2] Searching for 'Bad' Dates

2013-01-29 Thread Peter Cheney
How about a DICT item that performs a match like 2n'/'2n'/'2n on that field?

-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Greg Coelho
Sent: Wednesday, 30 January 2013 8:38
To: u2-users@listserver.u2ug.org
Subject: Re: [U2] Searching for 'Bad' Dates

All,

Can anyone help me with a query to find instances where an external date has 
been written to a date field?  I would like to use ...WITH MPAR.STATUS.DATE 
LIKE ..
./... (the dates would be in the fomat: '12/16/12', but this field is set with 
external formatting so I end up bringing back everything.  Is there a way I can 
'turn off' formatting for this field within the query?

Thanks,

Greg
___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users

 ---
Note: 
This email (inc all attachments) is for the use of the intended recipient(s) 
only.
Privileged or confidential information may be contained in this communication. 
If you have received this email in error, please notify the sender immediately 
and then delete all copies of this message from your computer network. If you 
are not the intended recipient, you must not keep, use, disclose, copy or 
distribute this email without the author's prior permission. If you are the 
intended recipient and you do not wish to receive similar electronic messages 
from us in future, then please respond to the sender to this effect. 
We have taken precautions to minimise the risk of transmitting software 
viruses, but advise you to carry out your own virus checks on this email and 
its attachments. We do not accept liability for any loss or damage caused by 
software viruses and do not represent that this transmission is free from 
viruses or other defects. 
Firstmac Limited (ABN 59 094 145 963) (AFSL 290600) 
 ---
___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users


Re: [U2] Searching for 'Bad' Dates

2013-01-29 Thread Bill Haskett

Greg:

Can you do something like:

   :select {FileName} WITH EVAL OCONV(MPAR.STATUS.DATE, 'D') = 

This works fine on UniData in PICK mode, where an invalid conversion 
returns a null (an empty string).


HTH,

Bill



-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Greg Coelho
Sent: Wednesday, 30 January 2013 8:38
To: u2-users@listserver.u2ug.org
Subject: Re: [U2] Searching for 'Bad' Dates

All,

Can anyone help me with a query to find instances where an external date has been 
written to a date field?  I would like to use ...WITH MPAR.STATUS.DATE LIKE ..
./... (the dates would be in the fomat: '12/16/12', but this field is set with 
external formatting so I end up bringing back everything.  Is there a way I can 
'turn off' formatting for this field within the query?

Thanks,

Greg

___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users


Re: [U2] Searching for 'Bad' Dates

2013-01-29 Thread Greg Coelho
Bill,

This is only returning those records that do not have a value in 
MPAR.STATUS.DATE.  Unfortunately I looking for records where an external 
date has been written instead of the internal date. 

Greg



From:   Bill Haskett wphask...@advantos.net
To: U2 Users List u2-users@listserver.u2ug.org
Date:   01/29/2013 06:01 PM
Subject:Re: [U2] Searching for 'Bad' Dates
Sent by:u2-users-boun...@listserver.u2ug.org



Greg:

Can you do something like:

:select {FileName} WITH EVAL OCONV(MPAR.STATUS.DATE, 'D') = 

This works fine on UniData in PICK mode, where an invalid conversion 
returns a null (an empty string).

HTH,

Bill



-Original Message-
From: u2-users-boun...@listserver.u2ug.org [
mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Greg Coelho
Sent: Wednesday, 30 January 2013 8:38
To: u2-users@listserver.u2ug.org
Subject: Re: [U2] Searching for 'Bad' Dates

All,

Can anyone help me with a query to find instances where an external date 
has been written to a date field?  I would like to use ...WITH 
MPAR.STATUS.DATE LIKE ..
./... (the dates would be in the fomat: '12/16/12', but this field is set 
with external formatting so I end up bringing back everything.  Is there a 
way I can 'turn off' formatting for this field within the query?

Thanks,

Greg

___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users

___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users


Re: [U2] Searching for 'Bad' Dates

2013-01-29 Thread Bill Haskett

Greg:

That was the point.  What happens is an externally formatted date isn't 
OCONV'd, if the dbms is configured to return an empty string for a bad 
conversion.  I've got this set up on our systems like this. So, when I 
run the following code:


2 Dev (0)- BPTEST CRT 'OConverted Date--: ' : OCONV('01/29/13', 'D') ; END

Compiling Unibasic: SAVEDLISTS\BpTest_547690521 in mode 'p'.
compilation finished
OConverted Date--:

...I get what you're looking for, an empty string for dates filed in 
external format.  In UniData I need UDT.OPTIONS 56 ON.


Bill


- Original Message -
*From:* coe...@american.edu
*To:* U2 Users List u2-users@listserver.u2ug.org
*Date:* 1/29/2013 3:09 PM
*Subject:* Re: [U2] Searching for 'Bad' Dates

Bill,

This is only returning those records that do not have a value in
MPAR.STATUS.DATE.  Unfortunately I looking for records where an external
date has been written instead of the internal date.

Greg



From:   Bill Haskett wphask...@advantos.net
To: U2 Users List u2-users@listserver.u2ug.org
Date:   01/29/2013 06:01 PM
Subject:Re: [U2] Searching for 'Bad' Dates
Sent by:u2-users-boun...@listserver.u2ug.org



Greg:

Can you do something like:

 :select {FileName} WITH EVAL OCONV(MPAR.STATUS.DATE, 'D') = 

This works fine on UniData in PICK mode, where an invalid conversion
returns a null (an empty string).

HTH,

Bill



-Original Message-
From: u2-users-boun...@listserver.u2ug.org [
mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Greg Coelho
Sent: Wednesday, 30 January 2013 8:38
To: u2-users@listserver.u2ug.org
Subject: Re: [U2] Searching for 'Bad' Dates

All,

Can anyone help me with a query to find instances where an external date
has been written to a date field?  I would like to use ...WITH
MPAR.STATUS.DATE LIKE ..
./... (the dates would be in the fomat: '12/16/12', but this field is set
with external formatting so I end up bringing back everything.  Is there a
way I can 'turn off' formatting for this field within the query?

Thanks,

Greg

___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users

___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users


___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users


Re: [U2] Searching for 'Bad' Dates

2013-01-29 Thread Greg Coelho
Aah, I see.  I'll need to check our UDT.OPTIONS.  I think that I've 
managed to get this one worked out by creating a DICT entry without 
formating.  This gave me the list that I need.  In that we are migrating 
more and more data to SQL Server which really chokes on a bad date I'll 
need to get better at flushing these out.

Thanks,

Greg



From:   Bill Haskett wphask...@advantos.net
To: U2 Users List u2-users@listserver.u2ug.org
Date:   01/29/2013 06:17 PM
Subject:Re: [U2] Searching for 'Bad' Dates
Sent by:u2-users-boun...@listserver.u2ug.org



Greg:

That was the point.  What happens is an externally formatted date isn't 
OCONV'd, if the dbms is configured to return an empty string for a bad 
conversion.  I've got this set up on our systems like this. So, when I 
run the following code:

2 Dev (0)- BPTEST CRT 'OConverted Date--: ' : OCONV('01/29/13', 'D') ; 
END

Compiling Unibasic: SAVEDLISTS\BpTest_547690521 in mode 'p'.
compilation finished
OConverted Date--:

...I get what you're looking for, an empty string for dates filed in 
external format.  In UniData I need UDT.OPTIONS 56 ON.

Bill


- Original Message -
*From:* coe...@american.edu
*To:* U2 Users List u2-users@listserver.u2ug.org
*Date:* 1/29/2013 3:09 PM
*Subject:* Re: [U2] Searching for 'Bad' Dates
 Bill,

 This is only returning those records that do not have a value in
 MPAR.STATUS.DATE.  Unfortunately I looking for records where an external
 date has been written instead of the internal date.

 Greg



 From:   Bill Haskett wphask...@advantos.net
 To: U2 Users List u2-users@listserver.u2ug.org
 Date:   01/29/2013 06:01 PM
 Subject:Re: [U2] Searching for 'Bad' Dates
 Sent by:u2-users-boun...@listserver.u2ug.org



 Greg:

 Can you do something like:

  :select {FileName} WITH EVAL OCONV(MPAR.STATUS.DATE, 'D') = 

 This works fine on UniData in PICK mode, where an invalid conversion
 returns a null (an empty string).

 HTH,

 Bill

 

 -Original Message-
 From: u2-users-boun...@listserver.u2ug.org [
 mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Greg Coelho
 Sent: Wednesday, 30 January 2013 8:38
 To: u2-users@listserver.u2ug.org
 Subject: Re: [U2] Searching for 'Bad' Dates

 All,

 Can anyone help me with a query to find instances where an external date
 has been written to a date field?  I would like to use ...WITH
 MPAR.STATUS.DATE LIKE ..
 ./... (the dates would be in the fomat: '12/16/12', but this field is 
set
 with external formatting so I end up bringing back everything.  Is there 
a
 way I can 'turn off' formatting for this field within the query?

 Thanks,

 Greg

 ___
 U2-Users mailing list
 U2-Users@listserver.u2ug.org
 http://listserver.u2ug.org/mailman/listinfo/u2-users

 ___
 U2-Users mailing list
 U2-Users@listserver.u2ug.org
 http://listserver.u2ug.org/mailman/listinfo/u2-users

___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users

___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users


Re: [U2] Searching for 'Bad' Dates

2013-01-29 Thread Tony Gravagno
 From: Peter Cheney
 How about a DICT item that performs a match like 2n'/'2n'/'2n on
 that field?

Better to simply look for anything non-numeric.

___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users


Re: [U2] Searching for 'Bad' Dates

2013-01-29 Thread Wjhonson
Just use the NUM function in an I-Descript.
Any external format, has to have at least one non-numeric in it.

 

 

 

-Original Message-
From: Bill Haskett wphask...@advantos.net
To: U2 Users List u2-users@listserver.u2ug.org
Sent: Tue, Jan 29, 2013 3:01 pm
Subject: Re: [U2] Searching for 'Bad' Dates


Greg:

Can you do something like:

:select {FileName} WITH EVAL OCONV(MPAR.STATUS.DATE, 'D') = 

This works fine on UniData in PICK mode, where an invalid conversion 
returns a null (an empty string).

HTH,

Bill



-Original Message-
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org] 
On Behalf Of Greg Coelho
Sent: Wednesday, 30 January 2013 8:38
To: u2-users@listserver.u2ug.org
Subject: Re: [U2] Searching for 'Bad' Dates

All,

Can anyone help me with a query to find instances where an external date has 
been written to a date field?  I would like to use ...WITH MPAR.STATUS.DATE 
LIKE 
..
./... (the dates would be in the fomat: '12/16/12', but this field is set with 
external formatting so I end up bringing back everything.  Is there a way I can 
'turn off' formatting for this field within the query?

Thanks,

Greg

___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users

 
___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users


Re: [U2] Searching for 'Bad' Dates

2013-01-29 Thread Richard Lewis
How about:

SELECT fname WITH MPAR.STATUS.DATE CONV  = [/]

Since you seem to know exactly the kind of erroneous data you want to find.


On Tue, Jan 29, 2013 at 3:38 PM, Greg Coelho coe...@american.edu wrote:

 All,

 Can anyone help me with a query to find instances where an external date
 has been written to a date field?  I would like to use ...WITH
 MPAR.STATUS.DATE LIKE ..
 ./... (the dates would be in the fomat: '12/16/12', but this field is set
 with external formatting so I end up bringing back everything.  Is there a
 way I can 'turn off' formatting for this field within the query?

 Thanks,

 Greg
 ___
 U2-Users mailing list
 U2-Users@listserver.u2ug.org
 http://listserver.u2ug.org/mailman/listinfo/u2-users

___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users