Re: ACS Run SQL Script Monitor for SQL Error

[K Crawford <kscx3ksc@xxxxxxxxx>]

Thanks for the tips.
I like the SYSTOOLS.VALIDATE_DATA option a lot unfortunately I am not at
TR8 for V7R3.
I did get the joblog to work.  I wrote a cte to find the rows in the job
log with the error message.  Substring the text field to get the row number.
a second cte to get rid of the duplicates (distinct).
then the SQL to join those with the table in issue using the RRN.
Worked great.

Thanks for your help

On Wed, Jul 7, 2021 at 2:10 PM Rob Berendt <rob@xxxxxxxxx> wrote:

> Any chance you can redefine this file by using DDL instead DDS?  You won't
> have that problem again.
>
> Rob Berendt
> --
> IBM Certified System Administrator - IBM i 6.1
> Group Dekko
> Dept 1600
> Mail to:  7310 Innovation Blvd, Suite 104
>           Ft. Wayne, IN 46818
> Ship to:  7310 Innovation Blvd, Dock 9C
>           Ft. Wayne, IN 46818
> http://www.dekko.com
>
>
> -----Original Message-----
> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of K
> Crawford
> Sent: Wednesday, July 7, 2021 12:35 PM
> To: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> Subject: ACS Run SQL Script Monitor for SQL Error
>
> CAUTION: This email originated from outside of the organization. Do not
> click links or open attachments unless you recognize the sender and know
> the content is safe.
>
>
> I have a SQL that runs fine but a field has those pesky red + in them for a
> few rows.
> The error is:
>    SQL State: 22023
>    Vendor Code: -802
>    Message: [SQL0802] Data conversion or data mapping error. Cause . . . .
> . :   Error type 6 has occurred. Error types and their meanings are:
>
> I am trying to figure out a way to say:
> Select * from fileA where (SQLState for Field1) = 22023
>
> This way I would know how many rows I am dealing with and might see a
> pattern why.
>
> I have done in in RPG but don't want to have to write a program over this
> file also.  Just want to run a SQL.
>
> --
> KCrawford
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com