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.

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

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>,

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>,

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?

*bump*

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

For even better performance you could sum the first table

BY KNUMV
BY KPOSNR
BY KSCHL

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.

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.

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

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

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?

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>,

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>,