Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Update or insert of view failed because it contains a derived or constant field

Answered Update or insert of view failed because it contains a derived or constant field

  • Thursday, January 03, 2013 5:31 PM
     
      Has Code
    Sign In to Vote
    0

    Hello All,

    I am trying to update a view but I am getting the error

    Update or insert of view failed because it contains a derived or constant field. This is the select statement to create my view.

    select 
	PODS_ID
	,CMDTY_DESC
	,COMMODITY
	,LineRegulatoryType
from 
		(SELECT DISTINCT
		L.PODS_ID,
		
		CAST(SUBSTRING((SELECT DISTINCT ', '  + CAST(PRODUCT AS VARCHAR(40))
					FROM DERIVEDASSET.DBO.LINEPRODUCTOPARSTATUS OP WHERE OP.LINE_GUID = L.LINE_GUID
					FOR XML PATH('')),3,1000) AS VARCHAR(4000)) AS CMDTY_DESC,
	
		
		' ' as COMMODITY,
		LRT.LineRegulatoryType
        FROM DERIVEDASSET.DBO.LINEMASTER L
		
		INNER JOIN DerivedAsset.dbo.LineProductOparStatus OP ON PS.LINE_GUID = OP.LINE_GUID 
		INNER JOIN DerivedAsset.dbo.LineRegulatoryType LRT ON OP.LINE_GUID = LRT.LINE_GUID
		WHERE LRT.LineRegulatoryType IN ('Federal Offshore Natural Gas Transmission', 
		'Natural Gas Transmission','Federal Offshore Liquids Transmission'
		,'Liquids Transmission')
		) NPMS_PIPELINE

    To get the values for CMDTY_DESC column as comma seperated values I am using xml path and substring. The data for this view will be something like this

    PODS_ID              CMDTY_dESC             COMMODITY       LineRegulatoryType

    20                        butane, isobutane                               Liquids Transmission       

    21                        Y-Grade, butane                                  Liquids Transmission

    22                        Nitrogen                                               Liquids Transmission

    23                        Nitrogen, Refined Products                   Liquids Transmission

    24                        Nitrogen, Refined Products                   Natural Gas Transmission

    Now, I want to update the commodity column as when the cmdty_desc contains Nitrogen, Line regulatoryType is 'Liquids Transmission' Commodity value will be EPL and when the CMDTY_DESC Contains Nitrogen, LineRegulatoryType is 'Natural Gas Transmission' Commodity value will be OTG. Rest of the records should have the value as calculated in the Else part. I am trying to execute the below update statement but getting an error. Please help me how to resolve this issue.  

      
    UPDATE GIS_NPMS_PIPELINE_Test
SET Commodity = CASE
		WHEN CMDTY_DESC like '%Nitrogen%' and LineRegulatoryType='Liquids Transmission' and OP.LineOperatingStatus='Idle' THEN 'EPL'
		WHEN CMDTY_DESC like '%Nitrogen%' and LineRegulatoryType='Natural Gas Transmission' and OP.LineOperatingStatus='Idle' THEN 'OTG'
		ELSE 
		LEFT(CAST(SUBSTRING((SELECT DISTINCT ', '  + CAST(NPMSCommodity AS CHAR(3))
					FROM DERIVEDASSET.DBO.LINEPRODUCTOPARSTATUS OP WHERE OP.LINE_GUID = L.LINE_GUID
					FOR XML PATH('')),3,1000) AS VARCHAR(4000)),3)  
		END  
from GIS_NPMS_PIPELINE_Test GV
inner join LINEPRODUCTOPARSTATUS OP
ON GV.PODS_ID=OP.PODS_ID
inner join LineMaster L
ON OP.Line_GUID=L.Line_GUID
INNER JOIN LineRegulatoryType LRT
ON LRT.LINE_GUID=OP.LINE_GUID

     

All Replies

  • Thursday, January 03, 2013 5:52 PM
     
     Answered
    Sign In to Vote
    0

    Hello,

    SQL Server views are only updatable under certain conditions, see SQL Views , Modify Data Through a View, and CREATE VIEW (Transact-SQL) => Remarks => Updatable Views. Your view don't fullfil this conditions/restrictions.

    Olaf Helper

    Blog Xing

     
  • Thursday, January 03, 2013 6:16 PM
     
     
    Sign In to Vote
    0
    Thanks for the reply. I am also trying to include the logic for Commodity column in the select statement itself instead of updating it later..But when I execute it is not recognizing the CMDTY_DESC column. It says invalid column CMDTY_DESC. Please help me with any suggestions how can I get the values for commodity column for my view.
     
  • Thursday, January 03, 2013 6:27 PM
    Moderator
     
     
    Sign In to Vote
    0
    Can you post the failing query for better assistance?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Thursday, January 03, 2013 6:33 PM
     
      Has Code
    Sign In to Vote
    0

    Here it is. 

    select 
	PODS_ID
	,CMDTY_DESC
	,COMMODITY
	,LineRegulatoryType
from 
		(SELECT DISTINCT
		L.PODS_ID,
		
		CAST(SUBSTRING((SELECT DISTINCT ', '  + CAST(PRODUCT AS VARCHAR(40))
					FROM DERIVEDASSET.DBO.LINEPRODUCTOPARSTATUS OP WHERE OP.LINE_GUID = L.LINE_GUID
					--ORDER BY ', ' + PRODUCT 
					FOR XML PATH('')),3,1000) AS VARCHAR(4000)) AS CMDTY_DESC,
	
		
		CASE
		WHEN CMDTY_DESC like '%Nitrogen%' and LRT.LineRegulatoryType='Liquids Transmission' and OP.LineOperatingStatus='Idle' THEN 'EPL'
		WHEN CMDTY_DESC like '%Nitrogen%' and LRT.LineRegulatoryType='Natural Gas Transmission' and OP.LineOperatingStatus='Idle' THEN 'OTG'
		ELSE 
		LEFT(CAST(SUBSTRING((SELECT DISTINCT ', '  + CAST(NPMSCommodity AS CHAR(3))
					FROM DERIVEDASSET.DBO.LINEPRODUCTOPARSTATUS OP WHERE OP.LINE_GUID = L.LINE_GUID
					
					FOR XML PATH('')),3,1000) AS VARCHAR(4000)),3)
		END AS COMMODITY,
		
		LRT.LineRegulatoryType
        FROM DERIVEDASSET.DBO.LINEMASTER L
		
		INNER JOIN DerivedAsset.dbo.LineProductOparStatus OP ON L.LINE_GUID = OP.LINE_GUID 
		INNER JOIN DerivedAsset.dbo.LineRegulatoryType LRT ON OP.LINE_GUID = LRT.LINE_GUID
		WHERE LRT.LineRegulatoryType IN ('Federal Offshore Natural Gas Transmission', 
		'Natural Gas Transmission','Federal Offshore Liquids Transmission'
		,'Liquids Transmission')
		) NPMS_PIPELINE

     
  • Thursday, January 03, 2013 6:34 PM
     
     
    Sign In to Vote
    0
    I am getting the error as

    Invalid column name 'CMDTY_DESC'

     
  • Thursday, January 03, 2013 6:40 PM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    0

    I see - you can not use new column name in the same select statement that selects it. There are alternatives, e.g.

    select 
	PODS_ID
	,CMDTY_DESC
	,COMMODITY
	,LineRegulatoryType
from 
		(SELECT DISTINCT
		L.PODS_ID,
		
		F.CMDTY_DESC,
	
		
		CASE
		WHEN F.CMDTY_DESC like '%Nitrogen%' and LRT.LineRegulatoryType='Liquids Transmission' and OP.LineOperatingStatus='Idle' THEN 'EPL'
		WHEN F.CMDTY_DESC like '%Nitrogen%' and LRT.LineRegulatoryType='Natural Gas Transmission' and OP.LineOperatingStatus='Idle' THEN 'OTG'
		ELSE 
		LEFT(CAST(SUBSTRING((SELECT DISTINCT ', '  + CAST(NPMSCommodity AS CHAR(3))
					FROM DERIVEDASSET.DBO.LINEPRODUCTOPARSTATUS OP WHERE OP.LINE_GUID = L.LINE_GUID
					
					FOR XML PATH('')),3,1000) AS VARCHAR(4000)),3)
		END AS COMMODITY,
		
		LRT.LineRegulatoryType
        FROM DERIVEDASSET.DBO.LINEMASTER L
		
		INNER JOIN DerivedAsset.dbo.LineProductOparStatus OP ON L.LINE_GUID = OP.LINE_GUID 
		INNER JOIN DerivedAsset.dbo.LineRegulatoryType LRT ON OP.LINE_GUID = LRT.LINE_GUID

CROSS APPLY (SELECT CAST(SUBSTRING((SELECT DISTINCT ', '  + CAST(PRODUCT AS VARCHAR(40))
					FROM DERIVEDASSET.DBO.LINEPRODUCTOPARSTATUS OP WHERE OP.LINE_GUID = L.LINE_GUID
					--ORDER BY ', ' + PRODUCT 
					FOR XML PATH('')),3,1000) AS VARCHAR(4000)) AS CMDTY_DESC) F

		WHERE LRT.LineRegulatoryType IN ('Federal Offshore Natural Gas Transmission', 
		'Natural Gas Transmission','Federal Offshore Liquids Transmission'
		,'Liquids Transmission')
		) NPMS_PIPELINE
    As you can see, I moved this column into CROSS APPLY. Alternative solution is to use an additional derived table.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked As Answer by srisql Thursday, January 03, 2013 6:50 PM
    •  
     
  • Thursday, January 03, 2013 6:50 PM
     
     
    Sign In to Vote
    0

    Thanks u so much. That worked. Thanks a lot.