CODEWITHSUNDEEP
mysql
Oğuz Çelikdemir
Oğuz Çelikdemir

Reputation: 4980

Check integer column value

I have an integer column in MySQL table which is table empty right now. I want to check that integer column value as follow but doesn't return anything.

SELECT IF(PROMO_NO = '', DATE_FORMAT(CURDATE(), '%y%u'), PROMO_NO) AS PROMO 
FROM PROMO_TYPE_P

// or
SELECT IF(PROMO_NO = 0, DATE_FORMAT(CURDATE(), '%y%u'), PROMO_NO) AS PROMO 
FROM PROMO_TYPE_P

// pehh, I tried this as well which is count value 0 but no success
SELECT IF((SELECT COUNT(*) FROM PROMO_TYPE_P) = 0, DATE_FORMAT(CURDATE(), '%y%u'), PROMO_NO)
AS PROMO FROM PROMO_TYPE_P

I don't understand the problem. Does anybody have any idea?

CREATE TABLE `PROMO_TYPE_P` (
  `PROMO_ID` int(6) UNSIGNED NOT NULL AUTO_INCREMENT,
  `PROMO_TYPE_CODE` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
  `PROMO_NO` int(6) UNSIGNED NOT NULL,
  `PROMO_DESC` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `PROMO_TRIGGER` tinyint(1) NOT NULL,
  `PROMO_TYPE` int(1) UNSIGNED NOT NULL,
  `PROMO_DISCOUNT` int(8) UNSIGNED NOT NULL,
  `PROMO_WORK` int(2) UNSIGNED NOT NULL,
  `PROMO_START_DATE` date NOT NULL DEFAULT '0000-00-00',
  `PROMO_END_DATE` date NOT NULL DEFAULT '0000-00-00',
  `PROMO_START_TIME` time NOT NULL DEFAULT '00:00:00',
  `PROMO_END_TIME` time NOT NULL DEFAULT '00:00:00',
  `PROMO_WORK_DAYS` int(7) UNSIGNED NOT NULL,
  `PROMO_BUNDLE` int(1) UNSIGNED NOT NULL,
  PRIMARY KEY (`PROMO_ID`),
  UNIQUE KEY `IDX_PROMO_NO` (`PROMO_NO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

PS: The queries that I tried must worked but nothing happened. As you can see unique index available in the column so I am not sure the problem related with this. I am using Toad for MySQL free edition.

enter image description here

RESOLVE :

SELECT IF(COUNT(*) = 0, DATE_FORMAT(CURDATE(), '%y%u'), 'DENEME') AS PROMO FROM PROMO_TYPE_P

Upvotes: 0

Views: 476

Answers (2)

echo_Me
echo_Me

Reputation: 37233

you are cheking for empty string while your column is integer.

try this

SELECT IF(PROMO_NO IS NULL, DATE_FORMAT(CURDATE(), '%y%u'), PROMO_NO) AS PROMO 
 FROM PROMO_TYPE_P

EDIT:

SELECT IF(PROMO_NO = '' or PROMO_NO IS NULL or PROMO_NO =0, DATE_FORMAT(CURDATE(), '%y%u'), PROMO_NO) AS PROMO 
FROM PROMO_TYPE_P

edit2.

 SELECT IF(PROMO_NO IS NULL, 'THERE IS NULL VALUE', 'there is no null value') AS PROMO 
 FROM PROMO_TYPE_P

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269543

PROMO_NO is defined as a not-null unsigned integer:

PROMO_NO int(6) UNSIGNED NOT NULL,

In addition, it is declared as unique, so any value would occur on at most one row.

The query in question is:

SELECT IF(PROMO_NO = '', DATE_FORMAT(CURDATE(), '%y%u'), PROMO_NO) AS PROMO 
FROM PROMO_TYPE_P;

It is doing no filtering. So, if it is returning no rows, then it is querying an empty table. Are you sure you are connected to the correct database? Try running:

SELECT *
FROM PROMO_TYPE_P;

I should add that the if statement is mixing strings and integers. I don't think this would result in no rows being returned, unless an error occurs (and you don't mention such an error).

EDIT:

If you want to return the current date if the table is empty and the promo otherwise, then use union all:

select max(DATE_FORMAT(CURDATE(), '%y%u'))
from dual
where not exists (select 1 from promo_type_p)
union all
select promo_no
from promot_type_p;

Upvotes: 1

Related Questions