Derived Field - Subroutine

[Chris Weitzel]

I have a derived field TEST.CES calling:  (B('TEST2.CES,@RECORD,@ID'))  TEST2.CES compiles data from a couple of different files and spits back a date.  TEST.CES works fine with the QRD except export to Excel gives strange results - mostly that this field is blank for all but the last record.  RD gives me an error indicating that the parameter count doesn't match.  TCL> LISTworks well every time.

SUBROUTINE TEST2.CES(RET.DATA,ENTIRE.RECORD,MATCH.FIELD)

What gives?

Also, should I be able to RUN TEST2.CES from TCL passing it parameters?  If, so what is the syntax?

Thanks. Chris

[precisonline]

Hey Chris...

A field definition can call a subroutine a number of different ways but there is a syntax difference between calling a Unidata SUBR-format subroutine and calling an SB+-enabled subroutine.  You might be wondering: "What's the diff?"  Well, an SB+ subroutine has no formal arguments and handles all its input and output via common variables as described in DMSKELCODE COMMON.  A Unidata SUBR-style dictionary has a signature exactly as you've described, with a return value in the first position and potentially several more parameters after that.

Each style of subroutine uses a different syntax when used via a field definition.  For the SB+ subroutine, we would use the (B("...")) syntax.  For a SUBR-format subroutine, however, we have to use SB+'s "virtual field" syntax for Unidata, which would be something like this:

V(SUBR("TEST2.CES",@RECORD,@ID))

Note there are no outer parens on this "virtual" derived value expression.  The V(..) wrapper basically says to take what's inside of the parens and create a Unidata V-descriptor field with it.  (The V(..) may also be expressed as I(..) for Universe; I believe SB+ handles both identically on both platforms, creating a V-type field on Unidata and an I-descriptor on Universe regardless of whether V(..) or I(..) are used.)

To your second question, no, you cannot run a subroutine directly from TCL.  It needs to be called from some context, whether from the query language (via virtual fields), from SB+ (called from a paragraph, expression, validation, or other context), or from another BASIC routine.

[Chris Weitzel]

Thanks, Kevin.

Your reply contained good info, but it seems to have no impact on my results - unless, of course, I'm missing something.  I have been debugging most of the day and I'm seeing some really strange stuff.  Here's one instance from the Basic code that is called from my derived field:

GETLIST "CONSLINE.LIST" TO CONSLINE.LIST SETTING TOT.CNT ELSE PRINT ""

In my sample data set TOT.CNT is 6.  When TOT.CNT is assigned using the GETLIST command and I use it in a For/Next loop, I only get output in my derived field for the 6th record.  However, if I assign TOT.CNT = 6 that same For/Next loop will produce data for each record of output.  Keep in mind that this only applies to Excel output from a QRD.

I'm using multiple select/bselect/getlist statements in my code that depending on which combination of those I use (E.g., EXECUTE 'GET.LIST' vs. GETLIST), the results very from a select list of 6 records (which is correct) to a select list of 65000 records.

Does any of this make sense?  Am I violating some rules interacting with the dictionary with this basic code?

Any help is appreciated.

Chris

[precisonline]

The short answer is: Quite possibly.  Am I understanding that it's necessary to go select a file based on a field from each input record?

When you do any kind of list processing in BASIC, it's going to overwrite any list that might be active when the command is being run from TCL.  So you have to take precautions to avoid GETLIST or EXECUTE 'GET.LIST ...' or even EXECUTE 'SELECT...' from stepping on the dictionary.  The most common way to do this is in Unidata is to use BASICTYPE "U" and use a later numbered list, like 8 or 9 for selecting records.  This should not impact GETLIST but it will definitely impact EXECUTE 'GET.LIST...' and EXECUTE 'SELECT...' and other similar constructs.

Are you using BASICTYPE "U"?  Could you explain the situation a little so I have a better understanding of the need for all these active lists?

[Chris Weitzel]

I'm using basic type P.

The reason for all of this...Prelude doesn't store and/or package the keys for consignment orders very well.  As such, I can't simply use key fields to derive the "invoice date" from the CONS file for when an item was relocated from our warehouse to the customer's.

If you know anything about consignment orders you also realize the Prelude changes the order numbers based upon whether it's 07 or 08 type of order.

What am I doing to get this data:
1) Using order line for the 08 order I list the items (QRD)
- BASIC FROM HERE - using my derived field
2) <101,1> is used to get the original 07 order number for the CONS file
3) CONS is bselected using that value to get all of the line items for every shipment - the dash lines
4) CONS.LINE is then selected from the bselect but only those items that have a ship quantity
5) Once the shipped line is found <68> is grabbed from CONS to report back to the derived field

There may be a better approach, but this seems to be the least invasive way of doing this - only because the keys are really hard to get to.  Besides that this is the first time I've taken on this type of task before and I find it challenging; a bit too challenging.

Clear as mud?  I can provide data specifics if that's helpful.

Chris

[precisonline]

I followed much of that.  I understand that your QRD is on ORDER.LINE and you're selecting with ORD.TYPE = "08".  So you get a list of lines.  ORDER.LINE<101,1> has the original consignment order line reference, so why can't you go directly back to that line and any dash lines that might be associated with it?  ORDER.LINE REL.NUM could be useful as well.

What am I missing?

[Chris Weitzel]

Good questions.  I agree, but here' comes the voodoo:

08 order.line = 0016000218!6
<50> (REL.NUM) = blank - item was direct ship.  Not sure if PSI stores this anyway for consignments
<101,1> = 0011236580!3
<101,2> = 0011236580-1!3

The actual CONS.LINE "release" that I need is (drum roll please) 0011236580-2!3

What in the world!?

[precisonline]

First, great data!  Second, I still think we can get there from here without as much I/O.

If you start with 08 order line 0016000218!6 and go to <101,1> (0011236580!3) and then go to ORDER.HISTORY.LINE (or CONS.LINE) 0011236580!3 is there anything in that line record that refers to 0011236580-2!3?  Even if not, we could split that 0011236580!3 value into the order part (0011236580) and the line part (3) and then loop order-1!line, -2, -3, -4, 5 until you don't find any more ORDER.HISTORY.LINE/CONS.LINE records, and then for each record read you could find the one with shipped quantity and then return that value.

Do you suppose that might work in this scenario?

[Colin Alfke]

Sorry - no idea where to look in the PSI files for your data. However, I do know the download to Excel can be challenging. If you look at the TUBP TU.BUILD.XFER.DATA you can see that it tries to take the query statement from your QRD definition parse it out into a REFORMAT command which creates a file which it then transfers to your PC. There have been some changes to this routine in different versions of SB or it could be an issue with (I'm guessing) UniData. I'd double check the REFORMAT statement it's building and that there isn't a conflict with your code. Like Kevin said working with lists while in a dictionary (which is usually within a list itself) can be tricky. It sounds like the GETLIST SETTING is causing issues - you may need to figure out a different way around reading all of the values.

hth
Colin

[Chris Weitzel]

This dragon is slain.

Lessons learned
1. Never use Unidata Select statements in code that is called from a derived field because the results are completely unpredictable.

2. Kevin’s suggestion of manually creating a list of record IDs and then traversing it worked extremely well and performance was increased.

3. V(SUBR… vs (B(“… calls to subroutines.  Both types of call to my basic subroutine worked with LIST / QRD, but not RD’s.  Only V(SUBR worked with both LIST/QRD and RDs.

In the end I converted my Basic routine into a Paragraph because I no longer needed to use Unidata select statements and by removing those pieces of code, the final product was small enough that a paragraph seemed more appropriate.

Thanks all for the excellent advice.

Chris

[precisonline]

Sweet!  Glad you got a working solution!