-
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
-
Run 'print @tsql' to check if the statement is correct.
-
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 ||.
-
The query inside OPENQUERY has to be syntactically correct for the target database, in this case DB2.
-
Originally Posted by SDas
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'.
-
Should you replace " with ' in the code?
-
Syntax is looks OK. I get this error when I execute it.
-
Does any one know another way to pass date parameter to DB2 openquery?
-
Message says incorrect syntax.
-
so, what is wrong with it?
-
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
')
-
Hi,
I have already tried and it works but when I pass the date parameter it doesn't
-
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)
-
Whta's result of print @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)) >= ''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
-
Forum Rules
|
|