SQL query to compare strings

[Former Member]

I am working on an unusual query. The need is to compare two strings and determine their sequence. These are revision codes, and I am identifying batches whose revision is less than a revsion cut off value. Items with revsions under the cut off value are considered obsolete.

The real issue is to compare string values of one or two letter codes, as such:

Item Revsion Rev Cut Off Obsolete Y/N

A00001 D B N D comes after B

A00002 E H Y E comes before H, so it's obsolete

A00003 A AJ Y A comes before AJ

A00004 CJ BD N CJ comes after BD

A00005 DT T N DT comes after T

Revision starts at A and goes to ZZ. The query is attempting to assign a unicode value to the above revsions and rev cut offs and compare the values, since a string comparrision does not see the sequence of strings correctly.

the query works for most cases, but not for all and I am trying to uncover a better way to do this query.

Query:

SELECT T0.itemcode, t1.itemname, T0.batchnum, T0.U_revision, t1.U_Revcutoff, T0.quantity,

(case when unicode(t0.u_revision) is not null then unicode(t0.u_revision) else 0 end) +

(case when unicode(substring(t0.u_revision,2,1)) is not null then unicode(substring(t0.u_revision,2,1)) else 0 end) as exp1,

(case when unicode(t1.u_revcutoff) is not null then unicode(t1.u_revcutoff) else 0 end) +

(case when unicode(substring(t1.u_revcutoff,2,1)) is not null then unicode(substring(t1.u_revcutoff,2,1)) else 0 end) as exp2

FROM OIBT t0 inner join OITM t1 on t0.itemcode = t1.itemcode

and ((case when unicode(t0.u_revision) is not null then unicode(t0.u_revision) else 0 end) +

(case when unicode(substring(t0.u_revision,2,1)) is not null then unicode(substring(t0.u_revision,2,1)) else 0 end)) <

((case when unicode(t1.u_revcutoff) is not null then unicode(t1.u_revcutoff) else 0 end) +

(case when unicode(substring(t1.u_revcutoff,2,1)) is not null then unicode(substring(t1.u_revcutoff,2,1)) else 0 end))

where t0.whscode = '01'

and t0.quantity > 0

order by t0.itemcode, t0.batchnum

Any ideas or other ways to sort and compare the string data is appreciated, thanks

[Former Member · Accepted solution]

You posting is difficult to read. Please check if this simplified query could run or not first:

SELECT T0.itemcode, t1.itemname, T0.batchnum, T0.U_revision, t1.U_Revcutoff, T0.quantity, (IsNull(unicode(t0.u_revision), 0) + IsNull(unicode(substring(t0.u_revision,2,1)),0)) as exp1,

(IsNull(unicode(t0.u_revcutoff), 0) + IsNull(unicode(substring(t0.u_revcutoff,2,1)),0)) as exp2

FROM OIBT t0

inner join OITM t1 on t0.itemcode = t1.itemcode and

(IsNull(unicode(t0.u_revision), 0) + IsNull(unicode(substring(t0.u_revision,2,1)),0)) < (IsNull(unicode(t0.u_revcutoff), 0) + IsNull(unicode(substring(t0.u_revcutoff,2,1)),0))

where t0.whscode = '01' and t0.quantity > 0

order by t0.itemcode, t0.batchnum

Thanks,

Gordon

[Former Member · Accepted solution]

Thanks Gordon, your query is simpler and produces the same results. An example of the problem is that Revision 'BG' has a value of 137 and Rev Cut Off 'AW' has a value of 152. So although BG comes after AW (BG is greater than AW) in an alpha sequence, and should be excluded from the results I need, but the values include it (137 is less than 152).

[Former Member · Accepted solution]

I am not quite sure what is your revision ordered. Your first posy said Revision starts at A and goes to ZZ. That sounds in alphabetical order. Is that not true?

[Former Member · Accepted solution]

It is in alpha order, A to ZZ. but when i compare and try to see if revsion is less (before) or greater (after) the rev check, not all combinations work. My original query was U_Revsion < U_revcutoff. If true then select the data. But U_revision could be 'AG' and Revcutoff could be 'B'. In our revision sequence, AG should come after B, but in the query AG comes before B. It sees a string starting with A and places it before B. Hope this helps make the problem a little clearer.

thanks

[Former Member · Accepted solution]

Gordon, a better way to explain the sequence is that it goes from A to Z, then AA to ZZ. So Z comes before AA.

[Former Member · Accepted solution]

Add some codes like:

IF IsNull(unicode(substring(t0.u_revision,2,2)),0))< ''' Then before SELECT

I am not quite sure what your UDF value actually look like.

[Former Member · Accepted solution]

The UDFs contain one or two letter codes, like A, B, C, AF, DT, ZZ. These serve as the sequence of revisions, and are compared to a cut off value, in the same pattern: A, B, AA, BB, DT etc. Any combination is possible within this framework.

the comparison to see if the revision value is higher or lower than the cut off value. With a hierarchy sequence of A, B, C, D...AA, AB, AC, AD....ZX, ZY, ZZ. the difficulty is a string doesn't compare values this way, in a standard compare, this data would sort as: A,AA, AB, AC...B, BA, BB, BC....Z, ZA, ZB, ZC....

[Former Member · Accepted solution]

You have to find some ways to distinguish the 1 letter from two letters to compare them. That is suggestion I given above. I am not sure what would follow the 1 letter in your example.