Lookup Query manipulating value of marker

[dwblore]

Hello

This is my requirement
=================
Source Seq File
=================

School Address
--------- +++++
Qwds POLS
DeNobili BVDFD
RobDeW DFDSF
Xrewq

================
Lookup Table in DB2
================

SchoolName
--------------
De
HJ



Output


School Address
--------- +++++
DeNobili BVDFD
RobDeW DFDSF


I have been trying to use the substr and locate funcations of DB2 in the lookup query to try and mimic a contains operation however I keep getting errors regarding the paramer marker.

Anyone Solved similar prblm?

[ray.wurlod]

Does this work?

Code: Select all

WHERE key LIKE '%?%'

Note that you can not do this with a hashed file, which can only do WHERE key = 'value'.

[dwblore]

Does this work?

Code: Select all

WHERE key LIKE '%?%'

Note that you can not do this with a hashed file, which can only do WHERE key = 'value'.

Hi

Unfortunately its the revers we're looking for

WHERE key LIKE '%?%'

key values -
De
HJ

while values coming into ?
DeNobili
RobDeW

I was trying something like this:

SELECT DISTINCT SCHOOL FROM LOOKUPBIRDS WHERE SCHOOL=SUBSTR(?,CAST(LOCATE(SCHOOL,CAST(? AS VARCHAR(32672)) AS INTEGER)) AND SCHOOL IS NOT NULL;

but evidently its not working

[kumar_s]

Still I couldnt understand how you manage to lookup RobDeW with HJ?

[dwblore]

Still I couldnt understand how you manage to lookup RobDeW with HJ?

We're fine with the first match
in our case - the 'HJ' is not needed

Considering that the first lookup value was lets say 'UI'
then since UI and HJ are not contained in the source records(school field) - we wud get a null result set

thnx

[ray.wurlod]

Are there only a few lookup values? If so you can pre-load the lookup values into a searchable structure (a dynamic array) in a before-stage subroutine and perform the search in a transform function (a Routine).

You will need a small job to get the keys out of DB2 and into a sequential file (sorted by key is best - include an ORDER BY clause in your SELECT).

The before-stage subroutine initializes and populates a dynamic array either in a variable that is declared to be in COMMON or in one of the user system variables @USER0 through @USER4.

Code: Select all

SUBROUTINE LoadSearchValues(InputArg, ErrorCode)
DEFFUN OpenSequentialFile(File, OpenMode, WriteMode, Logging) Calling "DSU.OpenSequentialFile"
ErrorCode = 0 ; * set to non-zero value to stop job
@USER3 = ""
hFile = OpenSequentialFile(filename, "R", "A", "Y")
If FileInfo(hFile, 0)
Then
   Loop
   While ReadSeq Key From hFile
      @USER3<-1> = Key
   Repeat
   CloseSeq hFile
End
RETURN

The transform function uses an Index function to search for the partial value in this dynamic array.

Code: Select all

FUNCTION FindCode(SearchValue)
* Returns 1 if found, 0 if not found.
* Search values are in @USER3
SetRem 0 On @USER3
Ans = @FALSE
Loop
   Remove PartKey From @USER3 Setting MoreKeys
   If Index(SearchValue, PartKey, 1) > 0
   Then
      Ans = @TRUE
      Exit
   End
While MoreKeys
Repeat
RETURN(Ans)

The OpenSequentialFile function can be downloaded from ADN.

[dwblore]

Hi

Had thought on similar lines:

what I could also do is this:

from the present lookup file
De
HJ
Create a new lookup ->
New School
1 De;HJ;...

add a dummy column to the source with the value of 1

do a lookup on the new lookup file and bring in the school column - which contains all the lookup values.

Next pass the original value for school coming in for each record and the lookup string to a routine which
for ; delimited field check if it is contained in the orginal value.


Lookup
Id LkpSchool
1 De;HJ;...


Source

School Address Id ---------> School Address Id LkpSchool

Qwds POLS 1 ----------> Qwds POLS 1 De;HJ;...
DeNobili BVDFD 1 ----------> DeNobili BVDFD 1 De;HJ;...
RobDeW DFDSF 1 ----------> RobDeW DFDSF 1 De;HJ;...
Xrewq 1 ----------> Xrewq 1 De;HJ;...
routine(school,LkpSchool) returns true or false

The only concern what is the limit to which the no. and length of the lookup values can increase to ensure "decent" efficiency

Please advice

Thnx

[ray.wurlod]

Your ";" delimited string is a de facto dynamic array. A "proper" dynamic array (delimited by @FM, @VM and so on) is slightly more efficient, because you can use the Remove statement to pick of one at a time; with your delimited string you need the Field() function which may require scanning from the beginning each time.

The approach should be OK for a few tens, maybe even a couple of hundreds, of values. The faster your machine, the more that can be handled, obviously.

[ArndW]

..WHOLE message quoted

There really is no reason to quote an entire post, particularly if it is the one right before yours. It keeps the pages to a manageable size and makes it easier to read and understand.

[dwblore]

..WHOLE message quoted

Lesson learnt

Always realize better ways of doing things out here

Thnx