LOV/Conditon object problem

[system]

I am having difficulty trying to solve this problem, any suggestions are very much appreciated.
the Problem is as follows:

Prompt created as a filter object in Designer

SESSION_FACT.START_DATE = @Prompt(‘Enter Session Date’,‘D’,‘Page Sessions\Session Date’,mono,free)

The filter object Parses OK.

I create a simple query using the filter object above. The SQL is as follows:

SELECT
SESSION_FACT.START_DATE,
SESSION_FACT.SESSION_ID,
COUNT(SESSION_FACT.SESSION_ID)
FROM
SESSION_FACT
WHERE
(
( SESSION_FACT.START_DATE = @Prompt(‘Enter Session Date’,‘D’,‘Page Sessions\Session Date’,mono,free) )
)
GROUP BY
SESSION_FACT.START_DATE,
SESSION_FACT.SESSION_ID

I amend the SQL to include a WHEN clause. The SQL is now

SELECT
SESSION_FACT.START_DATE,
COUNT(SESSION_FACT.SESSION_ID)
FROM
SESSION_FACT
WHERE
(
( SESSION_FACT.START_DATE = @Prompt(‘Enter Session Date’,‘D’,‘Page Sessions\Session Date’,mono,free) )
)
GROUP BY
SESSION_FACT.START_DATE
when
rank (COUNT(SESSION_FACT.SESSION_ID)) <= 50

I click on the ‘Do not generate SQL before running’ button. Parsing produces the following error:

Syntax error in variable

Thanks in advance

Minal

---

Listserv Archives (BOB member since 2002-06-25)

[system]

Durgesh

Thanks. Yes that works

But

When I run the query the ‘Values’ button is grayed out.

Any suggestions

Minal

Minal,
I tried the sql in free hand sql and here is my observation : If instead of the classname/object name, i give LOV , the sql checks OK.
Similarly if I revove
the quote in classname/object name, sql checks ok.

Try this out and hope it helps.

Regards,
Durgesh

I am having difficulty trying to solve this problem, any suggestions are
very much appreciated.
the Problem is as follows:

Prompt created as a filter object in Designer

SESSION_FACT.START_DATE = @Prompt(‘Enter Session
Date’,‘D’,‘Page Sessions\Session Date’,mono,free)

The filter object Parses OK.

I create a simple query using the filter object above. The SQL is as follows:

SELECT
SESSION_FACT.START_DATE,
SESSION_FACT.SESSION_ID,
COUNT(SESSION_FACT.SESSION_ID)
FROM
SESSION_FACT
WHERE
(
( SESSION_FACT.START_DATE = @Prompt(‘Enter Session Date’,‘D’,‘Page Sessions\Session Date’,mono,free) )
)
GROUP BY
SESSION_FACT.START_DATE,
SESSION_FACT.SESSION_ID

I amend the SQL to include a WHEN clause. The SQL is now

SELECT
SESSION_FACT.START_DATE,
COUNT(SESSION_FACT.SESSION_ID)
FROM
SESSION_FACT
WHERE
(
( SESSION_FACT.START_DATE = @Prompt(‘Enter Session Date’,‘D’,‘Page Sessions\Session Date’,mono,free) )
)
GROUP BY
SESSION_FACT.START_DATE
when
rank (COUNT(SESSION_FACT.SESSION_ID)) <= 50

I click on the ‘Do not generate SQL before running’ button. Parsing produces the following error:

Syntax error in variable

---

Listserv Archives (BOB member since 2002-06-25)

[system]

In a message dated 00-03-10 07:23:05 EST, you write:

SESSION_FACT.START_DATE = @Prompt(‘Enter Session Date’,‘D’,‘Page Sessions\Session Date’,mono,free)

The filter object Parses OK.

One quick word of advice: I generally do NOT provide a list of values for date objects. The overhead of scanning the database to build a “unique” list of possible dates is not worth it when a user can simply enter the date value they are looking for. Something to think about…

I create a simple query using the filter object above. The SQL is as follows:

[snip]

I amend the SQL to include a WHEN clause. The SQL is now

SELECT
SESSION_FACT.START_DATE,
COUNT(SESSION_FACT.SESSION_ID)
FROM
SESSION_FACT
WHERE
(
( SESSION_FACT.START_DATE = @Prompt(‘Enter Session Date’,‘D’,‘Page Sessions\Session Date’,mono,free) )
)
GROUP BY
SESSION_FACT.START_DATE
when
rank (COUNT(SESSION_FACT.SESSION_ID)) <= 50

Is this syntax supported by your database? You did not mention which database you are connecting to, but I am not familiar with this syntax. Typically you can add a condition clause after the group by only with a HAVING clause, not with an additional WHEN clause.

Does the SQL parse before you add this extra clause? I don’t see any problems with your @Prompt structure.

Is the “rank” function supported by your database?

Remember that everything the the SQL window must be legal for your database. The only exception would be the @functions (@Prompt, @Select, @Where, @Variable, @Aggregate_Aware) that are pre-processed by BusinessObjects.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

---

Listserv Archives (BOB member since 2002-06-25)

[system]

Dave

Sorry I should have provided more info,
Database : Redbrick Warehouse
BO : 4.1.4
OS : Windows NT

When WHEN and RANK expression are legal expression in Redbrick I have tried Similar SQL in Redbrick, it works fine.

Any more suggestions?

Thanks

Minal

In a message dated 00-03-10 07:23:05 EST, you write:

SESSION_FACT.START_DATE = @Prompt(‘Enter Session
Date’,‘D’,‘Page Sessions\Session Date’,mono,free)

The filter object Parses OK.

One quick word of advice: I generally do NOT provide a list of values for
date objects. The overhead of scanning the database to build a “unique” list
of possible dates is not worth it when a user can simply enter the date value
they are looking for. Something to think about…

I create a simple query using the filter object above. The SQL is as follows:

[snip]

I amend the SQL to include a WHEN clause. The SQL is now

SELECT
SESSION_FACT.START_DATE,
COUNT(SESSION_FACT.SESSION_ID)
FROM
SESSION_FACT
WHERE
(
( SESSION_FACT.START_DATE = @Prompt(‘Enter Session
Date’,‘D’,‘Page
Sessions\Session Date’,mono,free) )
)
GROUP BY
SESSION_FACT.START_DATE
when
rank (COUNT(SESSION_FACT.SESSION_ID)) <= 50

Is this syntax supported by your database? You did not mention which database
you are connecting to, but I am not familiar with this syntax. Typically you
can add a condition clause after the group by only with a HAVING clause, not
with an additional WHEN clause.

Does the SQL parse before you add this extra clause? I don’t see any problems
with your @Prompt structure.

Is the “rank” function supported by your database?

Remember that everything the the SQL window must be legal for your database. The only exception would be the @functions (@Prompt, @Select,
@Where, @Variable, @Aggregate_Aware) that are pre-processed by BusinessObjects.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

---

Listserv Archives (BOB member since 2002-06-25)

[system]

Yes, I also observed this.Infact I have many prompt in my universe and I am also getting the same
error now.Actually you need the quote around Class name/objectname to get the values button in dialog box.
The moment you make changes to SQL and click on ‘Do not generate SQL’ check box,running the query is giving
me the same ‘Syntax error in variable problem’.
I never encountered this problem although I used to modify SQL many a times as problem solving exercise
for users. I will check on this and will come back ASAP.

Any suggestions from fellow listers…

Thanks and Regards,
Durgesh

---

Listserv Archives (BOB member since 2002-06-25)

[system]

In a message dated 00-03-11 09:50:40 EST, you write:

Sorry I should have provided more info,
Database : Redbrick Warehouse
BO : 4.1.4
OS : Windows NT

When WHEN and RANK expression are legal expression in Redbrick I have tried Similar SQL in Redbrick, it works fine.

At this point I can only say that I tried the same logic and it does not appear to work, Redbrick or not.

I built a query using the famous Island Resorts universe. I then altered the SQL to include a prompt. As someone else on the list has mentioned, if you do NOT provide a list of values for the prompt, then the SQL seems to work. If you DO provide a list of values then the SQL does NOT parse.

Here are my thoughts:
I don’t believe that you can use List of Values code in Free Hand SQL. LOV’s are based on an object. In Free Hand SQL you are not referencing a universe, and therefore have no objects.

I am guessing - and it is a guess - that when you alter the SQL and check the “Do Not Generate…” button, that BusObj converts your SQL from being universe based to essentially being Free Hand SQL. I have never thought about this before, but it makes sense. And it would explain why you can do a prompt, but no list of values for that prompt.

I stand by my earlier suggestion, however. In designing a universe, I will generally disallow any LOV logic on any date value. Consider that there are over 700 possible values for dates in a two year spread (not including time, which would dramatically increase the number of values). Which is more effective:

1. Provide a list of values that must be refreshed every day, since dates are always being added to the database. Require the user to scroll through the list to find the exact date that they are interested in and select it.

Or…

2. Let the user type in the date.

I vote for number 2.

It would be interesting to hear from one of the programmers on this issue. Perhaps someone in BusObj tech support can confirm my conclusions about altered SQL being treated as Free Hand?

Thanks for the interesting question!

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

---

Listserv Archives (BOB member since 2002-06-25)