• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

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

0
emi_sastra
Asked:
emi_sastra
  • 10
  • 9
  • 2
1 Solution
 
AllamzCommented:
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
0
 
emi_sastraAuthor Commented:
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.
0
 
AllamzCommented:
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
SharathData EngineerCommented:
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

0
 
emi_sastraAuthor Commented:
Hi Allam,

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

Thank you.

0
 
emi_sastraAuthor Commented:
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.


0
 
SharathData EngineerCommented:
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

0
 
emi_sastraAuthor Commented:
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

0
 
SharathData EngineerCommented:
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
0
 
emi_sastraAuthor Commented:
I am sorry again.

Thank you.
0
 
SharathData EngineerCommented:
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

0
 
SharathData EngineerCommented:
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

0
 
emi_sastraAuthor Commented:
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.
0
 
emi_sastraAuthor Commented:
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

0
 
SharathData EngineerCommented:
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

0
 
SharathData EngineerCommented:
infact this.
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

0
 
emi_sastraAuthor Commented:
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.
0
 
SharathData EngineerCommented:
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

0
 
emi_sastraAuthor Commented:
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

0
 
SharathData EngineerCommented:
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
0
 
emi_sastraAuthor Commented:
I have tried using SSQA.

It is working great using it.

Thank you very much for your help.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 9
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now