August 3, 2014 at 1:56 am
[font="Courier New"]Hi,
My following query:
SELECT EmpID, Name, WorkDate, IsNull(Status,'*'), IsNull(WkdHrs,0) from Timecards
fetches following result:
00010 JOSEPH 10/07/2014 P 8
05200 ANTHONY 10/07/2014 P 8
35359 OMEGA 10/07/2014 Null 0
How can I replace 'Null' in column Status to show '*' since IsNull() functions fails for varchar whereas IsNull() works fine for column WkdHrs which is numeric.
Thanks.[/font][font="System"][/font][font="Courier New"][/font]
August 3, 2014 at 3:24 am
I can't reproduce the error you're getting:
create table #t (EmpID char(5), Name varchar(10), WorkDate date,Status varchar(1),WkdHrs int)
insert into #t
values
('00010', 'JOSEPH', '20140710','P', 8),
('05200', 'ANTHONY', '20140710','P', 8),
('35359', 'OMEGA', '20140710', Null, 0)
SELECT EmpID, Name, WorkDate, IsNull(Status,'*'), IsNull(WkdHrs,0) from #t
drop table #t
August 3, 2014 at 4:52 am
Hi Lutz,
I never mentioned that I'm getting any error.
My problem is that Null value of a varchar field should show a value of my choice. In this case I like to see an asterisk where the value is Null in the Status column. Hope it's clear.
August 3, 2014 at 4:56 am
Sorry for the wrong term "error".
When running the code I posted I get the expected result you're looking for.
So, to rephrase my response:
Running the code I posted I get the following result:
00010 JOSEPH 10/07/2014 P 8
05200 ANTHONY 10/07/2014 P 8
35359 OMEGA 10/07/2014 * 0
Did you try to run the code I posted and the result this code returns?
August 3, 2014 at 5:01 am
Using Lutz's code with the addition of NULLIF to also handle literal 'NULL' value
😎
create table #t (EmpID char(5), Name varchar(10), WorkDate date,Status varchar(5),WkdHrs int)
insert into #t
values
('00010', 'JOSEPH', '20140710','P', 8),
('05200', 'ANTHONY', '20140710','P', 8),
('35359', 'OMEGA', '20140710', 'Null', 0),
('35350', 'ALPHA', '20140801', Null, 0)
SELECT EmpID, Name, WorkDate, IsNull(NULLIF(Status,'NULL'),'*'), IsNull(WkdHrs,0) from #t
drop table #t
Results
EmpID Name WorkDate
----- ---------- ---------- ----- -----------
00010 JOSEPH 2014-07-10 P 8
05200 ANTHONY 2014-07-10 P 8
35359 OMEGA 2014-07-10 * 0
35350 ALPHA 2014-08-01 * 0
August 3, 2014 at 5:45 am
Hi Lutz,
Your query is the same as I originally posted. What may be confusing you is that in the data table, the last record contains null for WkdHrs and Status. The query shows WkdHrs correctly as 0 but Status shows null in the query.
However, I have tried the reply sent by another forum member which works ok.
Thanks for your effort.
August 3, 2014 at 5:52 am
The only modification I made to Lutz's code was to add the NULLIF, set to return NULL (Empty/Unknown) if the string value 'NULL' (four letter word) appears in the column.
😎
August 3, 2014 at 6:13 am
This thread is an excellent example of "logic guessing" because of missing sample data. :angry:
I didn't consider the option that an empty column would be represented by the string 'NULL' instead of being NULL (= empty).
I ruled out this option because of -again- guessing the data type of the column [Status] as VARCHAR(1) (which should be CHAR(1) anyway...) based on the sample data provided ('P').
If you had bother to provide tabel definition and sample data in any way or at least corrected my guessed data type and insert statement the reason for the unexpected result would have become obvious right away.
Instead you decided to hide any helpful information and expected some other guesses (which luckily have been provided by Eirikur).
I'm not confused at all. How should I be confused that your data table contain Null values as well as 'Null' values??? You didn't bother at all to provide any data to show that!
The solution I provided just doesn't match the table def and sample data you're using in your query.
And you failed completely to identify the mismatch and to post correct data.
August 3, 2014 at 6:18 am
Hi Eirikur Eiriksson,
Thanks for your response.
Your suggestion to use ISNULL(NULLIF (STATUS, 'NULL'), '*') works fine when I run a query on a View where the View contain NULL values. But when I create a view with your suggested solution, the data in View shows missing values as NULL. When I use the View in Crystal Report, I get errors as probably CR does not recognize NULL values or maybe I have to find a way to fix this problem in CR.
Thanks again.
August 3, 2014 at 6:26 am
Again: please clarify what you mean by "Null values"!
The best was to explain it is to provide ready to use sample data as described in the first link in my signature and also as shown by myself as well as by Eirikur.
Please don't waste our time by letting us guess what the data look like. Thank you.
August 3, 2014 at 6:42 am
Further on Lutz's last post, the solution to your problem is down to the actual, literal and exact data, at this point without the actual, literal and exact data sample, any effort is futile. We cannot see anything on your side but your post, and guessing isn't a part of the SQL Standard.
😎
August 3, 2014 at 7:31 am
Hi Lutz,
As my nickname describes, I am very new to the forum and to SQL, so please do not get upset. Maybe I have to learn how to post a query.
My table schema is:
EmpID varchar(5)
Name varchar(5)
WorkDate date
Status varchar(1)
WkdHrs numeric(4,1)
Sorry for any misunderstanding.
When I say 'Null values' it means the values returned by the View shows NULL in the status column.
August 3, 2014 at 7:54 am
New_2SQL (8/3/2014)
Hi Lutz,As my nickname describes, I am very new to the forum and to SQL, so please do not get upset. Maybe I have to learn how to post a query.
My table schema is:
EmpID varchar(5)
Name varchar(5)
WorkDate date
Status varchar(1)
WkdHrs numeric(4,1)
Sorry for any misunderstanding.
When I say 'Null values' it means the values returned by the View shows NULL in the status column.
Still not enough to help. Everyone has been doing their best shooting in the dark to help you. Now it is your turn to help us help you. Please read the first article I have linked in my signature block below. It will walk you step by step on the items you need to post and how to post them to get the best answers possible.
We can't see the things you can see, and guessing at what you mean by NULL isn't helping. You could mean an actual NULL value, or that an empty string '' is a null or the string value 'null' is a null value. By showing us actual sample data that represents your problem we will have a better idea as to what you are trying to accomplish.
August 3, 2014 at 8:12 am
Please to read the links Lynn provided, it makes things so much easier if one has a proper structure and data sample.
For the fun of it (and feeling lucky as in Lottery) here is another guess
😎
create table #t (EmpID char(5), Name varchar(10), WorkDate date,Status varchar(5),WkdHrs int)
insert into #t
values
('00010', 'JOSEPH', '20140710','P', 8),
('05200', 'ANTHONY', '20140710','P', 8),
('35359', 'OMEGA', '20140710', 'Null', 0),
('35350', 'ALPHA', '20140801', Null, 0),
('35351', 'BETA', '20140801', '', NULL)
SELECT EmpID, Name, WorkDate, IsNull(NULLIF(NULLIF(Status,'NULL'),''),'*'), IsNull(WkdHrs,0) from #t
drop table #t
This one is the same as before with the addition of a handling of an empty string, as noted by Lynn.
You might want to run the following query and post the relevant results
SELECT DISTINCT
CHAR(124) + Status + CHAR(124)
FROM [the table name]
August 4, 2014 at 7:40 am
Hi Luts,
Sorry for inconvenience.
I'm working on a project of timekeeping for my company. I have created SQL data tables and a view in SQL. There are particularly 2 tables used whose schema is:
Table Personnel:
Column-nameData-type Allow NullIndex
EMP_CODE VARCHAR(5)FalsePrimary
NAMEVARCHAR(50)False
Data in Personnel:
00010, JOSEPH
02535, PETER
50500, MATHEW
Table TS:
EMP_CODE VARCHAR(5) FalsePrimary
CARD_DATEDATE False
HOURSNumeric True
STATUSVARCHAR(1)
Data in TS:
00010,2014-08-01,8,N
00010,2014-08-02,8,N
02535,2014-08-01,9,N
02535,2014-08-02,8,N
02535,2014-08-03,10,N
50500,2014-08-01,0,A
50500,2014-08-03,8,N
Query in Veiw TEST:
[font="Arial"]SELECT DISTINCT
dbo.TS.EMP_CODE, dbo.Personnel.NAME, UPPER(SUBSTRING(DATENAME(month, dbo.TS.CARD_DATE), 1, 3)) + SUBSTRING(CONVERT(varchar(4),
YEAR(dbo.TS.CARD_DATE)), 3, 2) AS Month,
(SELECT ISNULL(SUM(HOURS), 0) AS [1]
FROM dbo.TS AS TS1
WHERE (EMP_CODE = dbo.TS.EMP_CODE) AND (DAY(CARD_DATE) = 1) AND (DATENAME(month, CARD_DATE) = DATENAME(month, dbo.TS.CARD_DATE)) AND
(YEAR(CARD_DATE) = YEAR(dbo.TS.CARD_DATE))) AS [1],
(SELECT ISNULL(SUM(HOURS), 0) AS [2]
FROM dbo.TS AS TS2
WHERE (EMP_CODE = dbo.TS.EMP_CODE) AND (DAY(CARD_DATE) = 2) AND (DATENAME(month, CARD_DATE) = DATENAME(month, dbo.TS.CARD_DATE)) AND
(YEAR(CARD_DATE) = YEAR(dbo.TS.CARD_DATE))) AS [2],
(SELECT ISNULL(SUM(HOURS), 0) AS [3]
FROM dbo.TS AS TS3
WHERE (EMP_CODE = dbo.TS.EMP_CODE) AND (DAY(CARD_DATE) = 3) AND (DATENAME(month, CARD_DATE) = DATENAME(month, dbo.TS.CARD_DATE)) AND
(YEAR(CARD_DATE) = YEAR(dbo.TS.CARD_DATE))) AS [3],
(SELECT STATUS AS S1
FROM dbo.TS AS TS1
WHERE (EMP_CODE = dbo.TS.EMP_CODE) AND (DAY(CARD_DATE) = 1) AND (DATENAME(month, CARD_DATE) = DATENAME(month, dbo.TS.CARD_DATE)) AND
(YEAR(CARD_DATE) = YEAR(dbo.TS.CARD_DATE))) AS S1,
(SELECT STATUS AS S2
FROM dbo.TS AS TS2
WHERE (EMP_CODE = dbo.TS.EMP_CODE) AND (DAY(CARD_DATE) = 2) AND (DATENAME(month, CARD_DATE) = DATENAME(month, dbo.TS.CARD_DATE)) AND
(YEAR(CARD_DATE) = YEAR(dbo.TS.CARD_DATE))) AS S2,
(SELECT STATUS AS S3
FROM dbo.TS AS TS3
WHERE (EMP_CODE = dbo.TS.EMP_CODE) AND (DAY(CARD_DATE) = 3) AND (DATENAME(month, CARD_DATE) = DATENAME(month, dbo.TS.CARD_DATE)) AND
(YEAR(CARD_DATE) = YEAR(dbo.TS.CARD_DATE))) AS S3
FROM dbo.TS INNER JOIN
dbo.Personnel ON dbo.TS.EMP_CODE = dbo.Personnel.EMP_CODE
GROUP BY dbo.TS.EMP_CODE, dbo.TS.CARD_DATE, dbo.Personnel.NAME
[/font]
The View TEST gives me the result as:
EMP_CODE NAMEMonth123S1S2S3
00010JOSEPHAUG148.08.00.0NNNULL
02535PETERAUG149.08.010.0NNN
50500MATHEWAUG140.00.08.0ANULLN
The data is shown here is for 3 days of the month for test purpose. The function ISNULL(SUM(HOURS), 0) in the View TEST takes care of showing a zero and shows zero where the hours are missing. But the status shows NULL where data is missing. I would like to show an asterisk in place of NULL shown against status. This is because the crystal report hangs when it find a NULL in the data.
N=Normal working Hours
A=Absent
Thanks for your efforts.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply