I have an application that lists in a grid files that are registered in the table
SCF_Process Documentation
However, now I need to list only if this file also exists in the table
SCF_ARQUIVO_UPLOAD
What the application does is fetch the file in the first table by the file type ID and list it. What it needs to do now is to get the ID, pick up the document path that is in the Path_Document column of the first table, then compare to the PathPartial, if it does, list them all.
I'm doing the query like this, but it's not listing anything:
ALTER PROC [dbo].[sp_RecuperaDocumentosProcesso] --2
@id_ProcessoCompra INT
AS
SELECT
id_ProcessoCompra
,Path_Documento
FROM SCF_DocumentoProcessoCompra DPC
LEFT JOIN SCF_ARQUIVO_UPLOAD SAP
ON DPC.id_DocumentoProcessoCompra = SAP.Id_Arquivo
WHERE DPC.Path_Documento LIKE '%' + SAP.CaminhoParcial + '%'
Create the 2 tables:
USE [SCF2_HOMOLOG_325]
GO
/****** Object: Table [dbo].[SCF_DocumentoProcessoCompra] Script Date: 24/01/2018 16:42:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SCF_DocumentoProcessoCompra](
[id_DocumentoProcessoCompra] [int] IDENTITY(1,1) NOT NULL,
[id_ModeloDocumento] [int] NULL,
[Desc_DocumentoProcessoCompra] [varchar](80) NULL,
[Path_Documento] [varchar](300) NULL,
[Dt_PublicacaoDocumento] [datetime] NULL,
[Dt_CadastroDocumento] [datetime] NULL,
[USR_CadastroDocumento] [varchar](20) NULL,
[id_ProcessoCompra] [int] NULL,
[id_TipoDocumento] [int] NULL,
[FormatoDocumento] [smallint] NULL,
[Texto_FinalDocumento] [text] NULL,
[id_TipoModeloDocumento] [int] NULL,
[ID_TIPOARQUIVO] [int] NULL,
CONSTRAINT [PK__SCF_DocumentoPro__29572725] PRIMARY KEY CLUSTERED
(
[id_DocumentoProcessoCompra] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[SCF_DocumentoProcessoCompra] WITH CHECK ADD CONSTRAINT [FK__SCF_Docum__id_Pr__55009F39] FOREIGN KEY([id_ProcessoCompra])
REFERENCES [dbo].[SCF_ProcessoCompra] ([id_ProcessoCompra])
GO
ALTER TABLE [dbo].[SCF_DocumentoProcessoCompra] CHECK CONSTRAINT [FK__SCF_Docum__id_Pr__55009F39]
GO
ALTER TABLE [dbo].[SCF_DocumentoProcessoCompra] WITH CHECK ADD CONSTRAINT [FK__SCF_Docum__id_Ti__55F4C372] FOREIGN KEY([id_TipoDocumento])
REFERENCES [dbo].[SCF_TipoDocumento] ([id_TipoDocumento])
GO
ALTER TABLE [dbo].[SCF_DocumentoProcessoCompra] CHECK CONSTRAINT [FK__SCF_Docum__id_Ti__55F4C372]
GO
ALTER TABLE [dbo].[SCF_DocumentoProcessoCompra] WITH CHECK ADD FOREIGN KEY([ID_TIPOARQUIVO])
REFERENCES [dbo].[SCF_TiposDeArquivos] ([ID_TIPOARQUIVO])
GO
ALTER TABLE [dbo].[SCF_DocumentoProcessoCompra] WITH CHECK ADD CONSTRAINT [FK_SCF_DocProcesso_ModeloDocumento] FOREIGN KEY([id_ModeloDocumento])
REFERENCES [dbo].[SCF_ModeloDocumentos] ([id_ModeloDocumento])
GO
ALTER TABLE [dbo].[SCF_DocumentoProcessoCompra] CHECK CONSTRAINT [FK_SCF_DocProcesso_ModeloDocumento]
GO
ALTER TABLE [dbo].[SCF_DocumentoProcessoCompra] WITH CHECK ADD CONSTRAINT [FK_SCF_id_TipoModeloDocumento] FOREIGN KEY([id_TipoModeloDocumento])
REFERENCES [dbo].[SCF_TipoModeloDocumento] ([id_TipoModeloDocumento])
GO
ALTER TABLE [dbo].[SCF_DocumentoProcessoCompra] CHECK CONSTRAINT [FK_SCF_id_TipoModeloDocumento]
GO
USE [SCF2_HOMOLOG_325]
GO
/****** Object: Table [dbo].[SCF_ARQUIVO_UPLOAD] Script Date: 24/01/2018 16:42:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SCF_ARQUIVO_UPLOAD](
[Id_Arquivo] [bigint] IDENTITY(1,1) NOT NULL,
[CaminhoParcial] [varchar](max) NOT NULL,
[ArquivoBinario] [varbinary](max) NOT NULL,
[Dt_HoraUpload] [datetime] NOT NULL,
[Id_Usuario] [int] NULL,
CONSTRAINT [PK_SCF_Arquivo_Upload] PRIMARY KEY CLUSTERED
(
[Id_Arquivo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[SCF_ARQUIVO_UPLOAD] WITH CHECK ADD CONSTRAINT [FK_SCF_Arquivo_Upload_ACS_Usuario1] FOREIGN KEY([Id_Usuario])
REFERENCES [dbo].[ACS_Usuario] ([id_Usuario])
GO
ALTER TABLE [dbo].[SCF_ARQUIVO_UPLOAD] CHECK CONSTRAINT [FK_SCF_Arquivo_Upload_ACS_Usuario1]
GO