SEO

#1 (**permalink**) 04-08-2008, 06:15 PM

I am creating this view:

create view
pfw_19_rsybtest_h
as
select
(
convert(varchar(4),datepart(yy,sybtest.mydate))
|| '-'
|| convert(varchar(2),datepart(mm,sybtest.mydate))
) sybdate

from sybtest
group by sybdate

Without the "group by" clause the view works well, returning the dates
as "2005-12" "2006-1" ... (i want to be able to group by months)...

But with the group by clause, i get the following error:

Column 'sybdate' specifies storage type 0, which does not currently exist.

I've tried using convert against the returned value...

eg: convert(varchar(255),....) sybdate

.... but the result is the same.

any tips for getting this done?

thanks.
alex.

#2 (**permalink**) 04-08-2008, 06:15 PM

alex wrote:
> I am creating this view:
>
> create view
> pfw_19_rsybtest_h
> as
> select
> (
> convert(varchar(4),datepart(yy,sybtest.mydate))
> || '-'
> || convert(varchar(2),datepart(mm,sybtest.mydate))
> ) sybdate
>
> from sybtest
> group by sybdate
>
>
> Without the "group by" clause the view works well, returning the dates
> as "2005-12" "2006-1" ... (i want to be able to group by months)...
>
> But with the group by clause, i get the following error:
>
> Column 'sybdate' specifies storage type 0, which does not currently exist.
>
>
> I've tried using convert against the returned value...
>
> eg: convert(varchar(255),....) sybdate
>
> ... but the result is the same.
>
> any tips for getting this done?
>
> thanks.
> alex.

GROUP BY is used to group the results of an aggregating function
such as summing or averaging values for a group. You are not
doing that in your query.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

#3 (**permalink**) 04-08-2008, 06:15 PM

En/na DA Morgan ha escrit:
> GROUP BY is used to group the results of an aggregating function
> such as summing or averaging values for a group. You are not
> doing that in your query.

sorry, i doubleposted. after finding this group i found
sybase.public.ase.general which had more recent messages...

the issue in this view was grouping by the alias name of the column
which seems not to be implemented in sybase.

i solved it by applying the same datepart conversions in the group by
clause:

create view
sybtestview
as
select
(
convert(varchar(4),datepart(yy,mydate))
|| '-'
|| convert(varchar(2),datepart(mm,mydate))
) sybdate

from sybtest
group by
(
convert(varchar(4),datepart(yy,mydate))
|| '-'
|| convert(varchar(2),datepart(mm,mydate))
)

this shows me the different months present in the sybtest table to which
i can apply other aggregate functions (for example count to get the
number of records for each month).

#4 (**permalink**) 04-08-2008, 06:15 PM

Since you don't have any sort of aggregation (sum(), count()) functions for
grouping (see Daniel's post), I'd have to ask what you mean by "i want to
be able to group by months"?

It sounds like you really want to order your results so that they show up
in date order ... eh?

If this is the case, try replacing the 'group by sybdate' clause with:

order by sybdate
or
order by 1
or
order by (convert....)

------------------------

On a separate but related note ...

You may want to consider left padding your month's with a '0'. You'll see
what I mean if you have the following dates in sybtest:

1/15/2006
4/15/2006
12/15/2006

These will end being ordered like such:

2006-1
2006-12
2006-4

By left padding the single-digit months with a 0 (zero) you should have no
problems getting them sorted properly, like such:

2006-01
2006-04
2006-12

Best bet would be to use something like:

convert(varchar(4),datepart(yy,sybtest.mydate))
|| '-'
|| right('0'+convert(varchar(2),datepart(mm,sybtest.m ydate)),2)

alex wrote:

> I am creating this view:
>
> create view
> pfw_19_rsybtest_h
> as
> select
> (
> convert(varchar(4),datepart(yy,sybtest.mydate))
> || '-'
> || convert(varchar(2),datepart(mm,sybtest.mydate))
> ) sybdate
>
> from sybtest
> group by sybdate
>
>
> Without the "group by" clause the view works well, returning the dates
> as "2005-12" "2006-1" ... (i want to be able to group by months)...
>
> But with the group by clause, i get the following error:
>
> Column 'sybdate' specifies storage type 0, which does not currently exist.
>
>
> I've tried using convert against the returned value...
>
> eg: convert(varchar(255),....) sybdate
>
> ... but the result is the same.
>
> any tips for getting this done?
>
> thanks.
> alex.

#5 (**permalink**) 04-08-2008, 06:15 PM

En/na Mark A. Parsons ha escrit:
> Since you don't have any sort of aggregation (sum(), count()) functions
> for grouping (see Daniel's post), I'd have to ask what you mean by "i
> want to be able to group by months"?

because that is a very stripped down version of what i'm actually trying
to do. anyway, i've sorted it out now.

think about doing something like (take a look at the new field using count):

create view
sybtestview
as
select
(
convert(varchar(4),datepart(yy,mydate))
|| '-'
|| convert(varchar(2),datepart(mm,mydate))
) sybdate
,count(mydate) as countdate

from sybtest
group by
(
convert(varchar(4),datepart(yy,mydate))
|| '-'
|| convert(varchar(2),datepart(mm,mydate))
)

this would count the records for each month and it would present the
results this way:

sybdate countdate
--------- ----------
2005-11 3
2005-12 1
2006-1 4

i think this is really useful!!!

alex.

#6 (**permalink**) 04-08-2008, 06:18 PM

>> (i want to be able to group by months <<

Why did you try a computational approach at all? Try this data-driven
approach:

CREATE TABLE ReportRanges
(report_name CHAR(10) NOT NULL PRIMARY KEY,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date),
range_type CHAR(2) DEFAULT 'mn' NOT NULL
CHECK (range_type IN ('DY', 'MN', 'YR', ..));

Now use predicates like " my_date BETWEEN start_date AND end_date" to
get your groups. You can use indexes and paralellism, the code will
port yo any SQL, etc.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Grouping by datepart() affected fields