Interbase Conversion Error From String
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About conversion error from string firebird date Us Learn more about Stack Overflow the company Business Learn more about hiring conversion error from string sql developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the
Firebird Date Format
Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Interbase: conversion error from string “” up vote 1 down vote favorite I'm running IB2009 and I'm trying to count the number of records where a specific field is neither NULL nor empty: SELECT COUNT(A.ID) FROM MYVIEW A WHERE ((A.VARCHARFIELD1 IS NOT NULL) OR (A.VARCHARFIELD1 <> '')) where MYVIEW is a VIEW, and MYVIEW.ID is an INTEGER, while MYVIEW.VARCHARFIELD1 is a VARCHAR(18). I'm getting the error message Error at line 1, conversion error from string "" which I don't really understand, since when I drop the COUNT()-function, the query executes nicely. Do anyone know what I'm doing wrong? Thanks! sql interbase share|improve this question edited Jan 15 '10 at 11:49 asked Jan 15 '10 at 10:22 conciliator 3,30332656 add a comment| 1 Answer 1 active oldest votes up vote 1 down vote I don't see anything wrong with using COUNT() in this case. Although it shouldn't matter you might try dropping all the parentheses in your WHERE clause. You also might try using COUNT(*) instead of COUNT(A.ID) just to see if it gives you different results. In any case, based on your description of the intent of the query I don't think that the query is going to do what you intended. If the query is supposed to return rows where the field is neither NULL nor empty I believe your WHERE clause should be WHERE A.VARCHARFIELD1 IS NOT NULL AND A.VARCHARFIELD1 <> '' With the 'OR' in there as originally written I think you'll get back every row in the table where A.VARCHARFIELD1 is NOT NULL, as the "field NOT NULL" predicate will allow in all non-NULL values, and
nr = 0; END ... In this example, string 'ch' will be converted to integer. If conversion fails, 'nr' would get value zero. Make sure you use a separate BEGIN..END block for this, as WHEN works for the entire block. With Firebird 2.5 and above you can use a http://stackoverflow.com/questions/2070789/interbase-conversion-error-from-string regular expression to check whether the string is a valid number before converting it. You even check if number has certain digits, etc. Full explanation of the syntax can be found in Firebird 2.5 release notes: http://www.firebirdsql.org/rlsnotesh/rlsnotes25.html#rnfb25-dml-regex Do you find this FAQ incorrect or http://www.firebirdfaq.org/faq139/ incomplete? Please e-mail us what needs to be changed. To ensure quality, each change is checked by our editors (and often tested on live Firebird databases), before it enters the main FAQ database. If you desire so, the changes will be credited to your name. To learn more, visit our add content page. If you are a commercial tool maker and your tool features a great way to handle the issue written about in this FAQ, please check out our advertisement page. All contents are copyright © 2007-2016 FirebirdFAQ.org unless otherwise stated in the text. Links Firebird News FlameRobin Home Inventory powered by FB Add content Advertise About The Firebird FAQ Categories NewbiesSQLInstallation and setupBackup and restorePerformanceSecurityConnectivity and APIHOWTOsErrors and error codesMiscellaneous
dron-s Member Откуда: Сообщений: 97 Привет всем!народ, объясните мне, почему появляется такая ошибка?в таблице есть поле Name_tovar_proizvodоно типа VarChar(50) выполняю такой запросinsert http://www.sql.ru/forum/552654/conversion-error-from-string into tovar (articul, name_tovar, edin_izm, kolvo, cena, summa_tov, id_num_nakl, id_proizvod, id_oper, name_tovar_proizvod) select tt.articul, tt.name_tovar, tt.edin_izm, tt.kolvo, tt.cena, tt.summa_tov, tt.id_num_nakl, tt.id_proizvod, tt.id_oper, tt.name_tovar_proizvod from temp_tovar tt; перенос http://forums.devshed.com/firebird-sql-development-61/converting-string-date-via-cast-345548.html данных из одной таблицы в другую...если в запросе вместо tt.name_tovar_proizvod поставить просто число, то всё нормально?почему так? 30 апр 08, 17:47 [5615411] Ответить | Цитировать Сообщить модератору conversion error Re: conversion error from string [new] fynda Member Откуда: Пенза Сообщений: 2787 dron-s пишет:> если в запросе вместо tt.name_tovar_proizvod поставить просто число, то > всё нормально?> почему так?Фигня, которая содержится в этом поле не может преобразоваться к типу поля в целевой таблице. Например строка "адин" не может прокаститься к числу 1Posted via ActualForum NNTP conversion error from Server 1.4 30 апр 08, 17:56 [5615434] Ответить | Цитировать Сообщить модератору Re: conversion error from string [new] dron-s Member Откуда: Сообщений: 97 в таблице tovar поле name_tovar_proizvod тоже имеет тип VarChar(50)почему СУБД считает что это числовое поле? 30 апр 08, 18:15 [5615465] Ответить | Цитировать Сообщить модератору Re: conversion error from string [new] Пьяный Винни-Пух Member Откуда: Сообщений: 1891 1. Никому и никогда не говори версию сервера.2. Таблица temp_tovar статическая или код типа Create Table temp_tovar многа-многа инсёртов в неё из разных мест пресловутый инсёрт-селект Drop Table temp_tovar Commit мысель имеет некоторое отношение к пы 1.3. Если таки статическая, то не было ли поле name_tovar_proizvod некогда VarChar(51) 30 апр 08, 18:27 [5615488] Ответить | Цитировать Сообщить модератору Re: conversion error from string [new] dron-s Member Откуда: Сообщений: 97 1) FireBird 1.5.42) таблица статическаяCREATE TABLE TEMP_TOVAR ( ARTICUL STR_10 NOT NULL /* STR_10 = VARCHAR(10) NOT NULL */, NAME_TOVAR STR_255 NOT NULL /* STR_255 = VARCHAR(255) NOT NULL */, EDIN_IZM INT_FULL
Search Username Password Remember Me? Register Lost Password? facebook google twitter rss Free Web Developer Tools Advanced Search Forum Databases Firebird SQL Development Converting string to date via CAST... Thread: Converting string to date via CAST... Share This Thread Tweet This + 1 this Post To Linkedin Subscribe to this Thread Subscribe to This Thread April 21st, 2006,02:48 PM #1 No Profile Picture chrisv View Profile View Forum Posts Registered User Devshed Newbie (0 - 499 posts) Join Date Apr 2006 Posts 1 Rep Power 0 Converting string to date via CAST... Hi everyone, Does Firebird have a function I can use to convert string to date. The string is formatted as YYYYMMDD. And when I run the following function I get an error because I didn't specify the format. Here is the code I tried to run: update PATIENT_TABLE set DATE_OF_BIRTH = CAST (CAT1 AS DATE); Here is the error received: ISC ERROR CODE:335544334 ISC ERROR MESSAGE: conversion error from string "19460705" I even tried things like CAST(CAT1 AS DATE"YYYYMMDD") or CAST(CAT1 AS DATE(YYYYMMDD)) none of these work, they just tell me unknown token because that's now how this function is designed to work. Thanks for any advice. Faq Reply With Quote April 21st, 2006,04:05 PM #2 pabloj View Profile View Forum Posts Visit Homepage Modding: Oracle MsSQL Firebird Devshed Supreme Being (6500+ posts) Join Date Jun 2001 Location Outside US Posts 8,517 Rep Power 541 I think you are out of luck, see this My blog Tutorials about OSS databases, DBMonster ... Contribute to OSS Development, fill bug reports! Developer Shed eSupport Commented my.ini/my.cnf (ADD YOUR OWN CONFIG TRICK) An introduction to database normalization Natural or Surrogate key Custom ordering for your results Correlated and uncorrelated subqueries Don't turn your outer joins into inner joins Random data (with a bias) Faq Reply With Quote February 8th, 2008,07:13 AM #3 No Profile Picture stonisa View Profile View Forum Posts Registered User Devshed Newbie (0 - 499 posts) Join Date Feb 2008 Posts 1 Rep Power 0 CAST string to date expects strings to represent date in format YYYY-MM-DD Reformat strings and CAST (stringvar AS DATE) will work. Faq Reply With Quote Share This Thread Tweet This + 1 this Post To Linkedin Subscribe to this Thread Subscribe to This Thread « Previous Thread | Next Thread » -- vB4 Default Style-- devshed_2014 Contact Us Develope