Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Question about a query

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Question about a query
 Login/Join
 
Silver Member
posted
Hello everyone,

I was wondering if you could help me build this query.

We've got a pricing table with 3 keys (2 id fields and one column that holds the pricing type) and 2 normal columns: unit price and rebate.
for every combination of keys and pricing type there are 2 records: one with the unit price and one with the rebate.
So we would have something like this:

key key pricingtype(unit) unitprice 0
key key pricingtype(rebate) 0 rebate

This means there are 2 pricing records for every invoice item.
If I join them normally I would get something like this:

invoiceitemstuff..........unitprice 0
sameinvoiceitemstuff...... 0 rebate

Now, what is required in the report is to have the unitprice and the rebate on the same line.

I tried some stuff with across but the result's not quite what I was hoping for :/
By the way, the way to know for sure if I need to use the rebate column is to check the pricing type. If it's in a range of values, use the rebate column, if it's in some other range of values, use the unit price column.

I've put this in COMPUTE fields, because I can only check the value of the pricing type field per record.

Any help would be much appreciated.
 
Posts: 44 | Location: Belgium | Registered: January 10, 2005Report This Post
Guru
posted Hide Post
You're on the right track with testing the pricingtype. Since you need to test every row of data, I would put it in a define.

DEFINE FILE filename
UPRICE/D12.2 = IF (PRICINGYTPE GE 'somevalue' AND PRICING TYPE LE 'somevalue') THEN UNITPRICE ELSE 0;
RBTE/D12.2= IF (PRICINGTYPE GE 'somevalue' AND PRICINGTYPE LE 'somevalue') THEN REBATE ELSE 0;
END

In the TABLE FILE
SUM the UPRICE and RBTE fields BY INVOICE
 
Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003Report This Post
Silver Member
posted Hide Post
Piipster, thanks for the reply.

Here's what I had already:

COMPUTE UnitPrice/P15.2 = IF KSCHL EQ 'ZMAN'
OR KSCHL EQ 'ZSAF'
OR KSCHL EQ 'ZBA2'
OR KSCHL EQ 'ZSA2'
THEN KBETR
ELSE 0;
AS 'Unit, Price,Rebate'
PRICING.WAERS AS 'Curr.'
AND COMPUTE Rebate/P15.2 = IF KSCHL EQ 'YO03'
OR KSCHL EQ 'YO02'
OR KSCHL EQ 'YO01'
OR KSCHL EQ 'BO01'
OR KSCHL EQ 'BO02'
OR KSCHL EQ 'BO03'
OR KSCHL EQ 'BO04'
OR KSCHL EQ 'BO05'
THEN KWERT * (-1)
ELSE 0;
AS 'Rebate, Value'
If I use a define, FOCUS complains about KSCHL not being known to the system.

But I've been looking for another method.
I know it's kind of ridiculous, but I'm joining the PRICING table to itself.
That is, I'm joining parts of the Pricing table to itself.
First I select all pricing records which are UnitPrices, and I put them in a HOLD file.
(ON TABLE HOLD AS UnitPrices FORMAT ALPHA)
Then I select all pricing records which are Rebates and put them in another HOLD file.
(ON TABLE HOLD AS Rebates FORMAT ALPHA)

Afterwards, I join those two hold files into one, and use this one table file to join to the invoice table.

However...(of course there's a "however")
I get following errors:

(FOC1071) VALUE FOR JOIN 'TO' FIELD OUT OF SEQUENCE. RETRIEVAL ENDED
0 ERROR AT OR NEAR LINE 50 IN PROCEDURE XXX FOCEXEC *
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: PRICINK
0 ERROR AT OR NEAR LINE 53 IN PROCEDURE XXX FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: Pricink.UnitPrice
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT[/code]If I just print the "Pricink" table it works just fine.

Here's the code:



TABLE FILE PRICING
PRINT
KNUMV
KPOSN
COMPUTE UnitPrice/P15.2 = IF KSCHL EQ 'ZMAN'
OR KSCHL EQ 'ZSAF'
OR KSCHL EQ 'ZBA2'
OR KSCHL EQ 'ZSA2'
THEN KBETR
ELSE 0;
WAERS
WHERE KSCHL IN ('ZMAN','ZSAF','ZBA2','ZSA2');
ON TABLE HOLD AS UnitPrices FORMAT ALPHA
END

TABLE FILE PRICING
PRINT
KNUMV
KPOSN
COMPUTE Rebate/P15.2 = IF KSCHL EQ 'YO03'
OR KSCHL EQ 'YO02'
OR KSCHL EQ 'YO01'
OR KSCHL EQ 'BO01'
OR KSCHL EQ 'BO02'
OR KSCHL EQ 'BO03'
OR KSCHL EQ 'BO04'
OR KSCHL EQ 'BO05'
THEN KWERT * (-1)
ELSE 0;
WHERE KSCHL IN ('YO01','YO02','YO03','BO01','BO02','BO03','BO04','BO05');
ON TABLE HOLD AS Rebates FORMAT ALPHA
END

JOIN KNUMV AND KPOSN IN UnitPrices
TO ALL KNUMV AND KPOSN IN Rebates AS J1
END

TABLE FILE UnitPrices

PRINT
UnitPrice
WAERS
Rebate

BY KNUMV
BY KPOSN
ON TABLE HOLD AS Pricink FORMAT ALPHA
END

JOIN INVOICEITEM.KNUMV AND INVOICEITEM.POSNR IN INVOICEITEM
TO KNUMV AND KPOSN IN Pricink AS J2
END

TABLE FILE INVOICEITEM
PRINT
Pricink.UnitPrice
Pricink.WAERS
Pricink.Rebate

BY KNUMV
BY KPOSN

WHERE READLIMIT EQ '20';
WHERE RECORDLIMIT EQ '20';
END

This message has been edited. Last edited by: <Mabel>,
 
Posts: 44 | Location: Belgium | Registered: January 10, 2005Report This Post
Silver Member
posted Hide Post
Ok, it's working now.
I think it was because I wasn't using
BY KNUMV
BY KPOSN
in my holdfiles.
Or perhaps the spaces between the BY and the ON TABLE statements.

Update:
Alright, so I managed to get the test-file working. But the report itself still isn't :/

It was because the BY fields weren't in the correct order.
But in my report I have to join the MASTER table file to this newly created holdfile with both the UnitPrice and Rebate in one record.
This MASTER table file does not have its records in the correct order (sorted by KNUMV and KPOSN that is), so I'm unable to join them.

I tried creating a FOCUS hold file

DEFINE FILE UnitPrices
KeyField/A16 = KNUMV||KPOSN;
END

TABLE FILE UnitPrices
PRINT
UnitPrice
WAERS
Rebate
BY KeyField
ON TABLE HOLD AS Pricink FORMAT FOCUS INDEX KeyField
END
And join this to the master file (already joined with 4 other tables!):

DEFINE FILE INVOICEITEM
KeyField/A16 = KNUMV||POSNR;
END

TABLE FILE INVOICEITEM
PRINT *
BY KeyField
ON TABLE HOLD AS InvItem FORMAT FOCUS INDEX KeyField
END
And join those tables together:
JOIN KeyField IN InvItem TO KeyField IN Pricink
AS J006
END
Now I'm getting a CGI time-out...

This message has been edited. Last edited by: <Mabel>,
 
Posts: 44 | Location: Belgium | Registered: January 10, 2005Report This Post
Silver Member
posted Hide Post
I don't know if anyone's interested in this thread, but a new solution requires a follow-up.

Alright, so I was getting the CGI Timeout. I figured it was because of the creation of the invoiceitem holdfile (result of 6 joins), and creating an index on that FOCUS holdfile.

What I've done this time around is:

*Moved the computes to the DEFINE statement in both the UnitPrices and Rebates holdfiles.

*Got rid of the FORMAT FOCUS and used ALPHA instead.

*Used CAPITALS in my PRINT statements.

*Changed my join method to use the Pricink holdfile (result table of joining the rebates with the unitprices) as a MASTER, and join all the other tables to this Pricink table.

*Rejoiced when I saw the report coming out without errors.

But the query is taking too long to my likings, so tuning performance is next.
Any tips?
 
Posts: 44 | Location: Belgium | Registered: January 10, 2005Report This Post
Silver Member
posted Hide Post
*bump*
 
Posts: 44 | Location: Belgium | Registered: January 10, 2005Report This Post
Master
posted Hide Post
WHY NOT YOU DO SOMETHING LIKE THIS

DEFINE FILE PRICING
PRICETYPE/A1 = IF KSCHL EQ 'ZMAN' OR 'ZSAF' OR 'ZBA2' OR 'ZSA2' THEN 'U' ELSE 'R';
END

TABLE FILE PRICING
PRINT
KNUMV
KPOSN
WAERS
KWERT
PRICETYPE
WHERE KSCHL EQ 'ZMAN' OR 'ZSAF' OR 'ZBA2' OR 'ZSA2' OR 'YO01' OR 'YO02' OR 'YO03' OR 'BO01' OR 'BO02' OR 'BO03' OR 'BO04' OR 'BO05';
ON TABLE HOLD
END

DEFINE FILE HOLD
NEWPRICE/P15.2 = IF PRICETYPE EQ 'U' THEN KWERT ELSE KWERT * (-1);
END

TABLE FILE HOLD
SUM
WAERS
NEWPRICE
BY KNUMV
BY KPOSNR
-* BY HIGHEST PRICETYPE if you want Unit Price above Rebate Price else remove highest
BY HIGHEST PRICETYPE
END
 
Posts: 865 | Registered: May 24, 2004Report This Post
Master
posted Hide Post
For even better performance you could sum the first table

BY KNUMV
BY KPOSNR
BY KSCHL
 
Posts: 865 | Registered: May 24, 2004Report This Post
Expert
posted Hide Post
Tim, sounds like you're requiring a cartesian product of your data sets. You can go to the support site, and do a knowledgebase search on the word CARTESIAN and come up with a list of articles and references on producing it.
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Silver Member
posted Hide Post
quote:
Originally posted by TexasStingray:
[qb] WHY NOT YOU DO SOMETHING LIKE THIS[/qb]
NOT I DO SOMETHING LIKE THAT, because that's not what I need.
I need rebate and unitprice on the same line, not on separate lines.

Susannah:
About that Cartesian product. I always thought a Cartesian product was when you joined 2 tables, rather than only getting the output of the records that have common join-fields, other records are printed out as well.

I don't see why I would need this. Could you explain as to why I would find this useful?
Thanks in advance.
 
Posts: 44 | Location: Belgium | Registered: January 10, 2005Report This Post
Master
posted Hide Post
Tim, you could still do the first step and sum them by the 3 fields. Then create you 2 define field on the hold file. Then sum them on the 2 key fields
 
Posts: 865 | Registered: May 24, 2004Report This Post
Expert
posted Hide Post
matrix
if in table A you have a1 , a2...
and in table B you have b1, b2
then in the result you have
a1 b1
a1 b2
a2 b1
a2 b2
From my reading of your case, you wanted 'every combination of keys and pricing type ';
Thats a cartesian product
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Virtuoso
posted Hide Post
quote:
Originally posted by Piipster:
[qb]
DEFINE FILE filename
UPRICE/D12.2 = IF (PRICINGYTPE GE 'somevalue' AND PRICING TYPE LE 'somevalue') THEN UNITPRICE ELSE 0;
RBTE/D12.2= IF (PRICINGTYPE GE 'somevalue' AND PRICINGTYPE LE 'somevalue') THEN REBATE ELSE 0;
END

In the TABLE FILE
SUM the UPRICE and RBTE fields BY INVOICE [/qb]
Too much trouble. If there are 2 and only 2 records for each distinct combo of key1 and key2, one giving UNITPRICE (with REBATE zero), t'other giving REBATE (with UNITPRICE zero), ignore PRINCINGTYPE and just add them:

TABLE FILE fn
SUM UNITPRICE REBATE BY key1 BY key2
END

to list the reference table, and similarly to with HOLD to create a flattened lookup table.

Or did I miss something?
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Silver Member
posted Hide Post
That was what I was thinking.

And I don't think it's a Cartesian Product that I require either.

Also I don't think it'd be wise to use the SUM statement. Because of KBETR (which would be UnitPrice) also has a value in the rebate line.
Example:

KNUMV KPOSN KSCH KBETR WAERS KPEIN KME KKURS KWERT
---------- ------ ---- ---------- ----- ---------- --- ---------- ----------
260979 10 ZMAN 1100 EUR 1 TO 1 22055
260979 10 ZTRA 0 EUR 0 1 0
260979 10 BO01 -20 0 0 -441,1
260979 10 SKTV 0 0 0 0[/code]What I need is the KWERT of the record with BO01 (that's the rebate) and the KBETR of the record with ZMAN (which is the unitprice). On ONE single line.
Example (this has just been copy/paste work):

KNUMV KPOSN UnitPrice WAERS Rebate
---------- ------ ---------- ----- ----------
260979 10 1100 EUR 441,1[/code]I agree with a SUM KWERT KBETR BY KNUMV BY KPOSN I would get such a result, but wouldn't KBETR be 1080 in the example case?

*puzzled*

This message has been edited. Last edited by: <Mabel>,
 
Posts: 44 | Location: Belgium | Registered: January 10, 2005Report This Post
Silver Member
posted Hide Post
My god...
I am so stupid. :/

Here's the solution.

DEFINE FILE PRICING

UPRICE/P15.2=IF KSCHL EQ 'ZMAN'
OR KSCHL EQ 'ZSAF'
OR KSCHL EQ 'ZBA2'
OR KSCHL EQ 'ZSA2'
THEN KBETR
ELSE 0;


REBATE/P15.2=IF KSCHL EQ 'YO01'
OR KSCHL EQ 'YO02'
OR KSCHL EQ 'YO03'
OR KSCHL EQ 'BO01'
OR KSCHL EQ 'BO02'
OR KSCHL EQ 'BO03'
OR KSCHL EQ 'BO04'
OR KSCHL EQ 'BO05'
THEN KWERT *(-1)
ELSE 0;
END

TABLE FILE PRICING
SUM
UPRICE
WAERS
REBATE
BY KNUMV
BY KPOSN
END
This is exactly what piipster suggested.
I just thought the SUM would screw things over.
But since I'm setting the value to 0 it wouldn't matter because we're adding 0.

Thank you everyone for your patience and help.

This message has been edited. Last edited by: <Mabel>,
 
Posts: 44 | Location: Belgium | Registered: January 10, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Question about a query

Copyright © 1996-2020 Information Builders