[sqr-users] Virtual Table

["Wyatt, Will" <WillW@aisd.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