Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: Pass date parameter on SQl database to DB2 OpenQuery error

  1. #1
    Join Date
    Oct 2010
    Posts
    10

    Pass date parameter on SQl database to DB2 OpenQuery error

    Hi,

    I have below openquery.
    I am getting below error when I pass the date parameters to open query:
    Msg 402, Level 16, State 1, Line 17
    The data types varchar and date are incompatible in the add operator.


    Is there any one to help me about this please?


    My query is as follows;

    DECLARE @ReportDate1 date
    DECLARE @ReportDate2 date
    DECLARE @TSQL varchar(8000)

    set @ReportDate1 = (Select prevsun from CAS_Dates)
    set @ReportDate2 = (Select sat from CAS_Dates)



    SELECT @TSQL = 'select * from openquery (DB2,
    ''SELECT CAS_NUMBER, DIVISION_CODE, T53.DISTRICT,
    T53.DSCH_TYPE, COUNT(*) AS TOTAL
    FROM CSL.BCSLCLI T52, BIF.VBIF_BRANCH, CSL.BCSLPDM T53
    WHERE REPORTING_NO = T53.BRANCH
    AND T52.SEC_NUM = T53.SEC_NUM
    AND T52.SEC_TYPE = T53.SEC_TYPE
    AND DATE(T52.CHKLST_TS) >= ''''' + @ReportDate1 + '''''
    AND T53.DSCH_TYPE <> ''0''
    AND DATE(T52.CHKLST_TS) <= ''''' + @ReportDate2 + '''''
    AND T52.CHKLST_ITEM = 101
    AND BANK_NO = ''004''
    GROUP BY CAS_NUMBER, DIVISION_CODE, T53.DISTRICT,T53.DSCH_TYPE
    '')'



    Thanks

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Run 'print @tsql' to check if the statement is correct.

  3. #3
    Join Date
    Apr 2009
    Posts
    86
    burakcan, I am not sure but if that query is being passed to DB2 to process, then the + sign is not used for concatenation. Instead, try either CONCAT or ||.

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    The query inside OPENQUERY has to be syntactically correct for the target database, in this case DB2.

  5. #5
    Join Date
    Oct 2010
    Posts
    10
    Quote Originally Posted by SDas View Post
    burakcan, I am not sure but if that query is being passed to DB2 to process, then the + sign is not used for concatenation. Instead, try either CONCAT or ||.
    Hi,

    Defination says about the concatenation operator:

    Both CONCAT and the vertical bars (||) represent the concatenation operator. Vertical bars (or the characters that must be used in place of vertical bars in some countries12) can cause parsing errors in statements passed from one DBMS to another. The problem occurs if the statement undergoes character conversion with certain combinations of source and target CCSIDs12. Thus, CONCAT is the preferable concatenation operator.

    When two strings operands are concatenated, the result of the expression is a string. The operands of concatenation must be compatible strings. A binary string cannot be concatenated with a character string, including character strings that are defined as FOR BIT DATA (for more information on the compatibility of data types

    So, I am trying the pass two dates parameters to DB2 but I am getting openquery. I have changed my code but still I am getting error. I could not understand the errorr message below.
    Thanks for helping

    DECLARE @ReportDate1 date
    DECLARE @ReportDate2 date
    DECLARE @TSQL varchar(8000)

    set @ReportDate1 = (Select prevsun from CAS_Dates)
    set @ReportDate2 = (Select sat from CAS_Dates)

    print @reportdate1
    print @reportdate2

    SELECT @TSQL = 'SELECT * FROM OPENQUERY(DB2,
    ''SELECT CAS_NUMBER, DIVISION_CODE, T53.DISTRICT,
    T53.DSCH_TYPE, COUNT(*) AS TOTAL
    FROM CSL.BCSLCLI T52, BIF.VBIF_BRANCH, CSL.BCSLPDM T53
    WHERE REPORTING_NO = T53.BRANCH
    AND T52.SEC_NUM = T53.SEC_NUM
    AND T52.SEC_TYPE = T53.SEC_TYPE
    AND cast(DATE(T52.CHKLST_TS) as varchar(10)) >= ''''' + cast(@ReportDate1 as varchar(10)) + '''''
    AND cast(DATE(T52.CHKLST_TS) as varchar(10)) <= ''''' + cast(@ReportDate2 as varchar(10)) + '''''
    AND T53.DSCH_TYPE <> ''0''
    AND T52.CHKLST_ITEM = 101
    AND BANK_NO = ''004''
    GROUP BY CAS_NUMBER, DIVISION_CODE, T53.DISTRICT,T53.DSCH_TYPE
    '')'
    print @TSQL
    EXEC (@TSQL)

    Here is the output when I execute it

    2010-09-26
    2010-10-02
    SELECT * FROM OPENQUERY(DB2,
    'SELECT CAS_NUMBER, DIVISION_CODE, T53.DISTRICT,
    T53.DSCH_TYPE, COUNT(*) AS TOTAL
    FROM CSL.BCSLCLI T52, BIF.VBIF_BRANCH, CSL.BCSLPDM T53
    WHERE REPORTING_NO = T53.BRANCH
    AND T52.SEC_NUM = T53.SEC_NUM
    AND T52.SEC_TYPE = T53.SEC_TYPE
    AND cast(DATE(T52.CHKLST_TS) as varchar(10)) >= ''2010-09-26''
    AND cast(DATE(T52.CHKLST_TS) as varchar(10)) <= ''2010-10-02''
    AND T53.DSCH_TYPE <> '0'
    AND T52.CHKLST_ITEM = 101
    AND BANK_NO = '004'
    GROUP BY CAS_NUMBER, DIVISION_CODE, T53.DISTRICT,T53.DSCH_TYPE
    ')
    Msg 102, Level 15, State 1, Line 10
    Incorrect syntax near '0'.

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    Should you replace " with ' in the code?

  7. #7
    Join Date
    Oct 2010
    Posts
    10
    Syntax is looks OK. I get this error when I execute it.

  8. #8
    Join Date
    Oct 2010
    Posts
    10
    Does any one know another way to pass date parameter to DB2 openquery?

  9. #9
    Join Date
    Sep 2002
    Posts
    5,938
    Message says incorrect syntax.

  10. #10
    Join Date
    Oct 2010
    Posts
    10
    so, what is wrong with it?

  11. #11
    Join Date
    Sep 2002
    Posts
    5,938
    Tried this:
    SELECT * FROM OPENQUERY(DB2,
    'SELECT CAS_NUMBER, DIVISION_CODE, T53.DISTRICT,
    T53.DSCH_TYPE, COUNT(*) AS TOTAL
    FROM CSL.BCSLCLI T52, BIF.VBIF_BRANCH, CSL.BCSLPDM T53
    WHERE REPORTING_NO = T53.BRANCH
    AND T52.SEC_NUM = T53.SEC_NUM
    AND T52.SEC_TYPE = T53.SEC_TYPE
    AND cast(DATE(T52.CHKLST_TS) as varchar(10)) >= ''2010-09-26''
    AND cast(DATE(T52.CHKLST_TS) as varchar(10)) <= ''2010-10-02''
    AND T53.DSCH_TYPE <> ''0''
    AND T52.CHKLST_ITEM = 101
    AND BANK_NO = ''004''
    GROUP BY CAS_NUMBER, DIVISION_CODE, T53.DISTRICT,T53.DSCH_TYPE
    ')

  12. #12
    Join Date
    Oct 2010
    Posts
    10
    Hi,

    I have already tried and it works but when I pass the date parameter it doesn't

  13. #13
    Join Date
    Oct 2010
    Posts
    10
    I have solved my problem like below but I am not happy with it. I just cast date field to VARCHAR.

    DECLARE @Date1 Date
    DECLARE @Date2 Date
    DECLARE @ReportDate1 varchar(10)
    DECLARE @ReportDate2 varchar(10)
    DECLARE @TSQL varchar(8000)

    set @Date1 = (Select prevsun from dbo.CAS_Dates)
    set @Date2 = (Select sat from CAS_Dates)

    set @ReportDate1=CAST(@date1 as varchar(10))
    set @ReportDate2=CAST(@date2 as varchar(10))



    SET @tsql =
    'insert into dbo.T_xxxxxxxx
    (cas_num,div_code,district,discharge_type,total,ty pe_status_heloc)' + ' ' +
    'select * from openquery (DB2,
    ''SELECT CAST(CAS_NUMBER AS CHAR(4)), DIVISION_CODE, T53.DISTRICT,
    T53.DSCH_TYPE, COUNT(*) AS TOTAL,''''TYPE'''' as Type_status_Heloc
    FROM CSL.BCSLCLI T52, BIF.VBIF_BRANCH, CSL.BCSLPDM T53
    WHERE REPORTING_NO = T53.BRANCH
    AND T52.SEC_NUM = T53.SEC_NUM
    AND T52.SEC_TYPE = T53.SEC_TYPE
    AND CAST(T52.CHKLST_TS AS CHAR(10)) >= ''''' + @ReportDate1 + '''''
    AND CAST(T52.CHKLST_TS AS CHAR(10)) <= ''''' + @ReportDate2 + '''''
    AND T53.DSCH_TYPE <> ''''0''''
    AND T52.CHKLST_ITEM = 101
    AND BANK_NO = ''''004''''
    GROUP BY CAS_NUMBER, DIVISION_CODE, T53.DISTRICT,T53.DSCH_TYPE
    '')'

    print @tsql
    exec (@tsql)

  14. #14
    Join Date
    Sep 2002
    Posts
    5,938
    Whta's result of print @tsql?

  15. #15
    Join Date
    Oct 2010
    Posts
    10
    insert into dbo.T_xxxxxxxx
    (cas_num,div_code,district,discharge_type,total,ty pe_status_heloc) select * from openquery (DB2,
    'SELECT CAST(CAS_NUMBER AS CHAR(4)), DIVISION_CODE, T53.DISTRICT,
    T53.DSCH_TYPE, COUNT(*) AS TOTAL,''TYPE'' as Type_status_Heloc
    FROM CSL.BCSLCLI T52, BIF.VBIF_BRANCH, CSL.BCSLPDM T53
    WHERE REPORTING_NO = T53.BRANCH
    AND T52.SEC_NUM = T53.SEC_NUM
    AND T52.SEC_TYPE = T53.SEC_TYPE
    AND CAST(T52.CHKLST_TS AS CHAR(10)) >= ''2010-09-26''
    AND CAST(T52.CHKLST_TS AS CHAR(10)) <= ''2010-10-02''
    AND T53.DSCH_TYPE <> ''0''
    AND T52.CHKLST_ITEM = 101
    AND BANK_NO = ''004''
    GROUP BY CAS_NUMBER, DIVISION_CODE, T53.DISTRICT,T53.DSCH_TYPE
    ')

Posting Permissions

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