Null Values

Hello,

This piece of code is not working with NULL values. there are some categories in db that have no specialty but this code showing them not null and displaying the previous record values for null records

Full code:

String query = 	" select cat_id,category from CvCategories "+
				        " where category not like 'Staff' order by category asc";
		String query1=  " select Specialty_id,Specialty from Specialty where cat_id=?";
		
		PreparedStatement ps=cnn.prepareStatement(query); 
		rs=ps.executeQuery();

while(rs.next()){		
		String catId   = rs.getString(1);
		String category = rs.getString(2);
		
		startList+= "\""+category+"\",";
			
		String subList1="";
	
			
				PreparedStatement ps1=cnn.prepareStatement(query1);
				ps1.setString(1, catId);
				rs1=ps1.executeQuery();
			
				while(rs1.next()){
				String sId   	= rs1.getString(1);
				specialty 	= rs1.getString(2);
				
				if((specialty==null || specialty.equals("")) && (sId==null || sId.equals(""))){
				System.out.println("catId="+catId+"\t is null and specialty="+specialty);
				}
				else{		
					subList1+= "\""+specialty+"\",";
					specialties= um.removeLastComma(subList1);
				}//else
				}//while
				ps1.close();

combo2+= "categories[\""+category+"\"] = ["+specialties+"];\n";	
		}
		ps.close();

OUTPUT:
======

categories["startList"] = ["Allieds","Executives","Nurses","Pharmacist","Pysicians"];

categories["Allieds"] = ["Audiology","Bioengineering","Biomedical Science","Clinical Psychology","Dietitian","Electrocardiogram Technician","Dental Assistant",
"Diagnostic Medical Sonography","Paramedic","Exercise Physiology","Health Administration","Health Information Technician","Laboratory Technician",
"Massage Therapist","Medical Coder","Medical Interpreter","Medical Laboratory Scientist","Nuclear Medicine Technologist","Nutrition and Dietetics",
"Occupational Therapy","Operating Department Practitioners","Optometry","Orthotics and Prosthetics","Orthoptist","Pedorthist","Perfusionist","Personal Trainer",
"Phlebotomist","Physical Therapist (Physiotherapist)","Public Health","Radiation Therapist","Radiography","Rehabilitation Counseling","Respiratory Therapist",
"Sonographer","Speech and Language Pathologist","Strength and Conditioning Specialist","Surgical Technologist","Recreational Therapist","Ultrasound"];

categories["Executives"] = ["HR General","Recruitment","Training and Career Development","Accounts/Finance","Chief Financial Officer",
"Marketing","Product Specialist","Medical Operations Manager","Medical Facilities  Manager","Senior Management","Medical Director",
"CPU Manager","Compliance Manager"];

categories["Nurses"] = ["Midwife","Medical-surgical ","Nursing educator","Ambulatory care","Clinical nurse specialist","Critical care ","Emergency","Flight Nurse",
"Obstetrics gynecology Nurse","Operating Room Nurse ","Orthopaedic Nurse","Pain management and palliative care ","Pediatric ","Private duty Nurse","Surgical ","Wound care"];

categories["Pharmacist"] = ["Midwife","Medical-surgical ","Nursing educator","Ambulatory care","Clinical nurse specialist","Critical care ","Emergency","Flight Nurse",
"Obstetrics gynecology Nurse","Operating Room Nurse ","Orthopaedic Nurse","Pain management and palliative care ","Pediatric ","Private duty Nurse","Surgical ","Wound care"];

categories["Pysicians"] = ["Allergy and Immunology","Anesthesiology","Cardiology","Dentistry","Dermatology","Emergency Medicine","Endocrinology",
"Family Medicine","Gastroenterology","Geriatric Medicine","Hospice and Palliative Medicine","Infectious Disease","Internal Medicine","Nephrology","Neurology",
"Neurosurgery","OB/GYN (Obstetrics & Gynecology)","Oncology and Hematology","Ophthalmology","Orthopaedic Surgery","Otolaryngology (E.N.T.)","Pediatrics",
"Psychiatry","Pulmonary Disease","Radiology","Rheumatology","Surgery","Urology"];

Thanks & best regards

Hello,

Can you please help me?

Best regards

If I were you I would re-write my queries as one sql using right join.

Fatih Keles wrote:If I were you I would re-write my queries as one sql using right join.

Thanks for your reply

I split queries as I have to use this data to populate combo boxes. Can you please get me some query example as to how can I proceed?

Best regards

May be something like this

public Object nvl(Object obj, Object def) {
        if (obj == null || "".equals(obj))
            return def;
        return obj;
    }

public Map<String, Map<Integer, String>> populate() {
        Map<String, Map<Integer, String>> data = 
            new HashMap<String, Map<Integer, String>>();

String SQL = 
            "SELECT   cat.cat_id, cat.CATEGORY, sp.specialty_id, sp.specialty " + 
            "    FROM cvcategories cat, specialty sp " + 
            "   WHERE 1 = 1 " + 
            "     AND cat.CATEGORY NOT LIKE 'Staff' " + 
            "     AND cat.cat_id = sp.cat_id(+) " + //your db may complain about this, I am not sure though
            "ORDER BY cat.CATEGORY,sp.specialty";

Connection c = trx.getJdbcConnection();
        PreparedStatement ps;
        try {
            ps = c.prepareStatement(SQL);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                String key = 
                    rs.getInt("cat_id") + "|" + rs.getString("CATEGORY");
                int specialty_id = rs.getInt("specialty_id");
                String specialty = rs.getString("specialty");
                if(!"^!X!$".equals(nvl(specialty,"^!X!$"))){
                    Map<Integer, String> sps = (Map<Integer, String>)nvl(data.get(key),new HashMap<Integer,String>());
                    sps.put(specialty_id,specialty);
                    data.put(key,sps);
                }
            }
            rs.close();
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
            //do the necessary job here
        } finally{
            //do the necessary job here
        }
        return data;
    }

Thanks for your reply