Re: Language problem

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Andrew John (aj_at_DELETEmistrose.com)
Date: 03/16/04 

Date: Tue, 16 Mar 2004 21:13:35 +1100

Simon,

It would help if you posted table structures and sample data in DDL
( query analyser runnable form ). "Wrong results" doesn't help much.

The layout of your code is so difficult to read that troubleshooting it
without the above is too hard.

Also are you sure that is the correct code for the sp. I don't
set @datum declared anywhere.

Here is ( IMHO ) a more readable version if someone else wants to have a go:

CREATE PROCEDURE e_olap_prenosPodatkov
(
  @datum datetime
)
AS

DECLARE @danZ int, @mesecZ int, @letoZ int,@danK int, @mesecK int, @letoK
int,@danM int, @mesecM int, @letoM int,@danS int, @mesecS int, @letoS int
DECLARE @dt DATETIME,@datumMesec dateTime,@datumTedenZ datetime,@datumTedenK
datetime

set @datumMesec=DATEADD(m, -1,@datum)
set @datumTedenZ=DATEADD(dd,-1*DATEPART(dw, @datum)-6,@datum)
set @datumTedenK=DATEADD(dd, -1*DATEPART(dw, @datum) +1,@datum)

set @danM=day(@datumMesec)
set @mesecM=month(@datumMesec)
set @letoM=year(@datumMesec)
set @danS=day(@datum)
set @mesecS=month(@datum)
set @letoS=year(@datum)
set @danZ=day(@datumTedenZ)
set @mesecZ=month(@datumTedenZ)
set @letoZ=year(@datumTedenZ)
set @danK=day(@datumTedenK)
set @mesecK=month(@datumTedenK)
set @letoK=year(@datumTedenK)

SET @dt = CONVERT(VARCHAR,@datum,112)

INSERT INTO [simonZ].[eNarocanje].[dbo].[zalogeOlap]
(
  zo_drzava_id,
  zo_izd_id,
  zo_datum,
  zo_zaloga_malo,
  zo_pr_mesec,
  zo_pr_teden
)
SELECT
  Tz.RCO,
  Tz.ID,
  @datum,
  Tz.zaloga ,
  COALESCE(Tm.monthQuantity,0)AS mesec,
  COALESCE(Tw.weekQuantity,0)AS teden
 FROM
  ( SELECT
       a.ID,
       a.RCO,
       SUM(kolicina)AS zaloga
      FROM
       ( select
            i.[ENOTNA_SIFRA]as ID,
            s.RCO,s.stanje as kolicina
          FROM [olap-server].[DW_Temp].[dbo].[t_stanje_cube] s
         INNER JOIN [olap-server].[DW_Temp].[dbo].[s_izdelek] i
            ON s.ID=i.[ID] AND s.RCO=i.RCO
         WHERE s.datum >= @dt AND s.datum < DATEADD(DAY,1,@dt)
           AND s.odpis
               in ( SELECT ol.idSkladisca
                       FROM [simonZ].[eNarocanje].[dbo].[olapSkladisce] ol
                      WHERE ol.idDrzave=s.RCO
                        AND ol.namenSkladisca=1
                  )
         UNION ALL
         SELECT
            i.[ENOTNA_SIFRA] as ID,
            b.rco,-b.quantity as kolicina
          FROM [olap-server].[DW_Temp].[dbo].[t_backorder_cube] b
         INNER JOIN [olap-server].[DW_Temp].[dbo].[s_izdelek] i
            ON b.[id_izdelka]=i.[ID]
           AND b.RCO=i.RCO
         WHERE b.day=@danS
           AND b.month=@mesecS
           AND b.year=@letoS
       ) as a GROUP BY a.ID,a.rco)as Tz
    LEFT JOIN
    ( SELECT
          c.rco,
          i.[ENOTNA_SIFRA] as ID,
          sum(c.quantity) as monthQuantity
        from [olap-server].[DW_Temp].[dbo].[t_post_narocilnice_cube] c
       INNER JOIN [olap-server].[DW_Temp].[dbo].[s_izdelek] i
          ON c.id_izdelka=i.[ID]
         AND c.RCO=i.RCO
       WHERE ( ( c.day >=@danM
                   AND c.month = @mesecM
                   AND c.year=@letoM
                 )
              OR ( c.day <= @danS
                   AND c.month = @mesecS
                   AND c.year=@letoS
                )
             )
       group by c.rco, i.[ENOTNA_SIFRA]
    )as Tm
    ON Tm.rco=Tz.Rco
   and Tm.ID=Tz.ID
  LEFT JOIN
  ( SELECT
        c1.rco,
        i.[ENOTNA_SIFRA]as ID,
        sum(c1.quantity)as weekQuantity
     from [olap-server].[DW_Temp].[dbo].[t_post_narocilnice_cube]c1
    INNER JOIN [olap-server].[DW_Temp].[dbo].[s_izdelek] i
       ON c1.id_izdelka=i.[ID]
      AND c1.RCO=i.RCO
    WHERE c1.day>@danZ
      AND c1.day<=@danK
      AND c1.month = @mesecZ
      AND c1.year=@letoZ
    group by c1.rco,i.[ENOTNA_SIFRA]
  ) as Tw
  ON Tw.rco=tz.Rco and Tw.ID=Tz.ID

Regards
 AJ

"simon" <simon.zupan@stud-moderna.si> wrote in message news:e7GOdEzCEHA.4080@TK2MSFTNGP09.phx.gbl...
> I have collation setting of my database: Slovenian_cl_as
>
> Then in application I have settings of my connection:
>
> Locale Identifier=1060 ;Current Language=Slovenian;
>
> So, everything is set to Slovenian.
>
> And everything works fine just in one example when I call procedure
> e_olap_prenosPodatkov
> I get the wrong results.
> In this procedure I use a lot of date functions.
> If I change connection settings to general, than this procedure works, but
> everything else doesn't because all other functions are set to slovenian.
> What can I do?
>
> Thank you,
> Simon
>
> This is my SP which doesn't work for slovenian settings:
>
> CREATE PROCEDURE e_olap_prenosPodatkov
> AS
>
> DECLARE @danZ int, @mesecZ int, @letoZ int,@danK int, @mesecK int, @letoK
> int,@danM int, @mesecM int, @letoM int,@danS int, @mesecS int, @letoS int
> DECLARE @dt DATETIME,@datumMesec dateTime,@datumTedenZ datetime,@datumTedenK
> datetime
>
> set @datumMesec=DATEADD(m, -1,@datum)
> set @datumTedenZ=DATEADD(dd,-1*DATEPART(dw, @datum)-6,@datum)
> set @datumTedenK=DATEADD(dd, -1*DATEPART(dw, @datum) +1,@datum)
>
> set @danM=day(@datumMesec)
> set @mesecM=month(@datumMesec)
> set @letoM=year(@datumMesec)
> set @danS=day(@datum)
> set @mesecS=month(@datum)
> set @letoS=year(@datum)
> set @danZ=day(@datumTedenZ)
> set @mesecZ=month(@datumTedenZ)
> set @letoZ=year(@datumTedenZ)
> set @danK=day(@datumTedenK)
> set @mesecK=month(@datumTedenK)
> set @letoK=year(@datumTedenK)
>
> SET @dt = CONVERT(VARCHAR,@datum,112)
>
> INSERT INTO [simonZ].[eNarocanje].[dbo].[zalogeOlap]
> (zo_drzava_id,zo_izd_id,zo_datum,zo_zaloga_malo,zo_pr_mesec,zo_pr_teden)
> SELECT Tz.RCO,Tz.ID,@datum,Tz.zaloga ,COALESCE(Tm.monthQuantity,0)AS
> mesec,COALESCE(Tw.weekQuantity,0)AS teden FROM
> (SELECT a.ID, a.RCO, SUM(kolicina)AS zaloga
> FROM
> (select i.[ENOTNA_SIFRA]as ID,s.RCO,s.stanje as kolicina FROM
> [olap-server].[DW_Temp].[dbo].[t_stanje_cube] s INNER JOIN
> [olap-server].[DW_Temp].[dbo].[s_izdelek] i ON s.ID=i.[ID] AND s.RCO=i.RCO
> WHERE s.datum >= @dt AND s.datum < DATEADD(DAY,1,@dt)
> AND s.odpis in(SELECT ol.idSkladisca FROM
> [simonZ].[eNarocanje].[dbo].[olapSkladisce] ol WHERE ol.idDrzave=s.RCO AND
> ol.namenSkladisca=1)
> UNION ALL
> SELECT i.[ENOTNA_SIFRA] as ID,b.rco,-b.quantity as kolicina FROM
> [olap-server].[DW_Temp].[dbo].[t_backorder_cube] b INNER JOIN
> [olap-server].[DW_Temp].[dbo].[s_izdelek] i ON b.[id_izdelka]=i.[ID] AND
> b.RCO=i.RCO
> WHERE b.day=@danS AND b.month=@mesecS AND b.year=@letoS
> ) as a GROUP BY a.ID,a.rco)as Tz
> LEFT JOIN
> (SELECT c.rco,i.[ENOTNA_SIFRA]as ID,sum(c.quantity)as monthQuantity from
> [olap-server].[DW_Temp].[dbo].[t_post_narocilnice_cube] c
> INNER JOIN [olap-server].[DW_Temp].[dbo].[s_izdelek] i ON
> c.id_izdelka=i.[ID] AND c.RCO=i.RCO
> WHERE ((c.day >=@danM AND c.month = @mesecM AND c.year=@letoM)
> OR (c.day<=@danS AND c.month=@mesecS AND c.year=@letoS))
> group by c.rco,i.[ENOTNA_SIFRA])as Tm
> ON Tm.rco=Tz.Rco and Tm.ID=Tz.ID
> LEFT JOIN
> (SELECT c1.rco,i.[ENOTNA_SIFRA]as ID,sum(c1.quantity)as weekQuantity from
> [olap-server].[DW_Temp].[dbo].[t_post_narocilnice_cube]c1
> INNER JOIN [olap-server].[DW_Temp].[dbo].[s_izdelek] i ON
> c1.id_izdelka=i.[ID] AND c1.RCO=i.RCO
> WHERE c1.day>@danZ AND c1.day<=@danK AND c1.month = @mesecZ AND
> c1.year=@letoZ
> group by c1.rco,i.[ENOTNA_SIFRA]) as Tw
> ON Tw.rco=tz.Rco and Tw.ID=Tz.ID
>
>
>

Relevant Pages

  • Re: EXECUTING SP
    ... Even all my settings are set to Slovenian? ... that could be a reason. ...
    (microsoft.public.sqlserver.programming)
  • Re: Spalten alternativ ansprechen
    ... DECLARE @Monat INT ... '01' AS DATETIME) ... case when DATUM between '...' ... then UMSATZ as ...
    (microsoft.public.de.access.clientserver)
  • Re: a view based on stored procedure
    ... DECLARE @Week_1 DATETIME ... DECLARE @CubeTime DATETIME ... INSERT INTO WrkAvailPlanHours ...
    (microsoft.public.sqlserver.programming)
  • RE: User Defined Function Help
    ... @startDateTime datetime = null, ... DECLARE @selectFromList varchar ... SET @subservice = RTRIM ... EXEC ...
    (microsoft.public.sqlserver.programming)
  • Re: Recordset or Object is closed HELP PLEASE!
    ... DECLARE @IntYearBeforeLast INT ... DECLARE @DateSepFirstYearBeforeLast DATETIME ...
    (microsoft.public.excel.programming)