Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: UNION Query Issue

    I'm writing what would seem to me to be a very simple query, but for some reason, keep getting an error. The two queries run separately, but the UNION seems to throw it for a loop:

    AIM.BILLING - Build started.
    DROP SPECIFIC PROCEDURE AIM.BILLING
    AIM.BILLING - Drop stored procedure completed.
    Create stored procedure returns -104.
    AIM.BILLING: 69: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "SELECT AIMRETRIEVEDITEM.ROUTINGTRANSITNU" was found following "SOR WITH RETURN FOR ". Expected tokens may include: "<values>". LINE NUMBER=69. SQLSTATE=42601


    Could someone take a look at this bad boy and maybe give me some clues just what it is I'm doing wrong?

    Thanks in advance!!

    CREATE PROCEDURE AIM.BILLING
    (IN TSFROMDATE TIMESTAMP,
    IN TSTODATE TIMESTAMP
    )
    SPECIFIC AIM.BILLING
    DYNAMIC RESULT SETS 1
    LANGUAGE SQL
    P1: BEGIN

    DECLARE SELECT_CURSOR CURSOR WITH RETURN FOR
    SELECT AIMRETRIEVEDITEM.ROUTINGTRANSITNUMBER, AIMRETRIEVEDITEM.POSTINGACCOUNTNUMBER, AIMRETRIEVEDITEM.BANKNUMBER, 'IN-BOX' AS DELIVERYMETHOD, ARCHIVERETRIEVAL.ARCHIVERETRIEVALRECEIPT AS REQUESTDATE,
    COUNT(AIMRETRIEVEDITEMID) AS VOLUME,

    CASE
    WHEN APPLICATIONID = 'DDA' AND CREDITDEBITFLAG IS NOT NULL
    THEN '5639'
    ELSE '5641'
    END
    AS CHARGEFEECODE
    FROM AIM.CHANNEL CHANNEL,
    AIM.CHANNELSESSION CHANNELSESSION,
    AIM.AIMRETRIEVEDITEM AIMRETRIEVEDITEM,
    AIM.ARCHIVERETRIEVAL ARCHIVERETRIEVAL,
    AIM.AIMRETRIEVAL AIMRETRIEVAL,
    AIM.AIMQUERY AIMQUERY,
    AIM.AIMCONNECTION AIMCONNECTION
    WHERE
    AIMRETRIEVEDITEM.ARCHIVERETRIEVALID = ARCHIVERETRIEVAL.ARCHIVERETRIEVALID
    AND ARCHIVERETRIEVAL.ARCHIVERETRIEVALRECEIPT <= tsToDate AND ARCHIVERETRIEVAL.ARCHIVERETRIEVALRECEIPT >= tsFromDate
    AND ARCHIVERETRIEVAL.AIMRETRIEVALID = AIMRETRIEVAL.AIMRETRIEVALID
    AND AIMRETRIEVAL.AIMQUERYID = AIMQUERY.AIMQUERYID
    AND AIMQUERY.AIMCONNECTIONID = AIMCONNECTION.AIMCONNECTIONID
    AND AIMCONNECTION.CHANNELSESSIONID = CHANNELSESSION.CHANNELSESSIONID
    AND CHANNELSESSION.CHANNELID = CHANNEL.CHANNELID
    AND CHANNEL.CHANNEL IN ('RIB', 'INTRANET')


    UNION

    SELECT AIMRETRIEVEDSTATEMENT.ROUTINGTRANSITNUMBER, AIMRETRIEVEDSTATEMENT.ACCOUNTNUMBER AS POSTINGACCOUNTNUMBER, AIMRETRIEVEDSTATEMENT.BANKNUMBER,
    CASE
    WHEN DELIVERYMETHOD IN('PRINT', 'MAIL')
    THEN 'MAIL'
    ELSE DELIVERYMETHOD.DELIVERYMETHOD
    END
    ,ARCHIVERETRIEVAL.ARCHIVERETRIEVALRECEIPT AS REQUESTDATE,
    COUNT(AIMRETRIEVEDSTATEMENTID) AS VOLUME, '5640' AS CHARGEFEECODE
    FROM AIM.CHANNEL CHANNEL,
    AIM.CHANNELSESSION CHANNELSESSION,
    AIM.AIMRETRIEVEDSTATEMENT,
    AIM.ARCHIVERETRIEVAL ARCHIVERETRIEVAL,
    AIM.AIMRETRIEVAL AIMRETRIEVAL,
    AIM.AIMQUERY AIMQUERY,
    AIM.AIMCONNECTION AIMCONNECTION,
    AIM.DELIVERYMETHOD DELIVERYMETHOD,
    AIM.DELIVERY DELIVERY
    WHERE
    AIMRETRIEVEDSTATEMENT.ARCHIVERETRIEVALID = ARCHIVERETRIEVAL.ARCHIVERETRIEVALID
    AND ARCHIVERETRIEVAL.ARCHIVERETRIEVALID = DELIVERY.ARCHIVERETRIEVALID
    AND DELIVERY.DELIVERMETHODID = DELIVERYMETHOD.DELIVERYMETHODID
    AND ARCHIVERETRIEVAL.ARCHIVERETRIEVALRECEIPT <= tsToDate AND AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALRECEIPT >= tsFromDate
    AND ARCHIVERETRIEVAL.AIMRETRIEVALID = AIMRETRIEVAL.AIMRETRIEVALID
    AND AIMRETRIEVAL.AIMQUERYID = AIMQUERY.AIMQUERYID
    AND AIMQUERY.AIMCONNECTIONID = AIMCONNECTION.AIMCONNECTIONID
    AND AIMCONNECTION.CHANNELSESSIONID = CHANNELSESSION.CHANNELSESSIONID
    AND CHANNELSESSION.CHANNELID = CHANNEL.CHANNELID
    AND CHANNEL.CHANNEL = 'INTRANET'
    FOR FETCH ONLY WITH UR
    GROUP BY ROUTINGTRANSITNUMBER, POSTINGACCOUNTNUMBER, ARCHIVERETRIEVALRECEIPT, BANKNUMBER, REQUESTDATE;

    OPEN SELECT_CURSOR;


    END P1
    Last edited by ansonee; 09-07-04 at 13:23.
    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
    Aug 2004
    Posts
    330
    The first query in the union is returning 7 columns, the second is returning 6 columns. (I think. Its hard to read when the source is not formatted)

  3. #3
    Join Date
    Sep 2004
    Posts
    111

    Angry Multiple Remote Buffer Overflow And Unspecified Vulnerabilities

    Team,

    Any body come across this problem.

    SUBJECT:IBM DB2 Universal Database Multiple Remote Buffer Overflow And Unspecified Vulnerabilities

    CONTENT:
    The remote buffer overflow vulnerabilities exist, ultimately resulting in execution of arbitrary code. Details NGSSoftware have reported that multiple remote buffer overflow and other unspecified vulnerabilities exist in IBM DB2 Universal Database.

    The reported vulnerabilities include two remote buffer overflows that are reported to be of 'critical' severity.* Details about any of the vulnerabilities are not known at this time.
    Affected Technology and Version:
    IBM DB2 Universal Database for AIX: 7.0.0-, 7.1.0-, 7.2.0-, 8.1.0
    IBM DB2 Universal Database for HP-UX: 7.0.0-, 7.1.0-, 7.2.0-, 8.1.0
    IBM DB2 Universal Database for Linux: 7.0.0-, 7.1.0-, 7.2.0-, 8.1.0
    IBM DB2 Universal Database for Solaris: 7.0.0-, 7.1.0-, 7.2.0-, 8.1.0 I
    BM DB2 Universal Database for Windows: 7.1.0-, 7.2.0-, 8.1.0

    Thank you in advance for your REPILES.

    -Raj

Posting Permissions

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