Results 1 to 4 of 4

Thread: Query Help

  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: Query Help

    Trying to run this query and am getting te following:

    "An unexpected token "Length(imagefront) AS ImageLength FROM" was found following "TEM.CREDITDEBIT FLAG". Expected tokens may include "<space>".

    I've invcluded the full query below. NO IDEA what is wrong with the sql statement.

    Any help would be great...

    DROP TABLE SESSION.RetrievedItem;

    DECLARE GLOBAL TEMPORARY TABLE RetrievedItem
    (ARCHIVERETRIEVALID DECIMAL(13, 0),
    AIMDOCUMENTID VARCHAR(256),
    CAPTUREDATE DATE,
    ROUTINGTRANSITNUMBER VARCHAR(9),
    MICRACCOUNTNUMBER VARCHAR(24),
    POSTINGACCOUNTNUMBER VARCHAR(24),
    LOCATION VARCHAR(24),
    SERIALNUMBER VARCHAR(20),
    AMOUNT DECIMAL(13, 2),
    ITEMTYPE VARCHAR(20),
    STATUS VARCHAR(24),
    SEQUENCENUMBER VARCHAR(24),
    SORRYDOCINDICATOR VARCHAR(20),
    APPLICATIONID VARCHAR(20),
    CREDITDEBITFLAG VARCHAR(5),
    IMAGELENGTH INTEGER,
    AVAILABLEFLAG CHAR(1)


    INSERT INTO RetrievedItem
    (
    ARCHIVERETRIEVALID ,
    AIMDOCUMENTID ,
    CAPTUREDATE ,
    ROUTINGTRANSITNUMBER ,
    MICRACCOUNTNUMBER ,
    POSTINGACCOUNTNUMBER ,
    LOCATION ,
    SERIALNUMBER ,
    AMOUNT ,
    ITEMTYPE ,
    STATUS ,
    SEQUENCENUMBER ,
    SORRYDOCINDICATOR ,
    APPLICATIONID ,
    CREDITDEBITFLAG ,
    IMAGELENGTH
    )
    VALUES
    SELECT
    (
    AIM.AIMRETRIEVEDITEM.AIMDOCUMENTID, AIM.AIMRETRIEVEDITEM.AIMRETRIEVEDITEMID, AIM.AIMRETRIEVEDITEM.CAPTUREDATE,
    AIM.AIMRETRIEVEDITEM.ROUTINGTRANSITNUMBER, AIM.AIMRETRIEVEDITEM.MICRACCOUNTNUMBER, AIM.AIMRETRIEVEDITEM.POSTINGACCOUNTNUMBER,
    AIM.AIMRETRIEVEDITEM.ITEMTYPE, AIM.AIMRETRIEVEDITEM.IMAGEFRONTTYPE, AIM.AIMRETRIEVEDITEM.IMAGEBACKTYPE,
    AIM.AIMRETRIEVEDITEM.LOCATION, AIM.AIMRETRIEVEDITEM.SERIALNUMBER, AIM.AIMRETRIEVEDITEM.AMOUNT,
    AIM.AIMRETRIEVEDITEM.SEQUENCENUMBER, AIM.AIMRETRIEVEDITEM.SORRYDOCINDICATOR, AIM.AIMRETRIEVEDITEM.APPLICATIONID,
    AIM.AIMRETRIEVEDITEM.CREDITDEBITFLAG, Length(ImageFront) AS IMAGELENGTH
    FROM
    AIM.AIMRETRIEVAL,
    AIM.STATUS,
    AIM.AIMRETRIEVEDITEM,
    AIM.ARCHIVERETRIEVAL,
    AIM.ARCHIVETYPE
    WHERE
    AIM.AIMRETRIEVAL.STATUSID = AIM.STATUS.STATUSID
    AND AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID = AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID
    AND AIM.AIMRETRIEVAL.AIMRETRIEVALID = AIM.ARCHIVERETRIEVAL.AIMRETRIEVALID
    AND AIM.ARCHIVERETRIEVAL.ARCHIVEID = AIM.ARCHIVETYPE.ARCHIVEID
    AND AIM.ARCHIVETYPE.ARCHIVETYPE = 'O'
    AND AIM.AIMRETRIEVAL.AIMRETRIEVALID = decAIMRetrievalID;
    Last edited by ansonee; 04-09-04 at 14:03.
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Sep 2003
    Posts
    237
    Should'nt one Specify the fully qualified column name for ImageFront?
    mota

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It appears that you have 16 columns in the insert statement and 17 columns named in the select. It also appears that some of the columns are out of order.
    Last edited by Marcus_A; 04-09-04 at 14:57.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    straightened out the columns stuff but getting this now:

    AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID is not valid in the context where it is used.

    Here's the statement:
    INSERT INTO SESSION.RetrievedItem
    (
    ARCHIVERETRIEVALID ,
    AIMDOCUMENTID ,
    CAPTUREDATE ,
    ROUTINGTRANSITNUMBER ,
    MICRACCOUNTNUMBER ,
    POSTINGACCOUNTNUMBER ,
    LOCATION ,
    SERIALNUMBER ,
    AMOUNT ,
    ITEMTYPE ,
    STATUS ,
    SEQUENCENUMBER ,
    SORRYDOCINDICATOR ,
    APPLICATIONID ,
    CREDITDEBITFLAG ,
    IMAGELENGTH
    )
    VALUES
    (
    AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID, AIM.AIMRETRIEVEDITEM.AIMDOCUMENTID, AIM.AIMRETRIEVEDITEM.CAPTUREDATE,
    AIM.AIMRETRIEVEDITEM.ROUTINGTRANSITNUMBER, AIM.AIMRETRIEVEDITEM.MICRACCOUNTNUMBER, AIM.AIMRETRIEVEDITEM.POSTINGACCOUNTNUMBER,
    AIM.AIMRETRIEVEDITEM.LOCATION, AIM.AIMRETRIEVEDITEM.SERIALNUMBER, AIM.AIMRETRIEVEDITEM.AMOUNT, AIM.AIMRETRIEVEDITEM.ITEMTYPE,
    AIM.AIMRETRIEVEDITEM.STATUS,
    AIM.AIMRETRIEVEDITEM.SEQUENCENUMBER, AIM.AIMRETRIEVEDITEM.SORRYDOCINDICATOR, AIM.AIMRETRIEVEDITEM.APPLICATIONID,
    AIM.AIMRETRIEVEDITEM.CREDITDEBITFLAG, Length(AIM.AIMRETRIEVEDITEM.ImageFront))

    Sorry if this is pretty basic stuff, but for some reason today not firing on all cylinders...
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •