| Author |
Topic |
|
JJ297
Aged Yak Warrior
940 Posts |
 Posted - 2010-10-06 : 15:27:26
|
| I'm trying to get all of the records except the one's that start with the letter R. I can't get this to work...SELECT COSSN, MVT_TYP, MVT_CDT, MVT_LOC, MVT_DESTFROM T16pendmvtWHERE MVT_LOC not like 'r%';The MVT_LOC field is a char(3) field and some of the data is:R48R46R42 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
JJ297
Aged Yak Warrior
940 Posts |
Posted - 2010-10-06 : 15:33:56
|
| Okay thanks I don't know why it's not working on my Database. Okay let me investigate more. |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2010-10-06 : 15:57:50
|
quote:
Originally posted by JJ297
I'm trying to get all of the records except the one's that start with the letter R. I can't get this to work...SELECT COSSN, MVT_TYP, MVT_CDT, MVT_LOC, MVT_DESTFROM T16pendmvtWHERE MVT_LOC not like 'r%';The MVT_LOC field is a char(3) field and some of the data is:R48R46R42
Is it possible that your collation is case sensitive?Be One with the OptimizerTG |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
JJ297
Aged Yak Warrior
940 Posts |
Posted - 2010-10-08 : 10:43:38
|
| Thanks all for getting back to me. I'm thinking it's not working because I need to set up a subquery and I was trying to set each one up by it self then tie all together. Here's my attempt at this but didn't get too far. Could you assist me please:Select Count(Cossn) as Cossnfrom t16pendall awhere (MFT_POSN1_CD = 'b' or MFT_POSN1_CD='d')or (MFT_POSN1_CD = 'a' and Aged_Alien_RSW='Y') ajoin t16pendmvt b on a.cossn=b.cossn where b.cossn not in t16pendmvtThis is what I want...Count (COSSN), MFT_POSN1_CD = B or D or (MFT_POSN1_CD = A and AGED_ALIEN_RSW = Y) and COSSN is not = COSSN in T16pendmvt, and (2) T16pendmvt table - Count (COSSN), (MVT_TYP = R and MVT_LOC not = R** or S** or V**), and (3) T16pendmvt table - Count (COSSN) and MVT_TYP = T |
|
|
|
JJ297
Aged Yak Warrior
940 Posts |
Posted - 2010-10-08 : 10:51:06
|
| Let me rewrite the query that I want...To get the Pending Count of COSSN I need to add the following...1) Use T16pendall table to Count (COSSN), MFT_POSN1_CD = B or D or (MFT_POSN1_CD = A and AGED_ALIEN_RSW = Y) and COSSN is not = COSSN in T16pendmvt, and (2) use T16pendmvt table to Count (COSSN), (MVT_TYP = R and MVT_LOC not = R** or S** or V**), and (3) use T16pendmvt table to Count (COSSN) and MVT_TYP = T |
|
|
|
JJ297
Aged Yak Warrior
940 Posts |
Posted - 2010-10-08 : 11:06:43
|
| okay got a bit farther. I broke them all up but don't know how to fix the top query and how to place them all together. Help please...select count(cossn) as cossnfrom t16pendall ajoin t16pendmvt b on a.cossn = b.cossn where (MFT_POSN1_CD = 'b' or MFT_POSN1_CD='d')or (MFT_POSN1_CD = 'a' and Aged_Alien_RSW='Y') andCossn not in the t16pendmvt table (don't know how to write this)select count(cossn) as cossnfrom t16pendmvtwhere Mvt_typ ='r' and mvt_loc <> 'r%' or mvt_loc='s%' or mvt_loc='v%'select count(cossn) as cossnfrom t16pendmvtwhere MVT_Typ ='t' |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
JJ297
Aged Yak Warrior
940 Posts |
Posted - 2010-10-08 : 14:52:30
|
| Yes I did confirm and it doesn't matter but I got this to work:select count(cossn) as cossnfrom t16pendmvtwhere Mvt_typ ='r' and mvt_loc <> 'r%' or mvt_loc='s%' or mvt_loc='v%'I have another question and will open another post. Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
JJ297
Aged Yak Warrior
940 Posts |
Posted - 2010-10-08 : 15:10:09
|
| Sorry I put the wrong one down:select count(cossn) as cossnfrom t16pendmvtwhere mvt_typ='r'and (mvt_loc like' R%' or mvt_loc like' S%')This one works! |
|
|
|
|
help with like...