Hi,

I have a procedure with the following SQL Passthru code in it...

ENGINE SQLMSS SET DEFAULT_CONNECTION WF_Dev
SQL SQLMSS PREPARE SQLOUT FOR
select (select sum(r_qty) from oewithregdata where (r_year = &R_YEAR) and (r_country = '&R_COUNTRY') and (oe_country != '&R_COUNTRY'))/
(select sum(r_qty) from oewithregdata where (r_year = &R_YEAR) and (r_country = '&R_COUNTRY'))* 100 AS 'IMP_PCT'

To translate this it reads, ((Sum of NonCaptive Registration / Sum of Captive Registration) * 100) = Import Percentage

So, here is my question, if I use a WebFOCUS synonym as my data source, how can I rewrite this using WebFOCUS code? I know what you're thinking, "Why would you want to write this in WebFOCUS if you already have it in SQL?" The goal is to make the SQL View go away and use a WF Synonym instead.

At a glance it looks like I need to run each query separately and -WRITE the values to a file. Then, -READ those values into DEFINE fields to create the equation. Is there a better way to do this?

Thanks,

Dan

This message has been edited. Last edited by: Dan Pinault,

Looks pretty straightforward:

All of the data is coming from the same file (oewithregdata) so it can probably be done in a single pass. The two subqueries share some similar criteria so those can be criteria for the entire request.

TABLE FILE OEWITHREGDATA
SUM
R_QTY
COMPUTE CAPTIVE/D12=IF OE_COUNTRY NE '&R_COUNTRY' THEN R_QTY ELSE 0;
COMPUTE I_PCT/D12.2%=(R_QTY/CAPTIVE)*100; AS 'IMP_PCT'
WHERE R_YEAR EQ '&R_YEAR';
WHERE R_COUNTRY EQ '&R_COUNTRY';
END

The key is using a COMPUTE to perform the operations on the aggregated values (as opposed to a DEFINE which operates in the individual record values.)

This message has been edited. Last edited by: Darin Lee,

Thanks Darin. That DOES look simple.

Are these meant to be on the same line (part of the same COMPUTE)?

 
COMPUTE I_PCT/D12.2%=(R_QTY/CAPTIVE)*100;
WHERE R_YEAR EQ '&R_YEAR'; AS 'IMP_PCT'

If so, I feel silly because I didn't realize you could put a WHERE clause in a COMPUTE!

If that is not the case, can you tell me what the AS 'IMP_PCT' is doing?

Thanks,

Dan

I got the AS phrase on the wrong line - it should be on the COMPUTE line - I have edited my code to show it correctly. The AS phrase is just a quick way to rename the field - same as what you had in your SQL. Would have been just as easy to say
COMPUTE IMP_PCT/D12.2%=(R_QTY/CAPTIVE)*100;

This was a good exchange. Sometimes all it takes is a different perspective to get your brain working in the right direction. After considering your response I was able to clean up my passthru SQL code and and come up with the equivalent in FOCUS. They are as follows...
SQL Version:

;with cte as
(
select sum(r_qty) as 'r_qty', sum((case when oe_country != r_country then r_qty else 0 end)) as 'noncaptive'
from oewithregdata
where (r_country = @R_COUNTRY) and (r_year = @R_YEAR)
)
select ((noncaptive/r_qty)*100) as 'imp_pct'
from cte
;

and

FOCUS Version:

DEFINE FILE OE_REG
NONCAPTIVE/D12=IF (OE_COUNTRY NE '&R_COUNTRY') THEN (R_QTY) ELSE (0);
CAPTIVE/D12=IF(OE_COUNTRY EQ '&R_COUNTRY') THEN (R_QTY) ELSE (0);
END
TABLE FILE OE_REG
SUM
	COMPUTE IMP_PCT/D12.2 = (TOT.NONCAPTIVE/(TOT.NONCAPTIVE + TOT.CAPTIVE))*100; AS 'Import Pct'
WHERE R_YEAR EQ &R_YEAR;
WHERE R_COUNTRY EQ '&R_COUNTRY';
WHERE TOTAL OE_COUNTRY NE '&R_COUNTRY';

Note that the SQL view name is oewithregdata and the FOCUS Synonym name is OE_REG.

I actually do need a DEFINE in this case because I am trying to come up with a total of registrations where the registration country is not the same as the manufacture country (this is NonCaptive registration). I need to get that total before I perform the import percentage calculation. Therefore, OE_COUNTRY must be compared to &R_COUNTRY on a row-by-row basis. Otherwise, if I do it as a COMPUTE the only value for OE_COUNTRY to be compared with &R_COUNTRY will be from the last row in the answer set.

Thanks again!

Dan

This message has been edited. Last edited by: Dan Pinault,

Excellent! It's actually refreshing when someone takes what they're given and makes it work for them (which I expected would be the case from Dan). What happens so often is someone takes the code, plugs it in, and when the results aren't what they expected, they re-post saying it doesn't work. Well, that's OK sometimes - that's how we learn - but taking one answer, figuring out why it's not quite right, and modifying it to fit your needs, IMHO, is the spirit of this forum!

quote:

but taking one answer, figuring out why it's not quite right, and modifying it to fit your needs, IMHO, is the spirit of this forum!

Agreed! Totally and absolutely!

T

quote:

Originally posted by Dan Pinault:
... and

FOCUS Version:

DEFINE FILE OE_REG
NONCAPTIVE/D12=IF (OE_COUNTRY NE '&R_COUNTRY') THEN (R_QTY) ELSE (0);
CAPTIVE/D12=IF(OE_COUNTRY EQ '&R_COUNTRY') THEN (R_QTY) ELSE (0);
END
TABLE FILE OE_REG
SUM
	COMPUTE IMP_PCT/D12.2 = (TOT.NONCAPTIVE/(TOT.NONCAPTIVE + TOT.CAPTIVE))*100; AS 'Import Pct'
WHERE R_YEAR EQ &R_YEAR;
WHERE R_COUNTRY EQ '&R_COUNTRY';
WHERE TOTAL OE_COUNTRY NE '&R_COUNTRY';

Is that your complete TABLE request? There is no OE_COUNTRY verb-object for

WHERE TOTAL OE_COUNTRY ...

to test.

Good catch Jack!

I edited the request to just show the relevant bits related to this discussion. I should have also removed the WHERE TOTAL line.

For my purpose the WHERE TOTAL is required because I don't want to show the country selected (&R_COUNTRY) in the results. I only want to show countries that produce stuff that is registered in &R_COUNTRY.

This is great - I get good advice and a code review!

Cheers,

Dan