|
|
You're in the
sql server mseq
group:Compare Character to numberic
sql server mseq:
I have to join two table based on the matching of two fields. The bad part
is one field is numeric but defined as Character and the other field is decmial. This is my SQL. FROM SLTEST1.STST1 STST1 LEFT OUTER JOIN SLTEST1.STST2 STST2 ON STST1.AUTH1 = STST2.AUTH2 AND STST1.SLIP1 = SUBSTRING( STST2.PRN2, 7, 7 ) STST1.SLIP1 is defined as NUMERIC STST2.PRN2 is defined as CHARACTER
[quoted text, click to view]
On Tue, 25 Oct 2005 16:11:01 -0700, Daniell wrote: >I have to join two table based on the matching of two fields. The bad part >is one field is numeric but defined as Character and the other field is >decmial. This is my SQL. > >FROM > SLTEST1.STST1 STST1 > LEFT OUTER JOIN SLTEST1.STST2 STST2 > ON > STST1.AUTH1 = STST2.AUTH2 AND > STST1.SLIP1 = SUBSTRING( STST2.PRN2, 7, 7 ) > >STST1.SLIP1 is defined as NUMERIC >STST2.PRN2 is defined as CHARACTER > >Any help will be appreciated. Hi Daniell, I'll gladly help, but it's not clear fto me what the question is. The query you posted will work. That is, it will 1. Take positions 7 up to and including 13 of the character string in STST2.PRN2; 2. Attempt to convert these 7 positions to decimal - if this fails, the query will be terminated with an error condition; 3. Check if the same value is in STST1.SLIP1. Is this what you want? Is the query executing as expected? If you need further help, you'll have to provide more detailed information. The most important is the structure of your tables (posted as CREATE TABLE statements, including all constraints and properties), a few rows of sample data to illustrate the problem (posted as INSERT statements), and the output you expect from the given sample data. Check out www.aspfaq.com/5006 as well. Best, Hugo --
I am new at this and am not sure I am doing it correct. I have attempted to
convert the fields to numeric but receive the same error: Character in cast argument not valid This is sample data: STST1 AUTH1 AREA DOOR REC DATE REC TIME COST SLIP1 ORDER ID 4986 03 3807 10/13/05 18:36:00 $32.30 384052 G2834 2752 08 1404 10/13/05 17:24:00 $113.20 934856 F3948 2130 06 208 10/13/05 12:54:00 $3.90 192857 K3948 2132 06 210 10/13/05 12:42:00 $4.30 384956 L3049 2752 08 1404 10/13/05 18:38:00 $42.00 192784 G2834 4986 03 3807 10/13/05 18:41:00 $1.24 49581 F3948 2765 08 1417 10/13/05 16:44:00 $4.33 792734 K3948 4438 06 212 10/13/05 3:17:00 $2.44 395867 L3049 5170 05 3603 10/13/05 5:31:00 $129.35 374856 K4856 Auth1 and SLIP1 are defined as numberic STST2(PRN2 is defined as 132 character) AUTH2 PRN2 2132 ORDERI 384956 10/14/05 L3049 2752 ORDERI 192784 10/15/05 G2834 Final Line AUTH1 AREA DOOR REC TIME COST SLIP1 ORDER ID CONFIRMED 2132 06 210 12:42:00 $4.30 384956 L3049 ORDERI 384956 L3049 2752 08 1404 18:38:00 $42.00 192784 G2834 ORDERI 192784 G2834 [quoted text, click to view] "Hugo Kornelis" wrote:
> On Tue, 25 Oct 2005 16:11:01 -0700, Daniell wrote: > > >I have to join two table based on the matching of two fields. The bad part > >is one field is numeric but defined as Character and the other field is > >decmial. This is my SQL. > > > >FROM > > SLTEST1.STST1 STST1 > > LEFT OUTER JOIN SLTEST1.STST2 STST2 > > ON > > STST1.AUTH1 = STST2.AUTH2 AND > > STST1.SLIP1 = SUBSTRING( STST2.PRN2, 7, 7 ) > > > >STST1.SLIP1 is defined as NUMERIC > >STST2.PRN2 is defined as CHARACTER > > > >Any help will be appreciated. > > Hi Daniell, > > I'll gladly help, but it's not clear fto me what the question is. > > The query you posted will work. That is, it will > 1. Take positions 7 up to and including 13 of the character string in > STST2.PRN2; > 2. Attempt to convert these 7 positions to decimal - if this fails, the > query will be terminated with an error condition; > 3. Check if the same value is in STST1.SLIP1. > > Is this what you want? Is the query executing as expected? > > If you need further help, you'll have to provide more detailed > information. The most important is the structure of your tables (posted > as CREATE TABLE statements, including all constraints and properties), a > few rows of sample data to illustrate the problem (posted as INSERT > statements), and the output you expect from the given sample data. > > Check out www.aspfaq.com/5006 as well. > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address)
[quoted text, click to view]
On Fri, 28 Oct 2005 11:35:03 -0700, Daniell wrote: >I am new at this and am not sure I am doing it correct. I have attempted to >convert the fields to numeric but receive the same error: > > Character in cast argument not valid Hi Daniell, If you try to convert the complete STST2.PRN2 column to numeric, than this error is to be expected, since there's non-numeric data in that column. If you try to convert SUBSTRING(STST2.PRN2, 7, 7) to numeric, than I don't understand - based on the data below, this should run without error. (snip) [quoted text, click to view] >STST2(PRN2 is defined as 132 character) > >AUTH2 PRN2 >2132 ORDERI 384956 10/14/05 L3049 >2752 ORDERI 192784 10/15/05 G2834 This might explain why you're not getting the desired results. If I count characters in PRN2, the 7th character is the first space after ORDERI. Taking 7 characters from there, I get 4 spaces and "384" for the first row, or four spaces and "192" for the second row. Maybe you should change the substring expression to SUBSTRING(STST2.PRN2, 7, 10) Best, Hugo --
Hugo,
Maybe I am doing to conversion wrong or maybe at the wrong place. I am attempting to do it on the join command using the cast verb. FROM SLTEST1.STST1 STST1 LEFT OUTER JOIN SLTEST1.STST2 STST2 ON STST1.AUTH1 = STST2.AUTH2 AND STST1.SLIP1 = CAST( SUBSTRING( STST2.PRN2, 7, 7 ) as numeric ) [quoted text, click to view] "Hugo Kornelis" wrote:
> On Fri, 28 Oct 2005 11:35:03 -0700, Daniell wrote: > > >I am new at this and am not sure I am doing it correct. I have attempted to > >convert the fields to numeric but receive the same error: > > > > Character in cast argument not valid > > Hi Daniell, > > If you try to convert the complete STST2.PRN2 column to numeric, than > this error is to be expected, since there's non-numeric data in that > column. If you try to convert SUBSTRING(STST2.PRN2, 7, 7) to numeric, > than I don't understand - based on the data below, this should run > without error. > > (snip) > >STST2(PRN2 is defined as 132 character) > > > >AUTH2 PRN2 > >2132 ORDERI 384956 10/14/05 L3049 > >2752 ORDERI 192784 10/15/05 G2834 > > This might explain why you're not getting the desired results. If I > count characters in PRN2, the 7th character is the first space after > ORDERI. Taking 7 characters from there, I get 4 spaces and "384" for the > first row, or four spaces and "192" for the second row. > > Maybe you should change the substring expression to > SUBSTRING(STST2.PRN2, 7, 10) > > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address)
[quoted text, click to view]
On Mon, 31 Oct 2005 09:51:37 -0800, Daniell wrote: >Hugo, >Maybe I am doing to conversion wrong or maybe at the wrong place. I am >attempting to do it on the join command using the cast verb. >FROM > SLTEST1.STST1 STST1 > LEFT OUTER JOIN SLTEST1.STST2 STST2 > ON > STST1.AUTH1 = STST2.AUTH2 AND > STST1.SLIP1 = CAST( SUBSTRING( STST2.PRN2, 7, 7 ) as numeric ) Hi Daniell, If there is no bad data in the table, then this should work. With the data you posted in an earlier message, this should produce no error (though you wouldn't get any matches on the join condition, as I explained in my previous message). If you want me to help you investigate this further, then please do the following: 1. Copy and paste the output of SELECT @@VERSION in your reply. 2. Also copy and paste the EXACT and COMPLETE text of the error message you get when running your query from Query Analyzer (if any). 3. Post the complete CREATE TABLE statements for the tables used in the query. Check www.aspfaq.com/5006 for how you can get the table definition scripted. 4. Post INSERT statements with some sample data. If the amount of data in your test database is not prohibitively large, post the complete set of data (check the link to Vyas' script in www.aspfaq.com/5006). 5. If the query doesn't result in an error but in incorrect output, then include the expected output from the query as well. Without the information above, I really can't help you any further than I've already done. Best, Hugo --
Thanks Hugo, I am going to look at it a little closer and maybe find a few
books to help me understand the working of SQL a little better because the table it a large one. I appreciate the help. [quoted text, click to view] "Hugo Kornelis" wrote:
> On Mon, 31 Oct 2005 09:51:37 -0800, Daniell wrote: > > >Hugo, > >Maybe I am doing to conversion wrong or maybe at the wrong place. I am > >attempting to do it on the join command using the cast verb. > >FROM > > SLTEST1.STST1 STST1 > > LEFT OUTER JOIN SLTEST1.STST2 STST2 > > ON > > STST1.AUTH1 = STST2.AUTH2 AND > > STST1.SLIP1 = CAST( SUBSTRING( STST2.PRN2, 7, 7 ) as numeric ) > > Hi Daniell, > > If there is no bad data in the table, then this should work. With the > data you posted in an earlier message, this should produce no error > (though you wouldn't get any matches on the join condition, as I > explained in my previous message). > > If you want me to help you investigate this further, then please do the > following: > > 1. Copy and paste the output of SELECT @@VERSION in your reply. > 2. Also copy and paste the EXACT and COMPLETE text of the error message > you get when running your query from Query Analyzer (if any). > 3. Post the complete CREATE TABLE statements for the tables used in the > query. Check www.aspfaq.com/5006 for how you can get the table > definition scripted. > 4. Post INSERT statements with some sample data. If the amount of data > in your test database is not prohibitively large, post the complete set > of data (check the link to Vyas' script in www.aspfaq.com/5006). > 5. If the query doesn't result in an error but in incorrect output, then > include the expected output from the query as well. > > Without the information above, I really can't help you any further than > I've already done. > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address)
Don't see what you're looking for? Try a search.
|
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |