RE: [sqr-users] Virtual Table

["Wyatt, Will" <WillW@aisd.org>]

I changed the subquery to just say 'AND N_ED.EFFDT <= $AsOfDate' and got the
same error. Thanks for the suggestion.

-----Original Message-----
From: sqr-users-bounces+willw=aisd.org@sqrug.org
[mailto:sqr-users-bounces+willw=aisd.org@sqrug.org] On Behalf Of Bob Stone
Sent: Tuesday, May 10, 2005 9:55 AM
To: 'This list is for discussion about the SQR database reporting language
from Hyperion Solutions.'
Subject: RE: [sqr-users] Virtual Table

It's called an inline view.  Making a 'fake table' or 'virtual table' in the
from clause of the select is an inline view.

I'm not sure why SQR is having this problem, but I know I've used variables
in an inline view in SQR and it's worked.  I'm wondering if maybe the
functions plus the variable (and being within the inline view) are what's
causing the problem.   (just an idea...i dont know for sure).

We use oracle, but I think a similar principal will work in MS SQL Server.
The default date type conversion of a string will happen in Oracle when the
string variable is in the form 'DD-MON-YYYY' or 'DD-MON-YY', so if you did a
little SQR variable conversion (or SQL edit mask if you're grabbing that
date from a table) to set up $AsOfDate like 'DD-MON-YYYY' (or whatever the
default string auto-date conversion type for SQL Server is) and then just
said " AND N_ED.EFFDT <= $AsOfDate " it might go through.  

Otherwise, if that doesn't work, you might have to figure out some other way
to write the SQL itself...maybe loop through one table on the name, set a
variable, and within that SQL block call a procedure to loop through the
other tables, using that name variable instead of a table join.  Might be
slightly less efficient, but at least it would work.

Bob


-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org]On Behalf Of
Wyatt, Will
Sent: Tuesday, May 10, 2005 9:38 AM
To: 'sqr-users@sqrug.org'
Subject: [sqr-users] Virtual Table


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

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users