Help with Data Type mismatch in criteria expression
-
For some reason, my query causes a data type mismatch error due to where clause #5 below. I am not sure how this is possible given clause #2 & 3. Where clauses 1) (NOT ID_BB_SEC_NUM_DES IS NULL AND TYPENAME(ID_BB_SEC_NUM_DES)='String') 2) AND (LEN(ID_BB_SEC_NUM_DES) BETWEEN 9 AND 13) 3) AND ( RIGHT(ID_BB_SEC_NUM_DES, 1) IN ('W', 'M', 'Y') ) 4) AND ( LEFT( RIGHT( RIGHT( ID_BB_SEC_NUM_DES, LEN( ID_BB_SEC_NUM_DES )-6 ), 3), 1) IN ('B','R','V') OR LEFT( RIGHT( RIGHT( ID_BB_SEC_NUM_DES, LEN( ID_BB_SEC_NUM_DES )-6 ), 4), 1) IN ('B','R','V') ) 5) AND ( INSTR( RIGHT(ID_BB_SEC_NUM_DES, LEN(ID_BB_SEC_NUM_DES)-6), 'O')=0)
-
For some reason, my query causes a data type mismatch error due to where clause #5 below. I am not sure how this is possible given clause #2 & 3. Where clauses 1) (NOT ID_BB_SEC_NUM_DES IS NULL AND TYPENAME(ID_BB_SEC_NUM_DES)='String') 2) AND (LEN(ID_BB_SEC_NUM_DES) BETWEEN 9 AND 13) 3) AND ( RIGHT(ID_BB_SEC_NUM_DES, 1) IN ('W', 'M', 'Y') ) 4) AND ( LEFT( RIGHT( RIGHT( ID_BB_SEC_NUM_DES, LEN( ID_BB_SEC_NUM_DES )-6 ), 3), 1) IN ('B','R','V') OR LEFT( RIGHT( RIGHT( ID_BB_SEC_NUM_DES, LEN( ID_BB_SEC_NUM_DES )-6 ), 4), 1) IN ('B','R','V') ) 5) AND ( INSTR( RIGHT(ID_BB_SEC_NUM_DES, LEN(ID_BB_SEC_NUM_DES)-6), 'O')=0)
I'm not familiar with INSTR, and googling
SQL instr functiontold me its semantics depend on the database one uses. I guess that is where the problem originates. :)Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
Getting an article published on CodeProject should be easier and faster.
-
I'm not familiar with INSTR, and googling
SQL instr functiontold me its semantics depend on the database one uses. I guess that is where the problem originates. :)Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
Getting an article published on CodeProject should be easier and faster.
-
I am querying a csv file using ADO .NET in C++, which uses the JET provider. I can select the Instr statement, but cannot apply the where statement for some reason.
AFAIK Jet providers target MS Access, and this[^] shows a different INSTR syntax than what you are using. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
Getting an article published on CodeProject should be easier and faster.
-
AFAIK Jet providers target MS Access, and this[^] shows a different INSTR syntax than what you are using. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
Getting an article published on CodeProject should be easier and faster.
I took a look at the page and it looks like there are multiple correct syntaxes. I am using the syntax like Instr ("Tech on the Net", "the"), except that I am using single quotes instead of double quotes. Thanks, that's a helpful sight. I now know that MID can be used instead of SUBSTR, which I was replicating with left and right.
-
For some reason, my query causes a data type mismatch error due to where clause #5 below. I am not sure how this is possible given clause #2 & 3. Where clauses 1) (NOT ID_BB_SEC_NUM_DES IS NULL AND TYPENAME(ID_BB_SEC_NUM_DES)='String') 2) AND (LEN(ID_BB_SEC_NUM_DES) BETWEEN 9 AND 13) 3) AND ( RIGHT(ID_BB_SEC_NUM_DES, 1) IN ('W', 'M', 'Y') ) 4) AND ( LEFT( RIGHT( RIGHT( ID_BB_SEC_NUM_DES, LEN( ID_BB_SEC_NUM_DES )-6 ), 3), 1) IN ('B','R','V') OR LEFT( RIGHT( RIGHT( ID_BB_SEC_NUM_DES, LEN( ID_BB_SEC_NUM_DES )-6 ), 4), 1) IN ('B','R','V') ) 5) AND ( INSTR( RIGHT(ID_BB_SEC_NUM_DES, LEN(ID_BB_SEC_NUM_DES)-6), 'O')=0)
That is one of the ugliest schema I have ever seen, reminds me of SAS. Anyway the error in (5) is due to comparing 'O' to 0, you are trying to compare a string to a numeric, one of them is wrong!
- AND ( INSTR( RIGHT(ID_BB_SEC_NUM_DES, LEN(ID_BB_SEC_NUM_DES)-6), 'O')=0)
Never underestimate the power of human stupidity RAH
-
That is one of the ugliest schema I have ever seen, reminds me of SAS. Anyway the error in (5) is due to comparing 'O' to 0, you are trying to compare a string to a numeric, one of them is wrong!
- AND ( INSTR( RIGHT(ID_BB_SEC_NUM_DES, LEN(ID_BB_SEC_NUM_DES)-6), 'O')=0)
Never underestimate the power of human stupidity RAH
I'm trying to exclude ID's where the letter 'O' appears after the 6th character in the ID. Instr returns an integer indicating the base one position where the character is found. I think it returns 0 if the character is not found. It's a bloomberg schema btw I realized that I can however directly tell INSTR to start at position 7 and look for the character 'O', so I will try doing that and see if I still have the same problem. After I get the results of my query, I create a pivot table using ADO .NET. Then I will try to figure out how to do a custom sort of the columns. Easy for me in C or C++ STL, but I am not used to doing it in .NET
modified on Monday, March 29, 2010 1:44 AM
-
I'm trying to exclude ID's where the letter 'O' appears after the 6th character in the ID. Instr returns an integer indicating the base one position where the character is found. I think it returns 0 if the character is not found. It's a bloomberg schema btw I realized that I can however directly tell INSTR to start at position 7 and look for the character 'O', so I will try doing that and see if I still have the same problem. After I get the results of my query, I create a pivot table using ADO .NET. Then I will try to figure out how to do a custom sort of the columns. Easy for me in C or C++ STL, but I am not used to doing it in .NET
modified on Monday, March 29, 2010 1:44 AM
Ted2102 wrote:
It's a bloomberg schema btw
Yah that would be right, stupid 1980's design. What database use your using btw, I found INSTR is not a SQL function so I can't follow through on the error. I think you are going about the process completely wrong, you are treating a csv file as a table in a database, trying to query and then bitching when the query does not work. I would import the data into the database, even if you just query it and chuck it away you will not then be using the jet crap to do real work and you can use SQL properly. Don't try and clean up the data format, just load it all into varchar fields.
Never underestimate the power of human stupidity RAH
-
Ted2102 wrote:
It's a bloomberg schema btw
Yah that would be right, stupid 1980's design. What database use your using btw, I found INSTR is not a SQL function so I can't follow through on the error. I think you are going about the process completely wrong, you are treating a csv file as a table in a database, trying to query and then bitching when the query does not work. I would import the data into the database, even if you just query it and chuck it away you will not then be using the jet crap to do real work and you can use SQL properly. Don't try and clean up the data format, just load it all into varchar fields.
Never underestimate the power of human stupidity RAH
I agree that it would be easier to load the instruments in a database and have used Oracle and Sql Server in the past. I am currently telecommuting over a thousand miles away, so database access is a little slow for me except for whatever portion I install locally in SQL Server Express. I am mainly doing this as an exercise to learn to use ADO .NET on live datasets that I get externally.
-
I agree that it would be easier to load the instruments in a database and have used Oracle and Sql Server in the past. I am currently telecommuting over a thousand miles away, so database access is a little slow for me except for whatever portion I install locally in SQL Server Express. I am mainly doing this as an exercise to learn to use ADO .NET on live datasets that I get externally.
Ted2102 wrote:
I am mainly doing this as an exercise to learn to use ADO .NET
In which case that explains why you are in areas no sane person dares to go (on a business process), using ado.net against csv files.
Never underestimate the power of human stupidity RAH
-
Ted2102 wrote:
I am mainly doing this as an exercise to learn to use ADO .NET
In which case that explains why you are in areas no sane person dares to go (on a business process), using ado.net against csv files.
Never underestimate the power of human stupidity RAH
-
Yes, it appears buggy. The code works when I replace RIGHT( ID, LEN( ID )-6 ) with MID(ID, 7). Thanks for your help. I may install a local database after I figure out what I can store locally and maybe get desktop with a bigger hard drive.
I use SQL Express with no problem and it does most jobs. I tried to install the developer version but it was not an option with the download I was using so I stuck with Express
Never underestimate the power of human stupidity RAH