Skipping first row in a query?

[artco]

Hi all, I am trying to create a flat file for a sap upload. The problem is this:

The flat file format has say 10 fields in it. The first 5 of those fields from the table will all be the same (for this query) and the flat file requires that the first row has all fields filled in, but subsequent rows have the first 5 blank.

I am trying to work out how to do this. I thought of some type of CASE using the rownum (old Oracle person) byt it seems that SQL server does not havea rownum.

I thought of a TOP to get the first row, unioned with the rest, but in the second part of the union I need to get rid of the first row to avoid duplicate entries.

Am a bit lost and help would be appreciated.



thanks

Andrew

 

[bborissov]

Can you post some data and desired result?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Govnor]

Hi,
You could place the data into a temp table first then update that temp table to fill in the gaps.

after select the temp table for the results

use Indentity(int 1,1) if you need to get around rownum

select Indentity(int 1,1) *
INTO ##temp_any_table_name
from your_table_name

hope that helps or points you to the right direction.

Thanks
Manraj

THE GOV' NOR

 

[artco]

Hi Borislav,

Below (althought not formatted well) is what the output should be. Cut and past what's between the lines into excell and you will see.

Basically each row has the first 7 fields the same, so we need to do the select and place nulls in those first 7 place.

After the mail from Manraj, it got me thinking. I could use an identity or perhaps even find a unique field (or combination) in the rows and then do a:

select all cols where criteria for unique field
UNION
select nulls + remaining cols where criteria <> unique field.

What do you think? I don't actually care about the order of the rows, so that should work - I think...

Andrew



-------------------------------------------
Document Date Doc Type Posting Date Reversal Date Header Company Code Reference Text Header Text Pst Key Account Code Item Company Code Amount Tax Code Cost Centre Profit Centre Fund Centre Line Item Text
30.06.2006 SA 30.06.2006 1000 TFR MOC Jun-06 $10 FEE FOR DIMIA MOC WORK 40 40400 1881.82 GZ 1140 Tfr to MOC work
40 40400 27009.11 GZ 2140 Tfr to MOC work
40 40400 6709.09 GZ 2240 Tfr to MOC work
40 40400 554.55 GZ 2340 Tfr to MOC work
40 40400 727.27 GZ 2440 Tfr to MOC work
40 40400 23354.55 GZ 3140 Tfr to MOC work
40 40400 10900.00 GZ 4140 Tfr to MOC work
40 40400 4445.45 GZ 5140 Tfr to MOC work
40 40400 9936.36 GZ 6140 Tfr to MOC work
40 40400 327.27 GZ 7140 Tfr to MOC work
40 40400 936.36 GZ 8140 Tfr to MOC work
50 40300 26195.35 GZ 2140 Tfr from MIG Exams
50 40300 8458.34 GZ 2240 Tfr from MIG Exams
50 40300 30443.09 GZ 3140 Tfr from MIG Exams
50 40300 13755.36 GZ 4140 Tfr from MIG Exams
50 40300 7929.69 GZ 6140 Tfr from MIG Exams
-----------------------------------------------------------

 

[bborissov]

It could be done, but I think there is easier method (w/o UNION):

DECLARE @Test TABLE (RowNum int Identity(1,1),
                     other fields here)
INSERT INTO @Test (fields here w/o RowNum)
SELECT * FROM MyTable WHERE ....

--- Final select
SELECT CASE WHEN RowNum = 1 THEN [Document Date]
            ELSE NULL END [Document Date],
       CASE WHEN RowNum = 1 THEN [Doc Type]
            ELSE NULL END [Doc Type],
....
        [Pst Key],
...
FROM @Temp
ORDER BY RowNum

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[artco]

Thanks Borislav - that makes sense and looks like a good solution.

regards
Andrew