Re: when v7r1 SQE is slower than v6r1 cqe

On 18-Mar-2014 08:11 -0700, Steve Landess wrote:

How 'bout:

where CHAR(thictl) = FCDKEY8

The [implicit] cast to CHAR, whether using the explicit CAST function or the CHAR scalar, will left-justify the value, with the insignificant leading zeroes suppressed. However what the DIGITS [casting] scalar does, maintaining the leading zeroes, apparently had been verified to produce the desired effect; see "was changed to" [in the oldest of the quoted messages] below.

Consider the example value '002021234' [although I think incorrectly expressed with an extra leading zero] being compared against:

• CHAR( cast('002021234' as numeric(9)), 9) = '2021234'

• CHAR( cast('002021234' as numeric(9)), 9) <> '002021234'

• DIGITS(cast('002021234' as numeric(9)) ) = '002021234'

Further comment included inline to the prior followup reply.

On 17-Mar-2014 10:57 -0700, J Franz wrote:

Well I spoke too soon...
The visual studio client editor won't pass the DIGITS keyword.

Apparently, from a followup reply, the editor had an option to ignore the errors [much like SEU]; i.e. the DIGITS was able to be used without difficulties. As Buck noted, the option of encapsulating the JOIN in a VIEW [with the DIGITS scalar in the JOIN predicate] should be both reasonable and often beneficial; every client query requiring the same join need not have to know\(re)learn the best way to write that join.

Additionally, the SQL derived index for the expression digits(THICTL) should enable implementation via that access path; although the effect already achieved, reducing the query run time from minutes to seconds, perhaps that already exists.?

How can I do something like this: cast(thictl as char)= FCDKEY8
but where result of cast has leading zeros ? Must be code Visual
Studio will accept.

btw - FCDKEY8 does have non numeric characters in some records -

That is very important to note. Implying the values can not be cast to numeric to enable the comparison.

it's a generic sorting field of Content Manager, and only the
records that have our control number in FCDKEY8 should be selected
(and they have leading zeros like '002021234'

Per "our control number", consider that a file can be created to map each numeric control number to the corresponding DIGITS(THICTL) value. That file could be included in the JOIN, and properly indexed to find the equivalent value in the indexed column FCDKEY8, providing a means for the query to rapidly resolve the match rather than evaluating the [character] expression. A file more generically could include every value, instead of only the specific control numbers, but obviously with the additional cost for the storage.

J Franz on Monday, March 17, 2014 1:16 PM wrote:

Appreciate all the responses. It appears the derived column in
the join stmt was the killer (trying to join from number to text
field (and prob 7 year old code)) - this is 15 lines into the SQL
stmt and executes 1 - 2 minutes

...
FROM DT LEFT JOIN EKD0312S8
ON FCDKEY8 =
CHAR(INSERT( CHAR(THICTL)
, 1
, 0
, (REPEAT('0'
, LENGTH(THICTL) - CHARACTER_LENGTH(THICTL)
)
)
)
, 9)
AND FCDCODE LIKE 'GBILL%'
... more stmts <ed: ¿predicates?>

was changed to (executes 1 - 2 seconds)

...
FROM DT LEFT JOIN EKD0312
ON digits(THICTL) = FCDKEY8
AND FCDCODE LIKE 'GBILL%'
... more stmts <ed: ¿predicates?>