[Riad]

Hi,

Is it possible to pass the maximo Where Clause to an asp web page ?
The Where clause will be used in the asp web page like this:
SQL = "SELECT PO.* FROM PO WHERE " + WHERECLAUSE

The asp web page will be put in the birt report as Hyperlink

Thanks

[johnw]

The Where clause is a parameter in BIRT reports, so yes, you just need to pass it through as params["where"]

[Riad]

johnw, on 01 March 2012 - 11:31 AM, said:

The Where clause is a parameter in BIRT reports, so yes, you just need to pass it through as params["where"]

Thanks johnw for the reply,

I tried this and it didn't work.
SQL = "SELECT PO.* FROM PO WHERE " + params["where"]


In fact I want to retrieve the where used by maximo to show selected PO
for example if maximo have (where PONUM like '14059%')

The sql in my asp web page will have the same clause where
SQL = "SELECT PO.* FROM PO WHERE PONUM like '14059%'

Thanks

[johnw]

SQL = "SELECT PO.* FROM PO WHERE 1 = 1 " + params["where"]

Give that a try. I seem to recall that Maximo will add and AND to everything in its where clause. I don't have a copy of Maximo handy to give it a try. Also, you can refer to the report in the Maximo examples directory, they show how that mechanism work. Or you can use the extract report tool to get a existing report.

[mblock]

johnw, on 01 March 2012 - 01:56 PM, said:

SQL = "SELECT PO.* FROM PO WHERE 1 = 1 " + params["where"]

Give that a try. I seem to recall that Maximo will add and AND to everything in its where clause. I don't have a copy of Maximo handy to give it a try. Also, you can refer to the report in the Maximo examples directory, they show how that mechanism work. Or you can use the extract report tool to get a existing report.

Use params["where"].value. Also it does not include a preceding 'AND', so it should be :
SQL = "SELECT PO.* FROM PO WHERE " + params["where"]

[Riad]

Thanks johnw and mblock for your reply,

It still not working. I put the [where] parameter in the report to show his value and it was
((po.poid = 1042))

I tried without succes all this:

SQL = "SELECT PO.* FROM PO where 1=1 " & params["where"]
SQL = "SELECT PO.* FROM PO where 1=1 " + params["where"]


SQL = "SELECT PO.* FROM PO where 1=1 and " & params["where"]
SQL = "SELECT PO.* FROM PO where 1=1 and " + params["where"]


SQL = "SELECT PO.* FROM PO where " & params["where"]
SQL = "SELECT PO.* FROM PO where " + params["where"]

[mblock]

Riad, on 02 March 2012 - 07:57 AM, said:

Thanks johnw and mblock for your reply,

It still not working. I put the [where] parameter in the report to show his value and it was
((po.poid = 1042))

I tried without succes all this:

SQL = "SELECT PO.* FROM PO where 1=1 " & params["where"]
SQL = "SELECT PO.* FROM PO where 1=1 " + params["where"]


SQL = "SELECT PO.* FROM PO where 1=1 and " & params["where"]
SQL = "SELECT PO.* FROM PO where 1=1 and " + params["where"]


SQL = "SELECT PO.* FROM PO where " & params["where"]
SQL = "SELECT PO.* FROM PO where " + params["where"]

This is the correct one:
SQL = "SELECT PO.* FROM PO where " + params["where"].value

Why it is not working I do not know. What error are you getting?

[Riad]

Hi,

Sorry for the delai, I'm not working full time on this issue.

It didn't work with SQL = "SELECT PO.* FROM PO where " + params["where"].value

It shows me HTTP 5000 error.

But when I use SQL = "SELECT PO.* FROM PO where po.ponum = '" & 13056218 & "'"

It works fine.

Thanks

[Riad]

Sorry not HTTP 5000 but HTTP 500

[mblock]

How are you calling the ASP page?

[Riad]

By a Hyperlink on a data set column and I call a asp file

"http://ServerName/main.asp"

[mblock]

Riad, on 05 March 2012 - 09:43 AM, said:

By a Hyperlink on a data set column and I call a asp file

"http://ServerName/main.asp"

What does your URI expression look like in BRD?

[Riad]

mblock, on 05 March 2012 - 08:46 AM, said:

What does your URI expression look like in BRD?

Location: "http://ServerName/main.asp"
Target: Self

[mblock]

Riad, on 05 March 2012 - 10:19 AM, said:

Location: "http://ServerName/main.asp"
Target: Self

So how are you passing the where clause? Should it notbe something like:

"http://ServerName/main.asp?where= " + params["where"].value

[Riad]

mblock, on 05 March 2012 - 09:22 AM, said:

So how are you passing the where clause? Should it notbe something like:

"http://ServerName/main.asp?where= " + params["where"].value

Ok the where is passing ... Thank you very much ...

Still a little issue, the where is using the poid not the ponum and the SQL is not recognising the POID

this is the error: ORA-00904: "PO"."POID": invalid identifier

Do you have a idea why ?

The where I retrieve within the asp is ( ( po.poid = 1042 ))

Thanks

[mblock]

Riad, on 05 March 2012 - 11:34 AM, said:

Do you have a idea why ?

Probably the SQL in your ASP page is being generated incorrectly.