No column was specified for Column 1 of C1-CodePudding

i need help please i have been stuck on this for 3 days now , i have googled endlessly but i cant seem to fix this, can someone please help me fix this and explain what i did wrong?

i keep getting the No Column was specified for C1 error and when i try to execute it i keep getting

Msg 4112, Level 15, State 1, Procedure XXX, Line 27 [Batch Start Line 7] The function 'ROW_NUMBER' must have an OVER clause with ORDER BY.

SELECT TOP 1 @DataIndex = DataIndex,
                 @ChannelDescription = Description,
                 @UserConfigurationData = UserConfigurationData
    FROM DevicesCW WHERE Id = @DTSourceRecId
    
        
    SELECT @DataIndex DataIndex,Column_Id ChannelID, value ChannelName INTO #CName FROM dbo.[fn_split_string_to_column] (@ChannelDescription,@delimiter)
    SELECT @DataIndex DataIndex,Column_Id ChannelID, value ChannelNumber INTO #CNumber FROM dbo.[fn_split_string_to_column] (@UserConfigurationData,@delimiter);
    
    --Test Code--
    --Channel 1
    With C1 AS (SELECT  @ReoDeviceId,CN.ChannelNumber,C.ChannelID,D.Totaliser1Units TotaliserUnits, C.ChannelName, 'CW', @DTSourceRecId,'B', C.ChannelName,
            RN = ROW_NUMBER() OVER(PARTITION BY @DTSourceRecId)
            FROM DevicesCW D
                JOIN #CName C
                    ON D.DataIndex = C.DataIndex AND C.ChannelID = 1
                JOIN #CNumber CN
                    ON D.DataIndex = C.DataIndex AND C.ChannelID = 1
                LEFT JOIN ReoDeviceMeter RDM
                    ON D.DataIndex = RDM.DeviceIndex AND RDM.ChannelNumber = 1
            WHERE D.Id = @DTSourceRecId AND RDM.ReoMeterId IS NULL)
    INSERT INTO ReoMeter(ReoDeviceId, MeterNumber, ChannelNumber, ChanUnitOfMeasure, Description, DtSource, DtSourceRecId, MeterType, SourceDescription)
    SELECT @ReoDeviceId,ChannelNumber,ChannelID,TotaliserUnits,ChannelName,'CW',@DTSourceRecId,'B',ChannelName

    FROM C1
    WHERE RN = 1
    Group BY @ReoDeviceId,ChannelNumber,ChannelID,TotaliserUnits,ChannelName,'CW',@DTSourceRecId,'B',ChannelName


    -- Test Code---

I am trying to go the above route to resolve an issue where the blow code duplicates the data in the database for each Channel. if i have 2 channels it inserts the data 2 times for CH1 and CH2 instead of just inserting the data for each Channel

--Channel1
    INSERT INTO ReoMeter(ReoDeviceId, MeterNumber, ChannelNumber, ChanUnitOfMeasure, Description, DtSource, DtSourceRecId, MeterType, SourceDescription)
    SELECT  @ReoDeviceId,CN.ChannelNumber,C.ChannelID,D.Totaliser1Units, C.ChannelName, 'CW', @DTSourceRecId,'B', C.ChannelName
    FROM DevicesCW D
        JOIN #CName C
            ON D.DataIndex = C.DataIndex AND C.ChannelID = 1
        JOIN #CNumber CN
            ON D.DataIndex = C.DataIndex AND C.ChannelID = 1
        LEFT JOIN ReoDeviceMeter RDM
            ON D.DataIndex = RDM.DeviceIndex AND RDM.ChannelNumber = 1
    WHERE D.Id = @DTSourceRecId AND RDM.ReoMeterId IS NULL

    --Channel2
    INSERT INTO ReoMeter(ReoDeviceId, MeterNumber, ChannelNumber, ChanUnitOfMeasure, Description, DtSource, DtSourceRecId, MeterType, SourceDescription)
    SELECT  @ReoDeviceId,CN.ChannelNumber,C.ChannelID,D.Totaliser2Units, C.ChannelName, 'CW', @DTSourceRecId, 'B', C.ChannelName
    FROM DevicesCW D
        JOIN #CName C
            ON D.DataIndex = C.DataIndex AND C.ChannelID = 2
        JOIN #CNumber CN
            ON D.DataIndex = C.DataIndex AND C.ChannelID = 2
        LEFT JOIN ReoDeviceMeter RDM
            ON D.DataIndex = RDM.DeviceIndex AND RDM.ChannelNumber = 2
    WHERE D.Id = @DTSourceRecId AND RDM.ReoMeterId IS NULL

CodePudding user response：

You need to Give Aleas for the all columns of C1 to fix the issue.

SELECT TOP 1 @DataIndex = dataindex,
             @ChannelDescription = description,
             @UserConfigurationData = userconfigurationdata
FROM   devicescw
WHERE  id = @DTSourceRecId

SELECT @DataIndex DataIndex,
       column_id  ChannelID,
       value      ChannelName
INTO   #cname
FROM   dbo.[Fn_split_string_to_column] (@ChannelDescription, @delimiter)

SELECT @DataIndex DataIndex,
       column_id  ChannelID,
       value      ChannelNumber
INTO   #cnumber
FROM   dbo.[Fn_split_string_to_column] (@UserConfigurationData, @delimiter);

--Test Code--
--Channel 1
WITH c1
     AS (SELECT @ReoDeviceId      AS ReoDeviceId,
                CN.channelnumber,
                C.channelid,
                D.totaliser1units TotaliserUnits,
                C.channelname,
                'CW'              AS CW,
                @DTSourceRecId    AS DTSourceRecId,
                'B'               AS B,
                C.channelname,
                RN = Row_number()
                       OVER(
                         partition BY @DTSourceRecId)
         FROM   devicescw D
                JOIN #cname C
                  ON D.dataindex = C.dataindex
                     AND C.channelid = 1
                JOIN #cnumber CN
                  ON D.dataindex = C.dataindex
                     AND C.channelid = 1
                LEFT JOIN reodevicemeter RDM
                       ON D.dataindex = RDM.deviceindex
                          AND RDM.channelnumber = 1
         WHERE  D.id = @DTSourceRecId
                AND RDM.reometerid IS NULL)
INSERT INTO reometer
            (reodeviceid,
             meternumber,
             channelnumber,
             chanunitofmeasure,
             description,
             dtsource,
             dtsourcerecid,
             metertype,
             sourcedescription)
SELECT reodeviceid,
       channelnumber,
       channelid,
       totaliserunits,
       channelname,
       cw,
       dtsourcerecid,
       b,
       channelname
FROM   c1
WHERE  rn = 1
GROUP  BY reodeviceid,
          channelnumber,
          channelid,
          totaliserunits,
          channelname,
          cw,
          dtsourcerecid,
          b,
          channelname