Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    El Salvador
    Posts
    46

    Question Unanswered: Row Number in a Query

    Could anyone help me insert in the first column a row number of this query:
    (notice that it has a UNION)
    Code:
    SELECT	B.DOCDATE,
    	B.DOCNUMBR,
    	A.USERDEF2,
    	(CASE WHEN B.VENDORID = '41221' OR B.VENDORID = '49697' THEN B.TRXDSCRN ELSE A.VENDNAME END) AS PROVEEDOR,
    	0 AS INT_EXEN,
    	0 AS IMP_EXEN,
    	(CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN 0 ELSE (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) END) AS INTERNAS,
    	(CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) ELSE 0 END) AS IMPORTACIONES,
    	(CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.TAXAMNT * -1) ELSE B.TAXAMNT END) AS IVA,
    	ISNULL(CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256')*-1 ELSE (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256') END ,0) AS RETENCION,
    	(CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN 0 ELSE (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) END) +
    	(CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) ELSE 0 END) +
    	(CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.TAXAMNT * -1) ELSE B.TAXAMNT END) +
    	ISNULL(CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256')*-1 ELSE (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256') END ,0) AS TOTAL
    FROM	PM20000 B INNER JOIN PM00200 A ON
            B.VENDORID = A.VENDORID
    WHERE	B.TAXSCHID >= 'PLAN IVA' AND
    	B.TAXSCHID <= 'PLAN TRANSP.CON' AND
    	B.VOIDED = 0 AND
    	B.TAXAMNT <> 0 AND
    UNION ALL
    SELECT 	B.DOCDATE,
    	B.DOCNUMBR,
    	A.USERDEF2,
    	(CASE WHEN B.VENDORID = '41221' OR B.VENDORID = '49697' THEN B.TRXDSCRN ELSE A.VENDNAME END) AS PROVEEDOR,
    	0 AS INT_EXEN,
    	0 AS IMP_EXEN,
    	(CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN 0 ELSE (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) END) AS INTERNAS,
    	(CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) ELSE 0 END) AS IMPORTACIONES,
    	(CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.TAXAMNT * -1) ELSE B.TAXAMNT END) AS IVA,
    	ISNULL(CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256')*-1 ELSE (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256') END ,0) AS RETENCION,
    	(CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN 0 ELSE (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) END) +
    	(CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) ELSE 0 END) +
    	(CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.TAXAMNT * -1) ELSE B.TAXAMNT END) +
    	ISNULL(CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256')*-1 ELSE (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256') END ,0) AS TOTAL
    FROM	PM00200 A INNER JOIN PM30200 B ON
            A.VENDORID = B.VENDORID
    WHERE	B.DOCTYPE <> 6 AND
    	B.TAXSCHID >= 'PLAN IVA' AND
    	B.TAXSCHID <= 'PLAN TRANSP.CON' AND
    	B.VOIDED = 0 AND
    	B.TAXAMNT <> 0 AND
    ORDER BY DOCDATE

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This is MUCH easier to do if you are using a stored procedure and can load this dataset into a temporary table or table variable. Is that an option?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    El Salvador
    Posts
    46
    Quote Originally Posted by blindman
    This is MUCH easier to do if you are using a stored procedure and can load this dataset into a temporary table or table variable. Is that an option?
    It is in a stored procedure... I use that SP to send the data to a report in visual basic 6... I guess I could use a temp table
    Could you explain me how to do it?
    Last edited by diegocro; 02-11-05 at 00:21.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Create a table variable or temporary table in your procedure to hold your dataset.

    Use your UNION query to INSERT records into your temporary table or table variable.

    Then use the following method to get your row numbers:


    Assuming DOCNUMBER is a unique value and can be used to decide ties when two or more records share a DOCDATE value:

    Code:
    select	TempTable.[Column1],
    	TempTable.[Column2],
    	.
    	.
    	.
    	TempTable.[ColumnN],
    	count(*) as RowNumber
    from	TempTable
    	inner join TempTable RowTable
    		on TempTable.DOCDATE > RowTable DOCDATE
    		or (TempTable.DOCDATE = RowTable DOCDATE
    			and TempTable.DOCNUMBER > RowTable.DOCNUMBER)
    Add 1 to your RowNumber if you want to start numbering with 1.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •