| Author |
Topic |
|
ilimax
Posting Yak Master
164 Posts |
 Posted - 2009-02-11 : 14:26:40
|
| I have TablaA with colmnus Col1, Col2, Col3TableB with columns Col1, Col2TableA.Col2 contain key column of TableB, column TableB.Col1So it is simple SELECT TableA.Col2,TableA,Col3,TableB.Col2FROM TableA INNER JOIN TableB ON TableB.Col1=TableA.Col2Problem is TableA.Col2 is stored sometimes as nagative number. How can read that number always as positive and use as key in INNER JOIN. |
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2009-02-11 : 14:30:24
|
| ABS(TableA.Col2) |
 |
|
|
Skorch
Constraint Violating Yak Guru
300 Posts |
Posted - 2009-02-11 : 14:33:08
|
| You can try using the ABS() function to return the absolute value of the id.SELECT TableA.Col2,TableA,Col3,TableB.Col2FROM TableA INNER JOIN TableBON TableB.Col1=ABS(TableA.Col2)Some days you're the dog, and some days you're the fire hydrant. |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-02-11 : 14:35:06
|
| question is why is it negative, never heard of this approach. can you enlighten me please. |
|
|
|
ilimax
Posting Yak Master
164 Posts |
Posted - 2009-02-11 : 14:50:42
|
| I did not build program ... I do not know why is sometimes negative ... I have to make report. However thanks to hanbingl and Skorch ... I will try this ABS ... Looks so simple but did not come up on my mind |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-02-11 : 15:11:56
|
quote:
Originally posted by yosiasz
question is why is it negative, never heard of this approach. can you enlighten me please.
Possibly up-sized from MS Access db with random surrogate key generator.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-11 : 16:58:29
|
If Col2 in table A is sometimes negative, and also is key column for Col1 in table B, why would you take the absolute value for the column?If it is key column, there surely must be records in table B having corresponding negative values too.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
ilimax
Posting Yak Master
164 Posts |
Posted - 2009-02-12 : 13:13:51
|
| I got answer why is key sometimes negative ... Looks like programmers did not think about reporting issue and they updated this key as negative for some other needs in program. If value is negative, program do something different ... But, now I need this field for relation between tables... Database of program is completelly unusual ... There is no any relationship in diagram ... I am having very hard time to develop reports ... But I made this one fine, and works very well with ABS function ...SELECT TableA.Col2,TableA,Col3,TableB.Col2FROM TableA INNER JOIN TableBON TableB.Col1=ABS(TableA.Col2)Thank you very much for all responding ... |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-02-12 : 13:56:09
|
| Can you change the design of program, this will hurt you later on cancerous stuff. Can you move these negative values to a different table and somehow redesign things. this is very bootleg appraoch that might creep out of it's grave at another time to bite you yet again |
|
|
|
ilimax
Posting Yak Master
164 Posts |
Posted - 2009-02-12 : 14:23:41
|
| No, I can not change program. |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-02-12 : 15:24:38
|
quote:
Originally posted by ilimax
I got answer why is key sometimes negative ... Looks like programmers did not think about reporting issue and they updated this key as negative for some other needs in program. If value is negative, program do something different ... But, now I need this field for relation between tables... Database of program is completelly unusual ... There is no any relationship in diagram ... I am having very hard time to develop reports ... But I made this one fine, and works very well with ABS function ...SELECT TableA.Col2,TableA,Col3,TableB.Col2FROM TableA INNER JOIN TableBON TableB.Col1=ABS(TableA.Col2)Thank you very much for all responding ...
The query will work for sure. Go through Peso's comment, that explains why your result might be wrong. |
|
|
|
ilimax
Posting Yak Master
164 Posts |
Posted - 2009-02-12 : 15:55:21
|
| Sorry, I did not see Peso's comment ... No, key is always positive in TableB ... TableA and TableB do not have direct realtionship in database. Field in TableA is populated with value of TableB field. I talked with one of programers who build the program. He said field is populated on biginning of process with right value, than later they choose update that field (value) to negative .. By that they know process is finish for that record ... This is not smart for me too, but you can see how some people think when they do programming ... |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-02-12 : 16:36:29
|
| just have them change that, this is crazy...just add a new Boolean field called ProcessFinishedYesNo ...default value it to ((0)) for all records. Then do an UPDATE on that table SET ProcessFinishedYesNo to 1 where Key field = -1. Then change all the negative Key fields to ABS(keyfield value)all new finished process updates ProcessFinishedYesNo not key field...ABS is nice but tomorrow another developer comes and the problem is just handed to anew developer2 cents |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-02-12 : 23:17:07
|
quote:
Originally posted by ilimax
I got answer why is key sometimes negative ... Looks like programmers did not think about reporting issue and they updated this key as negative for some other needs in program. If value is negative, program do something different ...
Every time I think I've heard of every possible stupid idea, I read another post on these forums and have my confidence in the ineptitude of my fellow man restored.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
|
Negative ID