Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Prompt for criteria using a computed column

3 posts in PB Infomaker Report Writer Last posting was on 1998-07-27 22:46:33.0Z
Rick Marden Posted on 1998-07-24 14:09:04.0Z
Newsgroups: sybase.public.infomaker.general
From: "Rick Marden" <mardens@cybertours.com>
Subject: Prompt for criteria using a computed column
Date: Fri, 24 Jul 1998 10:09:04 -0400
Lines: 31
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Message-ID: <348_s3xGlMwt9GA.189@forums.powersoft.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.infomaker.general:6975
Article PK: 243005

I am trying to use the prompt for criteria in a tabular report to allow the
user to search for a character within a column. The "search" character will
always be in the same position. The column name is submittal_no and the
"search" character is in the sixth position. I can display this value by
using either mid(submittal_no, 6, 1) or substr(submittal_no, 6, 1) (as a
computed column). I noticed that if I give the computed column a name by
adding to the select statement something like "substr(submittal_no, 6, 1)
search_character," then the search_character column shows up in the Prompt
for Criteria list. However, when the prompt window comes up and I try to
enter a value for this, I get an SQLState 37000 error, syntax error near
"search_character." The frustrating part to this is that I can use the
search_character computed column in a filter "search_character = 'x'" and it
works.

I know you are thinking why not use a retrieval argument. Believe me, I
wish I could, but we have a PB application which calls the report and
automatically passes two values to each report (which all have the same two
retrieval arguments defined), so if I have a report that contains more than
two arguments, I get a DataWindow Error -- "Expecting three retrieval, but
only got two."

So, if possible I would like to be able to do this as a Prompt for Criteria
so that the report works from within the application.

I am currently using IM 5.0.03.

Rick Marden
Primavera Systems, Inc.
rmarden@primavera.com


Terry Dykstra [TeamPS] Posted on 1998-07-24 19:18:35.0Z
Newsgroups: sybase.public.infomaker.general
From: "Terry Dykstra [TeamPS]" <no_spam_tdykstra@cfol.ab.ca>
Subject: Re: Prompt for criteria using a computed column
Date: Fri, 24 Jul 1998 13:18:35 -0600
Lines: 75
Organization: Canadian Forest Oil Ltd.
X-Newsreader: Microsoft Outlook Express 4.72.2106.4
X-MimeOle: Produced By Microsoft MimeOLE V4.72.2106.4
Message-ID: <348_9fmRC5yt9GA.256@forums.powersoft.com>
References: <348_s3xGlMwt9GA.189@forums.powersoft.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.infomaker.general:6974
Article PK: 243004

The PB app can check the report for the number of arguments passed. If
there are less then required, it can dynamically rewrite the report (i.e.
datawindow) to remove the extra argument (using dw.create().) This doesn't
affect the actual stored report.

You found an interesting bug. I tested with Oracle. To see the select
statement that IM builds, you can change the dbms parameter in your
db-profile by adding the word trace in front, eg. DBMS = 'trace O72'

1) The computed column in Oracle without using AS: (tariff_id,1,1)
search_char

SELECT "BASE_TARIFF"."TARIFF_ID" , "BASE_TARIFF"."CREATED_BY" ,
"BASE_TARIFF"."CREATED" , "BASE_TARIFF"."MODIFIED_BY" ,
"BASE_TARIFF"."MODIFIED" , "BASE_TARIFF"."REVISION_NO" ,
substr(tariff_id,1,1) search_char FROM "BASE_TARIFF" where
(((substr(tariff_id,1,1) search_char = 'B'))) (0 MilliSeconds)

As you see, the where clause contains both the computed field and the alias.


2) The computed column in Oracle using AS: (tariff_id,1,1) AS search_char

SELECT "BASE_TARIFF"."TARIFF_ID" , "BASE_TARIFF"."CREATED_BY" ,
"BASE_TARIFF"."CREATED" , "BASE_TARIFF"."MODIFIED_BY" ,
"BASE_TARIFF"."MODIFIED" , "BASE_TARIFF"."REVISION_NO" ,
substr(tariff_id,1,1) as search_char FROM "BASE_TARIFF" where
(((search_char = 'X'))) (0 MilliSeconds)
BIND SELECT OUTPUT BUFFER (DataWindow): (0 MilliSeconds)

This is correct. Unfortunately, Oracle doesn't allow this. But your
database might.
I'll pass this on to Sybase.
--
Terry Dykstra [TeamPS]
Canadian Forest Oil Ltd.
See you at the Powersoft Conference in L.A.
http://www.sybase.com/Events/psuc98/index.html

Rick Marden wrote in message ...
>I am trying to use the prompt for criteria in a tabular report to allow the
>user to search for a character within a column. The "search" character
will
>always be in the same position. The column name is submittal_no and the
>"search" character is in the sixth position. I can display this value by
>using either mid(submittal_no, 6, 1) or substr(submittal_no, 6, 1) (as a
>computed column). I noticed that if I give the computed column a name by
>adding to the select statement something like "substr(submittal_no, 6, 1)
>search_character," then the search_character column shows up in the Prompt
>for Criteria list. However, when the prompt window comes up and I try to
>enter a value for this, I get an SQLState 37000 error, syntax error near
>"search_character." The frustrating part to this is that I can use the
>search_character computed column in a filter "search_character = 'x'" and
it
>works.
>
>I know you are thinking why not use a retrieval argument. Believe me, I
>wish I could, but we have a PB application which calls the report and
>automatically passes two values to each report (which all have the same two
>retrieval arguments defined), so if I have a report that contains more than
>two arguments, I get a DataWindow Error -- "Expecting three retrieval, but
>only got two."
>
>So, if possible I would like to be able to do this as a Prompt for Criteria
>so that the report works from within the application.
>
>I am currently using IM 5.0.03.
>
>Rick Marden
>Primavera Systems, Inc.
>rmarden@primavera.com
>
>


Rick Marden Posted on 1998-07-27 22:46:33.0Z
Newsgroups: sybase.public.infomaker.general
From: "Rick Marden" <mardens@cybertours.com>
Subject: Re: Prompt for criteria using a computed column
Date: Mon, 27 Jul 1998 18:46:33 -0400
Lines: 112
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Message-ID: <348_RItvPbau9GA.189@forums.powersoft.com>
References: <348_s3xGlMwt9GA.189@forums.powersoft.com> <348_9fmRC5yt9GA.256@forums.powersoft.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.infomaker.general:6970
Article PK: 242997

I am using an SQL Anywhere 5.5.03 DB. I put in the "AS" as you mentioned
below and it worked! I appreciate your help on this. Here is the trace if
you're interested:

SELECT "sbmt"."ball_in_court" , "sbmt"."package_number" ,
"sbmt"."submittal_no" , "sbmt"."latest_rev_number" ,
"sbmt"."latest_recvd_date" , "sbmt"."latest_sent_date" ,
"sbmt"."latest_return_date" , "sbmt"."latest_forward_date" ,
"sbmt"."status" , "sbmt"."required_start" ,
"sbmt"."required_finish" , "sbmt"."title" ,
"sbmt"."project_name" , "proj"."company_name" ,
"proj"."job_number" , "proj"."project_number" ,
"proj"."project_title" , substr(submittal_no, 6, 1) AS
search_character FROM "sbmt" , "proj" , "vnmt"
WHERE ( "sbmt"."project_name" = "proj"."project_name" ) and
"proj"."company_abbrev" = "vnmt"."vendor_abbrev" ) and
( sbmt.project_name = 'demo' ) and ( vnmt.project_name =
) and (((search_character = 'a'))) (25 MilliSeconds)
BIND SELECT OUTPUT BUFFER (DataWindow): (2 MilliSeconds)

Rick Marden

>>Primavera Systems, Inc.
>>rmarden@primavera.com

Terry Dykstra [TeamPS] wrote in message
<9fmRC5yt9GA.256@forums.powersoft.com>...
>The PB app can check the report for the number of arguments passed. If
>there are less then required, it can dynamically rewrite the report (i.e.
>datawindow) to remove the extra argument (using dw.create().) This
doesn't
>affect the actual stored report.
>
>You found an interesting bug. I tested with Oracle. To see the select
>statement that IM builds, you can change the dbms parameter in your
>db-profile by adding the word trace in front, eg. DBMS = 'trace O72'
>
>1) The computed column in Oracle without using AS: (tariff_id,1,1)
>search_char
>
> SELECT "BASE_TARIFF"."TARIFF_ID" , "BASE_TARIFF"."CREATED_BY"
,
>"BASE_TARIFF"."CREATED" , "BASE_TARIFF"."MODIFIED_BY" ,
>"BASE_TARIFF"."MODIFIED" , "BASE_TARIFF"."REVISION_NO" ,
>substr(tariff_id,1,1) search_char FROM "BASE_TARIFF" where
>(((substr(tariff_id,1,1) search_char = 'B'))) (0 MilliSeconds)
>
>As you see, the where clause contains both the computed field and the
alias.
>
>
>2) The computed column in Oracle using AS: (tariff_id,1,1) AS search_char
>
> SELECT "BASE_TARIFF"."TARIFF_ID" , "BASE_TARIFF"."CREATED_BY"
,
>"BASE_TARIFF"."CREATED" , "BASE_TARIFF"."MODIFIED_BY" ,
>"BASE_TARIFF"."MODIFIED" , "BASE_TARIFF"."REVISION_NO" ,
>substr(tariff_id,1,1) as search_char FROM "BASE_TARIFF" where
>(((search_char = 'X'))) (0 MilliSeconds)
> BIND SELECT OUTPUT BUFFER (DataWindow): (0 MilliSeconds)
>
>This is correct. Unfortunately, Oracle doesn't allow this. But your
>database might.
>I'll pass this on to Sybase.
>--
>Terry Dykstra [TeamPS]
>Canadian Forest Oil Ltd.
>See you at the Powersoft Conference in L.A.
>http://www.sybase.com/Events/psuc98/index.html
>
>Rick Marden wrote in message ...
>>I am trying to use the prompt for criteria in a tabular report to allow
the
>>user to search for a character within a column. The "search" character
>will
>>always be in the same position. The column name is submittal_no and the
>>"search" character is in the sixth position. I can display this value by
>>using either mid(submittal_no, 6, 1) or substr(submittal_no, 6, 1) (as a
>>computed column). I noticed that if I give the computed column a name by
>>adding to the select statement something like "substr(submittal_no, 6, 1)
>>search_character," then the search_character column shows up in the Prompt
>>for Criteria list. However, when the prompt window comes up and I try to
>>enter a value for this, I get an SQLState 37000 error, syntax error near
>>"search_character." The frustrating part to this is that I can use the
>>search_character computed column in a filter "search_character = 'x'" and
>it
>>works.
>>
>>I know you are thinking why not use a retrieval argument. Believe me, I
>>wish I could, but we have a PB application which calls the report and
>>automatically passes two values to each report (which all have the same
two
>>retrieval arguments defined), so if I have a report that contains more
than
>>two arguments, I get a DataWindow Error -- "Expecting three retrieval, but
>>only got two."
>>
>>So, if possible I would like to be able to do this as a Prompt for
Criteria
>>so that the report works from within the application.
>>
>>I am currently using IM 5.0.03.
>>
>>Rick Marden
>>Primavera Systems, Inc.
>>rmarden@primavera.com
>>
>>
>
>