[SQL] sql server to postgreSQL
Hello All, I have a long list of sql server queries that needs to me remoulded in to postgres format can anyone please suggest me any tool that will convert sqlserver query into postgresql query except SwisSQL - SQLOne Console 3.0 Thanks in advance With regards Ashish Karalkar
[SQL] setof or array as input parameter to postgresql 8.2 functions
Hi, I have to pass a set of values and arrays in postgresql 8.2 functions. But I am not getting any help on that. Please let me know if any one has idea. Thanks, Jyoti
Re: [SQL] setof or array as input parameter to postgresql 8.2 functions
Hello maybe: create function foo(varchar[][]) returns void as $$ begin end$$ language plpgsql; postgres=# select foo(array[array[1,2], array[2,2]]::varchar[][]); foo - (1 row) Regards Pavel Stehule 2007/6/13, Jyoti Seth <[EMAIL PROTECTED]>: Hi, I have to pass a set of values and arrays in postgresql 8.2 functions. But I am not getting any help on that. Please let me know if any one has idea. Thanks, Jyoti ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] join problem
I have join problem: "select i.ivid, v.eventdate, v.deposit, v.invdate, cai.db, sum(i.tax) as tax, sum(i.tax + i.rowtot) as totalP, (sum(i.tax + i.rowtot) - v.deposit) as balance from invoice v left outer join invoiceitems i on v.ivid = i.ivid where v.cusid = $cusid and v.cusid = cai.cusid group by i.ivid, v.eventdate, v.deposit, v.invdate, cai.db ERROR: missing FROM-clause entry for table "cai" If I add cai to the from clause "from invoice v, cai, I get ERROR: missing FROM-clause entry for table "cai" ERROR: invalid reference to FROM-clause entry for table "v" Where do I add the cai table reference??? thanks -- Arthur R. Van Hook Mayor - Retired The City of Lake Lotawana [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] (816) 578-4704 - Home (816) 564-0769 - Cell ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] join problem
A. R. Van Hook a écrit : > I have join problem: > "select i.ivid, v.eventdate, v.deposit, v.invdate, cai.db, > sum(i.tax) as tax, > sum(i.tax + i.rowtot) as totalP, > (sum(i.tax + i.rowtot) - v.deposit) as balance > from invoice v > left outer join > invoiceitems i > on v.ivid = i.ivid > where v.cusid = $cusid > and v.cusid = cai.cusidgroup by > i.ivid, v.eventdate, v.deposit, v.invdate, cai.db > ERROR: missing FROM-clause entry for table "cai" > > If I add cai to the from clause "from invoice v, cai, I get > ERROR: missing FROM-clause entry for table "cai" > ERROR: invalid reference to FROM-clause entry for table "v" > > Where do I add the cai table reference??? > You probably need to change the order of the tables in the FROM clause. Replace FROM invoice v, cai with FROM cai, invoice v Regards. -- Guillaume. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] join problem
You can do it like this: select i.ivid, v.eventdate, v.deposit, v.invdate, cai.db, sum(i.tax) as tax, sum(i.tax + i.rowtot) as totalP, (sum(i.tax + i.rowtot) - v.deposit) as balance from invoice v join cai on v.cusid = cai.cusid left outer join invoiceitems i on v.ivid = i.ivid where v.cusid = $cusid group by i.ivid, v.eventdate, v.deposit, v.invdate, cai.db A. Aleš Vojáček FBL Group spol. s r.o. e-mail: [EMAIL PROTECTED] mobil: +420603893335 A. R. Van Hook napsal(a): > I have join problem: > "select i.ivid, v.eventdate, v.deposit, v.invdate, cai.db, > sum(i.tax) as tax, > sum(i.tax + i.rowtot) as totalP, > (sum(i.tax + i.rowtot) - v.deposit) as balance > from invoice v > left outer join > invoiceitems i > on v.ivid = i.ivid > where v.cusid = $cusid > and v.cusid = cai.cusidgroup by > i.ivid, v.eventdate, v.deposit, v.invdate, cai.db > ERROR: missing FROM-clause entry for table "cai" > > If I add cai to the from clause "from invoice v, cai, I get > ERROR: missing FROM-clause entry for table "cai" > ERROR: invalid reference to FROM-clause entry for table "v" > > Where do I add the cai table reference??? > > thanks > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] join problem
On Jun 13, 2007, at 8:19 , A. R. Van Hook wrote: I have join problem: "select i.ivid, v.eventdate, v.deposit, v.invdate, cai.db, sum(i.tax) as tax, sum(i.tax + i.rowtot) as totalP, (sum(i.tax + i.rowtot) - v.deposit) as balance from invoice v left outer join invoiceitems i on v.ivid = i.ivid where v.cusid = $cusid and v.cusid = cai.cusidgroup by i.ivid, v.eventdate, v.deposit, v.invdate, cai.db ERROR: missing FROM-clause entry for table "cai" If I add cai to the from clause "from invoice v, cai, I get ERROR: missing FROM-clause entry for table "cai" ERROR: invalid reference to FROM-clause entry for table "v" I think you may need to change the order of the JOIN clause. Does this work? SELECT i.ivid , v.eventdate , v.deposit , v.invdate , cai.db , sum(i.tax) as tax , sum(i.tax + i.rowtot) as totalP , (sum(i.tax + i.rowtot) - v.deposit) as balance FROM cai JOIN invoice v ON (cai.cusid = v.cusid) LEFT JOIN invoiceitems i ON (v.ivid = i.ivid) WHERE v.cusid = $cusid GROUP BY i.ivid , v.eventdate , v.deposit , v.invdate , cai.db Note I've also moved the cai.cusid = v.cusid into the JOIN condition (which is what it is). Also, if cai doesn't have a ivid column and invoiceitems doesn't have a cusid column, you can use USING (cusid) and USING (ivid) rather than ON (cai.cusid = v.cusid) and ON (v.ivid = i.ivid), which has the nice property of outputing only one join column rather than one column for each table, (i.e., only one cusid column rather than one each for cai and invoice). Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] join problem
There is not referenced table cai in from clausule. A. R. Van Hook napsal(a): > I have join problem: > "select i.ivid, v.eventdate, v.deposit, v.invdate, cai.db, > sum(i.tax) as tax, > sum(i.tax + i.rowtot) as totalP, > (sum(i.tax + i.rowtot) - v.deposit) as balance > from invoice v > left outer join > invoiceitems i > on v.ivid = i.ivid > where v.cusid = $cusid > and v.cusid = cai.cusidgroup by > i.ivid, v.eventdate, v.deposit, v.invdate, cai.db > ERROR: missing FROM-clause entry for table "cai" > > If I add cai to the from clause "from invoice v, cai, I get > ERROR: missing FROM-clause entry for table "cai" > ERROR: invalid reference to FROM-clause entry for table "v" > > Where do I add the cai table reference??? > > thanks > ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] calculate time diffs across rows with single timestamp
First post - please pardon if I'm posted to the wrong group.
I have a table 'statuslog'
type varchar NOT NULL
id varchar NOT NULL
status varchar
datetime timestamp NOT NULL
Example data
type id status datetime
ASSET 001 AAA 2007-06-08 13:42:00.00
ASSET 002 AAA 2007-06-08 13:42:00.00
ASSET 003 AAA 2007-06-08 13:42:00.00
ASSET 001 BBB 2007-06-08 14:42:00.00
ASSET 001 CCC 2007-06-08 14:52:00.00
ASSET 002 BBB 2007-06-08 13:45:00.00
ASSET 001 DDD 2007-06-08 15:00:00.00
Consider this a log of transitional status changes. I now need to
sumarize time-in-status with grouping on type, id, status.
I can't currently modify the schema to include a second timestamp...
I'm not (yet) well versed in temp tables and cursors, but from what I
have researched and the suggestions from helpful coworkers, this seems
the way to go...?
Any suggestions on how I can build a result set that would return
ASSET 001 AAA 1:00 (1 hour)
ASSET 001 BBB 0:10 (10 minutes)
ASSET 001 CCC 0:08 (8 minutes)
ASSET 001 DDD {difference between timestamp and now()}
ASSET 002 AAA 0:03 (3 minutes)
ASSET 002 BBB {difference detween timestamp and now()}
ASSET 003 AAA{diff between timestamp and now()}
(The time diff can be seconds since epoch, some int, or whatever... in
testing I set up the schema using a second timestamp (the 'in' stamp of
the latter record by type/id became the 'out' stamp of the previous
record) and I simply subtracted the in from the out time in a sum() with
grouping.)
Thanks,
Bob
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] calculate time diffs across rows with single timestamp
On Jun 13, 11:17 am, [EMAIL PROTECTED] (Bob Singleton) wrote:
> Any suggestions on how I can build a result set that would return
>
> ASSET 001 AAA 1:00 (1 hour)
> ASSET 001 BBB 0:10 (10 minutes)
> ASSET 001 CCC 0:08 (8 minutes)
> ASSET 001 DDD {difference between timestamp and now()}
> ASSET 002 AAA 0:03 (3 minutes)
> ASSET 002 BBB {difference detween timestamp and now()}
> ASSET 003 AAA{diff between timestamp and now()}
SELECT
TYPE, ID, STATUS
, (COALESCE(
(SELECT MIN(DATETIME) FROM STATUSLOG
WHERE TYPE = S.TYPE AND ID = S.ID AND DATETIME > S.DATETIME)
, NOW()::TIMESTAMP
) - DATETIME) AS DURATION
FROM STATUSLOG S
ORDER BY TYPE, ID, STATUS
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] calculate time diffs across rows with single timestamp
Rodrigo De León wrote:
On Jun 13, 11:17 am, [EMAIL PROTECTED] (Bob Singleton) wrote:
Any suggestions on how I can build a result set that would return
ASSET 001 AAA 1:00 (1 hour)
ASSET 001 BBB 0:10 (10 minutes)
ASSET 001 CCC 0:08 (8 minutes)
ASSET 001 DDD {difference between timestamp and now()}
ASSET 002 AAA 0:03 (3 minutes)
ASSET 002 BBB {difference detween timestamp and now()}
ASSET 003 AAA{diff between timestamp and now()}
SELECT
TYPE, ID, STATUS
, (COALESCE(
(SELECT MIN(DATETIME) FROM STATUSLOG
WHERE TYPE = S.TYPE AND ID = S.ID AND DATETIME > S.DATETIME)
, NOW()::TIMESTAMP
) - DATETIME) AS DURATION
FROM STATUSLOG S
ORDER BY TYPE, ID, STATUS
---(end of broadcast)---
TIP 6: explain analyze is your friend
Awesome - thank you very much! Slightly modified to collapse by TYPE /
ID / STATUS
SELECT
TYPE, ID, STATUS, SUM(
(COALESCE(
(SELECT MIN(DATETIME) FROM STATUSLOG
WHERE TYPE = S.TYPE AND ID = S.ID AND DATETIME > S.DATETIME)
, NOW()::TIMESTAMP
) - DATETIME))
FROM STATUSLOG S
ORDER BY TYPE, ID, STATUS
Thanks for the lesson!
Bob Singleton
Re: [SQL] setof or array as input parameter to postgresql 8.2 functions
Thanks for the solution. With this I am able to pass arrays and multidimensional arrays in postgresql functions. One of my problem is still left I want to pass set of values with different datatypes.For eg: I want to pass following values to the function: 1 ajay 1000.12 2 rita 2300.24 3 leena 1230.78 4 jaya 3432.45 As the values have different data types I have to create three different arrays. Is there any way with which I can pass this as a single setof values. Thanks, Jyoti -Original Message- From: Pavel Stehule [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 13, 2007 6:25 PM To: Jyoti Seth Cc: [EMAIL PROTECTED]; [email protected] Subject: Re: [SQL] setof or array as input parameter to postgresql 8.2 functions Hello maybe: create function foo(varchar[][]) returns void as $$ begin end$$ language plpgsql; postgres=# select foo(array[array[1,2], array[2,2]]::varchar[][]); foo - (1 row) Regards Pavel Stehule 2007/6/13, Jyoti Seth <[EMAIL PROTECTED]>: > > > > > Hi, > > > > I have to pass a set of values and arrays in postgresql 8.2 functions. But I > am not getting any help on that. Please let me know if any one has idea. > > > > Thanks, > > Jyoti ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] setof or array as input parameter to postgresql 8.2 functions
2007/6/14, Jyoti Seth <[EMAIL PROTECTED]>: Thanks for the solution. With this I am able to pass arrays and multidimensional arrays in postgresql functions. One of my problem is still left I want to pass set of values with different datatypes.For eg: I want to pass following values to the function: 1 ajay 1000.12 2 rita 2300.24 3 leena 1230.78 4 jaya 3432.45 As the values have different data types I have to create three different arrays. Is there any way with which I can pass this as a single setof values. You have to wait for 8.3 where arrays on composite types are supported. Currently in one our application we use array of arrays where different types are too, and we cast it to text. Regards Pavel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] setof or array as input parameter to postgresql 8.2 functions
Thanks a lot. Regards, Jyoti -Original Message- From: Pavel Stehule [mailto:[EMAIL PROTECTED] Sent: Thursday, June 14, 2007 11:27 AM To: Jyoti Seth Cc: [EMAIL PROTECTED]; [email protected] Subject: Re: [SQL] setof or array as input parameter to postgresql 8.2 functions 2007/6/14, Jyoti Seth <[EMAIL PROTECTED]>: > Thanks for the solution. With this I am able to pass arrays and > multidimensional arrays in postgresql functions. > > One of my problem is still left I want to pass set of values with different > datatypes.For eg: > I want to pass following values to the function: > 1 ajay 1000.12 > 2 rita 2300.24 > 3 leena 1230.78 > 4 jaya 3432.45 > > As the values have different data types I have to create three different > arrays. Is there any way with which I can pass this as a single setof > values. > > You have to wait for 8.3 where arrays on composite types are supported. Currently in one our application we use array of arrays where different types are too, and we cast it to text. Regards Pavel ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] function to find difference between in days between two dates
Hello all, Is there any function to find differences in days between two dates? I am using select abs(current_date - '2007-06-15') to get the desired result. but I think there must be a function and I am missing it, if so, can anybody please point me to that. Thanks in advance With regards Ashish Karalkar
