Quantcast

INSERT... From SELECT *

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

INSERT... From SELECT *

m. Th.
Hi,

Having an

INSERT INTO DELMOV SELECT * FROM MOVEMENT WHERE...

throws an 'Conversion error from string "09-02-2009"' on FB 2.1

We fixed it by having the same order of fields in both DELMOV and
MOVEMENT tables.
But it shouldn't work anyway? (Ie. without taking in account the order
of fields in dest and src tables).

IMHO, it's dangerous because it can silently 'relocate' data in unwanted
fields. We were lucky having an error returned...

Perhaps is worth mentioning that we have some calculated fields in MOVEMENT.

Regards,

m. Th.



------------------------------------------------------------------------------
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are
powering Web 2.0 with engaging, cross-platform capabilities. Quickly and
easily build your RIAs with Flex Builder, the Eclipse(TM)based development
software that enables intelligent coding and step-through debugging.
Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: INSERT... From SELECT *

Adriano dos Santos Fernandes-3
m. Th. escreveu:

> Hi,
>
> Having an
>
> INSERT INTO DELMOV SELECT * FROM MOVEMENT WHERE...
>
> throws an 'Conversion error from string "09-02-2009"' on FB 2.1
>
> We fixed it by having the same order of fields in both DELMOV and
> MOVEMENT tables.
> But it shouldn't work anyway? (Ie. without taking in account the order
> of fields in dest and src tables).
If you're talking about parameter names of DELMOV matches oens from
MOVEMENT, no. This was nothing to do with INSERT...SELECT.


Adriano


------------------------------------------------------------------------------
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are
powering Web 2.0 with engaging, cross-platform capabilities. Quickly and
easily build your RIAs with Flex Builder, the Eclipse(TM)based development
software that enables intelligent coding and step-through debugging.
Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: INSERT... From SELECT *

m. Th.
Adriano dos Santos Fernandes wrote:

> m. Th. escreveu:
>  
>> Hi,
>>
>> Having an
>>
>> INSERT INTO DELMOV SELECT * FROM MOVEMENT WHERE...
>>
>> throws an 'Conversion error from string "09-02-2009"' on FB 2.1
>>
>> We fixed it by having the same order of fields in both DELMOV and
>> MOVEMENT tables.
>> But it shouldn't work anyway? (Ie. without taking in account the order
>> of fields in dest and src tables).
>>    
> If you're talking about parameter names of DELMOV matches oens from
> MOVEMENT, no. This was nothing to do with INSERT...SELECT.
>
>
>  
Thanks for the response, Adrian.

So, you say that if we have:

CREATE TABLE MOVEMENT(
  MOVID LONGID DEFAULT 1,
  DB ID DEFAULT 1,
  CR ID DEFAULT 1,
  MOVDATE Date DEFAULT 'NOW',
  MOVVAL MONEY DEFAULT 0,
  REASON Varchar(80),
  MOVTYPE ID DEFAULT 1,
  REFID REFID DEFAULT '',
  MOVAUXTYPE ID DEFAULT 1,
  AUXREFID0 REFID DEFAULT '',
  STATUS Smallint DEFAULT 1,
  CHANNEL Smallint,
  DEPT Smallint DEFAULT 1,
  MOVCVAL MONEY DEFAULT 0,
  MOVCID ID DEFAULT 1,
  OK "BOOLEAN" DEFAULT '_' COLLATE WIN1253,
  SELECTED "BOOLEAN" DEFAULT '_',
  HIDDEN "BOOLEAN" DEFAULT '_',
  XVAL MONEY DEFAULT 0,
  USRID ID DEFAULT 1,
  EDITOR ID DEFAULT 1,
  BDATE Date,
  GROUPNO LONGID DEFAULT 0,
  REFID_SN REFID DEFAULT '',
  AUXREFID0_SN REFID DEFAULT '',
  CONSTRAINT PK_MOVEMENT PRIMARY KEY (MOVID)
); /* a part from the actual definition */

- and -

CREATE TABLE DELMOV(
  MOVID LONGID DEFAULT 1,
  DB ID DEFAULT 1,
  CR ID DEFAULT 1,
  MOVVAL MONEY DEFAULT 0, /* changed order here! */
  MOVDATE Date DEFAULT 'NOW', /* changed order here! */
  REASON Varchar(80),
  MOVTYPE ID DEFAULT 1,
  REFID REFID DEFAULT '',
  MOVAUXTYPE ID DEFAULT 1,
  AUXREFID0 REFID DEFAULT '',
  STATUS Smallint DEFAULT 1,
  CHANNEL Smallint,
  DEPT Smallint DEFAULT 1,
  MOVCVAL MONEY DEFAULT 0,
  MOVCID ID DEFAULT 1,
  OK "BOOLEAN" DEFAULT '_' COLLATE WIN1253,
  SELECTED "BOOLEAN" DEFAULT '_',
  HIDDEN "BOOLEAN" DEFAULT '_',
  XVAL MONEY DEFAULT 0,
  USRID ID DEFAULT 1,
  EDITOR ID DEFAULT 1,
  BDATE Date,
  GROUPNO LONGID DEFAULT 0,
  REFID_SN REFID DEFAULT '',
  AUXREFID0_SN REFID DEFAULT '',
  CONSTRAINT PK_MOVEMENT PRIMARY KEY (MOVID)
);

...this will NOT work because

INSERT INTO DELMOV SELECT * FROM MOVEMENT

will do a 'blind' transfer based on field Index not on field name, isn't?




------------------------------------------------------------------------------
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are
powering Web 2.0 with engaging, cross-platform capabilities. Quickly and
easily build your RIAs with Flex Builder, the Eclipse(TM)based development
software that enables intelligent coding and step-through debugging.
Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: INSERT... From SELECT *

Adriano dos Santos Fernandes-3
m. Th. escreveu:
> ...this will NOT work because
>
> INSERT INTO DELMOV SELECT * FROM MOVEMENT
>
> will do a 'blind' transfer based on field Index not on field name, isn't?
Exactly. And Firebird allows you to change field position:
    alter table delmov
        alter movdate position 4;


Adriano


------------------------------------------------------------------------------
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are
powering Web 2.0 with engaging, cross-platform capabilities. Quickly and
easily build your RIAs with Flex Builder, the Eclipse(TM)based development
software that enables intelligent coding and step-through debugging.
Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: INSERT... From SELECT *

m. Th.
Adriano dos Santos Fernandes wrote:
> Exactly. <snip>
>
> Adriano
>  
Thanks a lot!

m. Th.



------------------------------------------------------------------------------
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are
powering Web 2.0 with engaging, cross-platform capabilities. Quickly and
easily build your RIAs with Flex Builder, the Eclipse(TM)based development
software that enables intelligent coding and step-through debugging.
Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Loading...