IBM WCS - IBM WebSphere Commerce Blog: November 2013

Monday, November 18, 2013

Bing Integration with Jquery

Bing provides an interesting to alternative to Google maps for integration and if you want to integrate using Jquery. Please copy paste the example in a .html file and test it with a zip-code or city.

The key that I have in the example below will expire in 90 days so please use the below link to create a new key:
http://www.microsoft.com/maps/

Copy the below section after this line in a .html file:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Use Bing Maps REST Services with jQuery to build an autocomplete box and find a location dynamically</title>
<script src="http://ajax.aspnetcdn.com/ajax/jquery/jquery-1.5.1.js" type="text/javascript"></script>
<script src="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.8.10/jquery-ui.js" type="text/javascript"></script>
<link href="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.8.10/themes/redmond/jquery-ui.css" rel="stylesheet" type="text/css" />
<style type="text/css">
.ui-autocomplete-loading
{
background: white url('images/ui-anim_basic_16x16.gif') right center no-repeat;
}
#searchBox
{
width: 25em;
}
</style>

<script type="text/javascript">
$(document).ready(function () {
$("#searchBox").autocomplete({
source: function (request, response) {
$.ajax({
url: "http://dev.virtualearth.net/REST/v1/Locations",
dataType: "jsonp",
data: {
key: "AlJKmxkiJg2u0CIDEyaTM6CWC9jQ_q1pf4_xzxPdEJoaT_KsgKRy73ksHyl24oe5",
q: request.term
},
jsonp: "jsonp",
success: function (data) {
var result = data.resourceSets[0];
if (result) {
if (result.estimatedTotal > 0) {
response($.map(result.resources, function (item) {
return {
data: item,
label: item.name + ' (' + item.address.countryRegion + ')',
value: item.name
}
}));
}
}
}
});
},
minLength: 1,
change: function (event, ui) {
if (!ui.item)
$("#searchBox").val('');
},
select: function (event, ui) {
displaySelectedItem(ui.item.data);
}
});
});

function displaySelectedItem(item) {
$("#searchResult").empty().append('Result: ' + item.name).append(' (Latitude: ' + item.point.coordinates[0] + ' Longitude: ' + item.point.coordinates[1] + ')');
}
</script>
</head>
<body>
<div>
<div class="ui-widget">
<label for="searchBox">
Search:
</label>
<input id="searchBox" />
</div>
<div id="searchResult" class="ui-widget" style="margin-top: 1em;">
</div>
</div>
</body>
</html>

SQL | Joins Explained | Using Commerce tables.

This example below outlines lot of SQL Join functionality that we use in a lot of queries in our codes. The below example use commerce tables to demonstrate the joins functionality.

--EXAMPLE 2 OUTER JOIN Table XORDERITEMS:
CREATE TABLE XCATENTRY (catentry_id NUMBER,partnumber varchar2(255), lastupdate timestamp);

--2. Data insertion into table
insert into XCATENTRY values (11,'DSC_1',sysdate-800);
insert into XCATENTRY values (33,'DSC_3',sysdate-700);
insert into XCATENTRY values (1010,'DSC_10',sysdate-500);
insert into XCATENTRY values (1111,'DSC_11',sysdate-300);

output::
Tables Orders:
ORDERS_ID PARTNUMBER STATUS
-----------------------------------------
1 DSC_1 P
2 DSC_2 Y
3 DSC_3 c
4 DSC_4 c
5 DSC_5 c
6 DSC_4 P
7 DSC_3 y
8 DSC_7 c
9 DSC_8 P
10 DSC_9 P

Tables Catentry:
CATENTRY_ID PARTNUMBER LASTUPDATE
--------------------------------------------
11 DSC_1 29-08-2011 13:53:21.000000
33 DSC_3 07-12-2011 13:53:21.000000
1010 DSC_10 24-06-2012 13:53:21.000000
1111 DSC_11 10-01-2013 13:53:21.000000

Query: For fetching all partnumber from left table along with matching partnumber */

--Query 1
select o.partnumber from XORDERITEMS o left outer join xcatentry c on o.partnumber = c.partnumber;

--Query 2
select o.partnumber from XORDERITEMS o , xcatentry c where o.partnumber = c.partnumber(+);

output:
PARTNUMBER
DSC_1
DSC_3
DSC_3
DSC_4
DSC_4
DSC_8
DSC_9
DSC_7
DSC_5
DSC_2

--Query: For fetching all partnumber from right table along with matching partnumber

--QUERY
select distinct c.* from XORDERITEMS o right outer join xcatentry c on o.partnumber = c.partnumber;

--ALTERNATE QUERY:
select distinct c.* from XORDERITEMS o , xcatentry c where o.partnumber(+) = c.partnumber;

Result:
CATENTRY_ID PARTNUMBER LASTUPDATE
11 DSC_1 29-08-2011 13:53:21.000000
33 DSC_3 07-12-2011 13:53:21.000000
1010 DSC_10 24-06-2012 13:53:21.000000
1111 DSC_11 10-01-2013 13:53:21.000000

-- Query: For fetching all partnumber from left and right table along with matching partnumber

select o.*,c.* from XORDERITEMS o full outer join xcatentry c on o.partnumber = c.partnumber;

ORDERS_ID PARTNUMBER STATUS CATENTRY_ID PARTNUMBER_1 LASTUPDATE
1 DSC_1 P 11 DSC_1 29-08-2011 13:53:21.000000
7 DSC_3 y 33 DSC_3 07-12-2011 13:53:21.000000
3 DSC_3 c 33 DSC_3 07-12-2011 13:53:21.000000
6 DSC_4 P
4 DSC_4 c
9 DSC_8 P
10 DSC_9 P
8 DSC_7 c
5 DSC_5 c
2 DSC_2 Y
1111 DSC_11 10-01-2013 13:53:21.000000
1010 DSC_10 24-06-2012 13:53:21.000000

--EXAMPLE 3 Query with date range
--Query 1: select partnumber of year 2011
select partnumber from xcatentry where to_date(to_char ( lastupdate,'DD/MM/YYYY'),'DD/MM/YYYY') BETWEEN TO_DATE('01/01/2011','DD/MM/YYYY') AND TO_DATE('31/12/2011','DD/MM/YYYY')

output:
PARTNUMBER
DSC_1
DSC_3

--Query 2: select partnumber of year 2011 and 2012 but not in orders tables
select partnumber from xcatentry c where to_date(to_char ( c.lastupdate,'DD/MM/YYYY'),'DD/MM/YYYY') BETWEEN TO_DATE('01/01/2011','DD/MM/YYYY') AND TO_DATE('31/12/2012','DD/MM/YYYY')
and not exists (select 1 from XORDERITEMS o where o.partnumber = c.partnumber)

RESULTS:
PARTNUMBER
DSC_10

--EXAMPLE 4 Query : Count the status
select status,count(*) from orders group by status order by status;

output:
STATUS COUNT(*)
P 4
Y 1
c 4
y 1

Tables XORDERITEMS:
ORDERS_ID PARTNUMBER STATUS
-----------------------------------------
1 DSC_1 P
2 DSC_2 Y
3 DSC_3 c
4 DSC_4 c
5 DSC_5 c
6 DSC_4 P
7 DSC_3 y
8 DSC_7 c
9 DSC_8 P
10 DSC_9 P

Tables XCatentry:
CATENTRY_ID PARTNUMBER LASTUPDATE
--------------------------------------------
11 DSC_1 29-08-2011 13:53:21.000000
33 DSC_3 07-12-2011 13:53:21.000000
1010 DSC_10 24-06-2012 13:53:21.000000
1111 DSC_11 10-01-2013 13:53:21.000000

Tables Xcatentdesc:
CATENTRY_ID LANGUAGE_ID SHORTDESCRIPTION
11 -1 DSC_1_DESCRIPTION
33 -1 DSC_3_DESCRIPTION
1010 -1 DSC_10_DESCRIPTION
1111 -1 DSC_11_DESCRIPTION

Query 1: Example: inner join - two table
select o.orders_id, o.partnumber,c.lastupdate from Xorders o inner join Xcatentry c on o.partnumber= c.partnumber;
o/p:
ORDERS_ID PARTNUMBER LASTUPDATE
1 DSC_1 8/29/2011 1:53:21.000000 PM
3 DSC_3 12/7/2011 1:53:21.000000 PM
7 DSC_3 12/7/2011 1:53:21.000000 PM

Query 2: Example: inner join - three table
select o.orders_id, o.partnumber,cd.shortdescription from Xorders o inner join Xcatentry c on o.partnumber= c.partnumber inner join Xcatentdesc cd on c.catentry_id
= cd.catentry_id;

output:
ORDERS_ID PARTNUMBER SHORTDESCRIPTION
1 DSC_1 DSC_1_DESCRIPTION
3 DSC_3 DSC_3_DESCRIPTION
7 DSC_3 DSC_3_DESCRIPTION

Friday, November 8, 2013

SQL | Analysis and Query to find items that are not part of input list

I will have a few SQL series blogs and as a WCS developer, I feel it is very important to have good SQL skills.

--Exampple . Find all part numbers that are not found in orders table from the list (DSC_1,DSC_3, DSC_10, DSC_11) in the XORDERITEMS table below.
Result: DSC_10, DSC_11

--1. Table creation:

CREATE TABLE XORDERITEMS (orders_id NUMBER,partnumber varchar2(255), status varchar2(1)) NOLOGGING;

--2. Data insertion into table
insert into /*+ APPEND */ XORDERITEMS values (1,'DSC_1','P');
insert into /*+ APPEND */ XORDERITEMS values (2,'DSC_2','Y');
insert into /*+ APPEND */ XORDERITEMS values (3,'DSC_3','c');
insert into /*+ APPEND */ XORDERITEMS values (4,'DSC_4','c');
insert into /*+ APPEND */ XORDERITEMS values (5,'DSC_5','c');
insert into /*+ APPEND */ XORDERITEMS values (6,'DSC_4','P');
insert into /*+ APPEND */ XORDERITEMS values (7,'DSC_3','y');
insert into /*+ APPEND */ XORDERITEMS values (8,'DSC_7','c');
insert into /*+ APPEND */ XORDERITEMS values (9,'DSC_8','P');
insert into /*+ APPEND */ XORDERITEMS values (10,'DSC_9','P');

--two alternates to fetch the above result.
--Query-1
select pno partnumber from
(SELECT TRIM(SUBSTR ( partnumber , INSTR (partnumber, ',', 1, level ) + 1 , INSTR (partnumber, ',', 1, level+1 ) - INSTR (partnumber, ',', 1, level) -1)) pno
FROM ( SELECT ','||'DSC_1,DSC_3,DSC_10,DSC_11'||',' AS partnumber FROM dual )
CONNECT BY level <= LENGTH(partnumber)-LENGTH(REPLACE(partnumber,',',''))-1 )
where pno not in (select partnumber from XORDERITEMS);

--Alternate Query
select pno partnumber from
(select 'DSC_1' pno from dual
union
select 'DSC_3' pno from dual
union
select 'DSC_10' pno from dual
union
select 'DSC_11' pno from dual)
where pno not in (select partnumber from XORDERITEMS);

Pages

Monday, November 18, 2013

Bing Integration with Jquery

SQL | Joins Explained | Using Commerce tables.

Friday, November 8, 2013

SQL | Analysis and Query to find items that are not part of input list