Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183

    Unhappy Unanswered: Select distinct help?

    Can you have "Select Distinct" in Union Query,because that is what I am trying to do and this is the error message I get.

    "The text, ntext, or image data type cannot be selected as DISTINCT."

    I would need to do that because i have duplicate records,because these records are getting written into the db when templates are generated and sometimes if they double click it generates two and writes that many results as well, so that is why I was thinking that select distinct would solve my problem.


    Thanks for your help

    This is the query in question:



    SELECT Distinct 'O' AS Origin, a.RecordID, a.RelocateID, a.SupplierID, a.DateIn, a.DateOut, a.NoOfDays, a.AgreeAmt, a.PaymentMethod, a.AccomType, a.Reason,
    a.InvRecvd, a.RelocateeTempAccomTS, a.BedConfiguration, a.NumberOfPax, a.AdditionalItems, a.Currency, a.TotalAmount, a.EnteredBy,
    a.LastModifiedBy, a.ReferenceNumber, a.Location, a.Comments, a.ArrivalTime, a.PONumber,CommissionRate, ISNULL
    ((SELECT TOP 1 ExchangeRateToUSD
    FROM luCurrencyExchangeRates c
    WHERE a.Currency = c.CurrencyID AND a.DateIn >= c.ActiveDate), 1.0) AS ForeignExchangeRate, ISNULL
    ((SELECT TOP 1 ExchangeRateToUSD
    FROM luCurrencyExchangeRates c
    WHERE 'AUD' = c.CurrencyID AND a.DateIn >= c.ActiveDate), 1.0) AS AUDExchangeRate, a.WhenConfirmed, e.RequestID AS RequestID,
    e.DocumentID AS DocRequestID, e.RequestWhen AS RequestWhen, e.WhereClause AS WhereClause,
    dbo.luDecisionMaker.DecisionMakerName AS DecisionMadeBy, dbo.viewZYesno.Description AS CommissionableDesc
    FROM dbo.RelocateeTempAccom a LEFT OUTER JOIN
    dbo.luDecisionMaker ON a.DecisionMaker = dbo.luDecisionMaker.DecisionMakerID LEFT OUTER JOIN
    dbo.viewZYesno ON a.Commissionable = dbo.viewZYesno.[Value] LEFT OUTER JOIN
    dbo.docRequests e ON '{RelocateeTempAccom.RecordID}=' + CONVERT(VARCHAR a.RecordID) = e.WhereClause
    WHERE (ISNULL(a.Cancelled, 0) = 0)

    UNION ALL

    SELECT Distinct 'D' AS Origin, RecordID, RelocateID, DTASupplierID AS SupplierID, DTADateIn AS DateIn, DTADateOut AS DateOut, DTANoOfDays AS NoOfDays,
    DTAAgreeAmt AS AgreeAmt, DTAPaymentMethod AS PaymentMethod, DTAAccomType AS AccomType, Reason, InvRecvd,
    RelocateeDTATS AS RelocateeTempAccomTS, BedConfiguration, NumberOfPax, AdditionalItems, Currency, DailyTotal AS TotalAmount, EnteredBy,
    LastModifiedBy, ReferenceNumber, Location, Comments, ArrivalTime, PONumber,CommissionRate, ISNULL
    ((SELECT TOP 1 ExchangeRateToUSD
    FROM luCurrencyExchangeRates d
    WHERE b.Currency = d .CurrencyID AND b.DTADateIn >= d .ActiveDate), 1.0) AS ForeignExchangeRate, ISNULL
    ((SELECT TOP 1 ExchangeRateToUSD
    FROM luCurrencyExchangeRates d
    WHERE 'AUD' = d .CurrencyID AND b.DTADateIn >= d .ActiveDate), 1.0) AS AUDExchangeRate, WhenConfirmed, e.RequestID AS RequestID,
    e.DocumentID AS DocRequestID, e.RequestWhen AS RequestWhen, e.WhereClause AS WhereClause,
    dbo.luDecisionMaker.DecisionMakerName AS DecisionMadeBy, dbo.viewZYesno.Description AS CommissionableDesc
    FROM dbo.RelocateeDTA b LEFT JOIN
    dbo.luDecisionMaker ON b.DecisionMaker = dbo.luDecisionMaker.DecisionMakerID LEFT JOIN
    dbo.viewZYesno ON b.Commissionable = dbo.viewZYesno.[Value] LEFT OUTER JOIN
    dbo.docRequests e ON '{RelocateeDTA.RecordID}=' + CONVERT(VARCHAR, b.RecordID) = e.WhereClause
    WHERE ISNULL(Cancelled, 0) = 0
    Last edited by zobernjik; 01-19-05 at 18:37.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Distinct will not work with TEXT datatypes in any query, UNION or not. When you define a column as text, all that is stored with the record is a pointer to where the text blob is located. SQL Server would have to follow each pointer to its target and compare potentially massive strings to determine a unique result, and it is not set up to do that.

    Change your TEXT datatype to varchar(4000) (...or shorter...) if at all possible.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    It seems you're sacrificing a ton of performance to handle a double-click mistake in the database.

    How 'bout catching and disabling the double-click in the application?

    If you slow your application down further, you're gonna have triple-click then quadruple-click then quintuple-click then sextuple-click then ... problems.

  4. #4
    Join Date
    Dec 2004
    Posts
    47
    Yes.......... I had this problem once but I never tried to do a select distinct in this case, First I tried to clear the duplicates... but again and again I was getting the duplicates and I was not able to identify the bug... Finally i could find that its bcos when the appln is slow the user double or triple or .... clicks the Submit button........

    I added 1 line code in front-end to catch this,

    FormName.action = "proj_res_add.php";
    FormName.Submit.disabled = true; --The code added to discard double click events
    FormName.submit();

    Once submit happens I disable the submit button......

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •