Solved

# Add Seq Number at Query

Posted on 2009-02-08
385 Views
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
``````
0
Question by:emi_sastra

LVL 6

Expert Comment

Hello Emi,

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

Hop it helps

Regards,

Mohamed Allam
Senior Software Engineer
0

LVL 1

Author Comment

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

LVL 6

Expert Comment

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

LVL 40

Expert Comment

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
``````
0

LVL 1

Author Comment

Hi Allam,

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

Thank you.

0

LVL 1

Author Comment

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

LVL 40

Expert Comment

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
``````
0

LVL 1

Author Comment

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
``````
0

LVL 40

Expert Comment

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

LVL 1

Author Comment

I am sorry again.

Thank you.
0

LVL 40

Expert Comment

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
``````
0

LVL 40

Expert Comment

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
``````
0

LVL 1

Author Comment

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

LVL 1

Author Comment

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
``````
0

LVL 40

Expert Comment

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
``````
0

LVL 40

Accepted Solution

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
``````
0

LVL 1

Author Comment

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

LVL 40

Expert Comment

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
``````
0

LVL 1

Author Comment

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
``````
0

LVL 40

Expert Comment

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

LVL 1

Author Comment

I have tried using SSQA.

It is working great using it.

Thank you very much for your help.
0

## Featured Post

### Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common â€¦
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server.Â RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at: Â  htâ€¦
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final pâ€¦