[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
RE: [sqr-users] Virtual Table
- Subject: RE: [sqr-users] Virtual Table
- From: Olga.Gal@bbh.com
- Date: Tue, 10 May 2005 11:25:05 -0400
- Delivery-date: Tue, 10 May 2005 10:31:15 -0500
- In-reply-to: <FCA0BC3DEFADD31184F7009027CCC10A0C8BEC9B@SERVER04>
- List-id: "This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions." <sqr-users.sqrug.org>
That is because the error DOESN'T refer to the Where Clause of your 'Virtual
Table', but to the selected columns. In other words from some reason SQR is
expecting alias name after NAMES.FIRST_NAME and NAMES.LAST_NAME
"Wyatt, Will" <WillW@aisd.org>
Sent by: sqr-users-bounces+olga.gal=bbh.com@sqrug.org
To
"'This list is for discussion about the SQR
database reporting language from Hyperion Solutions.'" <sqr-users@sqrug.org>
05/10/2005 11:27 AM
cc
Subject
Please respond to
RE: [sqr-users] Virtual Table
"This list is for discussion about the SQR database reporting
language from Hyperion
Solutions." <sqr-users@sqrug.org>
Based on what James Womeldorf and you have said I changed the query to:
let $name = 'Wyatt'
begin-select
A.FIRST_NAME
A.LAST_NAME
print &A.LAST_NAME (+1, 1)
FROM (
SELECT NAMES.FIRST_NAME, NAMES.LAST_NAME
FROM PS_NAMES NAMES
WHERE NAMES.EFFDT <= '2005-05-31'
AND NAMES.LAST_NAME = $name
) AS A
end-select
and I still get an error, but
begin-select
A.FIRST_NAME
A.LAST_NAME
print &A.LAST_NAME (+1, 1)
FROM (
SELECT NAMES.FIRST_NAME, NAMES.LAST_NAME
FROM PS_NAMES NAMES
WHERE NAMES.EFFDT <= '2005-05-31'
AND NAMES.LAST_NAME = 'Wyatt'
) AS A
end-select
works, so I don't think it is the effective date logic.
This list is amazing, I never expected to get help so quickly.
-----Original Message-----
From: sqr-users-bounces+willw=aisd.org@sqrug.org
[mailto:sqr-users-bounces+willw=aisd.org@sqrug.org] On Behalf Of Pedroza,
Maricela
Sent: Tuesday, May 10, 2005 10:08 AM
To: This list is for discussion about the SQR database reporting language
from Hyperion Solutions.
Subject: RE: [sqr-users] Virtual Table
Have you tried replacing the effdt logic with something like,
AND N_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10)
Maricela
-----Original Message-----
From: sqr-users-bounces+mpedroza=cerritos.edu@sqrug.org
[mailto:sqr-users-bounces+mpedroza=cerritos.edu@sqrug.org] On Behalf Of
Wyatt, Will
Sent: Tuesday, May 10, 2005 8:13 AM
To: 'This list is for discussion about the SQR database reporting
language from Hyperion Solutions.'
Subject: RE: [sqr-users] Virtual Table
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
_______________________________________________
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