Serial No in SAP business One 8.82

[Former Member]

I have a table GRN No

GRN no

10

12

13

14

15

Expected output:

Sl no         GRN No

1          10

2          12

3          13

4          14

5          15

Pls help we need query for Serial no in SAP business One

[Former Member]

Hi,

Tks for your feedback.

But it is not working.It shows error that function identity can only be used when the select statement has an  INTO clause.

[clas_hortien]

Ok, so you have to use the ROW_NUMBER() OVER() function:

SELECT

    ROW_NUMBER() OVER(ORDER BY T0.DocEntry) as Serial,

    T1.seriesName,

    T0.DocNum,

    T0.[DocDate],

    T0.[CardCode] AS 'Coust Code',

    T0.CardName,

    T0.[CAShSum],

    T0.[CheckSum],

    T0.[TrsfrSum] AS 'RTGS Amount',

    T0.[DocTotal] AS 'Doc Total',

    T0.[PrjCode]

FROM ORCT T0 INNER JOIN NNM1 T1 ON    T0.Series = T1.Series

WHERE

        T0.[DocType]='C'

    AND    T0.[Canceled] ='N'

    AND    convert(varchar(12),T0.[DocDate],103)=convert(varchar(12),dateadd(DD,-1,getdate()),103)

ORDER BY

T0.[DocEntry]

Regards

Clas

[Former Member]

Dear Friend,

Tks for your support.But Some error coming.

[clas_hortien]

Hi,

what kind of error happens ? Can you send the complete error message to me ?

Regards

Clas

[clas_hortien]

Hi,

if there is a dependency between SINo and GRN No you can write something like:

select

[GRN No] - 9  as [SI No],

[GRN No]

from

Table....

If you need only a counter you can use

select

  IDENTITY(int, 1, 1) as [Si No],

  [GRN No]

From

  Table...

Regards

Clas

[Former Member]

Dear Friends,

Pls see the below query,i want serial no (like 1,2,3....) and row wise sum details..pls help

SELECT  T1.seriesName,T0.DocNum, T0.[DocDate], T0.[CardCode] AS 'Coust Code',T0.CardName, T0.[CashSum] ,T0.[CheckSum] ,T0.[TrsfrSum] as 'RTGS Amount', T0.[DocTotal] AS 'Doc Total', T0.[PrjCode] FROM ORCT T0  INNER JOIN NNM1 T1 ON T0.Series = T1.Series  WHERE  T0.[DocType]='C' and  T0.[Canceled] ='N' AND    convert(varchar(12),T0.[DocDate],103)=convert(varchar(12),dateadd(DD,-1,getdate()),103)  ORDER BY T0.[DocEntry]

[clas_hortien]

Hi,

as I asked in the other thread already: What do you mean with 'row wise sums' ?

See references to GROUP BY in your other thread.

For the Serial, you can add:

SELECT

    IDENTITY(INT,1,1) as Serial,

    T1.seriesName,

    T0.DocNum,

    T0.[DocDate],

    T0.[CardCode] AS 'Coust Code',

    T0.CardName,

    T0.[CAShSum],

    T0.[CheckSum],

    T0.[TrsfrSum] AS 'RTGS Amount',

    T0.[DocTotal] AS 'Doc Total',

    T0.[PrjCode]

FROM ORCT T0 INNER JOIN NNM1 T1 ON    T0.Series = T1.Series

WHERE

        T0.[DocType]='C'

    AND    T0.[Canceled] ='N'

    AND    convert(varchar(12),T0.[DocDate],103)=convert(varchar(12),dateadd(DD,-1,getdate()),103)

ORDER BY

T0.[DocEntry]

Regards

Clas