Re: [DUG] Sql query
For a while in the late 90's we had in house a running joke that functions
called Refresh never did anything, based on the fact that TDataset.Refresh
and a few others (perhaps TWinControl.Refresh?) never actually solved the
problem you were trying to solve when you called them. ;-)
I think things have moved on a bit since then but I still suspect Refresh is
the wrong method in this instance and Close followed by Open is necessary as
Alister suggests.
Cheers,
David.
From: delphi-boun...@delphi.org.nz [mailto:delphi-boun...@delphi.org.nz] On
Behalf Of Alister Christie
Sent: Friday, 14 January 2011 3:24 p.m.
To: NZ Borland Developers Group - Delphi List
Subject: Re: [DUG] Sql query
Rather than doing a Refresh you could do an Open and a Close. If you do
this you probably want to store what record you are on
id := QToDo.FieldByName(id).AsInteger;
QToDo.Close;
QToDo.Open
QToDo.Locate('id', id, []);
substituting id for whatever your primary key is. Not elegant but better
than restarting the application.
Alister Christie
Computers for People
Ph: 04 471 1849 Fax: 04 471 1266
http://www.salespartner.co.nz
PO Box 13085
Johnsonville
Wellington
On 14/01/2011 12:49 p.m., Marshland Engineering wrote:
I'm viewing a SQL query in a dbgrid. When I add a record to the source db
and I run QToDo.Refresh it does not include the new record in the dbgrid.
If I close the application and re-run it, the record is displayed.
Is refresh the correct property or is there something else.
Thanks Wallace
___
NZ Borland Developers Group - Delphi mailing list
Post: delphi@delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to delphi-requ...@delphi.org.nz with Subject:
unsubscribe
___
NZ Borland Developers Group - Delphi mailing list
Post: delphi@delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to delphi-requ...@delphi.org.nz with Subject:
unsubscribe
Re: [DUG] Sql query
Rather than doing a Refresh you could do an Open and a Close. If you do
this you probably want to store what record you are on
id := QToDo.FieldByName(id).AsInteger;
QToDo.Close;
QToDo.Open
QToDo.Locate('id', id, []);
substituting id for whatever your primary key is. Not elegant but
better than restarting the application.
Alister Christie
Computers for People
Ph: 04 471 1849 Fax: 04 471 1266
http://www.salespartner.co.nz
PO Box 13085
Johnsonville
Wellington
On 14/01/2011 12:49 p.m., Marshland Engineering wrote:
I'm viewing a SQL query in a dbgrid. When I add a record to the source
db and I run QToDo.Refresh it does not include the new record in the
dbgrid. If I close the application and re-run it, the record is
displayed.
Is refresh the correct property or is there something else.
Thanks Wallace
___
NZ Borland Developers Group - Delphi mailing list
Post: delphi@delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to delphi-requ...@delphi.org.nz with Subject:
unsubscribe
___
NZ Borland Developers Group - Delphi mailing list
Post: delphi@delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to delphi-requ...@delphi.org.nz with Subject:
unsubscribe
Re: [DUG] Sql query
This issue may also be Database Transaction related. Depending on how your app is structured you need to be aware of which transaction(s) things are running within. For example (and somewhat simplified) the record-add you did may have happened in a different transaction to the one QToDo is assigned to, if the record-add transaction wasn’t committed then the new record would not be visible to other transactions, hence QToDo did not see this record. In this example closing your app may have automatically triggered the commit of the add-record transaction. From: delphi-boun...@delphi.org.nz [mailto:delphi-boun...@delphi.org.nz] On Behalf Of Marshland Engineering Sent: Friday, 14 January 2011 12:50 p.m. To: delphi@delphi.org.nz Subject: [DUG] Sql query I'm viewing a SQL query in a dbgrid. When I add a record to the source db and I run QToDo.Refresh it does not include the new record in the dbgrid. If I close the application and re-run it, the record is displayed. Is refresh the correct property or is there something else. Thanks Wallace___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to delphi-requ...@delphi.org.nz with Subject: unsubscribe
Re: [DUG] Sql query
Try QToDo.Requery. Cheers, Laurence Bevan From: delphi-boun...@delphi.org.nz [mailto:delphi-boun...@delphi.org.nz] On Behalf Of Marshland Engineering Sent: Friday, 14 January 2011 12:50 p.m. To: delphi@delphi.org.nz Subject: [DUG] Sql query I'm viewing a SQL query in a dbgrid. When I add a record to the source db and I run QToDo.Refresh it does not include the new record in the dbgrid. If I close the application and re-run it, the record is displayed. Is refresh the correct property or is there something else. Thanks Wallace ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to delphi-requ...@delphi.org.nz with Subject: unsubscribe
Re: [DUG] Sql query
Call Post Edward Koryagin --- On Fri, 14/1/11, Marshland Engineering wrote: From: Marshland Engineering Subject: [DUG] Sql query To: delphi@delphi.org.nz Received: Friday, 14 January, 2011, 12:49 PM I'm viewing a SQL query in a dbgrid. When I add a record to the source db and I run QToDo.Refresh it does not include the new record in the dbgrid. If I close the application and re-run it, the record is displayed. Is refresh the correct property or is there something else. Thanks Wallace -Inline Attachment Follows- ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to delphi-requ...@delphi.org.nz with Subject: unsubscribe ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to delphi-requ...@delphi.org.nz with Subject: unsubscribe
Re: [DUG] sql query issue
Hey Naven
My problem got solved
i actually missed soemthign in where clause i have added the option called
aprove date in it and it fetched the record in 10 minutes time
Thanks so much for ur time in replying to my mail
Regards
Vik
On 5/8/08, Vikas... <[EMAIL PROTECTED]> wrote:
>
> Hi Naven
>
> thanskf ro the reply
>
> please find the query below
>
>
>
> SELECT top 100 percent convert(varchar,a.approvedate,112) as [date],
> rtrim(a.accountnumber)+replicate(' ',16-len(rtrim(a.accountnumber))) as
> accountnumber,
> dbo.Get_Value(accountname,100) as accountname ,
> convert(varchar, convert(datetime,createdate),112) as AccountOpenDate ,
> dbo.Get_Value(a.comment,1016) as OperatingIns,
> dbo.Get_Value((select acctstatuscode from svs_account..acctstatus c where
> c.acctstatusid=a.statusid),16) as AccountStatus ,
> (rtrim(icnumber1)+replicate (' ',15-len(rtrim(icnumber1 as CUSTID ,
> dbo.Get_Value(signaturename,100) as [CUSTID Name],
> (case when (select count(*) from svs_account..signcardtable s where
> s.accountnumber=b.accountnumber and s.branchnumber=b.branchnumber and
> s.signcardnumber=b.signcardnumber)>=1 then
> 'ENABLE '
> else 'DISABLE ' end)as CUSTIDstatus,
> rtrim(a.imagefilename)+replicate(' ',22-len(rtrim(a.imagefilename)))as
> imagefilename ,icnumber1 as CustomerID,signaturenumber ,
> (rtrim(b.ICNumber1)+'.tiff')+replicate('
> ',22-len(rtrim(b.ICNumber1)+'.tiff')) as SignatureFile ,a.Branchnumber,
> approvedate
> from svs_account..account a , svs_account..signature b
> where a.accountnumber=b.accountnumber and a.branchnumber=b.branchnumber
> and b.icnumber1 not in (
> select imagefile from legacydetailtbl)
>
>
>
>
>
> On 5/8/08, Neven MacEwan <[EMAIL PROTECTED]> wrote:
> >
> > Vikas
> >
> > Can you post the text of the query?
> >
> > Basic Pointers
> >
> > 1/ Add indexes for ther foreign keys (these can allow duplicates)
> > 2/ Restrict the result set (I assume you dont want the query to return
> > 10 million rows)
> > 3/ use ansi join syntax
> >
> > N
> >
> >
> > Hi
> > > I have an query related to an sql sever 200o using delphi 5. Not sure
> > > if this is the right palce to ask it. Still i belive may be soemone can
> > > help
> > > me in this
> > > I am creating a store procedure.
> > > In that i am fetching the information on the basis of join between
> > > two table.
> > > say table 1 and table2. Now thign is at my client place table 1 has
> > > 10 million records and table 2 has 15 million records.
> > > Now i have done a join between these two table depending on
> > > tabl1.x=table2.x and table1.y=table2.y. I have used the primary key in
> > > colum
> > > x of table 1 that emans it automaticaly created a clustered idex. Now
> > > problem is when i execute this query it takes more than hour to run this
> > > query. Any way i can reduce the time of query using any option.i can't use
> > > primary keys on table2 sicne i do have duplicate values.
> > > Regards
> > > Vikas
> > > Solution consultant
> > > NCR coorporation
> > >
> > > --
> > > vikas
> > >
> > >
> > >
> > > ___
> > > NZ Borland Developers Group - Delphi mailing list
> > > Post: delphi@delphi.org.nz
> > > Admin: http://delphi.org.nz/mailman/listinfo/delphi
> > > Unsubscribe: send an email to [EMAIL PROTECTED] with
> > > Subject: unsubscribe
> > >
> >
> > ___
> > NZ Borland Developers Group - Delphi mailing list
> > Post: delphi@delphi.org.nz
> > Admin: http://delphi.org.nz/mailman/listinfo/delphi
> > Unsubscribe: send an email to [EMAIL PROTECTED] with Subject:
> > unsubscribe
> >
>
>
>
> --
> vikas
--
vikas
___
NZ Borland Developers Group - Delphi mailing list
Post: delphi@delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe
Re: [DUG] sql query issue
Hi Naven
thanskf ro the reply
please find the query below
SELECT top 100 percent convert(varchar,a.approvedate,112) as [date],
rtrim(a.accountnumber)+replicate(' ',16-len(rtrim(a.accountnumber))) as
accountnumber,
dbo.Get_Value(accountname,100) as accountname ,
convert(varchar, convert(datetime,createdate),112) as AccountOpenDate ,
dbo.Get_Value(a.comment,1016) as OperatingIns,
dbo.Get_Value((select acctstatuscode from svs_account..acctstatus c where
c.acctstatusid=a.statusid),16) as AccountStatus ,
(rtrim(icnumber1)+replicate (' ',15-len(rtrim(icnumber1 as CUSTID ,
dbo.Get_Value(signaturename,100) as [CUSTID Name],
(case when (select count(*) from svs_account..signcardtable s where
s.accountnumber=b.accountnumber and s.branchnumber=b.branchnumber and
s.signcardnumber=b.signcardnumber)>=1 then
'ENABLE '
else 'DISABLE ' end)as CUSTIDstatus,
rtrim(a.imagefilename)+replicate(' ',22-len(rtrim(a.imagefilename)))as
imagefilename ,icnumber1 as CustomerID,signaturenumber ,
(rtrim(b.ICNumber1)+'.tiff')+replicate('
',22-len(rtrim(b.ICNumber1)+'.tiff')) as SignatureFile ,a.Branchnumber,
approvedate
from svs_account..account a , svs_account..signature b
where a.accountnumber=b.accountnumber and a.branchnumber=b.branchnumber
and b.icnumber1 not in (
select imagefile from legacydetailtbl)
On 5/8/08, Neven MacEwan <[EMAIL PROTECTED]> wrote:
>
> Vikas
>
> Can you post the text of the query?
>
> Basic Pointers
>
> 1/ Add indexes for ther foreign keys (these can allow duplicates)
> 2/ Restrict the result set (I assume you dont want the query to return 10
> million rows)
> 3/ use ansi join syntax
>
> N
>
>
> Hi
> > I have an query related to an sql sever 200o using delphi 5. Not sure
> > if this is the right palce to ask it. Still i belive may be soemone can help
> > me in this
> > I am creating a store procedure.
> > In that i am fetching the information on the basis of join between two
> > table.
> > say table 1 and table2. Now thign is at my client place table 1 has 10
> > million records and table 2 has 15 million records.
> > Now i have done a join between these two table depending on
> > tabl1.x=table2.x and table1.y=table2.y. I have used the primary key in colum
> > x of table 1 that emans it automaticaly created a clustered idex. Now
> > problem is when i execute this query it takes more than hour to run this
> > query. Any way i can reduce the time of query using any option.i can't use
> > primary keys on table2 sicne i do have duplicate values.
> > Regards
> > Vikas
> > Solution consultant
> > NCR coorporation
> >
> > --
> > vikas
> >
> >
> > ___
> > NZ Borland Developers Group - Delphi mailing list
> > Post: delphi@delphi.org.nz
> > Admin: http://delphi.org.nz/mailman/listinfo/delphi
> > Unsubscribe: send an email to [EMAIL PROTECTED] with Subject:
> > unsubscribe
> >
>
> ___
> NZ Borland Developers Group - Delphi mailing list
> Post: delphi@delphi.org.nz
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to [EMAIL PROTECTED] with Subject:
> unsubscribe
>
--
vikas
___
NZ Borland Developers Group - Delphi mailing list
Post: delphi@delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe
RE: [DUG] sql query issue
I think this is in general a sql server question. sql 2000 has a sql enterprise manager/ studio which I can analyze sql query plan which you may check which index you are using. The step what I do is debug a normal sql query in sql query analyzer, once I am happy, then I will put the code in store procedure Have a nice day Regards Leigh www.smootharm.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Neven MacEwan Sent: Thursday, May 08, 2008 6:04 PM To: NZ Borland Developers Group - Delphi List Subject: Re: [DUG] sql query issue Vikas Can you post the text of the query? Basic Pointers 1/ Add indexes for ther foreign keys (these can allow duplicates) 2/ Restrict the result set (I assume you dont want the query to return 10 million rows) 3/ use ansi join syntax N > Hi > > I have an query related to an sql sever 200o using delphi 5. Not sure > if this is the right palce to ask it. Still i belive may be soemone > can help me in this > > I am creating a store procedure. > > In that i am fetching the information on the basis of join between two > table. > > say table 1 and table2. Now thign is at my client place table 1 has 10 > million records and table 2 has 15 million records. > > Now i have done a join between these two table depending on > tabl1.x=table2.x and table1.y=table2.y. I have used the primary key in > colum x of table 1 that emans it automaticaly created a clustered > idex. Now problem is when i execute this query it takes more than hour > to run this query. Any way i can reduce the time of query using any > option.i can't use primary keys on table2 sicne i do have duplicate > values. > > Regards > Vikas > Solution consultant > NCR coorporation > > -- > vikas > > > ___ > NZ Borland Developers Group - Delphi mailing list > Post: delphi@delphi.org.nz > Admin: http://delphi.org.nz/mailman/listinfo/delphi > Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe
Re: [DUG] sql query issue
Vikas Can you post the text of the query? Basic Pointers 1/ Add indexes for ther foreign keys (these can allow duplicates) 2/ Restrict the result set (I assume you dont want the query to return 10 million rows) 3/ use ansi join syntax N Hi I have an query related to an sql sever 200o using delphi 5. Not sure if this is the right palce to ask it. Still i belive may be soemone can help me in this I am creating a store procedure. In that i am fetching the information on the basis of join between two table. say table 1 and table2. Now thign is at my client place table 1 has 10 million records and table 2 has 15 million records. Now i have done a join between these two table depending on tabl1.x=table2.x and table1.y=table2.y. I have used the primary key in colum x of table 1 that emans it automaticaly created a clustered idex. Now problem is when i execute this query it takes more than hour to run this query. Any way i can reduce the time of query using any option.i can't use primary keys on table2 sicne i do have duplicate values. Regards Vikas Solution consultant NCR coorporation -- vikas ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe ___ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe
RE: [DUG]: SQL Query Builder
[Reply] Sergey Orlik's Open QBuilder (OQBuilder) http://www.geocities.com/SiliconValley/Way/9006/index.html Kind Regards Gary At 23:31 on 2/04/2001 you wrote >To : >CC : >From: Neven MacEwan, [EMAIL PROTECTED] >Hi All > >Can anyone suggest a good open source (pref delphi) SQL Query Builder, Sort of Like a >cross between the MS SQL One (visual linking, field picking) without the clumsy >Access type >literal where clause input (and the ability to order) with a column 'browse data' and >paste value ala >Crystal Reports would be good, A template ability and syntax highlighting would be >good too > >TIA > >Neven > Gary Benner - Software Developer [EMAIL PROTECTED] Corporate Software New Zealand Limited Auckland - New Zealand tel: +64-9 846-6067 (24hr) fax: +64-9 846-6152 mob: (021)-966-992 Software System Design - Consulting - Mentoring - Data Modelling Client Server - Delphi - Interbase - Oracle - Web-based Technologies Electronic Automation and Systems - Microcontroller Design & Software http://www.corporate.co.nz Ref#: 41006 --- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz To UnSub, send email to: [EMAIL PROTECTED] with body of "unsubscribe delphi"
RE: [DUG]: SQL Query Builder
Neven, I didn't get around to persevering with either of these and ended up writing my own (mainly because I needed to include stored procedures), but they looked promising at the time last year: http://www.DuckTech.com http://www.korzh.com/ regards, Steve -Original Message- From: Neven MacEwan [mailto:[EMAIL PROTECTED]] Sent: Monday, 2 April 2001 23:27 To: Multiple recipients of list delphi Subject: [DUG]: SQL Query Builder Hi All Can anyone suggest a good open source (pref delphi) SQL Query Builder, Sort of Like a cross between the MS SQL One (visual linking, field picking) without the clumsy Access type literal where clause input (and the ability to order) with a column 'browse data' and paste value ala Crystal Reports would be good, A template ability and syntax highlighting would be good too TIA Neven --- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz To UnSub, send email to: [EMAIL PROTECTED] with body of "unsubscribe delphi"
RE: [DUG]: SQL Query...
Mark, The other bit of my message said that you can't insert back into the same table - because of, I assume, the scenario where you could get an infinite loop. I should have made that more clear. You CAN however insert into a second table - then put THAT back into the first one. I assume you are trying to insert back into the same table, you are selecting from, in one step - it might have to be done programatically - or via a temp table in two steps. Max -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Mark Derricutt Sent: Friday, 19 March 1999 10:26 To: Multiple recipients of list delphi Subject: Re: [DUG]: SQL Query... Max Renshaw-Fox wrote: > a table - you can imagine a scenario where, without a WHERE clause - as >is the case with the SQL below the insert becomes an infinite loop. I just tried adding a WHERE clause but I still get capability not supported, and also an additional error of "table does not exist: errorins.db" ... You may be able to suggest a different solution, I have a series of tables that have lots of data mapped to a configuration, I wish to "duplicate" this data with a new configuration id, so: 0, "fish", "red" 0, "cat", "brown" becomes 0, "fish", "red" 0, "cat", "brown" 1, "fish", "red" 1, "cat", "brown" I was hoping the INSERT INTO and the SELECT would work, but it's not looking good (unless I'm still missing something blindingly obvious). Mark -- Mark Derricutt | Chalice of Blood Software Developer | New Zealand Christian Music News Auckland, New Zealand | http://www.chalice.gen.nz [EMAIL PROTECTED]| UIN: 1934853 --- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz --- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz
Re: [DUG]: SQL Query...
Max Renshaw-Fox wrote: > a table - you can imagine a scenario where, without a WHERE clause - as id > the case with the SQL below the insert becomes an infinite loop. I just tried adding a WHERE clause but I still get capability not supported, and also an additional error of "table does not exist: errorins.db" ... You may be able to suggest a different solution, I have a series of tables that have lots of data mapped to a configuration, I wish to "duplicate" this data with a new configuration id, so: 0, "fish", "red" 0, "cat", "brown" becomes 0, "fish", "red" 0, "cat", "brown" 1, "fish", "red" 1, "cat", "brown" I was hoping the INSERT INTO and the SELECT would work, but it's not looking good (unless I'm still missing something blindingly obvious). Mark -- Mark Derricutt | Chalice of Blood Software Developer | New Zealand Christian Music News Auckland, New Zealand | http://www.chalice.gen.nz [EMAIL PROTECTED]| UIN: 1934853 --- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz
Re: [DUG]: SQL Query...
Max Renshaw-Fox wrote: > a table - you can imagine a scenario where, without a WHERE clause - as id > the case with the SQL below the insert becomes an infinite loop. Eeep - I missed the WHERE :( Doh... knew it must have been something simple. -- Mark Derricutt | Chalice of Blood Software Developer | New Zealand Christian Music News Auckland, New Zealand | http://www.chalice.gen.nz [EMAIL PROTECTED]| UIN: 1934853 --- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz
RE: [DUG]: SQL Query...
The problem is that you are trying to insert into the same table that you are selecting from. CAST works OK in Local SQL but not inserting back into a table - you can imagine a scenario where, without a WHERE clause - as id the case with the SQL below the insert becomes an infinite loop. Max -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Mark Derricutt Sent: Thursday, 18 March 1999 16:34 To: Multiple recipients of list delphi Subject: Re: [DUG]: SQL Query... Mark Derricutt wrote: > Unfortunately I get "Type Mismatch in Expression", I assume this is > possibly due to attempting to hardcode the value of ConfigurationID. If > I run the SELECT portion on its own it works ok, but returns 1 as 1.0 Ok, looking in my "SQL for Dummies" book I found I can get around this using: INSERT INTO CalcMethod ( ConfigurationID, Object, Attribute, Method, Description ) SELECT CAST(1 AS INTEGER), Object, Attribute, Method, Description FROM CalcMethod and casting 1 to an INTEGER. Works fine when I just do the SELECT, so I know LocalSQL/Paradox lets me do CASTing, but when I try the INSERT INTO I now get "Capability not supported". Does LocalSQL not allow me to use INSERT INTO and SELECT in the same query? Mark -- Mark Derricutt | Chalice of Blood Software Developer | New Zealand Christian Music News Auckland, New Zealand | http://www.chalice.gen.nz [EMAIL PROTECTED]| UIN: 1934853 --- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz --- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz
Re: [DUG]: SQL Query...
Mark Derricutt wrote: > Unfortunately I get "Type Mismatch in Expression", I assume this is > possibly due to attempting to hardcode the value of ConfigurationID. If > I run the SELECT portion on its own it works ok, but returns 1 as 1.0 Ok, looking in my "SQL for Dummies" book I found I can get around this using: INSERT INTO CalcMethod ( ConfigurationID, Object, Attribute, Method, Description ) SELECT CAST(1 AS INTEGER), Object, Attribute, Method, Description FROM CalcMethod and casting 1 to an INTEGER. Works fine when I just do the SELECT, so I know LocalSQL/Paradox lets me do CASTing, but when I try the INSERT INTO I now get "Capability not supported". Does LocalSQL not allow me to use INSERT INTO and SELECT in the same query? Mark -- Mark Derricutt | Chalice of Blood Software Developer | New Zealand Christian Music News Auckland, New Zealand | http://www.chalice.gen.nz [EMAIL PROTECTED]| UIN: 1934853 --- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz
Re: [DUG]: SQL Query...
Struan Judd wrote: > INSERT INTO table1 ( field11, field12 ) > SELECT field21, field22 FROM table2 Hi Struan, I just tried the following in database desktop (again a paradox table): INSERT INTO CalcMethod ( ConfigurationID, Object, Attribute, Method, Description ) SELECT 1, Object, Attribute, Method, Description FROM CalcMethod Unfortunately I get "Type Mismatch in Expression", I assume this is possibly due to attempting to hardcode the value of ConfigurationID. If I run the SELECT portion on its own it works ok, but returns 1 as 1.0 which is the wrong type for ConfigurationID which is a straight integer. Any idea how I can force the SELECT to return 1 instead of 1.0? Mark -- Mark Derricutt | Chalice of Blood Software Developer | New Zealand Christian Music News Auckland, New Zealand | http://www.chalice.gen.nz [EMAIL PROTECTED]| UIN: 1934853 --- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz
Re: [DUG]: SQL Query...
The source and destination must have the same schema so if the two queries
are the same then
insert into table select * from query blah blah
will work fine - maybe (depends on db type, autoinc/identity/timestamp cols
etc.)
If not then you need to
insert into table (field list) select matchingfieldlist from query
blah blah
Mark Derricutt <[EMAIL PROTECTED]> on 15/03/99 09:15:11
Please respond to [EMAIL PROTECTED]
To: Multiple recipients of list delphi <[EMAIL PROTECTED]>
cc:(bcc: Peter Jones/Logistics&Information
Technology/Christchurch/Foodstuffs)
Subject: [DUG]: SQL Query...
Is it possible to do an "insert into" query and supply a series of
values from a "select from" query?
I want to do something like:
insert into sometable values {
select * from someothertable where id = 2 }
???
---
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
---
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
RE: [DUG]: SQL Query...
Mark,
Yes.
The Syntax is
INSERT INTO table1
SELECT * FROM table2
This assumes that the two tables have the same number of fields and
compatible types.
If not you need to specify either a field list in the insert, ie:
INSERT INTO table1 ( field1, field2, ... )
SELECT * FROM table2
or a field list in the select statement, ie:
INSERT INTO table1
SELECT field1, field2, ... FROM table2
or even both, ie:
INSERT INTO table1 ( field11, field12 )
SELECT field21, field22 FROM table2
Clear?
TTFN.
Struan Judd <*> EMail: [EMAIL PROTECTED]
CJN Technologies LtdPhone: +64 (9) 415 4796
All opinions mine only Mobile: +64 (21) 685 335
> -Original Message-
> From: Mark Derricutt [mailto:[EMAIL PROTECTED]]
> Sent: Monday, 15 March 1999 10:15
> To: Multiple recipients of list delphi
> Subject: [DUG]: SQL Query...
>
>
> Is it possible to do an "insert into" query and supply a series of
> values from a "select from" query?
>
> I want to do something like:
>
> insert into sometable values {
> select * from someothertable where id = 2 }
>
> ???
> --
> -
> New Zealand Delphi Users group - Delphi List -
> [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
>
---
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
RE: [DUG]: SQL Query...
The syntax you gave is almost correct. You have to make sure that the
query is going to return a matching number of fields (with matching types
for the table). It's a good idea to explicitly name the fields.
e.g.
INSERT INTO MY_TABLE (MY_FIELD1, MY_FIELD2)
SELECT MY_FIELD3, MY_FIELD4
FROM MY_OTHER_TABLE
WHERE MY_FIELD3 = 'x';
You can do this with any query that returns a result set that will be
suitable for the table being loaded. Note: I know this works under Oracle
and Interbase - can't speak for others.
Cheers.
BJ...
--
From: Mark Derricutt[SMTP:[EMAIL PROTECTED]]
Reply To: [EMAIL PROTECTED]
Sent: Monday, 15 March 1999 10:15
To: Multiple recipients of list delphi
Subject:[DUG]: SQL Query...
Is it possible to do an "insert into" query and supply a series of
values from a "select from" query?
I want to do something like:
insert into sometable values {
select * from someothertable where id = 2 }
???
---
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
---
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
