The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

 CREATE TABLE Temp_LKLTN(  ID int,  
 ToolNumber int,  
 LastOfLocation Varchar(20),  
 MaxOfUploadTime DATETIME,  
 JobType Varchar(10),  
 Company Varchar(50),  
 Rig Varchar(50),  
 RigNumber Varchar(20),  
 WellName Varchar(50),  
 City Varchar(50),  
 State Varchar(60)  
 );  
INSERT INTO temp_lkltn 
            (toolnumber, 
             lastoflocation, 
             maxofuploadtime, 
             jobtype) 
SELECT toolassemblies.toolnumber                AS ToolNumber, 
       Max(toolassemblies.defaultlocationindex) AS 'LastOfLocation', 
       Max(toolassemblies.uploadtime)           AS 'MaxOfUploadTime', 
       'Assembly'                               AS JobType 
FROM   toolassemblies 
GROUP  BY toolassemblies.uploadtime, 
          toolassemblies.toolnumber 
ORDER  BY toolassemblies.uploadtime DESC; 

INSERT INTO temp_lkltn 
            (toolnumber, 
             lastoflocation, 
             maxofuploadtime, 
             jobtype) 
SELECT iis.serialnumber    AS 'ToolNumber', 
       l.location          AS 'LastOfLocation', 
       Max(iis.uploaddate) AS 'MaxOfUploadTime', 
       ''                  AS JobType 
FROM   inventory_serializedassets AS iis 
       JOIN locations AS l 
         ON l.locationindex = iis.locationid 
GROUP  BY iis.serialnumber, 
          iis.tooltype, 
          l.location  <---------------------------------------------------HERE 
HAVING iis.tooltype = 'TN'  

Open in new window

LVL 6
r3nderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Well, need to guess, cause you forgot the table DDL..

The column locations.location has a data type of TEXT, NTEXT or IMAGE. In this case you cannot group by this column.

When it's a TEXT/NTEXT column, then change the data type to [N]VARCHAR(MAX).

Caveat: depening on the IO necessary, changing the data can have a huge impact on your system.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
r3nderAuthor Commented:
I only posted part of the code
USE firetalk_test 

IF Object_id('Temp_LKLTN', 'U') IS NOT NULL 
  DROP TABLE temp_lkltn; 

CREATE TABLE temp_lkltn 
  ( 
     id              INT, 
     toolnumber      INT, 
     lastoflocation  VARCHAR(20), 
     maxofuploadtime DATETIME, 
     jobtype         VARCHAR(10), 
     company         VARCHAR(50), 
     rig             VARCHAR(50), 
     rignumber       VARCHAR(20), 
     wellname        VARCHAR(50), 
     city            VARCHAR(50), 
     state           VARCHAR(60) 
  ); 

INSERT INTO temp_lkltn 
            (toolnumber, 
             lastoflocation, 
             maxofuploadtime, 
             jobtype) 
SELECT sa.serialnumber                     AS 'ToolNumber', 
       Max(jobdownholeequipment.jobid)     AS 'LastOfLocation', 
       Max(jobdownholeequipment.starttime) AS 'MaxOfUploadTime', 
       'Job'                               AS JobType 
FROM   serializedassets AS sa 
       INNER JOIN jobdownholeequipment 
               ON sa.serialnumber = jobdownholeequipment.toolnumber 
GROUP  BY sa.serialnumber, 
          sa.tooltype 
HAVING sa.tooltype = 'TN'; 

INSERT INTO temp_lkltn 
            (toolnumber, 
             lastoflocation, 
             maxofuploadtime, 
             jobtype) 
SELECT toolassemblies.toolnumber                AS ToolNumber, 
       Max(toolassemblies.defaultlocationindex) AS 'LastOfLocation', 
       Max(toolassemblies.uploadtime)           AS 'MaxOfUploadTime', 
       'Assembly'                               AS JobType 
FROM   toolassemblies 
GROUP  BY toolassemblies.uploadtime, 
          toolassemblies.toolnumber 
ORDER  BY toolassemblies.uploadtime DESC; 

INSERT INTO temp_lkltn 
            (toolnumber, 
             lastoflocation, 
             maxofuploadtime, 
             jobtype) 
SELECT iis.serialnumber    AS 'ToolNumber', 
       l.location          AS 'LastOfLocation', 
       Max(iis.uploaddate) AS 'MaxOfUploadTime', 
       ''                  AS JobType 
FROM   inventory_serializedassets AS iis 
       JOIN locations AS l 
         ON l.locationindex = iis.locationid 
GROUP  BY iis.serialnumber, 
          iis.tooltype, 
          l.location <-------------------- here 
HAVING iis.tooltype = 'TN'; 

UPDATE tl 
SET    tl.company = j.company, 
       tl.rig = j.rig, 
       tl.rignumber = j.rignumber, 
       tl.wellname = j.wellname, 
       tl.city = j.city, 
       tl.state = j.state 
FROM   temp_lkltn AS tl 
       INNER JOIN job AS j 
               ON tl.lastoflocation = j.jobid 
WHERE  tl.lastoflocation = j.jobid 
       AND j.company <> 'Test Company'; 

UPDATE tl2 
SET    tl2.lastoflocation = l.location 
FROM   temp_lkltn AS tl2 
       INNER JOIN locations AS l 
               ON tl2.lastoflocation = l.locationindex 

SELECT toolnumber, 
       lastoflocation, 
       maxofuploadtime, 
       jobtype, 
       company, 
       rig, 
       rignumber, 
       wellname, 
       city, 
       state 
FROM   temp_lkltn 
WHERE  maxofuploadtime IN (SELECT Max(maxofuploadtime) 
                           FROM   temp_lkltn 
                           GROUP  BY toolnumber) 
ORDER  BY toolnumber ASC   

Open in new window

0
ste5anSenior DeveloperCommented:
Nope. The declaration of locations, inventory_serializedassets, serializedassets, jobdownholeequipment and toolassemblies is missing.
0
r3nderAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.