填充数据集时字符会发生变化 [英] Character changes when fill Dataset

问题描述

大家好，

我的查询没有任何问题..填写数据集时只面临问题..

以下是我的选择查询..只关注第一个请注意

 strSql =   
 strSql =  选择B.STORE_NAME3，A.STORE，A.SALES_DATE，B.STORE_NAME，& vbCrLf 
 strSql = strSql +  （例如NVL（C.CUT_OFF_DATE，'31 -JAN-2020'） ）< = A.SALES_DATE然后''''''''''ORPOS& vbCrLf 
 strSql = strSql +  ，SUM（例如，当TRAN_TYPE ='RES_TRN'那么结束时为0） RES_TRN& vbCrLf 
 strSql = strSql +  ，SUM（例如，当TRAN_TYPE ='FIN'，那么因为0结束） FIN& vbCrLf 
 strSql = strSql +  ，SUM（例如，当TRAN_TYPE ='EBS'那么结束时为0） EBS& vbCrLf 
 strSql = strSql +  ，cm.comments& vbCrLf 
 strSql = strSql +  来自XXRMS.INH_TMP_SALES_COMPARISON_RPT A& vbCrLf 
 strSql = strSql +  在a.store = cm.store上左外连接rms12.SA_TRAN_ERROR_LOG CM a.sales_date = cm.tran_date& vbCrLf 
 strSql = strSql +   LEFT OUTER JOIN STORE B ON A.STORE = B.STORE& vbCrLf 
 strSql = strSql +   LEFT OUTER JOIN rms12.AHG_STORE C ON A.STORE = C.STORE & vbCrLf 
 strSql = strSql +   GROUP BY B.STORE_NAME3，A.STORE，A.SALES_DATE，B。 STORE_NAME，& vbCrLf 
 strSql = strSql +  （例如NVL（C.CUT_OFF_DATE，'31 -JAN-2020'） ）< = A.SALES_DATE然后'Y'ELSE'N'END），cm.comments 
 
跳过：
 cmd = 新 OleDbCommand（strSql，conn）
 '  Dim dr As OleDbDataReader  
 '  dr = cmd.ExecuteReader  
 '  dr.Read（） 
 '  MessageBox.Show（dr（4）） 
 ' 结束时 
  Dim  dts  As  DataSet = 新 DataSet 
 da = 新 OleDbDataAdapter（cmd）
 dt = 新 DataTable 
 < span class =code-comment>'  dt.Locale = System.Globalization.CultureInfo.InvariantCulture  
 da.Fill（dts）

当我在TOAD中执行此查询时，它给了我预期的结果，但是当我执行时

 da.Fill（dts）

来自案例陈述N的字符在TOAD中显示正确显示数据集中的Y

解决方案

对于不起作用的情况，CUT_OFF_DATE的值是多少？并且CUT_OFF_DATE和SALES_DATE都是日期列吗？可能是您需要在那里进行日期转换，因为您的固定字符串不会被评估为日期。试试这个：

 strSql = strSql +（例如，当NVL（C.CUT_OFF_DATE，to_date（'31 -JAN-2020'， 'dd-MMM-yyyy'））< = A.SALES_DATE然后'Y'ELSE'N'END）ORPOS& vbCrLf 

Hi all,
nothing is wrong in my query..only facing problem when filling dataset..
following is my select query..only concentrate on the first case please

strSql = ""
            strSql = "select B.STORE_NAME3,A.STORE,A.SALES_DATE,B.STORE_NAME," & vbCrLf
            strSql = strSql + " (CASE WHEN NVL(C.CUT_OFF_DATE,'31-JAN-2020')<=A.SALES_DATE THEN 'Y' ELSE 'N' END) ORPOS " & vbCrLf
            strSql = strSql + " ,SUM(CASE WHEN TRAN_TYPE='RES_TRN' THEN AMOUNT ELSE 0 END ) RES_TRN" & vbCrLf
            strSql = strSql + " ,SUM(CASE WHEN TRAN_TYPE='FIN' THEN AMOUNT ELSE 0 END ) FIN" & vbCrLf
            strSql = strSql + " ,SUM(CASE WHEN TRAN_TYPE='EBS' THEN AMOUNT ELSE 0 END ) EBS" & vbCrLf
            strSql = strSql + " ,cm.comments " & vbCrLf
            strSql = strSql + " from XXRMS.INH_TMP_SALES_COMPARISON_RPT A" & vbCrLf
            strSql = strSql + " left outer join rms12.SA_TRAN_ERROR_LOG CM on a.store = cm.store and a.sales_date = cm.tran_date " & vbCrLf
            strSql = strSql + " LEFT OUTER JOIN STORE B ON A.STORE=B.STORE" & vbCrLf
            strSql = strSql + " LEFT OUTER JOIN rms12.AHG_STORE C ON A.STORE=C.STORE" & vbCrLf
            strSql = strSql + " GROUP BY B.STORE_NAME3,A.STORE,A.SALES_DATE,B.STORE_NAME, " & vbCrLf
            strSql = strSql + " (CASE WHEN NVL(C.CUT_OFF_DATE,'31-JAN-2020')<=A.SALES_DATE THEN 'Y' ELSE 'N' END), cm.comments"

SKIP:
            cmd = New OleDbCommand(strSql, conn)
            'Dim dr As OleDbDataReader
            'dr = cmd.ExecuteReader
            'While dr.Read()
            '    MessageBox.Show(dr(4))
            'End While
            Dim dts As DataSet = New DataSet
            da = New OleDbDataAdapter(cmd)
            dt = New DataTable
            'dt.Locale = System.Globalization.CultureInfo.InvariantCulture
            da.Fill(dts)

when i am executing this query in TOAD it is giving me my expected result but when i am performing

da.Fill(dts)

the character from case statement N which is showing correct in TOAD is showing Y in dataset

解决方案

what's the value of CUT_OFF_DATE for the case that's not working? And are CUT_OFF_DATE and SALES_DATE both date columns? Could be that you need a date conversion in there, as your fixed string will not be evaluated as a date. Try this instead:

strSql = strSql + " (CASE WHEN NVL(C.CUT_OFF_DATE,to_date('31-JAN-2020', 'dd-MMM-yyyy'))<=A.SALES_DATE THEN 'Y' ELSE 'N' END) ORPOS " & vbCrLf

这篇关于填充数据集时字符会发生变化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！