October 29, 2007 at 9:49 am
Hello all, First after 20 hours of looking at this I am a little brain dead so here goes.
I have 3 tables in a join to pull data, the second table is only to get information from the third table. That's the scenario. I need a case statement or something in there to produce the following
1. record exist in first table
2. record doesn't exist in second table (joined to 3rd table)
Here is what I need (kinda, it's not working)
select
custid,
Case
when ce_individual_license_ext.ixl_expire_date_ext != null then ce_individual_license_ext.ixl_expire_date_ext
else No license or Expired license' end as TestDate
FROM ce_individual_license_ext INNER JOIN
ce_individual_license ON ce_individual_license_ext.ixl_key_ext = ce_individual_license.ixl_key INNER JOIN
co_customer ON ce_individual_license.ixl_ind_cst_key = co_customer.cst_key
October 29, 2007 at 10:05 am
Stephen - unless you override the ANSI NULLS setting (which is not recommended), any arithmetic comparison to NULL is false. so
a=NULL ---> FALSE
A<>NULL --->FALSE
A>NULL --->FALSE, etc....
Just about the only thing you have is to test whether it is null or not, so -
A is NULL
or
A is not NULL
A less preferrable way to do that is to use ISNULL to approximate unknown values to some value you pick, e.g.
Isnull(A, -1)=-1
So - you only need one small change from what I can see (look for the bold). And - there's a missing quote in there...
select
custid,
Case
when ce_individual_license_ext.ixl_expire_date_ext
is not null /*here's the change to the NULL*/
then ce_individual_license_ext.ixl_expire_date_ext
else '/*you also missed this quote*/No license or Expired license' end as TestDate
FROM ce_individual_license_ext INNER JOIN
ce_individual_license ON ce_individual_license_ext.ixl_key_ext = ce_individual_license.ixl_key INNER JOIN
co_customer ON ce_individual_license.ixl_ind_cst_key = co_customer.cst_key
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 29, 2007 at 10:09 am
Here is the result I am getting, I had the original part of is not null in there
Conversion failed when converting datetime from character string
October 29, 2007 at 10:17 am
You have to explicitly convert the datetime ixl_expire_date_ext column to character data type since case statement could only return a value of same data type.
The case statement should look like....
Case when ce_individual_license_ext.ixl_expire_date_ext is not null
then convert( varchar(10), ce_individual_license_ext.ixl_expire_date_ext, 112 )
--You can specify your own style above
else 'No license or Expired license' end
--Ramesh
October 29, 2007 at 10:20 am
That must be because the value ce_individual_license_ext.ixl_expire_date_ext
is a DATETIME field. So - convert it to a varchar(50) so that you can get your error message in there.
i.e.
select
custid,
Case
when ce_individual_license_ext.ixl_expire_date_ext
is not null /*here's the change to the NULL*/
then
convert(varchar(50), ce_individual_license_ext.ixl_expire_date_ext,102)
else 'No license or Expired license' end as TestDate
FROM ce_individual_license_ext INNER JOIN
ce_individual_license ON ce_individual_license_ext.ixl_key_ext = ce_individual_license.ixl_key INNER JOIN
co_customer ON ce_individual_license.ixl_ind_cst_key = co_customer.cst_key
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 29, 2007 at 10:28 am
Ok, let me go back to original question. Here is what I need
I need to find all customers who have a license to print the license date if it exist. If they don't have one. Then print 'No licence'
Let's say it's only a 2 table join, Record exist in Table 1 but not in table to on the join (Ie not custID in the second table) I want to take the custid in the first table and put it in the record set with a following text in the second column 'No License'
Sorry if I wasn't clear on this.
so here is what I am using
select
substring(co_customer.cst_id,4,6)as cst_id,
Case when ce_individual_license_ext.ixl_expire_date_ext is not null
then ce_individual_license_ext.ixl_expire_date_ext
else 'No license' end as Test
FROM ce_individual_license_ext INNER JOIN
ce_individual_license ON ce_individual_license_ext.ixl_key_ext = ce_individual_license.ixl_key INNER JOIN
co_customer ON ce_individual_license.ixl_ind_cst_key = co_customer.cst_key
October 29, 2007 at 10:35 am
I think I have it, It has to be a Right Outer Join on CO_customer table in order for it to work.
SELECT SUBSTRING(co_customer.cst_id, 4, 6) AS cst_id,
ce_individual_license_ext.ixl_expire_date_ext,
CASE WHEN ce_individual_license_ext.ixl_expire_date_ext IS NOT NULL THEN CONVERT(varchar(10),
ce_individual_license_ext.ixl_expire_date_ext, 112) ELSE 'No license or Expired license' END AS Test
FROM ce_individual_license_ext INNER JOIN
ce_individual_license ON ce_individual_license_ext.ixl_key_ext = ce_individual_license.ixl_key RIGHT OUTER JOIN
co_customer ON ce_individual_license.ixl_ind_cst_key = co_customer.cst_key
Where SUBSTRING(co_customer.cst_id, 4, 6) = '008403'
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply