CODEWITHSUNDEEP
mysqlsqlcolumn-alias
JoSSte
JoSSte

Reputation: 3372

Retyping alias column in mysql query

I am trying to convert some data from old tables to a new structure, where I need to convert a single key ID to composite one, amd it is giving me some trouble:

My table (simplified):

CREATE TABLE `tmplt_spoergsmaal` (
  `SpID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `lbnr` int(10) unsigned DEFAULT NULL,
  `SpTekst` text,
  `SpTitel` varchar(100) NOT NULL DEFAULT '',
  `fk_Naeste_Sp` int(10) unsigned DEFAULT NULL,
  `kontrol` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `kontrol_kilde` int(10) unsigned NOT NULL DEFAULT 0,
  FOREIGN KEY (kontrol_kilde) 
    REFERENCES tmplt_spoergsmaal(SpID)
    ON DELETE ignore
)

Sample data:

Note that SPID is sequential

+--------+--------+-----------+-----------+------------+-----------------+
|  SpID  |  lbnr  |  SpTekst  |  SpTitel  |   kontrol  |  kontrol_kilde  | 
+--------+--------+-----------+-----------+------------+-----------------+
|  9000  |  100   | blablabla | title1    |      0     |     null        |
+--------+--------+-----------+-----------+------------+-----------------+
|  9001  |  101   | blablabla | title2    |      0     |     null        |
+--------+--------+-----------+-----------+------------+-----------------+
|  9002  |  102   | blablabla | title3    |      0     |     null        |
+--------+--------+-----------+-----------+------------+-----------------+
|  9003  |  103   | blablabla | title4    |      1     |       9000      |
+--------+--------+-----------+-----------+------------+-----------------+
|  9004  |  104   | blablabla | title5    |      1     |       9001      |
+--------+--------+-----------+-----------+------------+-----------------+

I am redesigning the database, and using the lbnr column instead of the kontrol_kilde column. My preliminary query is this:

SELECT spid, lbnr, kontrol, kontrol_kilde, (lbnr- (spid - kontrol_kilde)* kontrol)* kontrol AS k
FROM tmplt_spoergsmaal;

This solves my issue, but an issue cropped up at one point (because of a flip of the subtraction (spid - kontrol_kilde had become kontrol_kilde - spid) which made part of the equation negative. Since the column is unsigned,this caused an error:

Error Code: 1690. BIGINT UNSIGNED value is out of range in

My question:

Can I "cast" the columns in the alias column k so that it is an int instead of unsigned int ?

Upvotes: 0

Views: 136

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Well, you can cast() as signed:

SELECT spid, lbnr, kontrol, kontrol_kilde,
       cast(lbnr - (spid - kontrol_kilde) * kontrol)* kontrol as signed) AS k
FROM tmplt_spoergsmaal;

Upvotes: 1

Related Questions