Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: calculating variables in sql script

Re: calculating variables in sql script

From: <samir.vds_at_googlemail.com>
Date: 22 Mar 2007 07:15:17 -0700
Message-ID: <1174572917.751479.55710@o5g2000hsb.googlegroups.com>


On 22 Mrz., 14:38, "sybrandb" <sybra..._at_gmail.com> wrote:
> On Mar 22, 2:17 pm, "samir...._at_googlemail.com"
>
>
>
>
>
> <samir...._at_googlemail.com> wrote:
> > On 22 Mrz., 11:58, "hpuxrac" <johnbhur..._at_sbcglobal.net> wrote:
>
> > > On Mar 22, 6:45 am, "samir...._at_googlemail.com"
>
> > > <samir...._at_googlemail.com> wrote:
> > > > Hey guys,
>
> > > > I've this SQL script, which contains 3 insert statements. As you can
> > > > see in the first statement takes data from table MC
> > > > $TA_F_VVL_KMP_200604. In the 2nd statement it takes data from table MC
> > > > $TA_F_VVL_KMP_200605 and in the 3rd one from table MC
> > > > $TA_F_VVL_KMP_200606. So basically it is doing +1, +2 ...
> > > > Is there a way I can automatically calculate that in order to
> > > > automatize the script? SQL scripts doesn't seem to be capable of
> > > > calculating variables. If that is so, is there some other scripting/
> > > > programming language available in which I can accomplish this?
>
> > > > I'd be very grateful for any suggestions,
> > > > regards Samir
>
> > > > Code:
> > > > insert into SARIKAYA.MC$TA_H_VVL_KAMP_MORPU_200604(DWH_VERTRAG_ID,
> > > > QUELLTARIF_ID, ZIELTARIF_ID, CCOS_OFFER_FOLDER_ID, BERICHTSMONAT,
> > > > DATENMONAT, VERTRAGSBEGINN_M, RAHMENVERTRAG, ERLOES_NETTO_EURO,
> > > > VW_KENN, KKM_KAMPAGNE_ID, KKM_ANTWORTTYP_ID)
> > > > SELECT /*+parallel (T1,4)*/T2.DWH_VERTRAG_ID,
> > > > T2.QUELLTARIF_ID,
> > > > T2.ZIELTARIF_ID,
> > > > T2.CCOS_OFFER_FOLDER_ID,
> > > > to_number(to_char(T2.VVL_STICHTAG, 'YYYYMM')),
> > > > T1.Monats_ID,
> > > > T1.VERTRAGSBEGINN_M,
> > > > T1.RAHMENVERTRAG,
> > > > sum(T1.ERLOES_NETTO_CENT)/100,
> > > > T2.VW_KENN,
> > > > T2.KKM_KAMPAGNE_ID,
> > > > T2.KKM_ANTWORTTYP_ID
> > > > FROM DW_HOUSE_SCHEMA.DWH$TA_F_MORPU_VERTRAG T1,
> > > > MC$TA_F_VVL_KMP_200604 T2
> > > > where T1.DWH_VERTRAG_ID = T2.DWH_VERTRAG_ID
> > > > and T1.MONATS_ID = &1
> > > > group by T2.DWH_VERTRAG_ID, T2.QUELLTARIF_ID, T2.ZIELTARIF_ID,
> > > > T2.CCOS_OFFER_FOLDER_ID, to_number(to_char(T2.VVL_STICHTAG,
> > > > 'YYYYMM')), T1.Monats_ID, T1.VERTRAGSBEGINN_M, T1.VO_KENN,
> > > > T1.RAHMENVERTRAG, VW_KENN, KKM_KAMPAGNE_ID, KKM_ANTWORTTYP_ID;
>
> > > > commit;
>
> > > > insert into SARIKAYA.MC$TA_H_VVL_KAMP_MORPU_200605(DWH_VERTRAG_ID,
> > > > QUELLTARIF_ID, ZIELTARIF_ID, CCOS_OFFER_FOLDER_ID, BERICHTSMONAT,
> > > > DATENMONAT, VERTRAGSBEGINN_M, RAHMENVERTRAG, ERLOES_NETTO_EURO,
> > > > VW_KENN, KKM_KAMPAGNE_ID, KKM_ANTWORTTYP_ID)
> > > > SELECT /*+parallel (T1,4)*/T2.DWH_VERTRAG_ID,
> > > > T2.QUELLTARIF_ID,
> > > > T2.ZIELTARIF_ID,
> > > > T2.CCOS_OFFER_FOLDER_ID,
> > > > to_number(to_char(T2.VVL_STICHTAG, 'YYYYMM')),
> > > > T1.Monats_ID,
> > > > T1.VERTRAGSBEGINN_M,
> > > > T1.RAHMENVERTRAG,
> > > > sum(T1.ERLOES_NETTO_CENT)/100,
> > > > T2.VW_KENN,
> > > > T2.KKM_KAMPAGNE_ID,
> > > > T2.KKM_ANTWORTTYP_ID
> > > > FROM DW_HOUSE_SCHEMA.DWH$TA_F_MORPU_VERTRAG T1,
> > > > MC$TA_F_VVL_KMP_200605 T2
> > > > where T1.DWH_VERTRAG_ID = T2.DWH_VERTRAG_ID
> > > > and T1.MONATS_ID = &1
> > > > group by T2.DWH_VERTRAG_ID, T2.QUELLTARIF_ID, T2.ZIELTARIF_ID,
> > > > T2.CCOS_OFFER_FOLDER_ID, to_number(to_char(T2.VVL_STICHTAG,
> > > > 'YYYYMM')), T1.Monats_ID, T1.VERTRAGSBEGINN_M, T1.VO_KENN,
> > > > T1.RAHMENVERTRAG, VW_KENN, KKM_KAMPAGNE_ID, KKM_ANTWORTTYP_ID;
>
> > > > commit;
>
> > > > insert into SARIKAYA.MC$TA_H_VVL_KAMP_MORPU_200606(DWH_VERTRAG_ID,
> > > > QUELLTARIF_ID, ZIELTARIF_ID, CCOS_OFFER_FOLDER_ID, BERICHTSMONAT,
> > > > DATENMONAT, VERTRAGSBEGINN_M, RAHMENVERTRAG, ERLOES_NETTO_EURO,
> > > > VW_KENN, KKM_KAMPAGNE_ID, KKM_ANTWORTTYP_ID)
> > > > SELECT /*+parallel (T1,4)*/T2.DWH_VERTRAG_ID,
> > > > T2.QUELLTARIF_ID,
> > > > T2.ZIELTARIF_ID,
> > > > T2.CCOS_OFFER_FOLDER_ID,
> > > > to_number(to_char(T2.VVL_STICHTAG, 'YYYYMM')),
> > > > T1.Monats_ID,
> > > > T1.VERTRAGSBEGINN_M,
> > > > T1.RAHMENVERTRAG,
> > > > sum(T1.ERLOES_NETTO_CENT)/100,
> > > > T2.VW_KENN,
> > > > T2.KKM_KAMPAGNE_ID,
> > > > T2.KKM_ANTWORTTYP_ID
> > > > FROM DW_HOUSE_SCHEMA.DWH$TA_F_MORPU_VERTRAG T1,
> > > > MC$TA_F_VVL_KMP_200606 T2
> > > > where T1.DWH_VERTRAG_ID = T2.DWH_VERTRAG_ID
> > > > and T1.MONATS_ID = &1
> > > > group by T2.DWH_VERTRAG_ID, T2.QUELLTARIF_ID, T2.ZIELTARIF_ID,
> > > > T2.CCOS_OFFER_FOLDER_ID, to_number(to_char(T2.VVL_STICHTAG,
> > > > 'YYYYMM')), T1.Monats_ID, T1.VERTRAGSBEGINN_M, T1.VO_KENN,
> > > > T1.RAHMENVERTRAG, VW_KENN, KKM_KAMPAGNE_ID, KKM_ANTWORTTYP_ID;
>
> > > > commit;
>
> > > You can probably use plsql instead of straight sql.
>
> > > begin
> > > /* this is now in a plsql block*/
> > > null;
> > > end;
>
> > > There's a bunch of examples of plsql scripts all over the web. Plus
> > > the oracle documentation athttp://tahiti.oracle.com-ZitiertenText ausblenden -
>
> > > - Zitierten Text anzeigen -
>
> > Ok, Pl/SQL is a propertary language, which I don't like so very much.
> > Are there any useable alternatives?- Hide quoted text -
>
> > - Show quoted text -
>
> You are showing a SQL script. Has nothing to do with PL/SQL.
> If you don't like Sql*plus, dump Oracle and replace it by a toy like
> MySQL.
>
> --
> Sybrand Bakker
> Senior Oracle DBA- Zitierten Text ausblenden -
>
> - Zitierten Text anzeigen -

I know my script has nothing to do with PL/SQL (it's plain SQL). However, Hpuxrac suggested using PL/SQL for my intention and that's what I was reffering to ... Received on Thu Mar 22 2007 - 09:15:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US