Re: postgres confuses table alias for schema in selects

Write (bucket). instead

Am 30. November 2020 14:29:43 MEZ schrieb jim schmidt <txherper(at)gmail(dot)com>:
> List of relations
> Schema | Name | Type | Owner
>----------+---------------------------+------+-------
> aerodemo | vnd_avail_perf_bucket_sum | view | jjs
>(1 row)
>
>/* select * from vnd_avail_perf_bucket_sum; */
>/* #1 simple */
>select count(*) from vnd_avail_perf_bucket_sum;
> count
>-------
> 1708
>(1 row)
>
>/* #2 simple on view, no join */
>select count(*) from
>(
>select
> item_nbr,
> ic_category_nbr,
> weeks_late,
> count(*) bucket_count,
> line_stat_id
>from vnd_avail_perf_bucket_sum
>group by
> item_nbr,
> ic_category_nbr,
> weeks_late,
> line_stat_id
>) as count_query;
> count
>-------
> 1708
>(1 row)
>
>;
>/* #3 simple with alias */
>select count(*) from
>(
>select
> bucket.item_nbr,
> bucket.ic_category_nbr,
> bucket.weeks_late,
> count(*) bucket_count,
> bucket.line_stat_id
>from vnd_avail_perf_bucket_sum as bucket
>group by
> bucket.item_nbr,
> bucket.ic_category_nbr,
> bucket.weeks_late,
> bucket.line_stat_id
>) as count_query;
> count
>-------
> 1708
>(1 row)
>
>;
>/* #4 error sum on count
> bucket is a table alias, thinks its a schema */
>select count(*) from
>(
>select
> bucket.item_nbr,
> bucket.ic_category_nbr,
> bucket.weeks_late,
> sum(bucket.count(*)) bucket_count,
> bucket.line_stat_id
>from vnd_avail_perf_bucket_sum as bucket
>group by
> item_nbr,
> ic_category_nbr,
> weeks_late,
> line_stat_id
>) as count_query;
>psql:pct3.sql:60: ERROR: schema "bucket" does not exist
>LINE 9: sum(bucket.count(*)) bucket_count,
> ^
>;
>/* #4.1 bucket defined in with
> observation: works
>*/
>with bucket as
>(
>select
> item_nbr,
> ic_category_nbr,
> weeks_late,
> count(*) bucket_count,
> line_stat_id
>from vnd_avail_perf_bucket_sum
>group by
> item_nbr,
> ic_category_nbr,
> weeks_late,
> line_stat_id
>) select count(*) from bucket;
> count
>-------
> 1708
>(1 row)
>
>;
>/* #4.2
> no sum on count error
> bucket is a table alias, thinks its a schema */
>select count(*) from
>(
>select
> bucket.item_nbr,
> bucket.ic_category_nbr,
> bucket.weeks_late,
> bucket.count(*) bucket_count,
> bucket.line_stat_id
>from vnd_avail_perf_bucket_sum as bucket
>group by
> item_nbr,
> ic_category_nbr,
> weeks_late,
> line_stat_id
>) as count_query;
>psql:pct3.sql:101: ERROR: schema "bucket" does not exist
>LINE 10: bucket.count(*) bucket_count,
> ^
>;
>/* #4.3
> no table alias
>*/
>select count(*) from
>(
>select
> item_nbr,
> ic_category_nbr,
> weeks_late,
> count(*) bucket_count,
> line_stat_id
>from vnd_avail_perf_bucket_sum
>group by
> item_nbr,
> ic_category_nbr,
> weeks_late,
> line_stat_id
>) as count_query;
> count
>-------
> 1708
>(1 row)
>
>;
>/* #5 no-count */
>select
> bucket.item_nbr,
> bucket.ic_category_nbr,
> bucket.weeks_late,
> bucket.count(*) / bucket_sum.total_buckets bucket_pct,
> bucket.line_stat_id
>from vnd_avail_perf_bucket_sum bucket,
> bucket_sum
>where bucket_sum.item_nbr = bucket.item_nbr and
> bucket_sum.ic_category_nbr = bucket.ic_category_nbr and
> bucket_sum.line_stat_id = bucket.line_stat_id
>group by
> item_nbr,
> ic_category_nbr,
> weeks_late,
> line_stat_id
>;
>psql:pct3.sql:139: ERROR: relation "bucket_sum" does not exist
>LINE 9: bucket_sum
> ^
>/* #6 undefined view bucket_sum
> ??? what is wrong with the from clause */
>select count(*) from
>(
>select
> bucket.item_nbr,
> bucket.ic_category_nbr,
> bucket.weeks_late,
> bucket.count(*) / bucket_sum.total_buckets) bucket_pct,
> bucket.line_stat_id
>from vnd_avail_perf_bucket_sum bucket,
> bucket_sum
>where bucket_sum.item_nbr = bucket.item_nbr and
> bucket_sum.ic_category_nbr = bucket.ic_category_nbr and
> bucket_sum.line_stat_id = bucket.line_stat_id
>group by
> item_nbr,
> ic_category_nbr,
> weeks_late,
> line_stat_id
>) as count_query
>;
>psql:pct3.sql:163: ERROR: syntax error at or near "from"
>LINE 11: from vnd_avail_perf_bucket_sum bucket,
> ^
>/* #6.1 bucket_sum using with
> extraneous ')'
> observation: syntax error near from,
> question: why?
>*/
>select count(*) from
>(
>with bucket_sum as
>(select
> vs.item_nbr,
> vs.ic_category_nbr,
> count(vs.*) total_buckets,
> vs.line_stat_id
>from vnd_avail_perf_bucket vs
>group by
> vs.item_nbr,
> vs.ic_category_nbr,
> vs.line_stat_id
>)
>select
> bucket.item_nbr,
> bucket.ic_category_nbr,
> bucket.weeks_late,
> bucket.count(*) / bucket_sum.total_buckets) bucket_pct,
> bucket.line_stat_id
>from vnd_avail_perf_bucket_sum bucket,
> bucket_sum
>where bucket_sum.item_nbr = bucket.item_nbr and
> bucket_sum.ic_category_nbr = bucket.ic_category_nbr and
> bucket_sum.line_stat_id = bucket.line_stat_id
>group by
> item_nbr,
> ic_category_nbr,
> weeks_late,
> line_stat_id
>) as count_query
>;
>psql:pct3.sql:201: ERROR: syntax error at or near "from"
>LINE 26: from vnd_avail_perf_bucket_sum bucket,
> ^
>/* #6.2 bucket_sum using with, no alias on with select
> no extranoues ')'
> observation: syntax error near from,
> question: why?
>*/
>select count(*) from
>(
>with bucket_sum as
>(select
> item_nbr,
> ic_category_nbr,
> count(*) total_buckets,
> line_stat_id
>from vnd_avail_perf_bucket
>group by
> item_nbr,
> ic_category_nbr,
> line_stat_id
>)
>select
> bucket.item_nbr,
> bucket.ic_category_nbr,
> bucket.weeks_late,
> bucket.count(*) / bucket_sum.total_buckets bucket_pct,
> bucket.line_stat_id
>from vnd_avail_perf_bucket_sum bucket,
> bucket_sum
>where bucket_sum.item_nbr = bucket.item_nbr and
> bucket_sum.ic_category_nbr = bucket.ic_category_nbr and
> bucket_sum.line_stat_id = bucket.line_stat_id
>group by
> item_nbr,
> ic_category_nbr,
> weeks_late,
> line_stat_id
>) as count_query
>;
>psql:pct3.sql:238: ERROR: schema "bucket" does not exist
>LINE 24: bucket.count(*) / bucket_sum.total_buckets
>bucket_pct...
> ^
>/* #6.3 bucket_sum using with, no alias on with select, fixed
>extraneous
>paren
> observation: syntax error near from,
> question: why?
>*/
>select count(*) from
>(
>with bucket_sum as
>(select
> item_nbr,
> ic_category_nbr,
> count(*) total_buckets,
> line_stat_id
>from vnd_avail_perf_bucket
>group by
> item_nbr,
> ic_category_nbr,
> line_stat_id
>)
>select
> bucket.item_nbr,
> bucket.ic_category_nbr,
> bucket.weeks_late,
> bucket.count(*) / bucket_sum.total_buckets bucket_pct,
> bucket.line_stat_id
>from vnd_avail_perf_bucket_sum bucket,
> bucket_sum
>where bucket_sum.item_nbr = bucket.item_nbr and
> bucket_sum.ic_category_nbr = bucket.ic_category_nbr and
> bucket_sum.line_stat_id = bucket.line_stat_id
>group by
> item_nbr,
> ic_category_nbr,
> weeks_late,
> line_stat_id
>) as count_query
>;
>psql:pct3.sql:274: ERROR: schema "bucket" does not exist
>LINE 23: bucket.count(*) / bucket_sum.total_buckets
>bucket_pct...
> ^
>/* #7 bucket is a table alias thinks it's a schema */
>select count(*) from
>(
>select
> bucket.item_nbr,
> bucket.ic_category_nbr,
> bucket.weeks_late,
> sum(bucket.count(*) / bucket_sum.total_buckets) bucket_pct,
> bucket.line_stat_id
>from vnd_avail_perf_bucket_sum bucket
>where bucket_sum.item_nbr = bucket.item_nbr and
> bucket_sum.ic_category_nbr = bucket.ic_category_nbr and
> bucket_sum.line_stat_id = bucket.line_stat_id
>group by
> item_nbr,
> ic_category_nbr,
> weeks_late,
> line_stat_id
>) as count_query
>;
>psql:pct3.sql:295: ERROR: schema "bucket" does not exist
>LINE 8: sum(bucket.count(*) / bucket_sum.total_buckets) bucke...
> ^
>/* #8 ??? thinks bucket is a schema, but it is a table alias */
>with bucket_sum as (
>select
> vs.item_nbr,
> vs.ic_category_nbr,
> count(vs.*) total_buckets,
> vs.line_stat_id
>from vnd_avail_perf_bucket vs
>group by
> vs.item_nbr,
> vs.ic_category_nbr,
> vs.line_stat_id
>)
>select
> bucket.item_nbr,
> bucket.ic_category_nbr,
> bucket.weeks_late,
> bucket.count(*) / bucket_sum.total_buckets bucket_pct,
> bucket.line_stat_id
>from vnd_avail_perf_bucket_sum bucket,
> bucket_sum
>where bucket_sum.item_nbr = bucket.item_nbr and
> bucket_sum.ic_category_nbr = bucket.ic_category_nbr and
> bucket_sum.line_stat_id = bucket.line_stat_id
>group by
> item_nbr,
> ic_category_nbr,
> weeks_late,
> line_stat_id
>;
>psql:pct3.sql:326: ERROR: schema "bucket" does not exist
>LINE 18: bucket.count(*) / bucket_sum.total_buckets
>bucket_pct...
> ^
>
>
>--
>blog: homeless-by-choice.blogspot.com

--
Holger Jakobs, Bergisch Gladbach
+49 178 9759012
- sent from mobile, therefore short -