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.

REPOST: Problems with computed columns in a query against an Oracle database

3 posts in PB Infomaker Report Writer Last posting was on 1998-02-12 18:22:44.0Z
Matt Brennan Posted on 1998-02-10 01:04:30.0Z
Newsgroups: sybase.public.infomaker.general
From: "Matt Brennan" <mcb@fightspam.sd.znet.com>
Subject: REPOST: Problems with computed columns in a query against an Oracle database
Date: Mon, 9 Feb 1998 17:04:30 -0800
Organization: All USENET -- http://www.Supernews.com
Lines: 89
X-Newsreader: Microsoft Outlook Express 4.71.1712.3
X-MimeOLE: Produced By Microsoft MimeOLE V4.71.1712.3
Message-ID: <348_6btkaa$6mj$1@usenet40.supernews.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.powersoft.com!node17.frontiernet.net!node2.frontiernet.net!news-pen-16.sprintlink.net!newsfeed.nysernet.net!news.nysernet.net!207.41.200.14!news-pen-14.sprintlink.net!206.229.87.26!news-east.sprintlink.net!news-peer.sprintlink.net!news.sprintlink.net!Sprint!newsfeed.direct.ca!news.he.net!Supernews73!supernews.com!Supernews69!not-for-mail
Xref: forums-1-dub sybase.public.infomaker.general:7281
Article PK: 243311

Sorry for the duplicate post, but I didn't see the first post appear and I
had problems posting it originally....

******************************************
I'm having a problem with a query that I'm trying to write in Infomaker 5.0.
I can execute this query from an Oracle SQL*Plus command line prompt just
fine, but I'd like to replicate this query for a report in Infomaker and I'm
having problems with the GUI interface which won't let me do it, either due
to bugs or due to Infomaker's design, or both.

The first problem is that the query involves a lot of computed columns in
the select-list where substrings and decodes are used against database
columns. Infomaker doesn't seem to have a problem with me including that in
a select-list, but since I also have some sums in the select list, I need to
do a group by clause where I have to include the other (not summed/grouped
across rows) computed columns in the group by clause, but I can't seem to
find a way in Infomaker to write the correct group by clause to support my
select-list computed columns. The GUI interface won't let me pick the
computed columns in my select-list, but rather only the "uncomputed"
database columns and Oracle doesn't like that. (I get an appropriate Oracle
error about not having done my group by clause right - I don't remember the
error number offhand.) Is there a way around this? It would help if there
was a manual way to edit the query text (similar to how MS Query will let
the user do it), but there doesn't seem to be that functionality in
Infomaker either unless I'm totally missing it.

The second problem, which I can work around, but it's very annoying, is that
in entering the computed columns on the "Compute" tab folder, if I modify a
column and leave the tab folder or the line I'm working on, the column is
moved to the end of the select list in the query out of the order in the
select-list that I previously put it in. Is there a way to stop it from
doing this?

Problem #2 above leads to problem #3. If I have a lot of computed columns,
I sometimes can't even drag them around into a different order in my select
list and I have to almost start all over creating the select-list. The GUI
interface seems to have a problem if the computed columns have text past a
certain length or something.

This is quite maddening. Are there any preference setting that I'm missing
that might cause any of the above problems or am I trying to do things that
Infomaker doesn't support? Any insight is greatly appreciated!

For the record, here's the original query I'm trying to duplicate in
Infomaker:

select substr(l.store_cd||' '||st.store_name, 1, 18) "store",
l.slp_cd||' '||s.slp_fname||' '||decode(s.slp_init, null, null,
s.slp_init)||decode(s.slp_init, null, null, '.')||decode(s.slp_init, null,
null, '
')||s.slp_lname "salesperson",
l.subclass_cd, sum(l.qty) "tot_qty", sum(l.eff_amt*l.qty) "tot_eff_amt",
s.comm_pct,
round((sum(l.eff_amt*l.qty)*(s.comm_pct/100)), 2) "comm_amt", s.file_cd,
decode(s.status_cd, 'P', 'Part', 'F', 'Full', 'X', 'Mgr.', null) "status"
from sh_slp_trn_ln l, slp s, store st
where l.store_cd = st.store_cd
and l.slp_cd = s.slp_cd
and l.trn_dt >= '&beginning_transaction_date'
and l.trn_dt <= '&ending_transaction_date'
group by substr(l.store_cd||' '||st.store_name, 1, 18),
l.slp_cd||' '||s.slp_fname||' '||decode(s.slp_init, null, null,
s.slp_init)||decode(s.slp_init, null, null, '.')||decode(s.slp_init, null,
null, '
')||s.slp_lname, l.subclass_cd, s.comm_pct, s.file_cd,
decode(s.status_cd, 'P', 'Part', 'F', 'Full', 'X', 'Mgr.', null)
order by substr(l.store_cd||' '||st.store_name, 1, 18),
l.slp_cd||' '||s.slp_fname||' '||decode(s.slp_init, null, null,
s.slp_init)||decode(s.slp_init, null, null, '.')||decode(s.slp_init, null,
null, '
')||s.slp_lname

--
-Matt
Remove 'fightspam.' from email address to reply


Terry Dykstra [TeamPS] Posted on 1998-02-12 18:22:44.0Z
Newsgroups: sybase.public.infomaker.general
From: "Terry Dykstra [TeamPS]" <no_spam_tdykstra@cfol.ab.ca>
Subject: Re: REPOST: Problems with computed columns in a query against an Oracle database
Organization: Canadian Forest Oil Ltd.
X-Newsreader: Microsoft Internet News 4.70.1161
Date: Thu, 12 Feb 1998 13:22:44 -0500
Lines: 40
Message-ID: <348_01bd37ec$2999d980$3301020a@tdykstra>
References: <348_6btkaa$6mj$1@usenet40.supernews.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.infomaker.general:7277
Article PK: 243306


> The second problem, which I can work around, but it's very annoying, is
that
> in entering the computed columns on the "Compute" tab folder, if I modify
a
> column and leave the tab folder or the line I'm working on, the column is
> moved to the end of the select list in the query out of the order in the
> select-list that I previously put it in. Is there a way to stop it from
> doing this?

I don't think you can get around this. As long as you use aliases on your
computed columns it's not an issue, but it definitely is when you don't use
aliases. IM then names the computed columns compute_xxx where the xxx is
position dependent. I'll send this in as an issue.

>
> Problem #2 above leads to problem #3. If I have a lot of computed
columns,
> I sometimes can't even drag them around into a different order in my
select
> list and I have to almost start all over creating the select-list. The
GUI
> interface seems to have a problem if the computed columns have text past
a
> certain length or something.

When the computed column is larger than the width of the 'selection list'
bar, you definitely have this problem. Convert to syntax to get around
this. I'll send this in as an issue.

>
> This is quite maddening. Are there any preference setting that I'm
missing
> that might cause any of the above problems or am I trying to do things
that
> Infomaker doesn't support? Any insight is greatly appreciated!
>
> For the record, here's the original query I'm trying to duplicate in
> Infomaker:

Very impressive query.


Terry Dykstra [TeamPS] Posted on 1998-02-12 18:09:24.0Z
Newsgroups: sybase.public.infomaker.general
From: "Terry Dykstra [TeamPS]" <no_spam_tdykstra@cfol.ab.ca>
Subject: Re: REPOST: Problems with computed columns in a query against an Oracle database
Organization: Canadian Forest Oil Ltd.
X-Newsreader: Microsoft Internet News 4.70.1161
Date: Thu, 12 Feb 1998 13:09:24 -0500
Lines: 120
Message-ID: <348_01bd37ea$4c4f9200$3301020a@tdykstra>
References: <348_6btkaa$6mj$1@usenet40.supernews.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.infomaker.general:7278
Article PK: 243307

When you create a report, you can convert the SQL to syntax. That is the
way to get around the limitations of the GUI interface. I have an
enhancement request to make the GUI interface more consistent. It doesn't
make sense I can use computed fields in the HAVING clause but not in the
GROUP BY clause. You can use Webexpress to submit enhancement requests.
The more people ask for a feature, the better the chances of getting it.
--
Terry Dykstra [TeamPS]
Canadian Forest Oil Ltd.

Matt Brennan <mcb@fightspam.sd.znet.com> wrote in article
<6btkaa$6mj$1@usenet40.supernews.com>...
> Sorry for the duplicate post, but I didn't see the first post appear and
I
> had problems posting it originally....
>
> ******************************************
> I'm having a problem with a query that I'm trying to write in Infomaker
5.0.
> I can execute this query from an Oracle SQL*Plus command line prompt just
> fine, but I'd like to replicate this query for a report in Infomaker and
I'm
> having problems with the GUI interface which won't let me do it, either
due
> to bugs or due to Infomaker's design, or both.
>
> The first problem is that the query involves a lot of computed columns in
> the select-list where substrings and decodes are used against database
> columns. Infomaker doesn't seem to have a problem with me including that
in
> a select-list, but since I also have some sums in the select list, I need
to
> do a group by clause where I have to include the other (not
summed/grouped
> across rows) computed columns in the group by clause, but I can't seem to
> find a way in Infomaker to write the correct group by clause to support
my
> select-list computed columns. The GUI interface won't let me pick the
> computed columns in my select-list, but rather only the "uncomputed"
> database columns and Oracle doesn't like that. (I get an appropriate
Oracle
> error about not having done my group by clause right - I don't remember
the
> error number offhand.) Is there a way around this? It would help if
there
> was a manual way to edit the query text (similar to how MS Query will let
> the user do it), but there doesn't seem to be that functionality in
> Infomaker either unless I'm totally missing it.
>
> The second problem, which I can work around, but it's very annoying, is
that
> in entering the computed columns on the "Compute" tab folder, if I modify
a
> column and leave the tab folder or the line I'm working on, the column is
> moved to the end of the select list in the query out of the order in the
> select-list that I previously put it in. Is there a way to stop it from
> doing this?
>
> Problem #2 above leads to problem #3. If I have a lot of computed
columns,
> I sometimes can't even drag them around into a different order in my
select
> list and I have to almost start all over creating the select-list. The
GUI
> interface seems to have a problem if the computed columns have text past
a
> certain length or something.
>
> This is quite maddening. Are there any preference setting that I'm
missing
> that might cause any of the above problems or am I trying to do things
that
> Infomaker doesn't support? Any insight is greatly appreciated!
>
> For the record, here's the original query I'm trying to duplicate in
> Infomaker:
>
> select substr(l.store_cd||' '||st.store_name, 1, 18) "store",
> l.slp_cd||' '||s.slp_fname||' '||decode(s.slp_init, null, null,
> s.slp_init)||decode(s.slp_init, null, null, '.')||decode(s.slp_init,
null,
> null, '
> ')||s.slp_lname "salesperson",
> l.subclass_cd, sum(l.qty) "tot_qty", sum(l.eff_amt*l.qty) "tot_eff_amt",
> s.comm_pct,
> round((sum(l.eff_amt*l.qty)*(s.comm_pct/100)), 2) "comm_amt", s.file_cd,
> decode(s.status_cd, 'P', 'Part', 'F', 'Full', 'X', 'Mgr.', null) "status"
> from sh_slp_trn_ln l, slp s, store st
> where l.store_cd = st.store_cd
> and l.slp_cd = s.slp_cd
> and l.trn_dt >= '&beginning_transaction_date'
> and l.trn_dt <= '&ending_transaction_date'
> group by substr(l.store_cd||' '||st.store_name, 1, 18),
> l.slp_cd||' '||s.slp_fname||' '||decode(s.slp_init, null, null,
> s.slp_init)||decode(s.slp_init, null, null, '.')||decode(s.slp_init,
null,
> null, '
> ')||s.slp_lname, l.subclass_cd, s.comm_pct, s.file_cd,
> decode(s.status_cd, 'P', 'Part', 'F', 'Full', 'X', 'Mgr.', null)
> order by substr(l.store_cd||' '||st.store_name, 1, 18),
> l.slp_cd||' '||s.slp_fname||' '||decode(s.slp_init, null, null,
> s.slp_init)||decode(s.slp_init, null, null, '.')||decode(s.slp_init,
null,
> null, '
> ')||s.slp_lname
>
> --
> -Matt
> Remove 'fightspam.' from email address to reply
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>