In TSQL, How do I create a column in my view so that it display a number on each row that gets incremented?
TSQL: SQL SERVER 2008R2
Hi,
How do I create a column in my view so that it display a number on each row that gets incremented?
For example:
data of students:
LAST NAME, FIRSTNAME
white, snow
charming, prince
mouse, mickey
I want this:
LINE #, LAST NAME, FIRST NAME
1, white, snow
2, charming, prince
3, mouse, mickey
Hi,
How do I create a column in my view so that it display a number on each row that gets incremented?
For example:
data of students:
LAST NAME, FIRSTNAME
white, snow
charming, prince
mouse, mickey
I want this:
LINE #, LAST NAME, FIRST NAME
1, white, snow
2, charming, prince
3, mouse, mickey
Naturally you can specify [LAST NAME], [FIRST NAME] or other specific column(s) in the ORDER BY if you want that order for the line#s.
If you want the "order the rows were inserted", there's no way to guarantee that unless you have a datetime in the table that reflects that, and you can sort on that column.
(Sorry, I was busy earlier and didn't have time then to add this extra description.)
If you want the "order the rows were inserted", there's no way to guarantee that unless you have a datetime in the table that reflects that, and you can sort on that column.
(Sorry, I was busy earlier and didn't have time then to add this extra description.)
Actually, your solution worked as a query but not as a VIEW, which is what I need. Do you know how to make it work in as a VIEW?
It's the first field named Line_No, which is blank instead of each line getting an incremented number 1, 2, 3, etc.
VIEW:
SELECT '' AS Line_No, '' AS REJECTED, DOCNUM AS [External Claim ID], RXCLAIMNUM, MEMBER_ID, DOF, RX_NUMBER, QTY, DS, NCPDP,
RX_NETWORK_ID AS [RX Network ID], PRICING_TERM_DESC AS [Pricing Term Used for Claim], '' AS [Pharmacy AWP Discount Rate],
'' AS [Pharmacy Dispensing Fee], TOTAL_TAXED_LOE_AMOUNT AS [Total Taxed/LOE Amount], UNIT_PRICE_OF_AWP AS [Unit Price of AWP], '' AS [Unit Price of MAC],
'' AS [Ingredient Cost at AWP Discount], '' AS [Ingredient Cost at MAC], '' AS [Claim adjudicate using Lesser of logic], CLAIM_DISCOUNT_RATE AS [Claim Discount Rate],
CLAIM_DISPENSING_FEE AS [Claim Dispensing Fee], '' AS [Did Claim Adjudicate with Corect Discount/Disp Fee], '' AS [Additional Pricing review],
FORMULARY_COMPLIANCE_CODE AS [Formulary Compliance Code], '' AS [Expected Copay], '' AS [Copay for Claim], '' AS [Copay Comment],
BRAND_GENERIC_TRADEMARK AS [Brand / Generic Description],
CLAIM_OVERRIDE_REASON_CODE1 + ' ' + CLAIM_OVERRIDE_REASON_CODE2 + ' ' + CLAIM_OVERRIDE_REASON_CODE3 + ' ' + CLAIM_OVERRIDE_REASON_CODE4 +
' ' + CLAIM_OVERRIDE_REASON_CODE5 + ' ' + CLAIM_OVERRIDE_REASON_CODE6 AS [Claim Override Reason Code], '' AS [Claim Reject Code],
'' AS [Claim Reject Reason Code]
FROM dbo.tbl_FEP_PCL
Hmm, you're selecting '', try using row_number instead:
VIEW:
SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Line_No,
...
VIEW:
SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Line_No,
...
OK, it does work as a view but I had to click "ignore " about "SQL text cannot be represented in the grid pane and diagram pane. " and "the query cannot be represented graphically in the diagram and criteria Pane".
Thank you very much.
Thank you very much.