|
Hi All,
I am getting conversion error when i try to execute the following query... please help i need this scenario to be fixed for another business logic
Conversion failed when converting the varchar value '12r' to data type int.
declare @t table
(
val varchar(10)
)
insert into @t
select '1' union
select '2' union
select '3' union
select '4' union
select 'et' union
select '4' union
select '6' union
select '7' union
select '8' union
select '9' union
select '12r'
select * from @t where cast(val as int) > 3
|
|
|
|
|
12r cannot be converted because it is not a number.
Use the best guess
|
|
|
|
|
Try this:
select *
from @t
where
cast((case when isnumeric(val) = 1 then val else 0 end) as int) > 3
Edit -this will turn the 12r into a zero as it is not a valid integer.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
This question really does ask for a disparaging response.
If you can't work out that declaring a field of type int and trying to stuff text into it is going to fail and then cannot understand the very clear error message you should stop now and get an education.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Perhaps JavaScript could cast '12r' into an int with a value of 12, but many other programming/scripting languages won't do so.
|
|
|
|
|
Bernhard Hiller wrote: JavaScript could cast '12r' into an int
It does, how extraordinary, no wonder I loathe the script language.
There is certain crappy banking software that will allow the traders to enter 12.65m or 328k as a trade values, WTF!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: There is certain crappy banking software that will allow the traders to enter 12.65m or 328k as a trade values, WTF!
Considering how close K and M are on a qwerty keyboard 
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Mycroft, I see original poster did not respond, but really curious - what does J-script convert '12r' to???
|
|
|
|
|
Richard.Berry100 wrote: what does J-script convert '12r' to???
Dammed if I know, as a Silverlight dev I don't have to get my hands grubby by rabbiting around in script languages.
The r probably stands for a currency sign, I image the conversion just drops the non numeric characters and turn the rest to an integer.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
SELECT
T1.ROW_ID,
T1.CREATED_BY,
T1.LAST_UPD_BY,
T1.NAME,
T1.PRTNR_FLG,
T1.CMPT_FLG,
T1.EMP_COUNT,
T1.BASE_CURCY_CD,
T1.CUST_STAT_CD,
T1.DOM_ULT_DUNS_NUM,
T1.DUNS_NUM,
T1.EAI_ERROR_TEXT,
T1.GLBLULT_DUNS_NUM,
T1.MAIN_PH_NUM,
T1.OU_NUM,
T1.PAR_DUNS_NUM,
T1.REGION,
T1.URL,
T1.X_DO_CONTACT_FLAG,
T1.X_DOMESTIC_NAME,
T1.X_GU_NAME,
T1.X_MARKET,
T1.X_MARKET_SEGMENT,
T1.X_NAME_IN_LOCAL,
T1.X_ONECODE_STATUS,
T1.X_PARENT_NAME,
T1.X_VENDOR_FLAG,
T1.X_GU_COUNTRY,
T1.X_INTERMEDIARY_FLAG,
T1.X_ONECODE_NAME,
T1.X_NUM_OF_EMP_EDITABLE,
T1.X_DOMESTIC_COUNTRY,
T1.X_HQPARENT_COUNTRY,
T1.X_AGU_FLG,
T1.X_ASSOC_MHGOLD,
T1.X_DB_TOTAL_ASSET_USD,
T1.X_DC_TOTAL_ASSET_USD,
T1.X_MDN_GOC_MESSAGE,
T1.X_MDN_GOC_SUBMIT_STATUS,
T1.X_CLIENT_TYPE,
T1.X_INT_LEV_TYPE,
T1.X_INT_REL_MGR_ID_MDN,
T1.X_GLOBAL_REL_MGR_ID_MDN,
T1.X_DNB_NAME_MDN,
T1.X_DNB_TRADE_NAME_MDN,
T1.X_DMG_COMMENT_MDN,
T1.X_MDN_GOC_DUPLICATE_CODE,
T1.X_MDN_GOC_DUPLICATE_NAME,
T1.X_MDN_GOC_INACTIVATION_DESC,
T1.X_MDN_GOC_REJECT_DESC,
T1.X_ACTIVE_DRMGLOBAL_MDN,
T1.X_ACTIVE_DRMINTER_MDN,
T1.X_ASSOC_ATTGOLD,
T1.X_AUTO_MERGE_DATE,
T1.X_AUTO_MERGE_FLG,
T1.X_AUTO_MERGE_STATUS_MDN,
T1.X_MDN_GOC_DUPLICATE_CODE_FLAG,
T1.X_MDN_GOC_DUPLICATE_DATE,
T1.X_MDN_GOC_SURVIVING_CODE,
T1.X_DNB_GU_ANNUAL_REVN,
T4.ATTRIB_04,
T4.X_REL_CLIENT_DESC,
T4.ATTRIB_34,
T5.PR_EMP_ID,
--T6.DESC_TEXT as Primary_Industry_Category__c,
--T6.SIC as Primary_SIC_Code__c,
--T6.NAME as Primary_SIC_Description__c,
C.X_OFFICE_CODE,
C.X_REGION,
T1.PR_INDUST_ID
FROM
siebel.S_ORG_EXT T1,
siebel.S_ORG_EXT_X T4,
siebel.S_POSTN T5,
siebel.s_contact C--,
--siebel.S_INDUST T6
--
where
T1.X_CLIENT_TYPE is NOT NULL
and T1.CUST_STAT_CD in('Active','Inactive','Pending - Workflow Wizard')
AND C.EMP_FLG(+)='Y'
and T4.ROW_ID(+)=T1.ROW_ID
and T5.ROW_ID(+)=T1.PR_POSTN_ID
--and T6.ROW_ID(+)=T1.PR_INDUST_ID
and C.ROW_ID=T5.PR_EMP_ID
|
|
|
|
|
Here you go you can get forked[^]!
Seriously do you expect someone to rewrite your query for you! You do not state your database, from the joins I assume oracle but you have given absolutely zero information!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
There's nothing to rewrite. You select columns and join, that's it - it's as dressed down as it can get. If speed is an issue, consider partitioning your table and verifying the indexes.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Rearrange your WHERE clause as below -
WHERE C.ROW_ID = T5.PR_EMP_ID
AND T1.ROW_ID = T4.ROW_ID(+)
AND T1.PR_POSTN_ID = T5.ROW_ID(+)
AND C.EMP_FLG(+) = 'Y'
AND T1.CUST_STAT_CD IN ('Active','Inactive','Pending - Workflow Wizard')
AND T1.X_CLIENT_TYPE IS NOT NULL
Thanks & Regards,
Niral Soni
|
|
|
|
|
Hello,
Thanks in advance.
Table-1
startdate MSNUM
12/12/2012 1,2,3
12/10/2010 4
12/9/2009 5,6
12/13/2014
10/3/2003
Table-2
ID DESC
1 ONE
2 TWO
3 THREE
4 FOUR
5 FIVE
Expected OutPut Result
MSGNUM DESC
1,2,3 ONE,TWO,THREE
4 FOUR
5,6 FIVE,SIX
Note: Table-1 contains 7690 records and table-2 contains 160 fixed rows
Please let me know how can get the above output. I am trying using cursors still didn't found any solution. If anyone already worked on this can save my time.
best Regards,
Kumar
|
|
|
|
|
Because you have stored you MSNUM as a comma separated string (idiot) you are going to have to split the string. For this you need a split function that will return a table with the date and 1 ID per row. You then use this table to join you 2 existing tables.
This is the direct result of crappy design where someone is too bloody lazy to create the additional table to store the Date/MSNUM as discreet records.
There are plenty of examples around[^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
SELECT MSGNUM, LISTAGG(DESCR, ',') WITHIN GROUP (ORDER BY LVL) AS MSGDESCR
FROM (SELECT DISTINCT MSGNUM, LEVEL LVL
,SUBSTR(NVL2(MSGNUM, MSGNUM || ',', NULL)
,(CASE WHEN LEVEL > 1 THEN
INSTR(NVL2(MSGNUM, MSGNUM || ',', NULL), ',', 1, LEVEL - 1) + 1
ELSE LEVEL END)
,INSTR(NVL2(MSGNUM, MSGNUM || ',', NULL), ',', 1, LEVEL)
- (CASE WHEN LEVEL > 1 THEN
INSTR(NVL2(MSGNUM, MSGNUM || ',', NULL), ',', 1, LEVEL - 1) + 1
ELSE LEVEL END)
) MSGNUM_TO_ID
FROM
(SELECT SYSDATE - 1 STARTDATE, '1,2,3' MSGNUM FROM DUAL UNION
SELECT SYSDATE - 2 STARTDATE, '4' MSGNUM FROM DUAL UNION
SELECT SYSDATE - 3 STARTDATE, '5,6' MSGNUM FROM DUAL UNION
SELECT SYSDATE - 4 STARTDATE, '' MSGNUM FROM DUAL UNION
SELECT SYSDATE - 5 STARTDATE, '4,2,5' MSGNUM FROM DUAL UNION
SELECT SYSDATE - 6 STARTDATE, '' MSGNUM FROM DUAL)
CONNECT BY INSTR(NVL2(MSGNUM, MSGNUM || ',', NULL), ',', 1, LEVEL) != 0
) T1
,(SELECT 1 ID, 'ONE' DESCR FROM DUAL UNION
SELECT 2 ID, 'TWO' DESCR FROM DUAL UNION
SELECT 3 ID, 'THREE' DESCR FROM DUAL UNION
SELECT 4 ID, 'FOUR' DESCR FROM DUAL UNION
SELECT 5 ID, 'FIVE' DESCR FROM DUAL UNION
SELECT 6 ID, 'SIX' DESCR FROM DUAL) T2
WHERE T1.MSGNUM_TO_ID = T2.ID
GROUP BY MSGNUM
;
Thanks & Regards,
Niral Soni
|
|
|
|
|
We are just switching over to SQL Server 2012 from SQL Server 2008 and we are having some trouble with an XML Source (from a URL dontcha know).
It works fine on 2008, but always reports that it can't find the file on 2012 -- even though it is able to find the file to generate an XSD.
Even on the same server under the same user.
The file exists and the user has access to it.
Has anyone else seen this? Any ideas? A quick search didn't turn up any clues.
|
|
|
|
|
Hi all,
I'm relatively new to sql and need help to accomplish what should be a straight forward task.
I need to insert record templates in a database table. The records are identical except for IDfield which is a sequence from n to n+count. I can't use Autoincrement because I want to be able to enter different templates where I can define the base index n.
I'm currently using transactions to insert records in batches but need a faster method using a single SQL statement.
Example:
2000 Apple Fruit Granny Smith
2001 Apple Fruit Granny Smith
2002 Apple Fruit Granny Smith
..
..
2900 Apple Fruit Granny Smith
A different fruit may have a base index of 6000 and so on.
I would appreciate any help you can give me.
Thanks.
|
|
|
|
|
Not sure if MS Access supports while statement but you can try something like this... it works in SQL Server
declare @counter int
declare @max int
SET @counter = 0
SET @max = 12
while @counter < @max
begin
INSERT INTO tableName (FruitCodeName) VALUES(CONVERT(nvarchar(3), @counter) + 'Apple Fruit Granny Smith' )
SET @counter = @counter + 1
end
|
|
|
|
|
You Can't Do That With One Statement
I Do Wonder What Exactly Your Code Is. You Should Be Using A Parameterized Statement, In Which Case You Need Only Set The One Parameter Value And Execute The Statement Again.
|
|
|
|
|
Thanks for your input. I figured how to use AddWithValue but for such a trivial task I was hoping to avoid a loop in my application and let the database engine do the work for me.
Thanks
|
|
|
|
|
sobelhaj wrote: let the database engine do the work for me
That ain't gonna happen.
|
|
|
|
|
sobelhaj wrote: I can't use Autoincrement because I want to be able to enter different templates where I can define the base index n.
Well technically his design is wrong, the ID field should be stupid bejond identifying the record. What you are calling an ID field is actually your CODe field and may be edited by the users. What happens if they want to change the code for Granny Smith Apples to 7 series?
You could always write a scrip/code for the loop passing in the start series and the product. You cannot avoid a loop somewhere.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
sobelhaj wrote: I would appreciate any help you can give me.
Based on your description - a redesign would probably be the best thing.
Why doesn't your record just have the following for where the last value is the count?
<one id=""> Apple Fruit Granny Smith 2900
|
|
|
|
|
I have a table named(information)tha has 3 columns(id,fnam,lnam)
I know that I can select an special row by
select form information (id,fname,lname)WHERE id="1"
string lname="last name witch it's id is "1"";
plz help me!!!
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
