SQL Server : generate a different value for each output row

Question

I am running a query in which I need the SCORE to have a different output for each row. I am able to do it per execution but I have had no luck with RAND as I get a convert from varchar to int error. I need variable number per execution within a range with no decimals. Is there anyway to do this? Any help is appreciated.

SELECT
    'Row_Number^FEED_date^database_id^station_id^Form_Type^FBCS_CLAIM_ID^FBCS_LINE_ID^Pay^SCORE^score_date^reason_code^reason_description'
UNION ALL
SELECT
    CAST(ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) AS VARCHAR(20))  + '^' + 
          CONVERT(VARCHAR, getdate(), 112)+ '^' +  
          'FBCSTrans'+ '^' +  ---took off the 1 so if problems put it back
          stationnumber+ '^' +  
          case when claimtype = 0 then 'HCFA 1500' else 'UB 1450' end+ '^' + 
          CAST(Claims.claimid AS VARCHAR(50)) + '^' + 
          CAST(LineID AS VARCHAR(50)) + '^' + 
          'Y'+ '^' + 
          **RAND() * 100000 AS random_number** + '^' + 
          CONVERT(VARCHAR, getdate(), 112)+ '^' + 
          '' + '^' + ''
FROM 
    Claims 
JOIN 
    ClaimLines ON Claims.Claimid = Claimlines.Claimid 
JOIN 
    Facilities ON Claims.FacilityID = Facilities.FacilityID
WHERE  
    Claims.ClaimId IN (SELECT TOP(100000) ClaimId 
                       FROM CLAIMS 
                       WHERE Claims.RecordStatus = 55 AND facilityid = 40)

Sample data, desired results, and an appropriate database tag would all help. — Gordon Linoff, Mar 21 '19 at 20:21
Assuming sql server (you've not specified), you can try the approach in the accepted answer here https://stackoverflow.com/q/5003028/491907 Note also that your `As random_number` is ill placed and will be a syntax error since you're still building a concatenated string, you need to remove that at the very least — pinkfloydx33, Mar 21 '19 at 20:25

user2642587 · Answer 1 · 2019-03-22T16:52:29.113

    USE [DATABASE NAME]

SELECT 'Row_Number^FEED_date^database_id^station_id^Form_Type^FBCS_CLAIM_ID^FBCS_LINE_ID^Pay^SCORE^score_date^reason_code^reason_description'
UNION ALL
SELECT
CAST(ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) AS VARCHAR(20))  + '^' + 
          CONVERT(VARCHAR, getdate(), 112)+ '^' +  
          'DATABASE NAME'+ '^' +  ---took off the 1 so if problems put it back
          stationnumber+ '^' +  
          case when claimtype = 0 then 'HCFA 1500' else 'UB 1450' end+ '^' + 
          CAST(Claims.claimid AS VARCHAR(50)) + '^' + 
          CAST(LineID AS VARCHAR(50)) + '^' + 
          'Y'+ '^' + 
          CAST (FLOOR(RAND(CHECKSUM(NEWID()))*(1000-0+1)+100) As NVARCHAR) + '^' + 
          CONVERT(VARCHAR, getdate(), 112)+ '^' + 
          '' + '^' + ''
FROM Claims 
        JOIN ClaimLines on Claims.Claimid = Claimlines.Claimid 
        JOIN Facilities ON Claims.FacilityID = Facilities.FacilityID
WHERE  Claims.ClaimId IN (SELECT TOP(100000)ClaimId FROM CLAIMS WHERE Claims.RecordStatus = 55 and facilityid=40)

SQL Server : generate a different value for each output row

1 Answers1