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?
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.