JonYen
asked on
SQL query help
Hi Experts,
I need to create a dataset from a query to show me what the last non blank value in 5 fields within a record. The fields are TID1, TID2, TID3, TID4 and TID5.
They will be populated in this way - either just the the first one, the first 2, first 3, first 4 or all 5. There are never any gaps so I so only need to return the last non-blank value.
For example.
TID1 TID2 TID3 TID4 TID5
14 232 244 (this sholod return 244)
13 231 (this should return 231)
12 238 238 256 (this should return 256)
14 237 250 279 410 (this should return 410)
Hope I have explained this well enough.
Thanks
Jon
I need to create a dataset from a query to show me what the last non blank value in 5 fields within a record. The fields are TID1, TID2, TID3, TID4 and TID5.
They will be populated in this way - either just the the first one, the first 2, first 3, first 4 or all 5. There are never any gaps so I so only need to return the last non-blank value.
For example.
TID1 TID2 TID3 TID4 TID5
14 232 244 (this sholod return 244)
13 231 (this should return 231)
12 238 238 256 (this should return 256)
14 237 250 279 410 (this should return 410)
Hope I have explained this well enough.
Thanks
Jon
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You say "non blank". Are we to assume that the columns are character datatype? Are they nullable?
Open in new window
Using ANSI SQL it would look more like this.
Open in new window
The basics of this is your are looking at the fields in reverse until you find one that isn't NULL. If you're comparing against an empty string, just change "IS NOT NULL" to compare for any empty string: "<> ''"