Hi All.

I have a piece of SQL code that looks like:

select SPRIDEN_ID from spriden
where spriden_ntyp_code = 'TWID'
and SPRIDEN_ID like 'T%'
HAVING LENGTH(SPRIDEN_ID)>7 group by SPRIDEN_ID

The equivalent focus would look something like:

TABLE FILE SPRIDEN
BY SPRIDEN_ID
WHERE SPRIDEN_NTYP_CODE EQ 'TWID';
WHERE SPRIDEN_ID LIKE 'T%' ;

But I'm not familiar with the correct length() function, nor do I know the equivalent of "having".

Any suggestions?

Thanks,

This message has been edited. Last edited by: Max Nevill,

Hi Max,

You could use ARGLEN function to get the length, but that is unlikely to be passed to the SQL. Use SQL tracing to capture the SQL to check.

If the function is not passed to SQL then then your request is not going to be as efficient as you need. So look at using SQL. prefix to pass the function through, Should be something like SQL.LENGTH(SPRIDEN_ID) but you would need to check whether a define or compute would work better.

I have had success using this type of SQL function using a define within a synonym before, but you need to check using your synonym/code.

Good luck

T

You can do a COMPUTE, DEFINE OR WHERE, and, the length of SPRIDEN_ID in the DB is the 1st argument of ARGLEN:

TABLE FILE SPRIDEN
SUM
COMPUTE ID_LEN/I3 = ARGLEN(SPRIDEN_ID_LENGTH GOES HERE, SPRIDEN_ID, ID_LEN); NOPRINT
BY SPRIDEN_ID
WHERE SPRIDEN_NTYP_CODE EQ 'TWID';
WHERE SPRIDEN_ID LIKE 'T%' ;
WHERE TOTAL ID_LEN > 7;
END
-EXIT

I don't think there's a way to efficiently translate the LENGTH function. You would have to create a DEFINEd column using the ARGLEN function. This would be for each row in the table. Then you could filter on this DEFINEd column.

Instead, try the DB_EXPR function, though I'm not sure how to pass the HAVING portion of the statement.

quote:

The DB_EXPR function inserts a native SQL expression exactly as entered into the native SQL generated for a FOCUS or SQL language request.

The DB_EXPR function can be used in a DEFINE command, a DEFINE in a Master File, a WHERE clause, a FILTER FILE command, a filter in a Master File, or in an SQL statement. It can be used in a COMPUTE command if the request is an aggregate request (uses the SUM, WRITE, or ADD command) and has a single display command. The expression must return a single value.

WebFOCUS Release 8.1 Version 05M > Reporting Language > Using Functions > Data Source and Decoding Functions > DB_EXPR: Inserting an SQL Expression Into a Request

Or just use SQL passthru...

quote:

Originally posted by Tom Flynn:
You can do a COMPUTE, DEFINE OR WHERE, and, the length of SPRIDEN_ID in the DB is the 1st argument of ARGLEN:

TABLE FILE SPRIDEN
SUM
COMPUTE ID_LEN/I3 = ARGLEN(SPRIDEN_ID_LENGTH GOES HERE, SPRIDEN_ID, ID_LEN); NOPRINT
BY SPRIDEN_ID
WHERE SPRIDEN_NTYP_CODE EQ 'TWID';
WHERE SPRIDEN_ID LIKE 'T%' ;
WHERE TOTAL ID_LEN GT 7;
END
-EXIT

Thanks Tom, this did the trick.

Yes, Tom's suggestion works best.

Note that it will work only if the column specified in the COMPUTE and WHERE TOTAL is not aggregated by a SUM... BY... because the WHERE TOTAL filters on the output, not on the input.

Just for information, if you use ARGLEN, your request may result in the following warning messages -

FOC2565 - THE OBJECT  OF IF/WHERE CANNOT BE CONVERTED TO SQL
FOC2566 - DEFINE EMAIL_LEN CANNOT BE CONVERTED TO SQL
FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
FOC2598 - FOCUS IF/WHERE TEST CANNOT BE PASSED TO SQL

However, using the SQL prefix function in the following code (MS SQL source), get passed to the SQL efficiently (-

DEFINE FILE WF_RETAIL
  EMAIL_LEN/I9 = SQL.LEN(EMAIL_ADDRESS);
END

TABLE FILE WF_RETAIL
SUM 
     WF_RETAIL.WF_RETAIL_CUSTOMER.EMAIL_ADDRESS
	 EMAIL_LEN
BY   WF_RETAIL.WF_RETAIL_CUSTOMER.ID_CUSTOMER
WHERE EMAIL_LEN LE 20
IF READLIMIT EQ 10
END

AGGREGATION DONE ...
SELECT
TOP 10 T2."ID_CUSTOMER",
MAX(T2."EMAIL_ADDRESS"),
SUM(LEN(T2."EMAIL_ADDRESS"))
FROM
_wf_retail_customer T2
WHERE
(LEN(T2."EMAIL_ADDRESS") <= 20)
GROUP BY
T2."ID_CUSTOMER"
ORDER BY
T2."ID_CUSTOMER";
0 NUMBER OF RECORDS IN TABLE:      10  LINES:     10
0 HOLDING HTML FILE ON PC DISK ...

Ultimately, this might depend upon your result set size?

T

This message has been edited. Last edited by: Tony A,

Thanks Tony, I hadn't considered that. At the moment the simple solution works, but I'll come back to this if I'm doing more complex work with string lengths.