Re: [SQL] ORACLE PROCEDURE TO POSTGRES FUNCTION -ERROR IN THE OVER PART

2011-11-11 Thread Thomas Kellerer 

Manu T, 07.11.2011 08:18:

I am using this query in the procedure and i error is throwing as mentioned 
below.and i want to convert the same oracle query into Postgresql.

ERROR-->

*ERROR: syntax error at or near "OVER"
LINE 1: ...heme_id,d.value, d.dr_cr_flg , d.rule_id , RANK() OVER (part...
^
QUERY: SELECT d1.scheme_id,d1.value, d1.dr_cr_flg from ( select 
d.scheme_id,d.value, d.dr_cr_flg , d.rule_id , RANK() OVER (partition by d.rule_id 
order by to_number(d.value) desc) AS rk from ( select b.scheme_id, b.rule_id, 
to_number( CASE b.value_type WHEN '%' THEN to_number((select 
COALESCE((b.scheme_value * a.base_miles)/100,0) from point_mtrx_acrul 
a,rule_matrix b where a.ORG = $1 and a.DEST = $2 and $3 between a.EFF_DT and 
a.EXP_DT and a.ARLN_NBR_CD = $4 )) ELSE b.SCHEME_VALUE END ) as value1, 
b.dr_cr_flg from rule_matrix b ,scheme_mstr c where b.rule_id = $5 and b.scheme_id 
= c.scheme_id and $3 between c.EFF_DT and c.EXP_DT and b.value_type not in 
('AWARD') ) d ) d1, scheme_mstr c where d1.rk<=1 and c.scheme_id=d1.scheme_id 
and $3 between c.EFF_DT and c.EXP_DT and rownum=1
CONTEXT: SQL statement in PL/PgSQL function "rule_engine" near line 563

** Error **

ERROR: syntax error at or near "OVER"
SQL state: 42601
Context: SQL statement in PL/PgSQL function "rule_engine" near line 563


Windowing functions where introduced in Version 8.4. Which version are you 
using?




--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] clarification about ARRAY constructor implementation

2011-11-11 Thread the6campbells 
consider the following

create table TARRBINT ( RNUM integer  not null , CARRBINT bigint array[5] )
;

Can someone clarify why Postgres does not like examples 2, 6 and 7

1.insert into TARRBINT(RNUM, CARRBINT) values ( 0, null);
2.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[]);
3.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[]::integer[]);
4.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[null,1]);
5.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[1, null]);
6.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[null]);
7.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[null,null]);

Similarly, why does it seem to force casting expressions with other
instances of literal values used in the array constructor. Is this due to
Postgres treating some literal values to be a 'lob' type and thus
concluding that it cannot be used in the context of array constructor?

For example, this will work

create table TARRCHAR ( RNUM integer  not null , CARRCHAR char(72)
array[5]   ) ;
insert into TARRCHAR(RNUM, CARRCHAR) values ( 1, ARRAY [''])

But scenarios like this will not

create table TXML ( RNUM integer  not null , CXML xml   ) ;
insert into TXML(RNUM, CXML) values ( 1, '');

create table TARRXML ( RNUM integer  not null , CARRXML xml array[5]   ) ;
insert into TARRXML(RNUM, CARRXML) values ( 1, ARRAY ['']);

ERROR:  column "carrxml" is of type xml[] but expression is of type text[]
LINE 1: insert into TARRXML(RNUM, CARRXML) values ( 1, ARRAY ['

Re: [SQL] clarification about ARRAY constructor implementation

2011-11-11 Thread David Johnston 
On Nov 11, 2011, at 8:38, the6campbells  wrote:

> consider the following
>  
> create table TARRBINT ( RNUM integer  not null , CARRBINT bigint array[5] ) ;
>  
> Can someone clarify why Postgres does not like examples 2, 6 and 7

An array must have a base type; an empty array has nothing with which to infer 
a type so you must cast it yourself.  Null is typeless and so you need an 
explicit cast if all values are null.

>  
> 1.insert into TARRBINT(RNUM, CARRBINT) values ( 0, null);
> 2.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[]);
> 3.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[]::integer[]);
> 4.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[null,1]);
> 5.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[1, null]);
> 6.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[null]);
> 7.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[null,null]);
>  
> Similarly, why does it seem to force casting expressions with other instances 
> of literal values used in the array constructor. Is this due to Postgres 
> treating some literal values to be a 'lob' type and thus concluding that it 
> cannot be used in the context of array constructor?
> 
> For example, this will work
>  
> create table TARRCHAR ( RNUM integer  not null , CARRCHAR char(72) array[5]   
> ) ;
> insert into TARRCHAR(RNUM, CARRCHAR) values ( 1, ARRAY [''])
>  
> But scenarios like this will not
>  
> create table TXML ( RNUM integer  not null , CXML xml   ) ;
> insert into TXML(RNUM, CXML) values ( 1, '');
> 
> create table TARRXML ( RNUM integer  not null , CARRXML xml array[5]   ) ;
> insert into TARRXML(RNUM, CARRXML) values ( 1, ARRAY ['']);
>  
> ERROR:  column "carrxml" is of type xml[] but expression is of type text[]
> LINE 1: insert into TARRXML(RNUM, CARRXML) values ( 1, ARRAY ['^
> HINT:  You will need to rewrite or cast the expression.

Going by observation/inference here...

An array can never take on the "unknown" type whereas a scalar can.  The 
unknown type can be passed to the Insert where the target's column type can 
then be used for casting.  The array, needing immediate casting, chooses the 
most liberal type, in this case text, before it gets sent to the Insert.

David J.
-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql