Retrieval Parameters problem

That query has no retrieval argument, so I don't see how you can get that
error.
Out of curiosity, why are you coding "where 1 = 1" all over the place in
your query. If you are trying to influence the optimizer, there are better
ways of doing that.
--
Terry Dykstra (TeamSybase)
http://powerbuilder.codeXchange.sybase.com/
http://casexpress.sybase.com
product enhancement requests:
http://my.isug.com/cgi-bin/1/c/submit_enhancement

Post by unknown
I am trying to develop a treeview report with retrieval
SELECT DISTINCT
rnt_account.account_no,
rnt_account_totals.balance,
rnt_account.account_status,
LBR_Rent_Account_Parties (rnt_account.account_no)
Account_Parties,
SO_VIEW.Single_Occupancy,
D_VIEW.Age,
SUBSTR(
NVL2(PAV.property_addr_line1,PAV.property_addr_line1||'
','')||
NVL2(PAV.property_addr_line2,PAV.property_addr_line2||'
','')||
NVL2(PAV.property_addr_line3,PAV.property_addr_line3||'
','')||
NVL2(PAV.property_addr_line4,PAV.property_addr_line4||'
','')||
NVL2(PAV.property_post_code,property_post_code,''),1,125)
Property_Address,
SUBSTR(
NVL2(E.correspondence_addr_line1,E.correspondence_addr_line1||'
','')||
NVL2(E.correspondence_addr_line2,E.correspondence_addr_line2||'
','')||
NVL2(E.correspondence_addr_line3,E.correspondence_addr_line3||'
','')||
NVL2(E.correspondence_addr_line4,E.correspondence_addr_line4||'
','')||
NVL2(E.correspondence_addr_post_code,E.correspondence_addr_post_code
,''),1,125) Correspondence_Address,
rnt_property.area_code,
rnt_area.area_description Area_Description,
rnt_account.acnt_arrears_status,
rnt_property.patch_code,
rnt_patch.patch_description Patch_Description ,
rnt_account.cease_date
FROM rnt_account,
rnt_account_totals,
rnt_property,
rnt_area,
rnt_patch,
lbr_property_address_view PAV,
lbr_tenant_correspondence_view E,
(SELECT ROV.account_no,
(CASE count(*)
WHEN 1 THEN 'Y'
ELSE 'N'
END) Single_Occupancy
FROM rnt_occupants_view ROV
WHERE 1=1
GROUP BY ROV.account_no) SO_VIEW,
(
SELECT ROV.account_no,
(CASE TO_CHAR(person.date_of_birth,'DD-MM-YYYY')
WHEN '01-01-1800' THEN 'N/A'
ELSE TO_CHAR(person.date_of_birth,'DD-MM-YYYY')
END) D_O_B,
(CASE
WHEN TO_CHAR(person.date_of_birth,'DD-MM-YYYY') <>
'01-01-1800' THEN
SUBSTR(TO_CHAR(ABS(ROUND(Months_between(person.date_of_birth
,sysdate) /12,1))),1,2)
ELSE 'N/A'
END) Age,
RANK() OVER(PARTITION BY ROV.account_no ORDER BY
person.pin ASC) Ranking
FROM rnt_occupants_view ROV,
person
WHERE 1=1
AND ROV.pin = person.pin
AND ROV.party = 'Y'
) D_VIEW
WHERE 1=1
AND rnt_account.account_no =
rnt_account_totals.account_no
AND rnt_account.account_no = SO_VIEW.account_no
AND rnt_account.account_no = D_VIEW.account_no
AND D_VIEW.Ranking = 1
AND rnt_account.uprn = rnt_property.uprn
AND rnt_property.area_code = rnt_area.area_code
AND rnt_property.patch_code = rnt_patch.patch_code
AND rnt_account.uprn = PAV.uprn
AND rnt_account.account_no = E.rent_account_no
AND rnt_account.account_status= 'F'
AND rnt_account_totals.balance > 0.00
ORDER BY 2 DESC
Cannot apply criteria to a select statement with union or
nested select or nested report.
I have seen this error before when I have had Unions in SQL
queries, however, as far as I can seen the SQL does not
include any Union or Nested Select, nor am I using a nested
report. So why is this error occuring?
We are using Infomaker 10.5.1 Build 6662 with the Northgate
OHMS Oracle database.
Any assistance would be appreciated

unknown

2009-12-23 00:52:54 UTC

No I have set the Prompt for Criteria setting in the report
that uses this query so that I can use drop down data
windows to let the users to select from a list of values.
It is when the report is run with columns selected to prompt
criteria that this error occurs. The 1=1 is just my SQL
style, born from coding VB and passing SQL query strings
dynamically created in code i.e. you only ever have to
create AND clauses. I am not trying to influence anything !
It would be interesing to know why there is a restriction on
using Unions and Nested Selects and prompt criteria, also
why aliases cause prompt criteria problems.

In teh past I have solved these issues by creating DB views
but in his case I didnt think I would need to as no unions
or nests exist

Post by Terry Dykstra [TeamSybase]
That query has no retrieval argument, so I don't see how
you can get that error.
Out of curiosity, why are you coding "where 1 = 1" all
over the place in your query. If you are trying to
influence the optimizer, there are better ways of doing
that.
--
Terry Dykstra (TeamSybase)
http://powerbuilder.codeXchange.sybase.com/
http://casexpress.sybase.com
http://my.isug.com/cgi-bin/1/c/submit_enhancement

to develop a treeview report with retrieval paramaters

with the following SQL as the data source: >

SELECT DISTINCT
rnt_account.account_no,
rnt_account_totals.balance,
rnt_account.account_status,
LBR_Rent_Account_Parties
(rnt_account.account_no) Account_Parties,
SO_VIEW.Single_Occupancy,
D_VIEW.Age,
SUBSTR(
NVL2(PAV.property_addr_line1,PAV.property_addr_line1||'
','')||
NVL2(PAV.property_addr_line2,PAV.property_addr_line2||'
','')||
NVL2(PAV.property_addr_line3,PAV.property_addr_line3||'
','')||
NVL2(PAV.property_addr_line4,PAV.property_addr_line4||'
','')||
NVL2(PAV.property_post_code,property_post_code
,''),1,125) Property_Address,
SUBSTR(
NVL2(E.correspondence_addr_line1
,E.correspondence_addr_line1||' ','')||
NVL2(E.correspondence_addr_line2
,E.correspondence_addr_line2||' ','')||
NVL2(E.correspondence_addr_line3
,E.correspondence_addr_line3||' ','')||
NVL2(E.correspondence_addr_line4
,E.correspondence_addr_line4||' ','')||
NVL2(E.correspondence_addr_post_code
,E.correspondence_addr_post_code ,''),1,125)
Correspondence_Address, rnt_property.area_code,
rnt_area.area_description Area_Description,
rnt_account.acnt_arrears_status,
rnt_property.patch_code,
rnt_patch.patch_description Patch_Description ,
rnt_account.cease_date
FROM rnt_account,
rnt_account_totals,
rnt_property,
rnt_area,
rnt_patch,
lbr_property_address_view PAV,
lbr_tenant_correspondence_view E,
(SELECT ROV.account_no,
(CASE count(*)
WHEN 1 THEN 'Y'
ELSE 'N'
END) Single_Occupancy
FROM rnt_occupants_view ROV
WHERE 1=1
GROUP BY ROV.account_no) SO_VIEW,
(
SELECT ROV.account_no,
(CASE TO_CHAR(person.date_of_birth,'DD-MM-YYYY')
WHEN '01-01-1800' THEN 'N/A'
ELSE TO_CHAR(person.date_of_birth,'DD-MM-YYYY')
END) D_O_B,
(CASE
WHEN TO_CHAR(person.date_of_birth,'DD-MM-YYYY') <>
'01-01-1800' THEN

SUBSTR(TO_CHAR(ABS(ROUND(Months_between(person.date_of_bir

th ,sysdate) /12,1))),1,2)
ELSE 'N/A'
END) Age,
RANK() OVER(PARTITION BY ROV.account_no ORDER BY
person.pin ASC) Ranking
FROM rnt_occupants_view ROV,
person
WHERE 1=1
AND ROV.pin = person.pin
AND ROV.party = 'Y'
) D_VIEW
WHERE 1=1
AND rnt_account.account_no =
rnt_account_totals.account_no
AND rnt_account.account_no = SO_VIEW.account_no
AND rnt_account.account_no = D_VIEW.account_no
AND D_VIEW.Ranking = 1
AND rnt_account.uprn = rnt_property.uprn
AND rnt_property.area_code = rnt_area.area_code
AND rnt_property.patch_code = rnt_patch.patch_code
AND rnt_account.uprn = PAV.uprn
AND rnt_account.account_no = E.rent_account_no
AND rnt_account.account_status= 'F'
AND rnt_account_totals.balance > 0.00
ORDER BY 2 DESC
Cannot apply criteria to a select statement with union
or nested select or nested report.
I have seen this error before when I have had Unions in
SQL queries, however, as far as I can seen the SQL does
not include any Union or Nested Select, nor am I using a
nested report. So why is this error occuring?
We are using Infomaker 10.5.1 Build 6662 with the
Northgate OHMS Oracle database.
Any assistance would be appreciated

Rick

2009-12-23 14:31:52 UTC

Post by unknown
No I have set the Prompt for Criteria setting in the report
that uses this query so that I can use drop down data
windows to let the users to select from a list of values.
It is when the report is run with columns selected to prompt
criteria that this error occurs. The 1=1 is just my SQL
style, born from coding VB and passing SQL query strings
dynamically created in code i.e. you only ever have to
create AND clauses. I am not trying to influence anything !
It would be interesing to know why there is a restriction on
using Unions and Nested Selects and prompt criteria, also
why aliases cause prompt criteria problems.
In teh past I have solved these issues by creating DB views
but in his case I didnt think I would need to as no unions
or nests exist

to develop a treeview report with retrieval paramaters

with the following SQL as the data source: >

SUBSTR(TO_CHAR(ABS(ROUND(Months_between(person.date_of_bir

- Show quoted text -

The message is referring to having what InfoMaker is considering
nested selects in your from clause. For example:
(SELECT ROV.account_no,
(CASE count(*)
WHEN 1 THEN
'Y'
ELSE 'N'
END)
Single_Occupancy
FROM rnt_occupants_view ROV
WHERE 1=1
GROUP BY ROV.account_no) SO_VIEW,

These are not allowed because when using Prompt for Criteria InfoMaker
will process the restriction by appending the PFC to the end of your
WHERE clause at runtime, so IM cannot handle it when the select has
multiple WHERE clauses.

Rick

unknown

2009-12-23 15:54:47 UTC

Its ok I have now created a DB view instead.

The

Post by unknown
1=1 is just my SQL style, born from coding VB and
passing SQL query strings dynamically created in code
i.e. you only ever have to create AND clauses. I am

not

Post by unknown
trying to influence anything ! It would be interesing to
know why there is a restriction on using Unions and
Nested Selects and prompt criteria, also why aliases

cause prompt criteria problems. >

Post by unknown
In teh past I have solved these issues by creating DB
views but in his case I didnt think I would need to as
no unions or nests exist

Post by Terry Dykstra [TeamSybase]
That query has no retrieval argument, so I don't see
how you can get that error.
Out of curiosity, why are you coding "where 1 = 1"

all

Post by Terry Dykstra [TeamSybase]
over the place in your query. If you are trying

Post by Terry Dykstra [TeamSybase]
influence the optimizer, there are better ways of
doing that.
--
Terry Dykstra (TeamSybase)
http://powerbuilder.codeXchange.sybase.com/
http://casexpress.sybase.com
http://my.isug.com/cgi-bin/1/c/submit_enhancement

Post by unknown
trying to develop a treeview report with retrieval

SELECT DISTINCT

rnt_account.account_no,
rnt_account_totals.balance,
rnt_account.account_status,
LBR_Rent_Account_Parties
(rnt_account.account_no) Account_Parties,
SO_VIEW.Single_Occupancy,
D_VIEW.Age,
SUBSTR(
NVL2(PAV.property_addr_line1
,PAV.property_addr_line1||' ','')||
NVL2(PAV.property_addr_line2
,PAV.property_addr_line2||' ','')||
NVL2(PAV.property_addr_line3
,PAV.property_addr_line3||' ','')||
NVL2(PAV.property_addr_line4
,PAV.property_addr_line4||' ','')||
NVL2(PAV.property_post_code,property_post_code
,''),1,125) Property_Address,
SUBSTR(
NVL2(E.correspondence_addr_line1
,E.correspondence_addr_line1||' ','')||
NVL2(E.correspondence_addr_line2
,E.correspondence_addr_line2||' ','')||
NVL2(E.correspondence_addr_line3
,E.correspondence_addr_line3||' ','')||
NVL2(E.correspondence_addr_line4
,E.correspondence_addr_line4||' ','')||
NVL2(E.correspondence_addr_post_code
,E.correspondence_addr_post_code ,''),1,125)
Correspondence_Address,
rnt_property.area_code, rnt_area.area_description
Area_Description,
rnt_account.acnt_arrears_status,
rnt_property.patch_code, rnt_patch.patch_description
Patch_Description ,

rnt_account.cease_date

Post by unknown
FROM rnt_account,
rnt_account_totals,
rnt_property,
rnt_area,
rnt_patch,
lbr_property_address_view PAV,
lbr_tenant_correspondence_view E,
(SELECT ROV.account_no,
(CASE count(*)
WHEN 1 THEN 'Y'
ELSE 'N'
END) Single_Occupancy
FROM rnt_occupants_view ROV
WHERE 1=1
GROUP BY ROV.account_no) SO_VIEW,
(
SELECT ROV.account_no,
(CASE TO_CHAR(person.date_of_birth,'DD-MM-YYYY')
WHEN '01-01-1800' THEN 'N/A'
ELSE TO_CHAR(person.date_of_birth,'DD-MM-YYYY')
END) D_O_B,
(CASE
WHEN TO_CHAR(person.date_of_birth,'DD-MM-YYYY') <>
'01-01-1800' THEN

SUBSTR(TO_CHAR(ABS(ROUND(Months_between(person.date_of_bir

Post by unknown
th ,sysdate) /12,1))),1,2) ELSE 'N/A'
END) Age,
RANK() OVER(PARTITION BY ROV.account_no ORDER BY
person.pin ASC) Ranking
FROM rnt_occupants_view ROV,
person
WHERE 1=1
AND ROV.pin = person.pin
AND ROV.party = 'Y'
) D_VIEW
WHERE 1=1
AND rnt_account.account_no > > >
rnt_account_totals.account_no AND
rnt_account.account_no = SO_VIEW.account_no

AND

Post by unknown
rnt_account.account_no = D_VIEW.account_no

AND

Post by unknown
D_VIEW.Ranking = 1 AND

rnt_account.uprn =

Post by unknown
rnt_property.uprn AND rnt_property.area_code

Post by unknown
rnt_area.area_code AND

rnt_property.patch_code =

Post by unknown
rnt_patch.patch_code AND

rnt_account.uprn =

Post by unknown
PAV.uprn AND rnt_account.account_no =
E.rent_account_no AND

rnt_account.account_status=

Post by unknown
'F' AND rnt_account_totals.balance > 0.00
ORDER BY 2 DESC
However, I get an error message in Infomaker as

follows: >

Post by unknown
Cannot apply criteria to a select statement with
union or nested select or nested report.
I have seen this error before when I have had Unions
in SQL queries, however, as far as I can seen the
SQL does not include any Union or Nested Select, nor
am I using a nested report. So why is this error

occuring? >