"Unable to resolve column" in DataGrip wrt MySql statement for "Select fields into variable from table where conditions"

Answered

I have been searching for many hours.  I have tried many things.  Then it dawned on me that some statements are resolving and some are not.  For example:

This code runs without a problem, but the errors are:

There are not errors from the update statement.  I tired typing the code into a console:

and there were no errors.

It must be something I am overlooking.

8 comments
Comment actions Permalink

Also.  Just upgraded DataGrip to the new release.  That did not fix it.

1
Comment actions Permalink

Version for DataGrip is:

DataGrip 2021.1.3
Build #DB-211.7442.53, built on June 7, 2021
Licensed to Bill Linder
You have a perpetual fallback license for this version.
Subscription is active until June 8, 2022.
Runtime version: 11.0.11+9-b1341.57 amd64
VM: OpenJDK 64-Bit Server VM by JetBrains s.r.o.
Windows 10 10.0
GC: G1 Young Generation, G1 Old Generation
Memory: 2048M
Cores: 8

0
Comment actions Permalink

Bill Linder,

Could you provide full DDL to reproduce the issue?

0
Comment actions Permalink

Thank you for helping me with this.

 

The DDL for the containers table from DataGrip, minus comments due to company rules:

create table containers
(
idx bigint unsigned auto_increment
primary key,
containerID varchar(26) null,
type varchar(1) null,
workorder varchar(18) null,
number int unsigned default 1 not null,
split int unsigned default 0 not null,
status varchar(4) null,
current_op varchar(5) null,
current_loc varchar(24) null,
part varchar(18) null,
quantity int null,
sched_start_time datetime null,
sched_finish_time datetime null,
last_update timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP,
department varchar(8) null,
parent varchar(26) null,
source varchar(26) null,
last_operation datetime null,
verified_on datetime null,
verified_by varchar(8) null,
detail_check_on datetime null,
detail_check_by varchar(8) null,
flagNetChildren tinyint default 0 null,
unitType varchar(2) null,
workorderID varchar(8) null,
create_time time(6) null,
create_date date null,
create_user varchar(8) null,
expire_time time(6) null,
expire_date date null,
expire_user varchar(8) null,
woid varchar(8) null,
containerType varchar(8) null,
lot_ser varchar(18) null,
free_move tinyint null,
traveller_image varchar(300) null,
inspected tinyint null,
inspected_by varchar(8) null,
inspected_on date null,
inspected_at time null,
inspection_passed tinyint null,
qty_complete int null,
qty_scrapped int null,
top varchar(26) null,
serial varchar(12) null,
deleted_qty int null,
deleted_date datetime null,
deleted_by varchar(8) null,
deleted_area varchar(8) null,
deleted_dept varchar(8) null,
deleted_wkctr varchar(8) null,
deleted_location varchar(24) null,
deleted_op varchar(5) null,
deleted_status varchar(4) null,
deleted_reason varchar(80) null,
erp_status varchar(2) null,
erp_status_update tinyint default 1 null,
erp_status_last_update date null,
scrap_qty int null,
scrap_date datetime null,
scrap_by varchar(8) null,
scrap_area varchar(8) null,
scrap_dept varchar(8) null,
scrap_wkctr varchar(8) null,
scrap_location varchar(24) null,
scrap_op varchar(5) null,
scrap_status varchar(4) null,
scrap_reason varchar(80) null,
constraint container
unique (containerID, workorder, part),
constraint containerID_UNIQUE
unique (containerID)
);

create index WOID
on containers (woid);

create index containerID
on containers (containerID);

create index locations
on containers (current_loc);

create index lot
on containers (workorder);

create index part
on containers (part);

create index serial
on containers (serial);

create index status
on containers (status);

create index workorder
on containers (workorder);

 

0
Comment actions Permalink

Here is the scrubbed up (had to remove certain comments) version of the stored procedure in question as well:

CREATE PROCEDURE `cart_remove_2`(

thisSessionID BIGINT,
userid VARCHAR(8),
cartid VARCHAR(24),
reason VARCHAR(120),
updateThis INT
)
BEGIN
DECLARE authorized BOOL;
DECLARE woCount INT;
DECLARE wo BOOL;
DECLARE containers INT;
DECLARE locations INT;
DECLARE routings INT;
DECLARE thisLot VARCHAR(18);
DECLARE thisSessionID BIGINT;
DECLARE thisAction VARCHAR(8);
DECLARE thisQuantity INT; -- Container qty only.
DECLARE thisLocation VARCHAR(24);
DECLARE thisOp VARCHAR(5);
DECLARE thisStatus VARCHAR(4);
DECLARE constDeleted VARCHAR(4) DEFAULT "DLTD";
DECLARE thisTimeStamp DATETIME;
DECLARE thisUser VARCHAR(8);

-- This routine zeros out all cart entries in the system

DECLARE program VARCHAR(40) DEFAULT 'cart_remove_2';

SET thisTimeStamp =now();

SET thisSessionID = session_start('','');
select thisSessionID;

CALL log_entry_add(thisSessionID,0,userID,now(),program,'DEBUG',concat('START:',program));
CALL log_entry_add(thisSessionID,0,userID,now(),program,'DEBUG',concat('userid:',userid));
CALL log_entry_add(thisSessionID,0,userID,now(),program,'DEBUG',concat('cartid:',cartid));
CALL log_entry_add(thisSessionID,0,userID,now(),program,'DEBUG',concat('reason:',reason));
CALL log_entry_add(thisSessionID,0,userID,now(),program,'DEBUG',concat('updateThis:',updateThis));


SET thisUser = userid;

CASE updateThis
WHEN 0 THEN SET thisAction = "INSPECT";
WHEN 1 THEN SET thisAction = "DELETE";
WHEN 2 THEN SET thisAction = "RESET";
ELSE SET thisAction = "NOACTION";
END CASE;

select distinct(wr_num) into thisLot FROM wr_route where wr_cart_num = cartid or wr_num = cartid;

SELECT userAuthorize(userid,program) INTO authorized;

mainBlock:BEGIN

if authorized THEN

IF (updateThis = 1) THEN -- CLEARS ALL ENTRIES FOR THE CART/WORK ORDER
-- wr_route

CALL log_entry_add(thisSessionID,0,userID,now(),program,'DEBUG',concat('ACTION:','DELETING'));


start transaction;
-- update wr_route SET wr_cart_number = NULL where wr_cart_num = cartid or wr_num = cartid;

UPDATE wr_route
SET wr_deleted_qty = thisQuantity,
wr_deleted_datetime = thisTimeStamp,
wr_deleted_by = thisUser
WHERE (wr_cart_num = cartid OR wr_num = cartid);

-- delete FROM wr_route where (wr_cart_num = cartid OR wr_num = cartid);

SET routings = ROW_COUNT();

-- containers
select quantity, current_loc, current_op, `status` into thisQuantity, thisLocation, thisOp, thisStatus
from containers where (containerID = cartid or workorder = cartid);
update containers SET deleted_qty = thisQuantity,
deleted_location = thisLocation,
deleted_op = thisOp,
deleted_status = thisStatus,
deleted_by = thisUser,
deleted_date = thisTimeStamp,
quantity = 0,
-- current_loc = "deleted",
current_op = "",
`status` = constDeleted
where (containerID = cartid or workorder = cartid);
-- delete FROM containers where (containerID = cartid or workorder = cartid);

SET containers = ROW_COUNT();

-- location_detail
delete FROM location_detail where (container_idx = cartid or wo_num = cartid );

SET locations = ROW_COUNT();
commit;

CALL log_transactions('', thisSessionID, program, userid, thisAction, 0, 'SYSTEM', 'All ROUTES, CONTAINERS AND LOCATION DETAILS REMOVED', userid, now(), '', thisLot, 'DELETED');

-- containers


-- location_detail
ELSEIF updateThis=2 THEN -- CLEARS ALL RECORDS EXCEPT MASTER AND SETS PRINT DATE TO 0000-00-00


CALL log_entry_add(thisSessionID,0,userID,now(),program,'DEBUG',concat('ACTION:','RESETTING'));

start transaction;
-- update wr_route SET wr_cart_number = NULL where wr_cart_num = cartid;



UPDATE wr_route
SET wr_deleted_qty = thisQuantity,
wr_deleted_datetime = thisTimeStamp,
wr_deleted_by = thisUser
WHERE (wr_cart_num = cartid OR wr_num = cartid)
AND wr_cart_num <> 'MASTER';

SET routings = ROW_COUNT();
update wr_route set wr_date_printed = '0000-00-00' WHERE (wr_cart_num = cartid OR wr_num = cartid) and wr_cart_num = 'MASTER';

-- containers

select quantity, current_loc, current_op, `status` into thisQuantity, thisLocation, thisOp, thisStatus
from containers where (containerID = cartid or workorder = cartid);
update containers SET deleted_qty = thisQuantity,
deleted_location = thisLocation,
deleted_op = thisOp,
deleted_status = thisStatus,
quantity = 0,
-- current_loc = "deleted",
current_op = "",
`status` = constDeleted
where (containerID = cartid or workorder = cartid);

SET containers = ROW_COUNT();

-- location_detail
delete FROM location_detail where (container_idx = cartid or wo_num = cartid);

SET locations = ROW_COUNT();
COMMIT;
CALL log_transactions('', thisSessionID, program, userid, thisAction, 0, 'SYSTEM', 'All CART ROUTES (NOT MASTER), CONTAINERS AND LOCATION DETAILS REMOVED', userid, now(), '', thisLot, 'CLEARED');

ELSEIF updateThis=0 THEN
-- wr_route
CALL log_entry_add(thisSessionID,0,userID,now(),program,'DEBUG',concat('ACTION:','REVIEWING'));

start transaction;
SELECT 'Work Order Routing',wr_cart_num, wr_num, wr_part, wr_cart_qpc, wr_op, wr_date_start, wr_dept, wr_mch, wr_location, wr_queue_in_date, wr_queue_in_amount, wr_queue_out_date, wr_process_date_start, wr_process_date_finish, wr_process_qty
FROM wr_route
where (wr_cart_num = cartid or wr_num = cartid );

SET routings = ROW_COUNT();
-- containers
SELECT 'Container Master',containerID,workorder,status,current_op,current_loc,part,quantity, source, parent
FROM containers
where (containerID = cartid or workorder = cartid );


SET containers = ROW_COUNT();
-- location_detail
SELECT 'Location Detail', area,dept,wkctr,location, container_idx, pt_num, wo_num, quantity, date_change, employee_idx, status_group, status_idx
FROM location_detail
where (container_idx = cartid or wo_num = cartid );


SET locations = ROW_COUNT();
COMMIT;
CALL log_transactions('', thisSessionID, program, userid, thisAction, 0, 'SYSTEM', 'All ROUTES, CONTAINERS AND LOCATION DETAILS REVIEWED', userid, now(), '', thisLot, 'REVIEWED');

END IF;
END IF;
SELECT "SUCCESS" as STATE, 'SUCCESS', 'Cart Removed' as MESSAGE,routings,containers,locations,thisUser;

END mainBlock;

END

0
Comment actions Permalink

Had an idea:  What if I use a table alias?  And it worked. Now, why?  lol

0
Comment actions Permalink

Bill Linder,

For sure, it's an issue. I filed one to up vote and follow.

0
Comment actions Permalink

Thank you Vasily!

0

Please sign in to leave a comment.