I guess the other way of doing that could be creating a view with the select
statement that is in the FROM Clause, and referencing that view in your FROM
clause. Your view then should have EMPLID and EFFDT columns, and your main
Where Clause should have
the MAX subquery:
BEGIN-SELECT
A.FIRST_NAME
A.LAST_NAME
FROM MY_VIEW A
WHERE A.EFFDT = (SELECT MAX(EFFDT)
FROM MY_VIEW B
WHERE B.EMPLID = A.EMPLID
AND EFFDT <=
SUBSTRING(CONVERT(CHAR,$AsOfDate,121), 1, 10)
)
END-SELECT
"Wyatt, Will" <WillW@aisd.org>
Sent by: sqr-users-bounces+olga.gal=bbh.com@sqrug.org
To
"'sqr-users@sqrug.org'" <sqr-users@sqrug.org>
05/10/2005 10:37 AM
cc
Subject
Please respond to
[sqr-users] Virtual Table
"This list is for discussion about the SQR database reporting
language from Hyperion
Solutions." <sqr-users@sqrug.org>
Hi all. I'm not sure what to call the problem I'm having writing this query
in SQR. I'm using MS SQL Server w/ PeopleSoft. My query is actually more
complex, but this demonstrates the problem. The query works fine in Query
Analyzer.
SELECT A.FIRST_NAME, A.LAST_NAME
FROM (
SELECT NAMES.FIRST_NAME, NAMES.LAST_NAME
FROM PS_NAMES NAMES
WHERE NAMES.EFFDT =
(
SELECT MAX(N_ED.EFFDT) FROM PS_NAMES N_ED
WHERE NAMES.EMPLID = N_ED.EMPLID
AND N_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,'2005-05-31',121), 1, 10)
)
) AS A
I try to move this query to SQR and produced
BEGIN-SELECT
A.FIRST_NAME
A.LAST_NAME
FROM (
SELECT NAMES.FIRST_NAME, NAMES.LAST_NAME
FROM PS_NAMES NAMES
WHERE NAMES.EFFDT =
(
SELECT MAX(N_ED.EFFDT) FROM PS_NAMES N_ED
WHERE NAMES.EMPLID = N_ED.EMPLID
AND N_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,'2005-05-31',121), 1, 10)
)
) AS A
END-SELECT
My problem is when I try to replace the '2005-05-31' in the effective dated
logic with a variable, something like
AND N_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,$AsOfDate,121), 1, 10)
I get an SQR 1303 error 'Error in SQL (perhaps missing &name after
expression).
I suspect that I can't do this because the virtual table A isn't really a
table as far as SQR is concerned. Is there someway I can do this.
PS. Is there a technical name for what I'm trying to do with the Select
statement that is creating table A in the main query?
TIA.
--------------------------------------
Will Wyatt
PeopleSoft Analyst
Abilene Independent School District
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users