Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 0D0114758FE; Tue, 17 Sep 2002 05:07:27 -0400 (EDT) Received: from salem.vale-housing.co.uk (mailgate.vale-housing.co.uk [193.195.77.162]) by postgresql.org (Postfix) with ESMTP id 3881C4758E6; Tue, 17 Sep 2002 05:07:24 -0400 (EDT) Content-Class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C25E29.A237DE44" Subject: Re: [pgadmin-support] pgadtransport Procedural and Function from MS SQL Enterprise Manage to PostgreSQL X-MimeOLE: Produced By Microsoft Exchange V6.0.6249.0 Date: Tue, 17 Sep 2002 10:07:22 +0100 Message-ID: <03AF4E498C591348A42FC93DEA9661B86825@mail.vale-housing.co.uk> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [pgadmin-support] pgadtransport Procedural and Function from MS SQL Enterprise Manage to PostgreSQL Thread-Index: AcJeKNa9+J+eSS+TQuWnwES7xJEz3AAAKhqA From: "Dave Page" To: "XIE, Rong" , Cc: , "pgadmin-hackers" X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200209/17 X-Sequence-Number: 672 This is a multi-part message in MIME format. ------_=_NextPart_001_01C25E29.A237DE44 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable You will have to port your functions and procedures manually I'm afraid. =20 Regards, Dave. -----Original Message----- From: XIE, Rong [mailto:rong.xie@stud.tu-muenchen.de]=20 Sent: 17 September 2002 09:59 To: pgsql-docs@postgresql.org Subject: [pgadmin-support] transport Procedural and Function from MS SQL E= nterprise Manage to PostgreSQL=20 =09 =09 hallo, I have PostgreSQL in Linux. I use it with pgAdminII in Wondows2000. how can I transport Procedural and Function from MS SQL Enterprise Manage = to PostgreSQL. Thanks!!! rong =09 =09 CREATE FUNCTION dbo.getICNroh (@Bogen_Nr smallint) RETURNS smallint AS BEGIN DECLARE @myRet int --Rohwert der Skala 1 (ICN) ermitteln DECLARE @step01a smallint DECLARE @step02a smallint =09 DECLARE @step01b smallint DECLARE @step02b smallint =09 SELECT @step01a =3D Antwort FROM PAI_ANTWORTEN WHERE ITEM_NR =3D 75 AND Bogen_Nr =3D @Bogen_Nr SELECT @step01b =3D Antwort FROM PAI_ANTWORTEN WHERE ITEM_NR =3D 115 A= ND Bogen_Nr =3D @Bogen_Nr =09 SELECT @step02a =3D Antwort FROM PAI_ANTWORTEN WHERE ITEM_NR =3D 4 AND Bogen_Nr =3D @Bogen_Nr SELECT @step02b =3D Antwort FROM PAI_ANTWORTEN WHERE ITEM_NR =3D 44 AND Bogen_Nr =3D @Bogen_Nr =09 =09 SELECT @myRet =3D ABS(@Step01a - @step01b) + ABS(@Step02a - @step02b) =09 --Wert zur=FCckgeben RETURN @myRet END =09 =09 =09 =09 =09 =09 CREATE PROCEDURE sp_PAI_Werte @Bogen_Nr int AS =09 =09 create table #tmpPAI_Rohwerte (Skala_Nr smallint primary key, Rohwert smallint) =09 --Rohwerte ermitteln (au=DFer Skala 1 ICN) INSERT into #tmpPAI_Rohwerte SELECT dbo.PAI_SKALEN.Skala_Nr, sum((CONVERT(tinyint, dbo.PAI_ANTWORTEN.Antwort) + dbo.PAI_KEYcalc.toAdd) * dbo.PAI_KEYcalc.Faktor) AS Rohwert FROM dbo.PAI_SKALEN INNER JOIN dbo.PAI_ITEMinSKALA ON dbo.PAI_SKALEN.Skala_Nr =3D dbo.PAI_ITEMinSKALA.Skala_Nr INNER JOIN dbo.PAI_ITEMS ON dbo.PAI_ITEMinSKALA.Item_Nr =3D dbo.PAI_ITEMS.Item_Nr INNER JOIN dbo.PAI_ANTWORTEN ON dbo.PAI_ITEMS.Item_Nr =3D dbo.PAI_ANTWORTEN.Item_Nr INNER JOIN dbo.PAI_KEYcalc ON dbo.PAI_ITEMinSKALA.keyDir =3D dbo.PAI_KEYcalc.keyDir WHERE (dbo.PAI_ANTWORTEN.Antwort <> ' ') and PAI_Antworten.Bogen_Nr =3D @Bogen_Nr GROUP BY dbo.PAI_SKALEN.Skala_Nr =09 --ICN Rohwert einf=FCgen IF EXISTS(SELECT * from #tmpPAI_Rohwerte WHERE Skala_Nr =3D 1) UPDATE #tmpPAI_Rohwerte SET Rohwert =3D dbo.getICNroh(@Bogen_Nr) WHERE Skala_Nr =3D 1 ELSE INSERT INTO #tmpPAI_Rohwerte (Skala_Nr, Rohwert) VALUES(1,dbo.getICNroh(@Bogen_Nr)) =09 --T-Werte bereitstellen SELECT dbo.PAI_SKALEN.Skala_Nr, dbo.PAI_SKALEN.Kuerzel_D AS Kuerzel, dbo.PAI_SKALEN.Label_D AS Label, #tmpPAI_Rohwerte.rohwert as rohwert, ROUND(50 + (#tmpPAI_Rohwerte.rohwert - dbo.PAI_SKALEN.M) *10 / dbo.PAI_SKALEN.SD, 0) AS TWert FROM dbo.PAI_SKALEN LEFT OUTER JOIN #tmpPAI_Rohwerte ON dbo.PAI_SKALEN.Skala_Nr =3D #tmpPAI_Rohwerte.skala_nr GO =09 =09 =09 ------_=_NextPart_001_01C25E29.A237DE44 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Message
You=20 will have to port your functions and procedures manually I'm=20 afraid.
 
Regards, Dave.
-----Original Message-----
From: XIE, Ro= ng=20 [mailto:rong.xie@stud.tu-muenchen.de]
Sent: 17 September 2002= =20 09:59
To: pgsql-docs@postgresql.org
Subject:=20 [pgadmin-support] transport Procedural and Function from MS SQL Enterpris= e=20 Manage to PostgreSQL

hallo,

I have PostgreSQL in Linux. I use it wit= h=20 pgAdminII in Wondows2000.
how can I transport Procedural and Function = from=20 MS SQL Enterprise Manage=20 to
PostgreSQL.
Thanks!!!
rong


CREATE FUNCTION=20 dbo.getICNroh (@Bogen_Nr smallint)
RETURNS smallint=20 AS
BEGIN
    DECLARE @myRet int
--Rohwert der Ska= la 1=20 (ICN) ermitteln
    DECLARE @step01a=20 smallint
    DECLARE @step02a=20 smallint

    DECLARE @step01b=20 smallint
    DECLARE @step02b=20 smallint

    SELECT @step01a =3D Antwort FROM=20 PAI_ANTWORTEN WHERE ITEM_NR =3D 75 AND
Bogen_Nr =3D=20 @Bogen_Nr
    SELECT @step01b =3D Antwort FROM PAI_ANTW= ORTEN=20 WHERE ITEM_NR =3D 115 AND
Bogen_Nr =3D @Bogen_Nr

  &n= bsp;=20 SELECT @step02a =3D Antwort FROM PAI_ANTWORTEN WHERE ITEM_NR =3D 4 ANDBogen_Nr=20 =3D @Bogen_Nr
    SELECT @step02b =3D Antwort FROM PAI_= ANTWORTEN=20 WHERE ITEM_NR =3D 44 AND
Bogen_Nr =3D @Bogen_Nr


  = ; =20 SELECT @myRet =3D ABS(@Step01a -=20 @step01b)
          =    =20 + ABS(@Step02a - @step02b)

--Wert zur=FCckgeben
  &nb= sp;=20 RETURN @myRet
END






CREATE PROCEDURE=20 sp_PAI_Werte
    @Bogen_Nr int
 AS


c= reate=20 table #tmpPAI_Rohwerte (Skala_Nr smallint primary key,=20 Rohwert
smallint)

--Rohwerte ermitteln (au=DFer Skala 1 ICN)INSERT=20 into #tmpPAI_Rohwerte
SELECT dbo.PAI_SKALEN.Skala_Nr,=20 sum((CONVERT(tinyint,
        =             &nb= sp;=20 dbo.PAI_ANTWORTEN.Antwort) + dbo.PAI_KEYcalc.toAdd)=20 *
dbo.PAI_KEYcalc.Faktor) AS Rohwert
FROM dbo.PAI_SKALEN INNER=20 JOIN
           = ;          =20 dbo.PAI_ITEMinSKALA ON dbo.PAI_SKALEN.Skala_Nr=20 =3D
dbo.PAI_ITEMinSKALA.Skala_Nr INNER=20 JOIN
           = ;          =20 dbo.PAI_ITEMS ON dbo.PAI_ITEMinSKALA.Item_Nr =3D
dbo.PAI_ITEMS.Item_Nr= INNER=20 JOIN
           = ;          =20 dbo.PAI_ANTWORTEN ON dbo.PAI_ITEMS.Item_Nr =3D
dbo.PAI_ANTWORTEN.Item_= Nr=20 INNER=20 JOIN
           = ;          =20 dbo.PAI_KEYcalc ON dbo.PAI_ITEMinSKALA.keyDir=20 =3D
dbo.PAI_KEYcalc.keyDir
WHERE (dbo.PAI_ANTWORTEN.Antwort <>= ; ' ')=20 and PAI_Antworten.Bogen_Nr =3D
@Bogen_Nr
GROUP BY=20 dbo.PAI_SKALEN.Skala_Nr

--ICN Rohwert einf=FCgen
IF EXISTS(SELE= CT *=20 from #tmpPAI_Rohwerte WHERE Skala_Nr =3D 1)
    UPDATE= =20 #tmpPAI_Rohwerte SET Rohwert =3D dbo.getICNroh(@Bogen_Nr) WHERE
Skala_= Nr =3D=20 1
ELSE
    INSERT INTO #tmpPAI_Rohwerte (Skala_Nr,= =20 Rohwert)
VALUES(1,dbo.getICNroh(@Bogen_Nr))

--T-Werte=20 bereitstellen
SELECT dbo.PAI_SKALEN.Skala_Nr, dbo.PAI_SKALEN.Kuerzel_D= AS=20 Kuerzel,
dbo.PAI_SKALEN.Label_D AS Label, #tmpPAI_Rohwerte.rohwert as= =20 rohwert,
          &= nbsp;          =20 ROUND(50 + (#tmpPAI_Rohwerte.rohwert -
dbo.PAI_SKALEN.M) *10 /=20 dbo.PAI_SKALEN.SD, 0) AS TWert
FROM dbo.PAI_SKALEN LEFT OUTER=20 JOIN
           = ;          =20 #tmpPAI_Rohwerte ON dbo.PAI_SKALEN.Skala_Nr=20 =3D
#tmpPAI_Rohwerte.skala_nr
GO


=00 ------_=_NextPart_001_01C25E29.A237DE44--