none
Trazer dois campos de uma view para a outra RRS feed

  • Pergunta

  • Boa tarde.

    Sou iniciante em MSSQL e SQl em geral, estou com um probleminha e gostaria de ajuda se possível, desculpe se falar alguma bobagem ou usar um termo incorreto.

    Tenho duas views, pelo o que entendi uma delas importa informações da outra, a primeira é a BaseTodaView, que importa de BaseTodaReport. Em BaseTodaReport há duas colunas, chamadas OrderMgmtID e SystemReference, que eu preciso jogar no SELECT da BaseTodaView, porém sempre que coloco esses dois campos na query aparece the multi-part identifier could not be bound, como se os joins e talz não estivessem sendo feitos direito, e pelo oq entendi isso é feito direto no BaseTodaReport. Segue abaixo as duas views, a BaseTodaView ( que é a que preciso fazer aparecer os dois campos, que na cláusula FROM tem apenas a outra view ) e depois o código da BaseTodaReport completo. 
    Sabem o que pode ser?

    ALTER VIEW [dbo].[BaseTodaView] AS SELECT [Customer BFG ID], [Site BFG ID], CNS, [Global Circuit], QREF, [BT ID - SR], ID, [ID (upgrade/downgrade)],

    [BT Product Name], [Access Type], (continua...) FROM dbo.BaseTodaReport

    SELECT        QUOTENAME(dbo.CleanAndTrimString(dbo.contratos.CustomerBFGID), '"') AS [Customer BFG ID], QUOTENAME(dbo.CleanAndTrimString(dbo.solicita.QREF), '"') AS QREF, QUOTENAME(dbo.contratos.SR, '"') AS [BT ID - SR], dbo.solicita.sol_id AS ID, 
                             dbo.CleanAndTrimString(dbo.solicita.sol_updown_contr_sol_ID) AS [ID (upgrade/downgrade)], dbo.CleanAndTrimString(dbo.solicita.sol_productname) AS [BT Product Name], dbo.CleanAndTrimString(dbo.solicita.sol_list) AS [Access Type], dbo.CleanAndTrimString(dbo.solicita.sol_tipo) AS [Access Product Type], 
                             QUOTENAME(dbo.solicita.sol_solicitante, '"') AS [Feasibility Request User], QUOTENAME(dbo.CleanAndTrimString(dbo.solicita.sol_cliente), '"') AS Customer, dbo.CleanAndTrimString(dbo.taxas.taxa) AS [Rate/Speed], 
                             QUOTENAME(dbo.CleanAndTrimString(dbo.solicita.sol_razaoA), '"') AS [Company Name A], QUOTENAME(dbo.CleanAndTrimString(dbo.solicita.sol_ruaA), '"') AS [Address A], 
                             QUOTENAME(dbo.CleanAndTrimString(dbo.solicita.sol_numA), '"') AS [Num. A], QUOTENAME(dbo.CleanAndTrimString(dbo.solicita.sol_complemA), '"') AS [Complement A], 
                             QUOTENAME(dbo.CleanAndTrimString(dbo.solicita.sol_bairroA), '"') AS [District A], QUOTENAME(dbo.CleanAndTrimString(ISNULL(dbo.solicita.sol_cidadeA, '')), '"') AS [City A], 
                             QUOTENAME(dbo.CleanAndTrimString(dbo.solicita.sol_estadoA), '"') AS [State A], QUOTENAME(dbo.CleanAndTrimString(dbo.solicita.sol_cepA), '"') AS [ZIP Code A], 
                             QUOTENAME(dbo.CleanAndTrimString(dbo.solicita.sol_cnpjA), '"') AS [Corporate Taxpayer Registry A], QUOTENAME(dbo.CleanAndTrimString(dbo.solicita.sol_contatoA), '"') AS [Contact A], 
                             QUOTENAME(dbo.CleanAndTrimString(dbo.solicita.sol_deptoA), '"') AS [Department A], QUOTENAME(dbo.CleanAndTrimString(dbo.solicita.sol_foneA), '"') AS [Phone A], 
                             QUOTENAME(dbo.CleanAndTrimString(ISNULL(dbo.solicita.paisA, '')), '"') AS [Country A], QUOTENAME(dbo.CleanAndTrimString(dbo.solicita.sol_razaoB), '"') AS [Company Name B], 
                             QUOTENAME(dbo.CleanAndTrimString(dbo.solicita.sol_ruaB), '"') AS [Address B], QUOTENAME(REPLACE(dbo.solicita.sol_numB, CHAR(13) + CHAR(10), ' '), '"') AS [Num. B], 
                             QUOTENAME(dbo.CleanAndTrimString(dbo.solicita.sol_complemB), '"') AS [Complement B], QUOTENAME(dbo.CleanAndTrimString(dbo.solicita.sol_bairroB), '"') AS [District B], 
                             QUOTENAME(REPLACE(ISNULL(dbo.solicita.sol_cidadeB, N''), CHAR(13) + CHAR(10), ' '), '"') AS [City B], QUOTENAME(dbo.CleanAndTrimString(dbo.solicita.sol_estadoB), '"') AS [State B], 
                             QUOTENAME(dbo.CleanAndTrimString(dbo.solicita.sol_cepB), '"') AS [ZIP Code B], QUOTENAME(dbo.CleanAndTrimString(dbo.solicita.sol_cnpjB), '"') AS [Corporate Taxpayer Registry B], 
                             QUOTENAME(dbo.CleanAndTrimString(dbo.solicita.sol_contatoB), '"') AS [Contact B], QUOTENAME(dbo.CleanAndTrimString(dbo.solicita.sol_deptoB), '"') AS [Department B], 
                             QUOTENAME(dbo.CleanAndTrimString(dbo.solicita.sol_foneB), '"') AS [Phone B], QUOTENAME(dbo.CleanAndTrimString(ISNULL(dbo.solicita.paisB, '')), '"') AS [Country B], CONVERT(char(10), dbo.solicita.sol_data, 
                             126) AS [Feasibility Request Date], dbo.CleanAndTrimString(dbo.solicita.UserAssigned) AS [User Assigned], CONVERT(char(10), dbo.solicita.DateAssignment, 126) AS [Date Assignment], 
                             dbo.CleanAndTrimString(dbo.solicita.user_quotecompleted) AS [User 'Quote Completed' Status], dbo.solicita.data_quotecompleted AS ['Quote Completed' Status Date], dbo.CleanAndTrimString(dbo.solcota.solcota_operadora) AS Telco, 
                             QUOTENAME(dbo.CleanAndTrimString(dbo.operadoras.operadora_razao), '"') AS [Telco name], dbo.operadoras.operadora_cnpj AS [Telco Corporate Taxpayer Registry], QUOTENAME(dbo.CleanAndTrimString(dbo.operadoras.operadora_grupo), '"') AS [Telco Group], 
                             QUOTENAME(dbo.CleanAndTrimString(dbo.cota.cota_acesso), '"') AS Access, dbo.cota.cota_valorinstal AS [Installation Price without tax], dbo.cota.cota_valormensal AS [Monthly Price], 
                             dbo.cota.cota_valorsemimposto AS [Monthly Price without tax], dbo.cota.cota_valor_icms AS Tax, dbo.CleanAndTrimString(dbo.cota.cota_degrau) AS Step, dbo.cota.cota_prazo AS [Current Contract Term with Telco], 
                             dbo.cota.cota_moeda AS Currency, QUOTENAME(REPLACE(dbo.contratos.contr_interfaceA, CHAR(13) + CHAR(10), ' '), '"') AS [Interface A], QUOTENAME(REPLACE(dbo.contratos.contr_interfaceB, CHAR(13) 
                             + CHAR(10), ' '), '"') AS [Interface B], QUOTENAME(REPLACE(dbo.contratos.contr_conectorA, CHAR(13) + CHAR(10), ' '), '"') AS [Connector A], QUOTENAME(REPLACE(dbo.contratos.contr_conectorB, CHAR(13) 
                             + CHAR(10), ' '), '"') AS [Connector B], QUOTENAME(REPLACE(dbo.contratos.contr_designador, CHAR(13) + CHAR(10), ' '), '"') AS [Circuit ID], dbo.contratos.contr_ccontabil AS [Natural Account], 
                             dbo.contratos.contr_status AS Status, dbo.contratos.projeto_prime AS Project, QUOTENAME(dbo.contratos.contr_coordenador, '"') AS PM, CONVERT(char(10), dbo.contratos.contr_ativadata, 126) 
                             AS [Activation Date], CONVERT(char(10), dbo.contratos.contr_prazo_renova, 126) AS [Renewal Effective Date], CONVERT(char(10), dbo.contratos.data_solcancelamento, 126) 
                             AS [Date of Cancellation Request with Telco], CONVERT(char(10), dbo.contratos.contr_cancel, 126) AS [Deactivation Date], CONVERT(char(10), dbo.contratos.contr_limite, 126) AS [Deadline to invoices], 
                             dbo.contratos.valor_desinstal AS [Uninstallation Price A], dbo.contratos.prazo_carencia AS [Grace Period], dbo.contratos.multa_porcento AS [Contractual fine (%)], 
                             dbo.contratos.multa_meses AS [Contractual fine (months)],QUOTENAME(dbo.CleanAndTrimString(dbo.contratos.multa_observ),'"') AS [Remarks (fine)], CONVERT(char(10), dbo.contratos.vigencia, 126) 
                             AS [Contract Expiration date], dbo.contratos.valor_desinstalB AS [Uninstallation Price B], QUOTENAME(dbo.CleanAndTrimString(dbo.contratos.lp_ptaA), '"') AS [LP Point A], QUOTENAME(dbo.CleanAndTrimString(dbo.contratos.lp_ptaB), '"') AS [LP Point B], 
                             dbo.contratos.empresa AS Entity, dbo.contratos.contr_prazo AS [Contract period with Customer], QUOTENAME(dbo.contratos.contr_designador_CNS, '"') AS [Circuit ID CNS], CONVERT(char(10), 
                             dbo.contratos.contr_statusdata, 126) AS [Status Date], QUOTENAME(dbo.CleanAndTrimString(dbo.contratos.CONTRATO_SAP_ID), '"') AS [Contract ID (Revenue)], QUOTENAME(dbo.CleanAndTrimString(dbo.contratos.ITEMCONTRATO_SAP_ITEM), '"') 
                             AS [Service Item ID], QUOTENAME(dbo.CleanAndTrimString(dbo.contratos.contr_OS), '"') AS [Telco Service Order], CONVERT(char(10), dbo.contratos.contr_data, 126) AS [Telco Order Request Date], CONVERT(char(10), 
                             dbo.cota.cota_datacontr1, 126) AS Contract_Date, QUOTENAME(dbo.contratos.contr_designador_fatura, '"') AS [Invoice Circuit ID], QUOTENAME(dbo.CleanAndTrimString(dbo.contratos.contr_observ), '"') 
                             AS [Telco Order Request Remarks], CONVERT(char(10), dbo.contratos.data_solcancel, 126) AS [Date of Cancellation Request in the System], 
                             dbo.CleanAndTrimString(dbo.contratos.user_solcancel) AS [Cancellation Requestor in the System], dbo.CleanAndTrimString(dbo.contratos.user_undercancel) AS [User 'Under Cancellation' Status], CONVERT(char(10), dbo.contratos.data_undercancel, 126) 
                             AS [Date 'Under Cancellation' Status], QUOTENAME(dbo.CleanAndTrimString(LEFT(dbo.contratos.AccessSolutions_remarks, 128)), '"') AS [Access Solutions Remarks], 
                             QUOTENAME(REPLACE(LEFT(dbo.contratos.confirm_cancel, 128), CHAR(13) + CHAR(10), ' '), '"') AS [Confirmation of Cancellation], CONVERT(int, dbo.solicita.sol_mask) AS [Country Mask], 
                             dbo.GFR_COUNTRY.Country, QUOTENAME(dbo.CleanAndTrimString(LEFT(dbo.solicita.sol_observ, 128)), '"') AS [Request Remarks], QUOTENAME(dbo.CleanAndTrimString(LEFT(dbo.contratos.contr_reasonCancel, 
                             128)), '"') AS [Reason for cancellation], QUOTENAME(dbo.CleanAndTrimString(LEFT(dbo.contratos.motivo, 128)), '"') AS [Evidence for cancellation request], dbo.solicita.sol_id_linked AS [Billing ID], 
                             CONVERT(char(10), dbo.contratos.date_bt_active, 126) AS [Date 'BT Active'], CONVERT(char(10), dbo.contratos.date_customer_accepted, 126) AS [Date 'Customer Accepted'], 
                             dbo.CleanAndTrimString(dbo.contratos.activation_status) AS [Activation Status], QUOTENAME(dbo.contratos.num_contrato_operadora, '"') AS [Telco Contract Number], 
                             QUOTENAME(REPLACE(dbo.CleanAndTrimString(dbo.contratos.Procurement_remarks), ',', ' '),'"') AS [Procurement Remarks], CONVERT(char(10), dbo.contratos.date_adjustment, 126) AS [Date of contractual adjustment], 
                             dbo.contratos.OUC, dbo.contratos.product_code, QUOTENAME(REPLACE(LEFT(dbo.contratos.contr_designador, 50), CHAR(13) + CHAR(10), ' '), '"') AS [Circuit ID to STARS], 
                             QUOTENAME(REPLACE(LEFT(dbo.contratos.contr_designador_CNS, 30), CHAR(13) + CHAR(10), ' '), '"') AS [Circuit ID CNS to STARS], QUOTENAME(REPLACE(LEFT(dbo.contratos.contr_designador_fatura, 50), 
                             CHAR(13) + CHAR(10), ' '), '"') AS [Invoice Circuit ID to STARS], REPLACE(CONVERT(CHAR(11), dbo.contratos.contr_ativadata, 106), ' ', '-') AS [Activation Date to STARS], REPLACE(CONVERT(CHAR(11), 
                             dbo.contratos.contr_limite, 106), ' ', '-') AS [Deadline to invoices to STARS], REPLACE(CONVERT(CHAR(11), dbo.contratos.contr_prazo_renova, 106), ' ', '-') AS [Renewal Effective Date to STARS], 
                             QUOTENAME(dbo.CleanAndTrimString(dbo.solcota.solcota_operadora), '"') AS [Telco p BaseToda], dbo.contratos.date_wtelcoconfirm AS [Order sent to Supplier (Date)], dbo.CleanAndTrimString(dbo.cota.cota_login1) AS [User Contracting], 
                             dbo.cota.CurrentPriceValidFrom AS [Current Price Valid From], CASE WHEN sup_prod_code IS NULL THEN 'UNK-BT' WHEN Country = 'Argentina' AND sup_prod_code_arg IS NOT NULL 
                             THEN sup_prod_code_arg ELSE sup_prod_code END AS sup_prod_code, CASE WHEN contr_status = 'Cancelled' AND contr_limite < getdate() 
                             THEN 'D' ELSE CASE contr_status WHEN 'Pending Disconnect' THEN 'P' WHEN 'Under cancellation' THEN 'P' WHEN 'Ordered' THEN 'O' WHEN 'Active' THEN 'A' WHEN 'Invalid' THEN 'I' WHEN 'Cancelled' THEN 'C'
                              ELSE contr_status END END AS CCT_STATUS_CODE, dbo.contratos.multa_valor AS [Early Termination Fee], dbo.solicita.QoS_EF, dbo.solicita.QoS_AF, dbo.solicita.QoS_AF1, dbo.solicita.QoS_AF2, 
                             dbo.solicita.QoS_AF3, dbo.solicita.QoS_DE, 'EF: ' + CONVERT(varchar(10), dbo.solicita.QoS_EF) + '/ AF:' + CONVERT(varchar(10), dbo.solicita.QoS_AF) + '/ AF1:' + CONVERT(varchar(10), dbo.solicita.QoS_AF1) 
                             + '/ AF2:' + CONVERT(varchar(10), dbo.solicita.QoS_AF2) + '/ AF3:' + CONVERT(varchar(10), dbo.solicita.QoS_AF3) + '/ DE:' + CONVERT(varchar(10), dbo.solicita.QoS_DE) AS QoS, 
                             dbo.CleanAndTrimString(dbo.contratos.renewal_status) AS RENEWAL_STATUS_CODE, REPLACE(CONVERT(CHAR(11), dbo.contratos.vigencia, 106), ' ', '-') AS [Expiration Date to STARS], 
                             CASE WHEN globalCircuit = 1 THEN 'Yes' ELSE 'No' END AS [Global Circuit], dbo.contratos.SiteBFGID AS [Site BFG ID], dbo.contratos.CNS, REPLACE(CONVERT(CHAR(11), dbo.contratos.Bill_Start_Date, 106), ' ', 
                             '-') AS [Bill Start Date], dbo.contratos.WK, REPLACE(dbo.contratos.PartnerAccount, ',',CHAR(32)) AS PartnerAccount, dbo.taxas.speed_stars, dbo.contratos.OrderMgmtID AS OrderMgmtID, dbo.contratos.SystemReference AS SystemReference
    FROM            dbo.solicita INNER JOIN
                             dbo.solcota ON dbo.solicita.sol_id = dbo.solcota.solcota_sol_id INNER JOIN
                             dbo.cota ON dbo.solcota.solcota_id = dbo.cota.cota_solcota_id INNER JOIN
                             dbo.contratos ON dbo.cota.cota_id = dbo.contratos.contr_cota_id INNER JOIN
                             dbo.operadoras ON dbo.solcota.solcota_operadora = dbo.operadoras.operadora_nome INNER JOIN
                             dbo.GFR_COUNTRY ON CONVERT(int, dbo.solicita.sol_mask) = dbo.GFR_COUNTRY.Mask INNER JOIN
                             dbo.taxas ON dbo.solicita.sol_taxa = dbo.taxas.id LEFT OUTER JOIN
                             dbo.BT_PRODUCT ON dbo.solicita.sol_productname = dbo.BT_PRODUCT.BT_PRODUCTNAME AND dbo.solicita.sol_list = dbo.BT_PRODUCT.LIST AND dbo.solicita.sol_tipo = dbo.BT_PRODUCT.ACCESS_TYPE
    

    terça-feira, 3 de março de 2020 16:01

Todas as Respostas

  • Deleted
    terça-feira, 3 de março de 2020 16:31
  • Marcelo,

    Tomando como base este trecho da mensagem de erro: "the multi-part identifier could not be bound"

    Fiquei com algumas dúvidas:

    1 - O nome das colunas envolvidas na view estão corretos?

    2 - As tabelas declaradas na View realmente possuem as colunas declaradas?

    --------------------------------------

    Como na declaração das colunas, bem como, das próprias tabelas você esta informando o schema DBO, talvez, repito talvez uma possibilidade seja declarar no comando de create ou alteração da sua view a instrução With SchemaBinding, com isso estaremos informando o SQL Server que esta view necessita contem o mapeamento e vínculo dos respectivos objetos declarados no código fonte para com seus "donos".

    Tente alterar o código fonte deixando da seguinte forma:

    ALTER VIEW [dbo].[BaseTodaView]
    With Schemabinding -- Adicionando o Schemabinding --

    AS SELECT [Customer BFG ID], [Site BFG ID], CNS, [Global Circuit], QREF, [BT ID - SR], ID, [ID (upgrade/downgrade)], [BT Product Name], [Access Type], (continua...) FROM dbo.BaseTodaReport ..........


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 3 de março de 2020 17:42