asked on

Add Seq Number at Query

Hi,

I have a below query.

How to add Seq No. at first column?

No. BahanCode FullName Qty_PO Qty_SJ Qty_Sisa Satuan

Thank you.

SELECT  A.BahanCode, FullName, SUM(Qty_PO) AS Qty_PO, 
    SUM(Qty_SJ) AS Qty_SJ, SUM(Qty_PO - Qty_SJ) AS Qty_Sisa, 
    A.Satuan
FROM TDPOBELI2008 AS A LEFT JOIN
    TMBARANG AS B ON A.BahanCode = B.BarangCode
WHERE 1 = 1 AND Qty_PO > Qty_SJ AND A.KdSts = ''
GROUP BY B.FullName, BahanCode, A.Satuan
UNION
SELECT A.BahanCode, FullName, SUM(Qty_PO) AS Qty_PO, 
    SUM(Qty_SJ) AS Qty_SJ, SUM(Qty_PO - Qty_SJ) AS Qty_Sisa, 
    A.Satuan
FROM TDPOBELI2009 AS A LEFT JOIN
    TMBARANG AS B ON A.BahanCode = B.BarangCode
WHERE 1 = 1 AND Qty_PO > Qty_SJ AND A.KdSts = ''
GROUP BY B.FullName, BahanCode, A.Satuan

Open in new window

Allamz

Hello Emi,

Please Look At the Following.

http://www.psoug.org/reference/sequences.html
http://www.techonthenet.com/oracle/sequences.php

Hop it helps

Regards,

Mohamed Allam
Senior Software Engineer

emi_sastra

ASKER

Hi Allam,

I am not using oracle.

I have search several link regarding that but no idea how to do it.
Please give solution of the query provided.

Thank you.

Allamz

Emi,

Seq No. is only used in oracle, so when you asked about the sq. i thought you were using Oracle.

So what do you mean by Sequence in SQL Server? do you mean the count of the rows appearing or what?

Regards,

Mohamed Allam
Senior Software Engineer

Sharath S

i think you are looking for something like this.

SELECT  ROW_NUMBER OVER (ORDER BY t1.BahanCode,t1.FullName) AS [No.] 
        t1.BahanCode,t1.FullName,t1.Qty_PO,t1.Qty_SJ,t1.Qty_Sisa,t1.Satuan FROM(
SELECT  A.BahanCode, FullName, SUM(Qty_PO) AS Qty_PO, 
    SUM(Qty_SJ) AS Qty_SJ, SUM(Qty_PO - Qty_SJ) AS Qty_Sisa, 
    A.Satuan
FROM TDPOBELI2008 AS A LEFT JOIN
    TMBARANG AS B ON A.BahanCode = B.BarangCode
WHERE 1 = 1 AND Qty_PO > Qty_SJ AND A.KdSts = ''
GROUP BY B.FullName, BahanCode, A.Satuan
UNION
SELECT A.BahanCode, FullName, SUM(Qty_PO) AS Qty_PO, 
    SUM(Qty_SJ) AS Qty_SJ, SUM(Qty_PO - Qty_SJ) AS Qty_Sisa, 
    A.Satuan
FROM TDPOBELI2009 AS A LEFT JOIN
    TMBARANG AS B ON A.BahanCode = B.BarangCode
WHERE 1 = 1 AND Qty_PO > Qty_SJ AND A.KdSts = ''
GROUP BY B.FullName, BahanCode, A.Satuan) t1

Open in new window

emi_sastra

ASKER

Hi Allam,

do you mean the count of the rows appearing or what?
Yes.

Thank you.

emi_sastra

ASKER

Hi Sharath,

You mean I just add :

SELECT ROW_NUMBER OVER (ORDER BY t1.BahanCode,t1.FullName) AS [No.]
t1.BahanCode,t1.FullName,t1.Qty_PO,t1.Qty_SJ,t1.Qty_Sisa,t1.Satuan FROM(

at the beginning query ?

and change :

GROUP BY B.FullName, BahanCode, A.Satuan to GROUP BY B.FullName, BahanCode, A.Satuan) t1

Thank you.

Sharath S

I missed out Satuan. Just execute that query and check the result. I didn't change your query. I just wrapped it in another SELECT statemement and added ROW_NUMBER.

SELECT  ROW_NUMBER OVER (ORDER BY t1.BahanCode,t1.FullName,Satuan) AS [No.] 
        t1.BahanCode,t1.FullName,t1.Qty_PO,t1.Qty_SJ,t1.Qty_Sisa,t1.Satuan FROM(
SELECT  A.BahanCode, FullName, SUM(Qty_PO) AS Qty_PO, 
    SUM(Qty_SJ) AS Qty_SJ, SUM(Qty_PO - Qty_SJ) AS Qty_Sisa, 
    A.Satuan
FROM TDPOBELI2008 AS A LEFT JOIN
    TMBARANG AS B ON A.BahanCode = B.BarangCode
WHERE 1 = 1 AND Qty_PO > Qty_SJ AND A.KdSts = ''
GROUP BY B.FullName, BahanCode, A.Satuan
UNION
SELECT A.BahanCode, FullName, SUM(Qty_PO) AS Qty_PO, 
    SUM(Qty_SJ) AS Qty_SJ, SUM(Qty_PO - Qty_SJ) AS Qty_Sisa, 
    A.Satuan
FROM TDPOBELI2009 AS A LEFT JOIN
    TMBARANG AS B ON A.BahanCode = B.BarangCode
WHERE 1 = 1 AND Qty_PO > Qty_SJ AND A.KdSts = ''
GROUP BY B.FullName, BahanCode, A.Satuan) t1

Open in new window

emi_sastra

ASKER

Does it work also in MSSQL 7 ?

I am sorry, I mention SQL 2005.

I get error message "Incorrect syntax new keyword "OVER".

Thank you.

SELECT ROW_NUMBER OVER (ORDER BY t1.BahanCode, 
    t1.FullName, A.Satuan) AS [No.] t1.BahanCode, t1.FullName, 
t1.Qty_PO, t1.Qty_SJ, t1.Qty_Sisa, t1.Satuan
FROM (SELECT A.BahanCode, FullName, SUM(Qty_PO) AS Qty_PO, 
          SUM(Qty_SJ) AS Qty_SJ, SUM(Qty_PO - Qty_SJ) 
          AS Qty_Sisa, A.Satuan
      FROM TDPOBELI2008 AS A LEFT JOIN
          TMBARANG AS B ON 
          A.BahanCode = B.BarangCode
      WHERE 1 = 1 AND Qty_PO > Qty_SJ AND 
          A.KdSts = ''
      GROUP BY B.FullName, BahanCode, A.Satuan
      UNION
      SELECT A.BahanCode, FullName, SUM(Qty_PO) AS Qty_PO, 
          SUM(Qty_SJ) AS Qty_SJ, SUM(Qty_PO - Qty_SJ) 
          AS Qty_Sisa, A.Satuan
      FROM TDPOBELI2009 AS A LEFT JOIN
          TMBARANG AS B ON 
          A.BahanCode = B.BarangCode
      WHERE 1 = 1 AND Qty_PO > Qty_SJ AND 
          A.KdSts = ''
      GROUP BY B.FullName, BahanCode, A.Satuan) t1

Open in new window

Sharath S

You mentioned as SQL Server 2005. Thats why i provided this. This won't wotk in MS SQL 7. Let me try to implement this without ROW_NUMBER

emi_sastra

ASKER

I am sorry again.

Thank you.

Sharath S

Try creating a temp table with identity column.

CREATE TABLE #Temp(BahanCode int IDENTITY,FullName varchar(100),Satuan int,Qty_PO int,Qty_SJ int,Qty_Sisa int)
 
INSERT INTO #Temp
SELECT BahanCode,FullName,Satuan,Qty_PO,Qty_SJ,Qty_Sisa FROM(
SELECT A.BahanCode, FullName, SUM(Qty_PO) AS Qty_PO, 
          SUM(Qty_SJ) AS Qty_SJ, SUM(Qty_PO - Qty_SJ) 
          AS Qty_Sisa, A.Satuan
      FROM TDPOBELI2008 AS A LEFT JOIN
          TMBARANG AS B ON 
          A.BahanCode = B.BarangCode
      WHERE 1 = 1 AND Qty_PO > Qty_SJ AND 
          A.KdSts = ''
      GROUP BY B.FullName, BahanCode, A.Satuan
      UNION
      SELECT A.BahanCode, FullName, SUM(Qty_PO) AS Qty_PO, 
          SUM(Qty_SJ) AS Qty_SJ, SUM(Qty_PO - Qty_SJ) 
          AS Qty_Sisa, A.Satuan
      FROM TDPOBELI2009 AS A LEFT JOIN
          TMBARANG AS B ON 
          A.BahanCode = B.BarangCode
      WHERE 1 = 1 AND Qty_PO > Qty_SJ AND 
          A.KdSts = ''
      GROUP BY B.FullName, BahanCode, A.Satuan
) t1 ORDR BY BahanCode,FullName,Satuan
 
 
SELECT * FROM #Temp
DROP TABLE #Temp

Open in new window

Sharath S

ignore my previous post. check this. I assume that your BahanCode is int type.

CREATE TABLE #Temp({No.] int IDENTITY,BahanCode int,FullName varchar(100),Satuan int,Qty_PO int,Qty_SJ int,Qty_Sisa int)
 
INSERT INTO #Temp
SELECT BahanCode,FullName,Satuan,Qty_PO,Qty_SJ,Qty_Sisa FROM(
SELECT A.BahanCode, FullName, SUM(Qty_PO) AS Qty_PO, 
          SUM(Qty_SJ) AS Qty_SJ, SUM(Qty_PO - Qty_SJ) 
          AS Qty_Sisa, A.Satuan
      FROM TDPOBELI2008 AS A LEFT JOIN
          TMBARANG AS B ON 
          A.BahanCode = B.BarangCode
      WHERE 1 = 1 AND Qty_PO > Qty_SJ AND 
          A.KdSts = ''
      GROUP BY B.FullName, BahanCode, A.Satuan
      UNION
      SELECT A.BahanCode, FullName, SUM(Qty_PO) AS Qty_PO, 
          SUM(Qty_SJ) AS Qty_SJ, SUM(Qty_PO - Qty_SJ) 
          AS Qty_Sisa, A.Satuan
      FROM TDPOBELI2009 AS A LEFT JOIN
          TMBARANG AS B ON 
          A.BahanCode = B.BarangCode
      WHERE 1 = 1 AND Qty_PO > Qty_SJ AND 
          A.KdSts = ''
      GROUP BY B.FullName, BahanCode, A.Satuan
) t1 ORDR BY BahanCode,FullName,Satuan
 
 
SELECT * FROM #Temp
DROP TABLE #Temp

Open in new window

emi_sastra

ASKER

I get message :

An explicit value for the identity column in table "#temp" can only be specified when a column list is used and IDENTITY_INSERT is on.

Thank you.

emi_sastra

ASKER

I change the field type.

CREATE TABLE #Temp({ No. ] int IDENTITY, BahanCode Char(30),
FullName varchar(100), Satuan Char(3), Qty_PO Money,
Qty_SJ Money, Qty_Sisa Money)

Get message:

Incorrect syntax near '{'

Thank you.

CREATE TABLE #Temp({ No. ] int IDENTITY, BahanCode Char(30), 
FullName varchar(100), Satuan Char(3), Qty_PO Money, 
Qty_SJ Money, Qty_Sisa Money) INSERT INTO #Temp
                           SELECT BahanCode, FullName, 
                               Satuan, Qty_PO, Qty_SJ, 
                               Qty_Sisa
                           FROM (SELECT A.BahanCode, 
                                     FullName, 
                                     SUM(Qty_PO) 
                                     AS Qty_PO, 
                                     SUM(Qty_SJ) 
                                     AS Qty_SJ, 
                                     SUM(Qty_PO - Qty_SJ) 
                                     AS Qty_Sisa, 
                                     A.Satuan
                                 FROM TDPOBELI2008 AS A LEFT
                                      JOIN
                                     TMBARANG AS B ON 
                                     A.BahanCode = B.BarangCode
                                 WHERE 1 = 1 AND 
                                     Qty_PO > Qty_SJ AND 
                                     A.KdSts = ''
                                 GROUP BY B.FullName, 
                                     BahanCode, 
                                     A.Satuan
                                 UNION
                                 SELECT A.BahanCode, 
                                     FullName, 
                                     SUM(Qty_PO) 
                                     AS Qty_PO, 
                                     SUM(Qty_SJ) 
                                     AS Qty_SJ, 
                                     SUM(Qty_PO - Qty_SJ) 
                                     AS Qty_Sisa, 
                                     A.Satuan
                                 FROM TDPOBELI2009 AS A LEFT
                                      JOIN
                                     TMBARANG AS B ON 
                                     A.BahanCode = B.BarangCode
                                 WHERE 1 = 1 AND 
                                     Qty_PO > Qty_SJ AND 
                                     A.KdSts = ''
                                 GROUP BY B.FullName, 
                                     BahanCode, A.Satuan) 
                               t1 ORDR BY BahanCode, 
                               FullName, Satuan
                                   SELECT *
                                 FROM #Temp DROP TABLE #Temp

Open in new window

Sharath S

can you try this

CREATE TABLE #Temp([No.] int IDENTITY,BahanCode int,FullName varchar(100),Satuan int,Qty_PO int,Qty_SJ int,Qty_Sisa int)
 
INSERT INTO #Temp
SELECT BahanCode,FullName,Satuan,Qty_PO,Qty_SJ,Qty_Sisa FROM(
SELECT A.BahanCode, FullName, SUM(Qty_PO) AS Qty_PO, 
          SUM(Qty_SJ) AS Qty_SJ, SUM(Qty_PO - Qty_SJ) 
          AS Qty_Sisa, A.Satuan
      FROM TDPOBELI2008 AS A LEFT JOIN
          TMBARANG AS B ON 
          A.BahanCode = B.BarangCode
      WHERE 1 = 1 AND Qty_PO > Qty_SJ AND 
          A.KdSts = ''
      GROUP BY B.FullName, BahanCode, A.Satuan
      UNION
      SELECT A.BahanCode, FullName, SUM(Qty_PO) AS Qty_PO, 
          SUM(Qty_SJ) AS Qty_SJ, SUM(Qty_PO - Qty_SJ) 
          AS Qty_Sisa, A.Satuan
      FROM TDPOBELI2009 AS A LEFT JOIN
          TMBARANG AS B ON 
          A.BahanCode = B.BarangCode
      WHERE 1 = 1 AND Qty_PO > Qty_SJ AND 
          A.KdSts = ''
      GROUP BY B.FullName, BahanCode, A.Satuan
) t1 ORDR BY BahanCode,FullName,Satuan
 
 
SELECT * FROM #Temp
DROP TABLE #Temp

Open in new window

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

emi_sastra

ASKER

I get message :

An explicit value for the identity column in table "#temp" can only be specified when a column list is used and IDENTITY_INSERT is on.

What is the difference if the query in run using Enterprise Manager and Query Analyzer?

Thank you.

Sharath S

Don't execute the enitre script. First create the temp table. ( Run the temp table creation script only).
Then run the insert statement.

-- Execute this only
CREATE TABLE #Temp([No.] int IDENTITY,BahanCode Char(30),FullName varchar(100),Satuan Char(3),Qty_PO Money,Qty_SJ Money,Qty_Sisa Money)
 
-- After creating the temp table with above statement,execute this insert statement.
INSERT INTO #Temp
SELECT BahanCode,FullName,Satuan,Qty_PO,Qty_SJ,Qty_Sisa FROM(
SELECT A.BahanCode, FullName, SUM(Qty_PO) AS Qty_PO, 
          SUM(Qty_SJ) AS Qty_SJ, SUM(Qty_PO - Qty_SJ) 
          AS Qty_Sisa, A.Satuan
      FROM TDPOBELI2008 AS A LEFT JOIN
          TMBARANG AS B ON 
          A.BahanCode = B.BarangCode
      WHERE 1 = 1 AND Qty_PO > Qty_SJ AND 
          A.KdSts = ''
      GROUP BY B.FullName, BahanCode, A.Satuan
      UNION
      SELECT A.BahanCode, FullName, SUM(Qty_PO) AS Qty_PO, 
          SUM(Qty_SJ) AS Qty_SJ, SUM(Qty_PO - Qty_SJ) 
          AS Qty_Sisa, A.Satuan
      FROM TDPOBELI2009 AS A LEFT JOIN
          TMBARANG AS B ON 
          A.BahanCode = B.BarangCode
      WHERE 1 = 1 AND Qty_PO > Qty_SJ AND 
          A.KdSts = ''
      GROUP BY B.FullName, BahanCode, A.Satuan
) t1 ORDR BY BahanCode,FullName,Satuan
 
-- This is the SELECT statement from temp table 
SELECT * FROM #Temp
DROP TABLE #Temp

Open in new window

emi_sastra

ASKER

The code works on SSQA but not SSEM.

On SSEM get message :

An explicit value for the identity column in table "#temp" can only be specified when a column list is used and IDENTITY_INSERT is on.

Why is that?

Thank you.

CREATE TABLE #Temp(SEQNO int IDENTITY, BahanCode Char(30), 
FullName char(45), Satuan Char(3), Qty_PO Money, Qty_SJ Money, 
Qty_Sisa Money) INSERT INTO #Temp
              SELECT BahanCode, FullName, Satuan, Qty_PO, 
                  Qty_SJ, Qty_Sisa
              FROM (SELECT A.BahanCode, FullName, 
                        SUM(Qty_PO) AS Qty_PO, 
                        SUM(Qty_SJ) AS Qty_SJ, 
                        SUM(Qty_PO - Qty_SJ) AS Qty_Sisa, 
                        A.Satuan
                    FROM TDPOBELI2008 AS A LEFT JOIN
                        TMBARANG AS B ON 
                        A.BahanCode = B.BarangCode
                    WHERE 1 = 1 AND Qty_PO > Qty_SJ AND 
                        A.KdSts = ''
                    GROUP BY B.FullName, BahanCode, 
                        A.Satuan
                    UNION
                    SELECT A.BahanCode, FullName, 
                        SUM(Qty_PO) AS Qty_PO, 
                        SUM(Qty_SJ) AS Qty_SJ, 
                        SUM(Qty_PO - Qty_SJ) AS Qty_Sisa, 
                        A.Satuan
                    FROM TDPOBELI2009 AS A LEFT JOIN
                        TMBARANG AS B ON 
                        A.BahanCode = B.BarangCode
                    WHERE 1 = 1 AND Qty_PO > Qty_SJ AND 
                        A.KdSts = ''
                    GROUP BY B.FullName, BahanCode, 
                        A.Satuan) t1
              ORDER BY BahanCode, FullName, Satuan
                      SELECT *
                    FROM #Temp DROP TABLE #Temp

Open in new window

Sharath S

so are you not working on SSQA? i dont know the reason for that. Is this an urgent one for you. I am getting late. will check it tomorrow.

Thanks
Sharath

emi_sastra

ASKER

I have tried using SSQA.

It is working great using it.

Thank you very much for your help.