Hello all,

I'm trying to update a database with SQL Passthrough. I'm only having a problem with the YYMD field. Fow some reason I can't get it to go. I've tried a few variations of the following:

Master File:

  
FILENAME=SQLTEST, SUFFIX=DB2     , $
  SEGMENT=SEG01, SEGTYPE=S0, $
    FIELDNAME=FOCLIST, ALIAS=FOCLIST, USAGE=I5, ACTUAL=I4, $
    FIELDNAME=DT1, ALIAS=DT1, USAGE=YYMD, ACTUAL=DATE, $
    FIELDNAME=BBUYR, ALIAS=BBUYR, USAGE=A3, ACTUAL=A3,
      DESCRIPTION='Buyer ID', $
    FIELDNAME=SUPPID, ALIAS=SUPPID, USAGE=A6, ACTUAL=A6, $
    FIELDNAME=SUPPCON, ALIAS=SUPPCON, USAGE=A50, ACTUAL=A50, $
    FIELDNAME=SUPPCOM, ALIAS=SUPPCOM, USAGE=A50, ACTUAL=A50, $

Fex:

  
SQL DB2 SET ISOLATION NC
-SET &DT11=&DATEYYMD;

ENGINE DB2 SET DEFAULT_CONNECTION Y
SQL DB2
INSERT INTO HSIUSRFLE/SQLTEST (FOCLIST, DT1, BBUYR, SUPPID, SUPPCON, SUPPCOM) VALUES ('1','&DT11','91','SEPTDT','TEST','TEST');
END
-RUN

Log:

  
 SQL DB2
 INSERT INTO HSIUSRFLE/SQLTEST (FOCLIST, DT1, BBUYR, SUPPID, SUPPCON, SUPPCOM) VALUES ('1','2013/06/03','91','SEPTDT','TEST','TEST');
 END
 -RUN
 (FOC1400) SQLCODE IS -180 (HEX: FFFFFF4C)
 : [22007] Syntax of date, time, or timestamp value not valid.
 (FOC1414) EXECUTE IMMEDIATE ERROR.

Thank you!

This message has been edited. Last edited by: John C.,

I believe you need to present the date as:
'yyyy-mm-dd'

quote:

Originally posted by j.gross:
I believe you need to present the date as:
'yyyy-mm-dd'

Yep, you are 100% correct. Once I figure out how to convert YYMD to YY-MM-DD I'll post the solution and change the title. Appreciate your help!

You will need to know the format of the date field in the database and convert the focus data accordingly.

Usage = YYMD, Actual = Date

found this if it helps:

Using the DB2 Web Query Adapter for Microsoft SQL Server
Reference: SQL Limitations on Optimization of DEFINE Expressions
Since the Web Query reporting language is more extensive than native SQL, the data
adapter cannot pass certain DEFINE expressions to the RDBMS for processing. The data
adapter does not offload DEFINE-based aggregation and record selection if the DEFINE
includes:

❏ User-written subroutines.
❏ Self-referential expressions such as:
X=X+1;
❏ EDIT functions for numeric-to-alpha or alpha-to-numeric field conversions.
❏ DECODE functions for field value conversions.
❏ Relational operators INCLUDES and EXCLUDES.
❏ Web Query subroutines ABS, INT, MAX, MIN, LOG, and SQRT.
Note: Do not confuse the Web Query user-written subroutines MAX and MIN with the
MAX. and MIN. prefix operators. DEFINE fields cannot include prefix operators.
❏ Expressions involving fields with ACTUAL=DATE ACTUAL=DATE, except for the subtraction of one DATE
field from another and all logical expressions on DATE fields.
❏ Date-time manipulation handled by the Web Query date-time functions is not
converted to SQL.
In addition, IF-THEN-ELSE optimization does not support the following features:
❏ Any type of DECODE expression.
❏ STATIC SQL.
❏ IF/WHERE DDNAME.
❏ Partial date selection.

This message has been edited. Last edited by: J,

I'm always looking for today, I'm sure this isn't the best way, but this works well.

  
SQL DB2 SET ISOLATION NC
-SET &YR=&DATEYY;
-SET &MT=&DATEM;
-SET &DY=&DATED;
-SET &DT11=&YR | '-' | &MT | '-' | &DY;

ENGINE DB2 SET DEFAULT_CONNECTION Y
SQL DB2
INSERT INTO HSIUSRFLE/SQLTEST (FOCLIST, DT1, BBUYR, SUPPID, SUPPCON, SUPPCOM) VALUES ('101','&DT11','91','SEPTDT','TEST','TEST');
END
-RUN

I don't recall a &DATEfmt option to yield the ISO date directly, but you can use EDIT(), or STRREP(), to change your slashes to hyphens:

-SET &DT11=EDIT(&DATEYYMD,'9999$/99$/99');