Author Topic: Query Report - BSELECT Prior to Running  (Read 4197 times)

Chris Weitzel

  • Professional
  • ***
  • Posts: 30
Query Report - BSELECT Prior to Running
« on: May 09, 2013, 01:07:58 PM »
Disclaimer: I'm not sure which area best fits this question, so I just grabbed one.  :)

What?
I need to run a BSELECT on PO.HISTORY to obtain the line keys prior executing the QRD to report on PO.HISTORY.LINE

Why?
1. Speed.  We have over 1 million Line records.  Creating an index on LINE > REC.DATE proved to be a bust because in Prelude it is derived from the header and HEADER > REC.DATE isn't available at the time the line record is created, so the index gets updated with null values.

2. Using the HEADER and pulling the line data via derived fields doesn't work when the selection criteria includes Product Numbers (no associated value errors).

How?
The only way I can think to tackle this is as described in the What?  Other suggestions are welcome.

Issue?
Prior to executing the QRD I want the user to input 2 REC.DATES, what's the best way to do this?  I'm current writing a Basic program to grab the input (called from Process Before in QRD), but I want to present a date "mask" for the user input to make it more polished.  How in the world do I do that?

Conclusion
If there's an entirely better approach to this whole thing, please tell me.  QRD's satisfy 98% of our data extraction needs and having some kind of slick pre-processor available as I've described would be very useful.

Chris W.

precisonline

  • President
  • Administrator
  • Rock Star
  • *****
  • Posts: 1612
    • Precision Solutions
Re: Query Report - BSELECT Prior to Running
« Reply #1 on: May 09, 2013, 01:55:34 PM »
Hey Chris.

Start by downloading my standard processes document at http://www.PrecisOnline.com/pdf/stdproc.pdf .  There's a process in there called SELECT.TO.LIST.  You may have to modify it a little for your system, but this will be a good foundation.

Create a screen that prompts for the dates you want into work variables, build the selection statement(s) into @VALUE, and then call this.  This will do the selection and save a list called WRK.port (where port is the current port#).  Then when you run your query report, use the selection criteria:

WRK.:PORT

(and while it looks odd, there is no @ in front of PORT.)

The @VALUE is a multivalued list of statements that should be executed sequentially.  So for example, if you wanted to do something like this:

SELECT PO.HISTORY WITH PO.DATE >= "..." AND WITH PO.DATE <= "..."
SELECT PO.HISTORY LINE.KEY

You could build a paragraph like this:

@VALUE = 'SELECT PO.HISTORY WITH PO.DATE >= "' : @WORK<3,1> : '" AND WITH PO.DATE <= "' : @WORK<3,2> : '"'
@VALUE<1,2> = 'SELECT PO.HISTORY LINE.KEY'
EXEC 'SELECT.TO.LIST'
EXEC 'myReport'

This is a simplistic example, but it shows how you can do multiple select statements back to back, ending with either a saved list named WRK.2 (for port 2) or no list at all if nothing was selected.  The report should then be able to consume this list.

I also have a document that describes how to front end a query with a /URM with a couple of helper paragraphs.  This would make it even easier to prompt for input and update a couple of work variables.  Drop me an email if you want that doc.
-Kevin
Accidents "happen"; success, however, is planned and executed.

Chris Weitzel

  • Professional
  • ***
  • Posts: 30
Re: Query Report - BSELECT Prior to Running
« Reply #2 on: May 14, 2013, 02:23:58 PM »
Kevin,

Thanks for the excellent information!  It took me a bit to understand the tools, but I now have a working solution that's a hybrid of your suggestions.  For the edification of anyone who's interested, here's what I ended up with:

Code: [Select]
EXEC "GET.CO,0"
@WORK<29>="VENDOR.DEL.RPT.CES"
EXEC "GRI.ENT"
IF @WORK<29,1>#"GRI" THEN GOTO 9999; * Confirm user didn't Escape
@VALUE = 'SELECT PO.HISTORY WITH REC.DATE >= "' : @WORK<1,1>
@VALUE = @VALUE : '" AND WITH REC.DATE <= "' : @WORK<1,2> : '" LINE.KEY'
EXEC 'SELECT.TO.LIST.CES'
EXEC 'E*VENDOR.DEL.DD.CES'
EXEC 'S:DELETE-LIST WRK.' : @PORT
9999