You Going Where? Mudik..

Selamat Hari Raya Idul Fitri

Mohon Maaf Lahir dan Batin

“Semoga Alloh menerima semua amal ibadah kita. Amin.“

Arief & Keluarga

How To Convert Null Value to Zero On LEFT JOIN Query

I got stuck on my software development process on how to show items that are not completely ordered to sub-contractor. The SQL query result always shows only items that some are already ordered but not yet complete. I need this to warn the production department as well as marketing department.

This article tells how you can easily convert NULL value using MySQL COALESCE function.

I have order table like this:

SELECT p.code AS product_code, pd.qty AS buyer_qty_order, wd.qty AS qty_ordered_to_sub_con FROM pi_detail pd
INNER JOIN product p ON p.id = pd.product_id
INNER JOIN lpp ON pd.pi_id = lpp.pi_id
WHERE lpp.id = '1'

Buyer Order Quantity

Since I have ordered some items to sub contractor, I need to know how many quantity has been ordered.

SELECT p.code AS product_code, pd.qty AS buyer_qty_order,
(SELECT SUM(wd.qty) FROM wo_detail wd WHERE pd.id=wd.pi_detail_id) AS qty_ordered_to_sub_con FROM pi_detail pd
INNER JOIN product p ON p.id = pd.product_id
INNER JOIN lpp ON pd.pi_id = lpp.pi_id
LEFT JOIN wo_detail wd ON wd.pi_detail_id=pd.id
WHERE lpp.id = '1'
GROUP BY pd.id

Sub Contractor Ordered Quantity

NULL value on AU-01A means that this item is not yet ordered to sub contractor.

I also need to know the balance.

SELECT p.code AS product_code, pd.qty AS buyer_qty_order,
(SELECT SUM(wd.qty) FROM wo_detail wd WHERE pd.id=wd.pi_detail_id) AS qty_ordered_to_sub_con,
(pd.qty-(SELECT SUM(wd.qty) FROM wo_detail wd WHERE pd.id=wd.pi_detail_id)) AS qty_balance FROM pi_detail pd
INNER JOIN product p ON p.id = pd.product_id
INNER JOIN lpp ON pd.pi_id = lpp.pi_id
LEFT JOIN wo_detail wd ON wd.pi_detail_id=pd.id
WHERE lpp.id = '1'
GROUP BY pd.id

Balance

AU-01A has NULL value on both last two columns.

Since I need to eliminate item that is completely ordered to sub contractor, I execute this query and the result is not like what I want. The query only returns items which is ordered but not all.

SELECT p.code AS product_code, pd.qty AS buyer_qty_order,
(SELECT SUM(wd.qty) FROM wo_detail wd WHERE pd.id=wd.pi_detail_id) AS qty_ordered_to_sub_con,
(pd.qty-(SELECT SUM(wd.qty) FROM wo_detail wd WHERE pd.id=wd.pi_detail_id)) AS qty_balance FROM pi_detail pd
INNER JOIN product p ON p.id = pd.product_id
INNER JOIN lpp ON pd.pi_id = lpp.pi_id
LEFT JOIN wo_detail wd ON wd.pi_detail_id=pd.id
WHERE lpp.id = '1' AND (SELECT SUM(wd.qty) FROM wo_detail wd WHERE pd.id=wd.pi_detail_id) < pd.qty
GROUP BY pd.id

Wrong Result

The query returns AU-02 only instead of returning both AU-01A and AU-02.

I have no idea about this at the first time, but after look again at my query result, I notice that the condition:

..AND (SELECT SUM(wd.qty) FROM wo_detail wd WHERE pd.id=wd.pi_detail_id) < pd.qty ..

is only valid if the result is not returning NULL value, that is why I got the result above.

Reading at MySQL Manual, Null value can be converted to Zero using COALESCE function. Quoting from MySQL Manual:

• COALESCE(value,...)Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

  mysql> SELECT COALESCE(NULL,1);
          -> 1
  mysql> SELECT COALESCE(NULL,NULL,NULL);
          -> NULL

  COALESCE() was added in MySQL 3.23.3.

Therefore I use this function in the query:

SELECT p.code AS product_code, pd.qty AS buyer_qty_order,
(SELECT SUM(wd.qty) FROM wo_detail wd WHERE pd.id=wd.pi_detail_id) AS qty_ordered_to_sub_con,
(pd.qty-(SELECT SUM(wd.qty) FROM wo_detail wd WHERE pd.id=wd.pi_detail_id)) AS qty_balance FROM pi_detail pd
INNER JOIN product p ON p.id = pd.product_id
INNER JOIN lpp ON pd.pi_id = lpp.pi_id
LEFT JOIN wo_detail wd ON wd.pi_detail_id=pd.id
WHERE lpp.id = '1'
AND COALESCE((SELECT SUM(wd.qty) FROM wo_detail wd WHERE pd.id=wd.pi_detail_id),0) < pd.qty
GROUP BY pd.id

Result Using COALESCE

Now the result is correct, but still has NULL value on the last two columns. Adding COALESCE at the query gives me the result I want.

SELECT p.code AS product_code, pd.qty AS buyer_qty_order,
COALESCE((SELECT SUM(wd.qty) FROM wo_detail wd WHERE pd.id=wd.pi_detail_id),0) AS qty_ordered_to_sub_con,
(pd.qty-COALESCE((SELECT SUM(wd.qty) FROM wo_detail wd WHERE pd.id=wd.pi_detail_id),0)) AS qty_balance FROM pi_detail pd
INNER JOIN product p ON p.id = pd.product_id
INNER JOIN lpp ON pd.pi_id = lpp.pi_id
LEFT JOIN wo_detail wd ON wd.pi_detail_id=pd.id
WHERE lpp.id = '1'
AND COALESCE((SELECT SUM(wd.qty) FROM wo_detail wd WHERE pd.id=wd.pi_detail_id),0) < pd.qty
GROUP BY pd.id

Correct Result

Finally I can continue my development..

Ups.. here is the screen-shoot for the application.

How To Download Images and Ringtones From WAP with Nokia 3105

I digged forumponsel.com just now to find my post regarding how to download ringtones and images from WAP Site with Nokia 3105 (a CDMA Phone). I didn’t have a data cable that time, so I tried to upload the images and ringtones on my website and then accessed them from phone’s wap browser directly. But the result is the ringtones are played and the images are showed rather than dowloaded to the phone.

I asked the Nokia Priority Care and they said much better if I went to the city mall to buy ringtones and images from upload service booth 😦

I am not easily gave up, so I went to Google and worked with Nokia Mobile Internet Toolkit and Nokia Content Publishing Toolkit. I found some solutions for this download problem.

Easy Ways:

1. Buy a Nokia Data Cable (DKU-5) or other compatible cable like MA
2. Download from the operator wap site or buy from their affiliation

Hard Way:
To do this, you need to have a webspace on the Internet and you are able to add .htaccess file. Then follow this step by step instruction:

1. Edit or add .htaccess file on your web directory with this line:
   
   AddType application/vnd.oma.dd+xml dd
2. Create DD file (DD: Download Descriptor) using notepad or other text editor and add the following code:

   <media xmlns=”http://www.openmobilealliance.org/xmlns/dd”&gt; <objectURI>http://your-web-address.com/image_name.gif</objectURI&gt; <size>Your File Size In Byte (ie: 2400)</size>
   <type>Your Data Type (ie:image/gif) </type> <type>application/vnd.oma.drm.message</type> <name>file_name_for_phone.gif</name>
   <vendor>Vendor Name (ie: Up To You Vendor)</vendor>
   <description>File Description</description> <nextURL>http://url-after-successful-download</nextURL&gt;
   </media>

3. Save the file with .dd extention
4. Upload to your webspace (don’t forget to upload the image file too)
5. Finish
6. Open your WAP Browser on your 3105 and than access the url for the DD file. For example, if you name your DD file as britney.dd and store it in the main directory of your web, you need to enter:
   http://your-web-address.com/britney.dd
7. Follow the on screen installation procedure and save to any folder on your Nokia 3105.
8. Have Fun
   

The instruction above is a hard way for you to add a content to your Nokia 3105, so I think a data cable can make your life easier. But, in the other hand, the sample above can make you think that you can start your own content provider business.

Note: With a little PHP programming, you can create a dynamic WAP download site and dynamically create DD files on the fly. I made a PHP version for this, I will post it here soon.

Javascript: Highlight All Text In Textfield On Focus

This post has been moved. Click here.