none
Forma de distinguir sem ser Distinct RRS feed

  • Pergunta

  • Pessoal estou com um provbelma na query abaixo que é o seguinte, esta trazendo o registor 4x depois que tirei uma das chaves primarias da clausura WHERE, pois o cliente quer que usque em todas safes.

    SELECT  SQ.TypeID, SQ.ChurchID, SQ.SafeID, SQ.QuestionID, SQ.UserID , SQ.Status, S.Created, SQ.Observation, Q.QuestionDescription, 
    CASE WHEN SQ.Status <> I.Status THEN ' ' ELSE I.InformationDescription  END AS InformationDescription
    FROM         SafeQuestion AS SQ INNER JOIN
                          Information AS I ON SQ.TypeID = I.TypeID AND SQ.QuestionID = I.QuestionID INNER JOIN
                          Question AS Q ON SQ.TypeID = Q.TypeID AND SQ.QuestionID = Q.QuestionID INNER JOIN
                          Safe AS S ON SQ.ChurchID = S.ChurchID
    WHERE    (SQ.ChurchID = 1024) AND (SQ.TypeID = 4) AND ((Q.QuestionDescription LIKE '%anci%') OR(I.InformationDescription LIKE '%anci%'))
    ORDER BY SQ.SafeID, Sq.QuestionID

    Preciso saber se consigo usar uma forma de distrinção sme ser o distinct pois quando estou usando o distinct me retorna o seguinte erro!

    Msg 421, Level 16, State 1, Line 1
    The text data type cannot be selected as DISTINCT because it is not comparable.
    Msg 421, Level 16, State 1, Line 1
    The text data type cannot be selected as DISTINCT because it is not comparable.

    Att.


    Moizés Cerqueira | MCP
    Site: www.moizes.cerqueira.nom.br
    Twitter: @moicer

    quinta-feira, 5 de abril de 2012 17:49

Respostas

  • Resolvi usando um case.

    SELECT dbo.Type.TypeDescription, dbo.Type.TypeID, dbo.v_Church.NameChurch, dbo.v_Church.ChurchID, dbo.[User].Name, SQ.SafeID, SQ.QuestionID, 
    CASE WHEN SQ.Status = 0 THEN 'Não' WHEN SQ.Status = 1 THEN 'Sim' END AS Status,Q.QuestionDescription,
    CASE WHEN SQ.Status <> I.Status THEN ' ' ELSE I.InformationDescription  END AS InformationDescription,
    SQ.Observation, S.Created 
    FROM dbo.SafeQuestion AS SQ
    INNER JOIN   dbo.Question AS Q ON SQ.TypeID = Q.TypeID AND SQ.QuestionID = Q.QuestionID 
    LEFT OUTER JOIN   dbo.Information AS I ON SQ.TypeID = I.TypeID AND SQ.QuestionID = I.QuestionID 
    INNER JOIN   dbo.Safe AS S ON SQ.ChurchID = S.ChurchID AND SQ.SafeID = S.SafeID 
    INNER JOIN   dbo.Type ON Q.TypeID = dbo.Type.TypeID 
    INNER JOIN   dbo.v_Church ON S.ChurchID = dbo.v_Church.ChurchID COLLATE Latin1_General_CI_AS 
    INNER JOIN   dbo.[User] ON S.UserID = dbo.[User].UserID  
    WHERE     (SQ.TypeID = 4) AND (SQ.ChurchID = 1024) 
    AND ((Q.QuestionDescription LIKE '%anci%') OR(I.InformationDescription LIKE '%anci%'))
    ORDER BY SQ.SafeID, SQ.QuestionID


    Moizés Cerqueira | MCP
    Site: www.moizes.cerqueira.nom.br
    Twitter: @moicer


    quinta-feira, 5 de abril de 2012 19:17

Todas as Respostas

  • Moizes,


    Antes de tudo, troque seu TEXT para um VARCHAR(MAX) rs..ENfim, qual a versão do SQL Server? Tente agrupar por todos os campos (OBS: Nao sei se funciona com text tambem, precisa testar....)


    Fabrizzio A. Caputo
    MCT
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Blog Empresa: www.tripletech.com.br/blog
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com

    quinta-feira, 5 de abril de 2012 17:53
    Moderador
  • Cara não tme como eu trocar os campos, gostaria de saber se consigo distingir os dados de outra forma.

    Moizés Cerqueira | MCP
    Site: www.moizes.cerqueira.nom.br
    Twitter: @moicer

    quinta-feira, 5 de abril de 2012 18:43
  • Moizes,

    Será que a função NewId() não poderia ser útil?

    DECLARE @myid uniqueidentifier
    SET @myid = NEWID()
    PRINT 'Value of @myid is: '+ CONVERT(varchar(255), @myid)

    CREATE TABLE cust
    (
    CustomerID uniqueidentifier NOT NULL
       DEFAULT newid(),
    Company varchar(30) NOT NULL,
    ContactName varchar(60) NOT NULL,
    Address varchar(30) NOT NULL,
    City varchar(30) NOT NULL,
    StateProvince varchar(10) NULL,
    PostalCode varchar(10) NOT NULL,
    CountryRegion varchar(20) NOT NULL,
    Telephone varchar(15) NOT NULL,
    Fax varchar(15) NULL
    )
    GO
    -- Inserting data into cust table.
    INSERT cust
    (CustomerID, Company, ContactName, Address, City, StateProvince,
    PostalCode, CountryRegion, Telephone, Fax)
    VALUES
    (NEWID(), 'Wartian Herkku', 'Pirkko Koskitalo', 'Torikatu 38', 'Oulu', NULL,
    '90110', 'Finland', '981-443655', '981-443655')
    INSERT cust
    (CustomerID, Company, ContactName, Address, City, StateProvince,
    PostalCode, CountryRegion, Telephone, Fax)
    VALUES
    (NEWID(), 'Wellington Importadora', 'Paula Parente', 'Rua do Mercado, 12', 'Resende', 'SP',
    '08737-363', 'Brasil', '(14) 555-8122', '')
    INSERT cust
    (CustomerID, Company, ContactName, Address, City, StateProvince,
    PostalCode, CountryRegion, Telephone, Fax)
    VALUES
    (NEWID(), 'Cactus Comidas para Ilevar', 'Patricio Simpson', 'Cerrito 333', 'Buenos Aires', NULL,
    '1010', 'Argentina', '(1) 135-5555', '(1) 135-4892')
    INSERT cust
    (CustomerID, Company, ContactName, Address, City, StateProvince,
    PostalCode, CountryRegion, Telephone, Fax)
    VALUES
    (NEWID(), 'Ernst Handel', 'Roland Mendel', 'Kirchgasse 6', 'Graz', NULL,
    '8010', 'Austria', '7675-3425', '7675-3426')
    INSERT cust
    (CustomerID, Company, ContactName, Address, City, StateProvince,
    PostalCode, CountryRegion, Telephone, Fax)
    VALUES
    (NEWID(), 'Maison Dewey', 'Catherine Dewey', 'Rue Joseph-Bens 532', 'Bruxelles', NULL,
    'B-1180', 'Belgium', '(02) 201 24 67', '(02) 201 24 68')
    GO


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    quinta-feira, 5 de abril de 2012 18:47
  • Resolvi usando um case.

    SELECT dbo.Type.TypeDescription, dbo.Type.TypeID, dbo.v_Church.NameChurch, dbo.v_Church.ChurchID, dbo.[User].Name, SQ.SafeID, SQ.QuestionID, 
    CASE WHEN SQ.Status = 0 THEN 'Não' WHEN SQ.Status = 1 THEN 'Sim' END AS Status,Q.QuestionDescription,
    CASE WHEN SQ.Status <> I.Status THEN ' ' ELSE I.InformationDescription  END AS InformationDescription,
    SQ.Observation, S.Created 
    FROM dbo.SafeQuestion AS SQ
    INNER JOIN   dbo.Question AS Q ON SQ.TypeID = Q.TypeID AND SQ.QuestionID = Q.QuestionID 
    LEFT OUTER JOIN   dbo.Information AS I ON SQ.TypeID = I.TypeID AND SQ.QuestionID = I.QuestionID 
    INNER JOIN   dbo.Safe AS S ON SQ.ChurchID = S.ChurchID AND SQ.SafeID = S.SafeID 
    INNER JOIN   dbo.Type ON Q.TypeID = dbo.Type.TypeID 
    INNER JOIN   dbo.v_Church ON S.ChurchID = dbo.v_Church.ChurchID COLLATE Latin1_General_CI_AS 
    INNER JOIN   dbo.[User] ON S.UserID = dbo.[User].UserID  
    WHERE     (SQ.TypeID = 4) AND (SQ.ChurchID = 1024) 
    AND ((Q.QuestionDescription LIKE '%anci%') OR(I.InformationDescription LIKE '%anci%'))
    ORDER BY SQ.SafeID, SQ.QuestionID


    Moizés Cerqueira | MCP
    Site: www.moizes.cerqueira.nom.br
    Twitter: @moicer


    quinta-feira, 5 de abril de 2012 19:17