Sequence number for multi valued data

wdudek · Post by **wdudek** » Wed Jun 16, 2004 10:31 am

We are extracting multi valued data from a Unidata table into Oracle, which contains an asset number and a current reading for the asset. So when the data normalizes there are multiple records per asset. We want to add a sequence number to this, that would start at 1 with the most recent reading and work it's way up for the other readings. i.e.

Unidata
Asset Reading
1234 10
25
45
4567 25
50
Oracle
Asset Reading Sequence
1234 10 3
1234 25 2
1234 45 1
4567 25 2
4567 50 1

Is this possible? If so does anyone have any suggestions?

kduke · Post by **kduke** » Wed Jun 16, 2004 11:05 am

You can create a dictionary item to count multivalues if that is what your question is about.

I
@NV

MvCnt
5R
M

This should work in Unidata as well as Universe. If not then let us know.

ray.wurlod · Post by **ray.wurlod** » Wed Jun 16, 2004 4:22 pm

Add BY.EXP.DSND Reading into your Selection tab. Allow DataStage to generate the counter values, using stage variable or other techniques to detect changes in the value of Asset.

Use the "Normalize On" drop down to normalize on Reading.

Does UniData support dynamic normalization in the same fashion that UniVerse does? If so, the synthetic key @ASSOC_ROW contains exactly the inverse of the counter you require; but you can perform an inverse sort (as above).

Have you read the technical bulletin Accessing UniVerse and UniData Databases from DataStage? It's with the DataStage manuals (where your client is installed, in the Docs folder, and is called techbull.pdf).

wdudek · Post by **wdudek** » Thu Jun 17, 2004 3:21 pm

Thanks, I've found the tech bulletin and will take a look into it. I never knew that was out there.

wdudek · Post by **wdudek** » Wed Jun 23, 2004 1:59 pm

How should I use the By.EXP.DSND in the selection, I tried the following and neither worked for me.

tAsAssetUsageReading..source.strSource: ds_udtopen() Failed to Open file - Failed to execute SELECT command(SELECT AS_ASSET_USAGE WITH BY.EXP.DSND AU_READING_DATES)
SELECT AS_ASSET_USAGE WITH BY.EXP.DSND AU_READING_DATES TO 1

tAsAssetUsageReading..source.strSource: ds_udtopen() Failed to Open file - Failed to execute SELECT command(SELECT AS_ASSET_USAGE WITH WITH ID = "21849" BY.EXP.DSND AU_READING_DATES)
SELECT AS_ASSET_USAGE WITH WITH ID = "21849" BY.EXP.DSND AU_READING_DATES TO 1

The first seemed to imply that I needed some type of selection criteria so I threw a valid key into the criteria but it still not not work. Any ideas or thoughts would be great. [/code]

wdudek · Post by **wdudek** » Wed Jun 23, 2004 2:01 pm

ok, I took the second with out of the selection criteria, I seem to have a habit of putting this in there. But now I am getting the following

tAsAssetUsageReading..source.strSource: ds_udtGetNextPacket Error calling DSHELPER subroutine - Record 21849
1
-5 not found.

any thoughts? The Records 21849 is a valid record and is the one that I am selecting.

ray.wurlod · Post by **ray.wurlod** » Wed Jun 23, 2004 4:29 pm

No, it seems to be trying to read record "21849" : @VM : "1" - the delimiter is what causes the 1 to appear on a new line in the logged event.

Somehow you're not properly normalizing the data.

BY.EXP.DSND is not part of the WITH phrase - it is a separate clause. To use an SQL analogy WITH maps to WHERE, and BY.EXP.DSND maps to ORDER BY. Try putting BY.EXP.DSND Reading at the very end of the selection criteria, so that you end up with

Code: Select all

WITH criteria BY.EXP.DSND AU_READING_DATES

wdudek · Post by **wdudek** » Fri Jun 25, 2004 1:52 pm

My selection looks like

Code: Select all

ID =  "21849" BY.EXP.DSND AU_READING_DATES

, but I am still experiencing the problem when I run this or try to view the data. I tried changing the selection to

Code: Select all

ID # "" BY.EXP.DSND AU_READING_DATES

and got the error

tAsAssetUsageReading..source.strSource: ds_udtGetNextPacket Error calling DSHELPER subroutine - Record 221798
3
-5 not found.

In the Unidata stage I am normalizing on the association related to the au_reading_dates, which happens to be the only option besides not normalized. I'm probably going to get support involved to help me figure out what I'm doing wron here, but always appreciate any suggestions.

ray.wurlod · Post by **ray.wurlod** » Fri Jun 25, 2004 6:26 pm

If you're normalizing on the multi-valued field, you don't need an exploded sort. I wouldn't be a problem with UniVerse, but I'm not sure with UniData. Try BY.DSND rather than BY.EXP.DSND.

wdudek · Post by **wdudek** » Mon Jun 28, 2004 11:16 am

Using the exploded sort on the normalized data was definately a problem in Unidata. I changed this to BY.DSND and am getting the results I was looking for. Thanks for the help

Should the BY.EXP.DSND work if I am not normalizing in Unidata? just for fun I took out the nomralization and put back the BY.EXP.DSND, and it came back with a similiar error to what I was receiving originally.

ray.wurlod · Post by **ray.wurlod** » Mon Jun 28, 2004 3:53 pm

I really don't know UniData well enough to be cogent on this. In UniVerse it should be OK. Try the same command at the ECL prompt (assuming you can get at it) and see the rows that are generated using BY.EXP.DSND to determine whether that's what you'd expect DataStage to be receiving.

wdudek · Post by **wdudek** » Tue Jun 29, 2004 3:12 pm

Ok, I have achieved my original goal, but would like to hear anyone's opinion on the way I did this.
Step 1.

Code: Select all

Extracted the un-normalized data into a hash file

Step 2.

Code: Select all

Normalized out of the hash file

Step 3.

Code: Select all

In a Transformer used UtilityHashLookup on the hash file just populated, to get the un-normalized data, then called a custom routine to find the position the current meter_reading (normalized row) occurred in the multi valued field. (this is the data I needed)

As for the BY.EXP.DSND, it works great when I executed it at tcl in Unidata, but not when I used it with teh Unidata stage in Datastage. However by doing this type of lookup I haven't needed to use it. Although it also worked fine from the hash file when I first tried that.

I started out trying to keep the value of the last key in a variable and compare it to the current key, then either increment or reset another variable to use in my outpur from a transformer, but found that for my level of datastage knowledge the method I used was faster to code/easier.