add line count

[lyudmila]

I need to build invoice table based on accounts payable table. Depends on value in some fields, I need to populate invoice amount field. Actualy I have a RPG program printout and I need to translate this program in SQL code. Part of RPG program adds line count to program. If record line for particular voucher number=1, them value of amount field in input file popupates X field, if line<>1 then value in amt field populates some Y field. How I can code this procedure in SQL?

 

[sguslan]

It would help if you post an example of the uoutput with records with more than 1 lines and another with only one line .. ________________________________________________________________________________
If you do not like change, get out of the IT business...

 

[lyudmila]

Here is example of my input table
VCH VEN PTYPE AMT
1571 33 60 -264.15
1571 33 60 250.82
1571 33 60 7.11
1571 33 60 6.22
1571 33 03 264.15
1571 33 03 -264.15
4472 708 60 -108.26
4472 708 03 108.26
4472 708 03 -108.26
For each ven and vch I have two line with ptype='03'
If AMT in line =1 them AMT goes to field X
If AMT in line<>1 them AMT goes to field Y. How I can incorporate line counter in T_SQL. I create new table from this table using &quot;SELECT INTO&quot; statement and selecting only field I need.
Is any one know how I can do this?

 

[SQLSister]

There is no line count in SQL server. You can create a temp table with an identity field to generate automunbers. Then insert your data into the temp table and write your code to look at the identity field value in our select into statement. You'll need an order by statement though or your data may not always come in in the correct order and your code won't work properly. I personally would look for some other way to identify the record you want than line number. Since SQL doesn't necessarily order tables in the order they were entered, this seems like a very poor way to me to identify the first record. Your structure seems bascially flawed to me. You need some way of telling which is the first record based on the data in the record not it's position in the table. You could add an identity field and look for the min identity number and compare it to the identity number of the record, or you could do the same thing with a date field.

 

[SQLBill]

Based on the data you provided, what do you want your new table to look like?

-SQLBill

 

[lyudmila]

I want my output table to be:

VCH VEN PTYPE X Y
1571 33 03 264.15 0
4472 708 03 0 -108.26

X value - line record #1 with PTYPE='03' for ven 33
Y value - line record #2 with PTYPE='03' for ven 708