tag:blogger.com,1999:blog-23721300589475128862024-03-06T05:30:30.759+05:30i-Series BlogsSumit Chhabrahttp://www.blogger.com/profile/05338620317642568093noreply@blogger.comBlogger16125tag:blogger.com,1999:blog-2372130058947512886.post-3436807519863720032011-08-06T07:20:00.002+05:302011-08-07T09:00:36.063+05:30Special Registers<div dir="ltr" style="text-align: left;" trbidi="on"><br>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span class="Apple-style-span" style="font-size: 12px;">Hi Guys,</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span class="Apple-style-span" style="font-size: 12px;"><br>
</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span class="Apple-style-span" style="font-size: 12px;">This time, I know I am back with long interval....</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span class="Apple-style-span" style="font-size: 12px;">I would like to thank Issak Brender to provide me examples on Special Register....</span><br>
<span class="Apple-style-span" style="font-size: 12px;"><br>
</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><a href="http://www.blogger.com/post-edit.g?blogID=2372130058947512886&postID=343680751986372003" name="wq1956"></a><span class="apple-style-span"><span style="font-size: 9pt;">A special register is a storage area <a href="http://www.blogger.com/post-edit.g?blogID=2372130058947512886&postID=343680751986372003" name="IDX4551"></a>that is defined for an application process by the database manager and is used to store information that can be referenced in SQL statements. A reference to a special register is a reference to a value provided by the current server<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br>
</div></div><a href="http://iseriesblogs.blogspot.com/2011/08/system-registers.html#more">Read more »</a>Sumit Chhabrahttp://www.blogger.com/profile/05338620317642568093noreply@blogger.com0tag:blogger.com,1999:blog-2372130058947512886.post-15916209685791300922011-07-16T21:52:00.000+05:302011-07-16T21:52:21.325+05:30SQL Query with CASE keyword<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><b><u><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">SQL example CASE WHEN<o:p></o:p></span></u></b></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span class="apple-style-span"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 10.0pt;">SQL</span></span><span class="apple-converted-space"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 10.0pt;"> </span></span><span class="apple-style-span"><b><i><span style="color: black; font-family: "Arial","sans-serif"; font-size: 10.0pt;">CASE</span></i></b></span><span class="apple-converted-space"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 10.0pt;"> </span></span><span class="apple-style-span"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 10.0pt;">is a very unique conditional statement providing if/then/else logic for any ordinary SQL command. It then provides when-then-else functionality (WHEN this condition is met THEN do_this).</span><o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span class="apple-style-span"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 10.0pt;">CASE can be used with both SELECT Clause and WHERE Clause</span></span><b><u><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;"><o:p></o:p></span></u></b></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><a href="" name="wq1956"></a><span style="color: black; font-family: "Courier New"; font-size: 10.0pt;">Select Name,DOB,<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Courier New"; font-size: 10.0pt;"> Case <o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Courier New"; font-size: 10.0pt;"> When Substr( Digits( DOB ), 4, 2 ) = '01' Then 'JAN'<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Courier New"; font-size: 10.0pt;"> When Substr( Digits( DOB ), 4, 2 ) = '02' Then 'FEB'<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Courier New"; font-size: 10.0pt;"> When Substr( Digits( DOB ), 4, 2 ) = '03' Then 'MAR'<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Courier New"; font-size: 10.0pt;"> When Substr( Digits( DOB ), 4, 2 ) = '04' Then 'APR'<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Courier New"; font-size: 10.0pt;"> When Substr( Digits( DOB ), 4, 2 ) = '05' Then 'MAY'<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Courier New"; font-size: 10.0pt;"> When Substr( Digits( DOB ), 4, 2 ) = '06' Then 'JUN'<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Courier New"; font-size: 10.0pt;"> When Substr( Digits( DOB ), 4, 2 ) = '07' Then 'JUL'<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Courier New"; font-size: 10.0pt;"> When Substr( Digits( DOB ), 4, 2 ) = '08' Then 'AUG'<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Courier New"; font-size: 10.0pt;"> When Substr( Digits( DOB ), 4, 2 ) = '09' Then 'SEP'<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Courier New"; font-size: 10.0pt;"> When Substr( Digits( DOB ), 4, 2 ) = '10' Then 'OCT'<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Courier New"; font-size: 10.0pt;"> When Substr( Digits( DOB ), 4, 2 ) = '11' Then 'NOV'<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Courier New"; font-size: 10.0pt;"> When Substr( Digits( DOB ), 4, 2 ) = '12' Then 'DEC'<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Courier New"; font-size: 10.0pt;"> End , DeptMst.DeptManager<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Courier New"; font-size: 10.0pt;"> From EmpData, DeptMst <o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Courier New"; font-size: 10.0pt;">Where DeptMst.Dept =<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Courier New"; font-size: 10.0pt;"> Case <o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Courier New"; font-size: 10.0pt;"> When Substr( EMPID , 1, 1 ) = 'P' Then 'Purchase'<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Courier New"; font-size: 10.0pt;"> When Substr( EMPID , 1, 1 ) = 'Q' Then 'Quality'<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Courier New"; font-size: 10.0pt;"> Else ‘Sale’<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-family: "Courier New"; font-size: 10.0pt;"> End <o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="apple-style-span"><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 10.0pt;">The above query will provide data from Employee Data file and Department Master File. It will have 4 columns as Employee Name, Date of Birth, Month of Birth and Department Manager’s Name. The query gets department Id from first Character of Employee ID.</span></span><span class="apple-converted-space"><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 10.0pt;"><o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">Do write me @ iSeriesblogs@gmail.com for any query or you can simply post comments below.<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">Happy Learning<o:p></o:p></span></div><div class="MsoNormal" style="background: white; line-height: normal;"><br />
</div>Sumit Chhabrahttp://www.blogger.com/profile/05338620317642568093noreply@blogger.com1tag:blogger.com,1999:blog-2372130058947512886.post-45569952494551372942011-07-15T14:35:00.000+05:302011-07-15T14:35:19.472+05:30XML host Variable in ILE RPG<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span class="Apple-style-span" style="font-size: 12px;">Hi Guys,</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span class="Apple-style-span" style="font-size: 12px;"><br>
</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span class="Apple-style-span" style="font-size: 12px;">In Continuation of my previous post.</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br>
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><i style="mso-bidi-font-style: normal;"><u><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">The ILE RPG precompilers support an XML host variable type:<o:p></o:p></span></u></i></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br>
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">To exchange XML data between the database server and an embedded SQL application, you need to declare host variables in your application source code.<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">To declare XML host variables in embedded SQL applications, in the declaration section of the application declare the XML host variables AS LOB data types<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br>
</div><a href="http://iseriesblogs.blogspot.com/2011/07/xml-host-variable-in-ile-rpg.html#more">Read more »</a>Sumit Chhabrahttp://www.blogger.com/profile/05338620317642568093noreply@blogger.com0tag:blogger.com,1999:blog-2372130058947512886.post-85082197170218999392011-07-15T11:39:00.000+05:302011-07-15T11:39:03.188+05:30Host Variable<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="apple-style-span"><b style="mso-bidi-font-weight: normal;"><u><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 10.0pt;">Host Variable Usage<o:p></o:p></span></u></b></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="apple-style-span"><span style="font-size: 9.0pt; mso-bidi-font-size: 10.0pt;">Host variables are used in SQL statements to pass values between the database and the application program.<span style="color: black;"><o:p></o:p></span></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><a href="" name="1112502"></a><span class="apple-style-span"><i style="mso-bidi-font-style: normal;"><u><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 10.0pt;">Using Variables in Statements<o:p></o:p></span></u></i></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><a href="" name="1112504"></a><span class="apple-style-span"><span style="font-size: 9.0pt; mso-bidi-font-size: 10.0pt;">Host variables may be used:<o:p></o:p></span></span></div><div class="MsoListParagraphCxSpFirst" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l0 level1 lfo1; text-indent: -.25in;"><a href="" name="1112505"></a><!--[if !supportLists]--><span class="apple-style-span"><span style="font-family: Symbol; font-size: 9.0pt; mso-bidi-font-family: Symbol; mso-bidi-font-size: 10.0pt; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font: 7.0pt "Times New Roman";"> </span></span></span></span><!--[endif]--><span class="apple-style-span"><span style="font-size: 9.0pt; mso-bidi-font-size: 10.0pt;">to receive information from the database (SELECT INTO, FETCH, CALL and SET statements)<o:p></o:p></span></span></div><div class="MsoListParagraphCxSpMiddle" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l0 level1 lfo1; text-indent: -.25in;"><a href="" name="1112506"></a><!--[if !supportLists]--><span class="apple-style-span"><span style="font-family: Symbol; font-size: 9.0pt; mso-bidi-font-family: Symbol; mso-bidi-font-size: 10.0pt; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font: 7.0pt "Times New Roman";"> </span></span></span></span><!--[endif]--><span class="apple-style-span"><span style="font-size: 9.0pt; mso-bidi-font-size: 10.0pt;">to assign values to columns in the database (CALL, INSERT and UPDATE statements)<o:p></o:p></span></span></div><div class="MsoListParagraphCxSpMiddle" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l0 level1 lfo1; text-indent: -.25in;"><a href="" name="1112507"></a><!--[if !supportLists]--><span class="apple-style-span"><span style="font-family: Symbol; font-size: 9.0pt; mso-bidi-font-family: Symbol; mso-bidi-font-size: 10.0pt; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font: 7.0pt "Times New Roman";"> </span></span></span></span><!--[endif]--><span class="apple-style-span"><span style="font-size: 9.0pt; mso-bidi-font-size: 10.0pt;">to manipulate information taken from the database or contained in other variables (in expressions)<o:p></o:p></span></span></div><div class="MsoListParagraphCxSpLast" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l0 level1 lfo1; text-indent: -.25in;"><a href="" name="1112508"></a><a href="" name="1112509"></a><!--[if !supportLists]--><span class="apple-style-span"><span style="font-family: Symbol; font-size: 9.0pt; mso-bidi-font-family: Symbol; mso-bidi-font-size: 10.0pt; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font: 7.0pt "Times New Roman";"> </span></span></span></span><!--[endif]--><span class="apple-style-span"><span style="font-size: 9.0pt; mso-bidi-font-size: 10.0pt;">in dynamic SQL statements.<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="apple-style-span"><span style="font-size: 9.0pt; mso-bidi-font-size: 10.0pt;">In all these contexts, the data type of the host variable or database column must be compatible with the data type of the corresponding database value or host variable. <o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="apple-style-span"><b style="mso-bidi-font-weight: normal;"><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 10.0pt;">Note: The colon is not part of the host variable name, and should not be used when the variable is referenced in host language statements.<o:p></o:p></span></b></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">Do write me @ iSeriesblogs@gmail.com for any query or you can simply post comments below.<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">Happy Learning<o:p></o:p></span></div><div class="MsoNormal" style="background: white; line-height: normal;"><br />
</div>Sumit Chhabrahttp://www.blogger.com/profile/05338620317642568093noreply@blogger.com0tag:blogger.com,1999:blog-2372130058947512886.post-59178967585750768152011-07-14T15:00:00.000+05:302011-07-14T15:23:54.016+05:30Whats new in Embedded SQL Programming for V7.1<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span class="Apple-style-span" style="font-size: 12px;">Hi Guys,</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9pt;">I just thought of sharing the new components added by IBM for Embedded SQL Programming in V7.1. I would be covering each below point as a separate blog.<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><i style="mso-bidi-font-style: normal;"><u><span style="color: black; font-size: 9pt;">The ILE RPG precompilers support an XML host variable type:<o:p></o:p></span></u></i></div><div class="MsoNormal"><span style="color: black; font-size: 9pt; line-height: 115%;">ILE RPG does not have variables that correspond to the SQL data type for XML. In V7.1 IBM has extended the usage of SQLTYPE for declaring XML Host variable. <o:p></o:p></span></div><div class="MsoNormal"><span style="color: black; font-size: 9pt; line-height: 115%;">XML can be define as <o:p></o:p></span></div><div class="MsoListParagraphCxSpFirst" style="mso-list: l0 level1 lfo1; text-indent: -.25in;"><span style="color: black; font-size: 9pt; line-height: 115%;">1.<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span></span><span style="color: black; font-size: 9pt; line-height: 115%;">XML_BLOB<o:p></o:p></span></div><div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;"><span style="color: black; font-size: 9pt; line-height: 115%;">2.<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span></span><span style="color: black; font-size: 9pt; line-height: 115%;">XML_CLOB<o:p></o:p></span></div><div class="MsoListParagraphCxSpLast" style="mso-list: l0 level1 lfo1; text-indent: -.25in;"><span style="color: black; font-size: 9pt; line-height: 115%;">3.<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span></span><span style="color: black; font-size: 9pt; line-height: 115%;">XML_DBCLOB<o:p></o:p></span></div><div class="MsoListParagraphCxSpLast" style="mso-list: l0 level1 lfo1; text-indent: -.25in;"><span style="color: black; font-size: 9pt; line-height: 115%;"><br />
</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><i style="mso-bidi-font-style: normal;"><u><span style="color: black; font-size: 9pt;">The ILE RPG precompilers support result set locator host variable type:<o:p></o:p></span></u></i></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9pt;">ILE RPG does not have variables that correspond to the SQL data type for Result Set. In V7.1 IBM has extended the usage of SQLTYPE for declaring Result Set variable using keyword “Result_Set_Locator”.<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><i style="mso-bidi-font-style: normal;"><u><span style="color: black; font-size: 9pt;">The DBGENCKEY (Debug encryption key) parameter is passed to the compiler for ILE programs and service programs.<o:p></o:p></span></u></i></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9pt;">The DBGENCKEY value from the CRTSQLxxx command is specified on the CRTxxxMOD or CRTBNDxxx commands.<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9pt;">Do write me @ iSeriesblogs@gmail.com for any query or you can simply post comments below.<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9pt;">Happy Learning<o:p></o:p></span></div><div class="MsoNormal" style="background: white; line-height: normal;"><br />
</div>Sumit Chhabrahttp://www.blogger.com/profile/05338620317642568093noreply@blogger.com0tag:blogger.com,1999:blog-2372130058947512886.post-73440994279779485982011-07-08T17:12:00.000+05:302011-07-08T17:13:20.334+05:30SQL where Clause (Row Value Expressions)<span class="Apple-style-span" style="-webkit-text-size-adjust: none; font-family: Arial, Helvetica, 'Nimbus Sans L', sans-serif; line-height: 12px;"></span><br />
<div style="-webkit-text-size-adjust: none; border-bottom-width: 0px; border-color: initial; border-left-width: 0px; border-right-width: 0px; border-style: initial; border-top-width: 0px; font-family: inherit; font-weight: inherit; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; outline-color: initial; outline-style: initial; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; vertical-align: baseline;"></div><div class="MsoNormal" style="font-size: 13px; font-style: inherit; line-height: normal; margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 9pt;">Hi Guys,<span style="color: black;"><o:p></o:p></span></span></div><div class="MsoNormal" style="-webkit-text-size-adjust: none; border-color: initial; border-style: initial; font-size: 13px; font-style: inherit; font-weight: inherit; line-height: normal; margin-bottom: 0.0001pt; outline-color: initial; outline-style: initial; outline-width: 0px; text-align: justify;"><span data-li-comment-text="" style="-webkit-text-size-adjust: none; border-color: initial; border-style: initial; font-style: inherit; font-weight: inherit; outline-color: initial; outline-style: initial; outline-width: 0px;"><span style="color: black; font-size: 9pt;"><br style="mso-special-character: line-break;" /> <br style="mso-special-character: line-break;" /> <o:p></o:p></span></span></div><div class="MsoNormal" style="-webkit-text-size-adjust: none; border-color: initial; border-style: initial; font-size: 13px; font-style: inherit; font-weight: inherit; line-height: normal; margin-bottom: 0.0001pt; outline-color: initial; outline-style: initial; outline-width: 0px; text-align: justify;"><span data-li-comment-text="" style="-webkit-text-size-adjust: none; border-color: initial; border-style: initial; font-style: inherit; font-weight: inherit; outline-color: initial; outline-style: initial; outline-width: 0px;"><span style="font-size: 9pt;">Again thanks to Issak (my linkedin Friend) who made me re-call the new feature which was introduce in V5R4 for SQL Where clause<span style="color: black;"><o:p></o:p></span></span></span></div><div class="MsoNormal" style="-webkit-text-size-adjust: none; border-color: initial; border-style: initial; font-size: 13px; font-style: inherit; font-weight: inherit; line-height: normal; margin-bottom: 0.0001pt; outline-color: initial; outline-style: initial; outline-width: 0px; text-align: justify;"><span data-li-comment-text="" style="-webkit-text-size-adjust: none; border-color: initial; border-style: initial; font-style: inherit; font-weight: inherit; outline-color: initial; outline-style: initial; outline-width: 0px;"><span style="color: black; font-size: 9pt;">Quoting his words.<br />
</span><span style="font-size: 9pt;"></span>SQL WHERE clause usually checks if a condition is true, false or unknown. In the past, each condition was evaluated for a single value. Starting with V5R4 groups or "rows" of values can be evaluated at once. These groups of values are called "row value expressions." <span style="color: black;"><o:p></o:p></span></span><br />
</div><div class="MsoNormal" style="-webkit-text-size-adjust: none; border-color: initial; border-style: initial; font-style: inherit; font-weight: inherit; line-height: normal; margin-bottom: 0.0001pt; outline-color: initial; outline-style: initial; outline-width: 0px; text-align: justify;"><span class="Apple-style-span" style="font-size: 12px;"><br />
</span></div><div class="MsoNormal" style="font-size: 13px; font-style: inherit; line-height: normal; margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 9pt;">Below is a simple example with constants: <o:p></o:p></span></div><div class="MsoNormal" style="font-size: 13px; font-style: inherit; line-height: normal; margin-bottom: 0.0001pt; text-align: justify;"><br />
</div><div class="MsoNormal" style="-webkit-text-size-adjust: none; border-color: initial; border-style: initial; font-size: 13px; font-style: inherit; font-weight: inherit; line-height: normal; margin-bottom: 0.0001pt; outline-color: initial; outline-style: initial; outline-width: 0px; text-align: justify;"><span data-li-comment-text="" style="-webkit-text-size-adjust: none; border-color: initial; border-style: initial; font-style: inherit; font-weight: inherit; outline-color: initial; outline-style: initial; outline-width: 0px;"><i style="mso-bidi-font-style: normal;"><span style="font-family: 'Courier New'; font-size: 9pt;">SELECT * FROM DEPTBL WHERE (DEPT, BRANCH)=('Shipping', '0012M')<o:p></o:p></span></i></span></div><div class="MsoNormal" style="font-size: 13px; font-style: inherit; line-height: normal; margin-bottom: 0.0001pt; text-align: justify;"><span style="color: black; font-size: 9pt;"><br />
</span><span style="font-size: 9pt;"></span>The number of columns must be equal and of compatible data types. <o:p></o:p></div><div class="MsoNormal" style="font-size: 13px; font-style: inherit; line-height: normal; margin-bottom: 0.0001pt; text-align: justify;"><span style="color: black; font-size: 9pt;"><br />
</span><span style="font-size: 9pt;">Statement provides the same result as <o:p></o:p></span></div><div class="MsoNormal" style="font-size: 13px; font-style: inherit; line-height: normal; margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 9pt;"><br />
</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt; text-align: justify;"><span style="color: black;"></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><i style="mso-bidi-font-style: normal;"><span style="font-family: 'Courier New'; font-size: 9pt;">SELECT * FROM DEPTBL WHERE DEPT='Shipping' AND BRANCH='0012M' <o:p></o:p></span></i></div><span class="Apple-style-span" style="font-size: xx-small;"><i><br />
</i></span><br />
<div class="MsoNormal" style="font-size: 13px; font-style: inherit; line-height: normal; margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 9pt;"></span>Please note the parentheses usage in first example. <o:p></o:p></div><div class="MsoNormal" style="font-size: 13px; font-style: inherit; line-height: normal; margin-bottom: 0.0001pt; text-align: justify;"><br />
</div><div class="MsoNormal" style="-webkit-text-size-adjust: none; border-color: initial; border-style: initial; font-size: 13px; font-style: inherit; font-weight: inherit; line-height: normal; margin-bottom: 0.0001pt; outline-color: initial; outline-style: initial; outline-width: 0px; text-align: justify;"><span data-li-comment-text="" style="-webkit-text-size-adjust: none; border-color: initial; border-style: initial; font-style: inherit; font-weight: inherit; outline-color: initial; outline-style: initial; outline-width: 0px;"><span style="font-size: 9pt;">The same technique applies to IN (SELECT…) predicate. However a constant list is not allowed when using a row value expression in this case. <o:p></o:p></span></span></div><div class="MsoNormal" style="font-size: 13px; font-style: inherit; line-height: normal; margin-bottom: 0.0001pt; text-align: justify;"><span style="color: black; font-size: 9pt;"><br />
</span><span style="font-size: 9pt;">This saves some coding, but could look confusing in the beginning. It is just the matter of preferences.<span style="color: black;"><o:p></o:p></span></span></div><div class="MsoNormal" style="font-size: 13px; font-style: inherit; line-height: normal; margin-bottom: 0.0001pt; text-align: justify;"><br />
</div><br />
<div style="-webkit-text-size-adjust: none; border-bottom-width: 0px; border-color: initial; border-left-width: 0px; border-right-width: 0px; border-style: initial; border-top-width: 0px; font-family: inherit; font-style: inherit; font-weight: inherit; line-height: 12px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; outline-color: initial; outline-style: initial; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; vertical-align: baseline;"></div><div class="MsoNormal" style="font-size: 13px; line-height: normal; margin-bottom: 0.0001pt; text-align: justify;"><br />
</div><div class="MsoNormal" style="font-size: 13px; line-height: normal; margin-bottom: 0.0001pt; text-align: justify;"><span style="color: black; font-size: 9pt;">Do write me @ iSeriesblogs@gmail.com for any query or you can simply post comments below.<o:p></o:p></span></div><div class="MsoNormal" style="font-size: 13px; line-height: normal; margin-bottom: 0.0001pt; text-align: justify;"><br />
</div><div class="MsoNormal" style="font-size: 13px; line-height: normal; margin-bottom: 0.0001pt; text-align: justify;"><span style="color: black; font-size: 9pt;">Happy Learning<o:p></o:p></span></div><div class="MsoNormal" style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: initial initial; font-size: 13px; line-height: normal;"><br />
</div><br />
<div class="extra" style="-webkit-text-size-adjust: none; border-bottom-width: 0px; border-color: initial; border-left-width: 0px; border-right-width: 0px; border-style: initial; border-top-width: 0px; float: none; font-family: inherit; font-size: 10px; font-style: inherit; font-weight: inherit; line-height: 12px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; outline-color: initial; outline-style: initial; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 10px; vertical-align: baseline; width: 525px;"></div>Sumit Chhabrahttp://www.blogger.com/profile/05338620317642568093noreply@blogger.com0tag:blogger.com,1999:blog-2372130058947512886.post-32654073870051070002011-07-05T16:57:00.000+05:302011-07-05T16:57:08.374+05:30OLAP Functions in i-Series<div dir="ltr" style="text-align: left;" trbidi="on"><br>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span class="Apple-style-span" style="font-size: 12px;"> Hi Guys, </span></div><div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span class="Apple-style-span" style="font-size: 12px;">One of my Linkedin friend asked me about OLAP Functions in i-Series, So just thought of writing a brief on OLAP.</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><b><u><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;"><br>
</span></u></b></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><b><u><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">OLAP functions in i-Series<o:p></o:p></span></u></b></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br>
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><a href="" name="wq1956"></a><span class="apple-converted-space"><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 10.0pt;"> IBM</span></span><span class="apple-style-span"><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 10.0pt;"> gave developers an impressive set of Online Analytical Processing (OLAP) tools to aid with common ranking and numbering issues. The significance of these OLAP tools lies in the amount of work they do and the time it will save database programmers.</span></span><i><span style="color: black; font-family: "Arial","sans-serif"; font-size: 9.0pt; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></i></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br>
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="apple-style-span"><i style="mso-bidi-font-style: normal;"><u><span style="font-size: 9.0pt; mso-bidi-font-size: 11.0pt;">ROW_NUMBER<o:p></o:p></span></u></i></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="apple-style-span"><span style="font-size: 9.0pt; mso-bidi-font-size: 11.0pt;">To start, the ROW_NUMBER is a huge blessing as it finally furnishes SQL with a built-in counter function. Here's a trivial example that adds a row number column to a result set:<o:p></o:p></span></span></div><pre><span style="color: black; font-size: 9.0pt;">Insert Into OrderItm<o:p></o:p></span></pre><pre><span style="color: black; font-size: 9.0pt;">Select OrderNo,<o:p></o:p></span></pre><pre><span style="color: black; font-size: 9.0pt;"><span style="mso-spacerun: yes;"> </span>Row_Number() Over (Partition By OrderNo<o:p></o:p></span></pre><pre><span style="color: black; font-size: 9.0pt;"><span style="mso-spacerun: yes;"> </span>Order By OrderNo,ItemNo),<o:p></o:p></span></pre><pre><span style="color: black; font-size: 9.0pt;"><span style="mso-spacerun: yes;"> </span>ItemNo,<o:p></o:p></span></pre><pre><span style="color: black; font-size: 9.0pt;"><span style="mso-spacerun: yes;"> </span>QtyOrdered,<o:p></o:p></span></pre><pre><span style="color: black; font-size: 9.0pt;"><span style="mso-spacerun: yes;"> </span>SalePrice<o:p></o:p></span></pre><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br>
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br>
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="apple-style-span"><span style="font-size: 9.0pt; mso-bidi-font-size: 11.0pt;">In this scenario, ROW_NUMBER will continually increment the ItemSeq field over all orders that are being imported and counter would get reset for every change in order number.<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br>
</div></div><a href="http://iseriesblogs.blogspot.com/2011/07/olap-functions-in-i-series.html#more">Read more »</a>Sumit Chhabrahttp://www.blogger.com/profile/05338620317642568093noreply@blogger.com0tag:blogger.com,1999:blog-2372130058947512886.post-91627068205367286212011-07-05T16:34:00.000+05:302011-07-05T16:34:39.753+05:30SQL Stored Procedures<div dir="ltr" style="text-align: left;" trbidi="on"><br />
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span class="Apple-style-span" style="font-family: Verdana, sans-serif; font-size: 11px;">Hi Guys,</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span class="Apple-style-span" style="font-family: Verdana, sans-serif; font-size: 11px;"><br />
</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span class="Apple-style-span" style="font-family: Verdana, sans-serif; font-size: 11px;">Today I am writing about Stored Procedures. What are stored Procedures, How to write them, How to compile and how to call them?</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><b><u><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;"><br />
</span></u></b></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><b><u><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">Stored Procedures<o:p></o:p></span></u></b></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span class="Apple-style-span" style="font-size: 12px;"><b><u><br />
</u></b></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><a href="" name="wq1956"></a><span class="apple-style-span"><span style="color: black; font-family: "Verdana","sans-serif"; font-size: 8.5pt;">Stored procedures are programs that are called from SQL. These programs can be written using the SQL procedure language, but they may also be written using RPG</span></span><span class="apple-style-span"><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 11.0pt;">.<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="apple-style-span"><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 11.0pt;">So how do you write SQL Procedure? It is simple. You just need to write SQLRPGLE with “CREATE PROCEDURE” SQL Statement<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="apple-style-span"><u><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 11.0pt;">Example 1.</span></u></span><u><span style="color: black; font-family: "Verdana","sans-serif"; font-size: 8.5pt;"><o:p></o:p></span></u></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div><pre><span style="color: black; font-size: 9.0pt;">C/Exec SQL<o:p></o:p></span></pre><pre><span style="color: black; font-size: 9.0pt;">C+CREATE PROCEDURE GetName
C+ (IN ProgLang CHARACTER(10),
C+IN Comp CHARACTER(20),
C+INOUT FOUND INTEGER(4))
C+LANGUAGE SQL
C+SELECT COUNT(*) INTO FOUND FROM TestLib/NameData
C+WHERE TechLang = ProgLang AND Employeer = Comp<o:p></o:p></span></pre><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: black; font-size: 9.0pt;">C/End-Exec</span><span class="apple-style-span"><span style="color: #333333; font-family: "Arial","sans-serif"; font-size: 10.5pt;"><o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="apple-style-span"><u><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 10.0pt;">How to compile the Procedure?<o:p></o:p></span></u></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="apple-style-span"><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 10.0pt;">You can compile the above code like any other SQLRPGLE code</span></span><span class="apple-style-span"><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 11.0pt;"><o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="apple-style-span"><u><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 11.0pt;">How to call above procedure?<o:p></o:p></span></u></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="apple-style-span"><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 11.0pt;">Create another RPGLE code as below<o:p></o:p></span></span></div><div style="margin-bottom: .75pt; margin-left: 0in; margin-right: 0in; margin-top: 3.0pt;"><span style="color: black; font-family: "Courier New"; font-size: 8.5pt;">D GetName PI</span><span style="color: black; font-family: "Verdana","sans-serif"; font-size: 8.5pt;"><o:p></o:p></span></div><div style="margin-bottom: .75pt; margin-left: 0in; margin-right: 0in; margin-top: 3.0pt;"><span style="color: black; font-family: "Courier New"; font-size: 8.5pt;"> D ProgLang 10A</span><span style="color: black; font-family: "Verdana","sans-serif"; font-size: 8.5pt;"><o:p></o:p></span></div><div style="margin-bottom: .75pt; margin-left: 0in; margin-right: 0in; margin-top: 3.0pt;"><span style="color: black; font-family: "Courier New"; font-size: 8.5pt;"> D COMP<span style="mso-spacerun: yes;"> </span><span style="mso-spacerun: yes;"> </span> 20A</span><span style="color: black; font-family: "Verdana","sans-serif"; font-size: 8.5pt;"><o:p></o:p></span></div><div style="margin-bottom: .75pt; margin-left: 0in; margin-right: 0in; margin-top: 3.0pt;"><span style="color: black; font-family: "Courier New"; font-size: 8.5pt;"> D Found<span style="mso-spacerun: yes;"> </span> <span style="mso-spacerun: yes;"> </span>4<span style="mso-spacerun: yes;"> </span></span><span style="color: black; font-family: "Verdana","sans-serif"; font-size: 8.5pt;"><o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="apple-style-span"><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 11.0pt;">.<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="apple-style-span"><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 11.0pt;">.<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="apple-style-span"><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 11.0pt;">.<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="apple-style-span"><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 11.0pt;">.<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="apple-style-span"><span style="color: black; font-size: 8.5pt;"><span style="mso-spacerun: yes;"> </span></span></span><span class="apple-style-span"><span style="color: black; font-family: "Courier New"; font-size: 8.5pt;">Call </span></span><span class="apple-style-span"><span style="color: black; font-size: 8.5pt;">GetName</span></span><span class="apple-style-span"><span style="color: black; font-family: "Courier New"; font-size: 8.5pt;">( :</span></span><span class="apple-style-span"><span style="color: black; font-size: 8.5pt;">EMPID</span></span><span class="apple-style-span"><span style="color: black; font-family: "Courier New"; font-size: 8.5pt;">, :</span></span><span class="apple-style-span"><span style="color: black; font-size: 8.5pt;">Comp</span></span><span class="apple-style-span"><span style="color: black; font-family: "Courier New"; font-size: 8.5pt;">, :</span></span><span class="apple-style-span"><span style="color: black; font-size: 8.5pt;">Found</span></span><span class="apple-style-span"><span style="color: black; font-family: "Courier New"; font-size: 8.5pt;">)</span></span><span class="apple-style-span"><span style="color: black; font-size: 8.5pt;">;</span></span><span class="apple-style-span"><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 11.0pt;"><o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="apple-style-span"><u><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 11.0pt;">Example 2.<o:p></o:p></span></u></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div><div style="margin-bottom: .75pt; margin-left: 0in; margin-right: 0in; margin-top: 3.0pt;"><span style="color: black; font-family: "Courier New"; font-size: 8.5pt;">c/EXEC SQL<span style="mso-spacerun: yes;"> </span><o:p></o:p></span></div><div style="margin-bottom: .75pt; margin-left: 0in; margin-right: 0in; margin-top: 3.0pt;"><span style="color: black; font-family: "Courier New"; font-size: 8.5pt;">c+ Create procedure OrderSumm(<span style="mso-spacerun: yes;"> </span><o:p></o:p></span></div><div style="margin-bottom: .75pt; margin-left: 0in; margin-right: 0in; margin-top: 3.0pt;"><span style="color: black; font-family: "Courier New"; font-size: 8.5pt;">c+<span style="mso-spacerun: yes;"> </span>in<span style="mso-spacerun: yes;"> </span>partnerID<span style="mso-spacerun: yes;"> </span>NUMERIC(5,0),<span style="mso-spacerun: yes;"> </span><o:p></o:p></span></div><div style="margin-bottom: .75pt; margin-left: 0in; margin-right: 0in; margin-top: 3.0pt;"><span style="color: black; font-family: "Courier New"; font-size: 8.5pt;">c+<span style="mso-spacerun: yes;"> </span>in<span style="mso-spacerun: yes;"> </span>storeNumber<span style="mso-spacerun: yes;"> </span>NUMERIC(7,0) )<span style="mso-spacerun: yes;"> </span><o:p></o:p></span></div><div style="margin-bottom: .75pt; margin-left: 0in; margin-right: 0in; margin-top: 3.0pt;"><span style="color: black; font-family: "Courier New"; font-size: 8.5pt;">C+ LANGUAGE RPGLE NOT DETERMINISTIC<span style="mso-spacerun: yes;"> </span><o:p></o:p></span></div><div style="margin-bottom: .75pt; margin-left: 0in; margin-right: 0in; margin-top: 3.0pt;"><span style="color: black; font-family: "Courier New"; font-size: 8.5pt;">c+ READS SQL DATA<span style="mso-spacerun: yes;"> </span><o:p></o:p></span></div><div style="margin-bottom: .75pt; margin-left: 0in; margin-right: 0in; margin-top: 3.0pt;"><span style="color: black; font-family: "Courier New"; font-size: 8.5pt;">c+ EXTERNAL NAME EXTPGM01<span style="mso-spacerun: yes;"> </span><o:p></o:p></span></div><div style="margin-bottom: .75pt; margin-left: 0in; margin-right: 0in; margin-top: 3.0pt;"><span style="color: black; font-family: "Courier New"; font-size: 8.5pt;">c+ PARAMETER STYLE GENERAL<span style="mso-spacerun: yes;"> </span><o:p></o:p></span></div><div style="margin-bottom: .75pt; margin-left: 0in; margin-right: 0in; margin-top: 3.0pt;"><span style="color: black; font-family: "Courier New"; font-size: 8.5pt;">c/END-EXEC</span><span style="mso-spacerun: yes;"> </span><span style="mso-spacerun: yes;"> </span><span class="apple-style-span"><u><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 12.0pt;"><o:p></o:p></span></u></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="apple-style-span"><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 11.0pt;">Above code will be called as below<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="apple-style-span"><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 11.0pt;">Create another RPGLE code as below<o:p></o:p></span></span></div><div style="margin-bottom: .75pt; margin-left: 0in; margin-right: 0in; margin-top: 3.0pt;"><span style="color: black; font-family: "Courier New"; font-size: 8.5pt;">D OrderSumm PI</span><span style="color: black; font-family: "Verdana","sans-serif"; font-size: 8.5pt;"><o:p></o:p></span></div><div style="margin-bottom: .75pt; margin-left: 0in; margin-right: 0in; margin-top: 3.0pt;"><span style="color: black; font-family: "Courier New"; font-size: 8.5pt;"> D PartnerID 5</span><span style="color: black; font-family: "Verdana","sans-serif"; font-size: 8.5pt;"><o:p></o:p></span></div><div style="margin-bottom: .75pt; margin-left: 0in; margin-right: 0in; margin-top: 3.0pt;"><span style="color: black; font-family: "Courier New"; font-size: 8.5pt;"> D StoreNumber 7</span><span style="color: black; font-family: "Verdana","sans-serif"; font-size: 8.5pt;"><o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="apple-style-span"><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 11.0pt;">.<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="apple-style-span"><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 11.0pt;">.<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="apple-style-span"><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 11.0pt;">.<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="apple-style-span"><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 11.0pt;">.<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="apple-style-span"><span style="color: black; font-size: 8.5pt;"><span style="mso-spacerun: yes;"> </span></span></span><span class="apple-style-span"><span style="color: black; font-family: "Courier New"; font-size: 8.5pt;">Call </span></span><span class="apple-style-span"><span style="color: black; font-size: 8.5pt;">OrderSumm</span></span><span class="apple-style-span"><span style="color: black; font-family: "Courier New"; font-size: 8.5pt;">( :</span></span><span class="apple-style-span"><span style="color: black; font-size: 8.5pt;">PartnerId</span></span><span class="apple-style-span"><span style="color: black; font-family: "Courier New"; font-size: 8.5pt;">, :</span></span><span class="apple-style-span"><span style="color: black; font-size: 8.5pt;">StoreNumber</span></span><span class="apple-style-span"><span style="color: black; font-family: "Courier New"; font-size: 8.5pt;">)</span></span><span class="apple-style-span"><span style="color: black; font-size: 8.5pt;">;</span></span><span class="apple-style-span"><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 11.0pt;"><o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="apple-style-span"><span style="color: black; font-size: 9.0pt; mso-bidi-font-size: 11.0pt;">In example 2 when OrderSumm is called, it in returns call further a new program “EXTPGM01” <o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">Do write me @ iSeriesblogs@gmail.com for any query or you can simply post comments below.<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">Happy Learning<o:p></o:p></span></div><div class="MsoNormal" style="background: white; line-height: normal;"><br />
</div></div>Sumit Chhabrahttp://www.blogger.com/profile/05338620317642568093noreply@blogger.com0tag:blogger.com,1999:blog-2372130058947512886.post-88314036893076312942011-07-04T11:05:00.000+05:302011-07-04T11:05:50.605+05:30XML Parsing using XML-INTO<div dir="ltr" style="text-align: left;" trbidi="on"><br />
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><b><u><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">Something on XML-INTO<o:p></o:p></span></u></b></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">IBM has introduce a new OP-Code XML-INTO, which has make XML parsing as smooth as butter..:) <o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">Steps to use XML-INTO<o:p></o:p></span></div><div class="MsoListParagraphCxSpFirst" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l0 level1 lfo1; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify; text-indent: -.25in;"><!--[if !supportLists]--><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: Calibri; mso-hansi-font-family: Calibri;"><span style="mso-list: Ignore;">1.<span style="font: 7.0pt "Times New Roman";"> </span></span></span><!--[endif]--><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">Define a Data structure or array of data structures that matches the layout of XML document.<o:p></o:p></span></div><div class="MsoListParagraphCxSpLast" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l0 level1 lfo1; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify; text-indent: -.25in;"><!--[if !supportLists]--><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: Calibri; mso-hansi-font-family: Calibri;"><span style="mso-list: Ignore;">2.<span style="font: 7.0pt "Times New Roman";"> </span></span></span><!--[endif]--><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">Run XML-INTO (This loads the values from XML to Data Structure<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">Below sample code will help you write simple XML Parsing code.<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="font-family: "Courier New"; font-size: 8.0pt; mso-bidi-font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">D qualName<span style="mso-spacerun: yes;"> </span>DS<span style="mso-spacerun: yes;"> </span>QUALIFIED<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="font-family: "Courier New"; font-size: 8.0pt; mso-bidi-font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">D<span style="mso-spacerun: yes;"> </span>FrstName<span style="mso-spacerun: yes;"> </span><span style="mso-spacerun: yes;"> </span><span style="mso-spacerun: yes;"> </span>10A<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="font-family: "Courier New"; font-size: 8.0pt; mso-bidi-font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">D<span style="mso-spacerun: yes;"> </span>LastName<span style="mso-spacerun: yes;"> </span>10A<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="font-family: "Courier New"; font-size: 8.0pt; mso-bidi-font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">D EmpInfo<span style="mso-spacerun: yes;"> </span>DS<span style="mso-spacerun: yes;"> </span>QUALIFIED<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="font-family: "Courier New"; font-size: 8.0pt; mso-bidi-font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">D* Employee Name<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="font-family: "Courier New"; font-size: 8.0pt; mso-bidi-font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">D<span style="mso-spacerun: yes;"> </span>EMPNme <span style="mso-spacerun: yes;"> </span>LIKEDS(qualName)<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="font-family: "Courier New"; font-size: 8.0pt; mso-bidi-font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">D* Manager Name<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="font-family: "Courier New"; font-size: 8.0pt; mso-bidi-font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">D<span style="mso-spacerun: yes;"> </span>MName<span style="mso-spacerun: yes;"> </span><span style="mso-spacerun: yes;"> </span>LIKEDS(qualName)<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="font-family: "Courier New"; font-size: 8.0pt; mso-bidi-font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">D* Manager Last Name<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="font-family: "Courier New"; font-size: 8.0pt; mso-bidi-font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">D MLName<span style="mso-spacerun: yes;"> </span>S<span style="mso-spacerun: yes;"> </span>10A<span style="mso-spacerun: yes;"> </span>VARYING<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="font-family: "Courier New"; font-size: 8.0pt; mso-bidi-font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>//<span style="mso-spacerun: yes;"> </span>Assume file Emplpoyee.xml contains the following lines:<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="font-family: "Courier New"; font-size: 8.0pt; mso-bidi-font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>//<span style="mso-spacerun: yes;"><</span>empinfo><o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="font-family: "Courier New"; font-size: 8.0pt; mso-bidi-font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>//<span style="mso-spacerun: yes;"> </span><EMPNAME><Frstname>Scott</Frstname><LastName>Sherbert</LastName></EMPNAME><o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="font-family: "Courier New"; font-size: 8.0pt; mso-bidi-font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>//<span style="mso-spacerun: yes;"> </span><MNANE Frstname="Ken" LastName="Mahajan"></MNAME><o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="font-family: "Courier New"; font-size: 8.0pt; mso-bidi-font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>//</empinfo><o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="font-family: "Courier New"; font-size: 8.0pt; mso-bidi-font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"><br />
</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="font-family: "Courier New"; font-size: 8.0pt; mso-bidi-font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>/free<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="font-family: "Courier New"; font-size: 8.0pt; mso-bidi-font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span><span style="mso-spacerun: yes;"> </span>xml-into empInfo %XML('Employee.xml' : 'doc=file');<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="font-family: "Courier New"; font-size: 8.0pt; mso-bidi-font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>// EMPInfo.EMPNme.Frstname='Scott' EMPInfo.EMPNme.Lastname = 'Sherbert'<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="font-family: "Courier New"; font-size: 8.0pt; mso-bidi-font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>// EMPInfo.MName.Frstname='Ken<span style="mso-spacerun: yes;">' </span>EMPInfo.MName.Lastname = 'Mahajan'<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="font-family: "Courier New"; font-size: 8.0pt; mso-bidi-font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>// Parse the "empinfo/Mname/Lastname" information into variable<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="font-family: "Courier New"; font-size: 8.0pt; mso-bidi-font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>// "MLName".<span style="mso-spacerun: yes;"> </span>Use the "path" option to specify the location<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="font-family: "Courier New"; font-size: 8.0pt; mso-bidi-font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>// of this information in the XML document.<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="font-family: "Courier New"; font-size: 8.0pt; mso-bidi-font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>xml-into MLName %XML('Employee.xml'<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="font-family: "Courier New"; font-size: 8.0pt; mso-bidi-font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span><span style="mso-spacerun: yes;"> </span>: 'doc=file path=empinfo/MName/lastname';<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="font-family: "Courier New"; font-size: 8.0pt; mso-bidi-font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>// MLName = 'Mahajan'<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">Do write me @ iSeriesblogs@gmail.com for any query or you can simply post comments below.<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">Happy Learning<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br />
</div></div>Sumit Chhabrahttp://www.blogger.com/profile/05338620317642568093noreply@blogger.com0tag:blogger.com,1999:blog-2372130058947512886.post-72224528420243550602011-07-03T23:28:00.000+05:302011-07-03T23:28:44.034+05:30Advantages and Disadvantages of AS400<div dir="ltr" style="text-align: left;" trbidi="on"><br />
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9pt;">Hi Guys, </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9pt;"><br />
</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9pt;">One of my friend over Linkedin want to know the advantages and disadvantages of AS400. So thought of sharing the same.</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><b><u><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">Advantage of AS400<o:p></o:p></span></u></b></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">The main advantages of AS400 over other are</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span class="Apple-style-span" style="font-size: 12px;"> 1. <i><u>Stability</u></i></span></div><div class="MsoListParagraphCxSpMiddle" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">You would have sometime re-booted your desktop twice or thrice a day. But can you afford to do the same with your Servers. I know few organizations where IT admin are not aware of AS/400 Machine reboot date<o:p></o:p></span></div><div class="MsoListParagraphCxSpMiddle" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l0 level1 lfo1; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify; text-indent: -.25in;"><!--[if !supportLists]--><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: Calibri; mso-hansi-font-family: Calibri;"><span style="mso-list: Ignore;">2.<span style="font: 7.0pt "Times New Roman";"> </span></span></span><!--[endif]--><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;"><u><i>Integration</i></u><o:p></o:p></span></div><div class="MsoListParagraphCxSpMiddle" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">Using AS400 you need not worry about integration of database, Programing languages to use<o:p></o:p></span></div><div class="MsoListParagraphCxSpMiddle" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">Will the security system work with the database and will the database work with the operating system, etc are questions that do not apply to AS/400. It is amazing how much time can be invested in making these things work together, but on iSeries (as with AS/400 and S/38), the question is simply irrelevant.<o:p></o:p></span></div><div class="MsoListParagraphCxSpMiddle" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l0 level1 lfo1; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify; text-indent: -.25in;"><!--[if !supportLists]--><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: Calibri; mso-hansi-font-family: Calibri;"><span style="mso-list: Ignore;">3.<span style="font: 7.0pt "Times New Roman";"> </span></span></span><!--[endif]--><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;"><u><i>Security</i></u><o:p></o:p></span></div><div class="MsoListParagraphCxSpLast" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">OS/400 is an object orientated operating system it is virtually immune to viruses.<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span class="Apple-style-span" style="font-size: 12px;"><b><u><br />
</u></b></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><b><u><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">Disadvantage of AS400<o:p></o:p></span></u></b></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">To count disadvantages of AS400, there would be less. Still to count, below are the few disadvantages of AS400<o:p></o:p></span></div><div class="MsoListParagraphCxSpFirst" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l1 level1 lfo2; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify; text-indent: -.25in;"><!--[if !supportLists]--><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-size: 11.0pt; mso-fareast-font-family: Calibri; mso-hansi-font-family: Calibri;"><span style="mso-list: Ignore;">1.<span style="font: 7.0pt "Times New Roman";"> </span></span></span><span style="color: black; font-size: 9pt;"><u><i>Cost</i></u></span><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;"> - </span></div><div class="MsoListParagraphCxSpFirst" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l1 level1 lfo2; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify; text-indent: -.25in;"><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;"> T</span><span class="Apple-style-span" style="font-size: 12px;">he hardware costs for NEW machines are significantly higher than other platforms.</span></div><div class="MsoListParagraphCxSpMiddle" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l1 level1 lfo2; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify; text-indent: -.25in;"><!--[if !supportLists]--><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: Calibri; mso-hansi-font-family: Calibri;"><span style="mso-list: Ignore;">2.</span></span><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: Calibri; mso-hansi-font-family: Calibri;"><span style="mso-list: Ignore;"><span style="font: 7.0pt "Times New Roman";"> </span></span></span><u><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;"><i>Developer Resource</i></span></u><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;"> – </span></div><div class="MsoListParagraphCxSpMiddle" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l1 level1 lfo2; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify; text-indent: -.25in;"><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;"> Nowadays RPG Developer are a rare breed of developers which comes with high Dollar value as compare to other development languages like JAVA</span></div><div class="MsoListParagraphCxSpLast" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l1 level1 lfo2; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify; text-indent: -.25in;"><!--[if !supportLists]--><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: Calibri; mso-hansi-font-family: Calibri;"><span style="mso-list: Ignore;">3.<span style="font: 7.0pt "Times New Roman";"> </span></span></span><u><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;"><i>Mindset</i></span></u><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;"> - </span></div><div class="MsoListParagraphCxSpLast" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l1 level1 lfo2; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify; text-indent: -.25in;"><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;"> T</span><span class="Apple-style-span" style="font-size: 12px;">he biggest problem is mindset of people. An AS400 is seen as a clumsy box capable of only green screen applications and suitable only for large businesses having large cash transactions. This is simply untrue. The box needs almost no supervision. All you ever have to do once it's loaded up is change the backup tapes. BUT, it is perceived as old technology when in fact it is probably more robust and cutting edge than most of it's competitors.</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">Do write me @ iSeriesblogs@gmail.com for any query or you can simply post comments below.<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9.0pt; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-weight: bold; mso-fareast-font-family: "Times New Roman"; mso-hansi-font-family: Calibri;">Happy Learning<o:p></o:p></span></div></div>Sumit Chhabrahttp://www.blogger.com/profile/05338620317642568093noreply@blogger.com2tag:blogger.com,1999:blog-2372130058947512886.post-13650714645820999352011-07-03T13:02:00.000+05:302011-07-03T13:02:32.274+05:30Few SQL Questions<div dir="ltr" style="text-align: left;" trbidi="on"><br>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; text-align: justify;"><span class="Apple-style-span" style="font-size: 12px;">Hi Guy, </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; text-align: justify;"><span class="Apple-style-span" style="font-size: 12px;">This time I am back with Few SQL Questions....</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; text-align: justify;"><br>
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><b><i style="mso-bidi-font-style: normal;"><span style="color: black; font-size: 9pt;">What will runsqlstm will do?</span></i></b></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9pt;">If we want to execute set of SQL statement then we can write all the SQL statement to be including with the source.<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9pt;">Type as SQL<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9pt;">Only insert, update and delete and no select is allowed.<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9pt;">Then use STRSQLSTM to execute the program<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9pt;">RUNSQLSTM SRCFILE (LIB/TEST) SRCMBR (SQL01) COMMIT (*NONE)<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br>
</div></div><a href="http://iseriesblogs.blogspot.com/2011/07/few-sql-questions.html#more">Read more »</a>Sumit Chhabrahttp://www.blogger.com/profile/05338620317642568093noreply@blogger.com2tag:blogger.com,1999:blog-2372130058947512886.post-51917968365248656152011-07-02T12:19:00.000+05:302011-07-02T19:01:19.969+05:30Embedded SQL in RPGLE (SQLRPGLE)<div dir="ltr" style="text-align: left;" trbidi="on"><br>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; text-align: justify;"><span style="color: black; font-size: 9pt;">Hi Guys,</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; text-align: justify;"><span style="color: black; font-size: 9pt;">Today I am here with Embedded SQL Program.</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; text-align: justify;"><b><u><span style="color: black; font-size: 9pt;"><br>
</span></u></b></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; text-align: justify;"><b><u><span style="color: black; font-size: 9pt;">How to write embedded SQL Program.<o:p></o:p></span></u></b></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; text-align: justify;"><br>
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><span style="color: black; font-size: 9pt;">SQLRPGLE is very simple if we can understand the flow of program. SQLRPGLE basically consist of below keyword<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br>
</div><div class="MsoListParagraphCxSpFirst" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l2 level1 lfo1; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify; text-indent: -.25in;"><span style="color: black; font-size: 9pt;">1.<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span></span><span style="color: black; font-size: 9pt;">Declare Cursor<o:p></o:p></span></div><div class="MsoListParagraphCxSpMiddle" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l2 level1 lfo1; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify; text-indent: -.25in;"><span style="color: black; font-size: 9pt;">2.<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span></span><span style="color: black; font-size: 9pt;">Open Cursor<o:p></o:p></span></div><div class="MsoListParagraphCxSpMiddle" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l2 level1 lfo1; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify; text-indent: -.25in;"><span style="color: black; font-size: 9pt;">3.<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span></span><span style="color: black; font-size: 9pt;">Fetch Cursor<o:p></o:p></span></div><div class="MsoListParagraphCxSpLast" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l2 level1 lfo1; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify; text-indent: -.25in;"><span style="color: black; font-size: 9pt;">4.<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span></span><span style="color: black; font-size: 9pt;">Close Cursor<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; tab-stops: 14.0in; text-align: justify;"><br>
<br>
</div></div><a href="http://iseriesblogs.blogspot.com/2011/07/embedded-sql-in-rpgle-sqlrpgle.html#more">Read more »</a>Sumit Chhabrahttp://www.blogger.com/profile/05338620317642568093noreply@blogger.com0tag:blogger.com,1999:blog-2372130058947512886.post-87632328174827920452011-07-01T18:01:00.000+05:302011-07-03T13:22:51.740+05:30SQL400 for Beginners Part 4<div dir="ltr" style="text-align: left;" trbidi="on"><br>
<h1 style="margin-bottom: 0pt; margin-left: 0in; margin-right: 0in; margin-top: 0in; text-align: justify; text-indent: 0in;"><span class="Apple-style-span" style="font-family: Calibri, sans-serif;"><span class="Apple-style-span" style="font-size: 12px;"><div class="MsoNormal" style="line-height: normal; margin-bottom: 0in; text-align: justify;"><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><span class="Apple-style-span"><span style="color: black; font-size: 9pt; font-weight: normal;">Hi Guys,</span></span></div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><span class="Apple-style-span"><span style="color: black; font-size: 9pt; font-weight: normal;">I am back with Joins in SQL400. I hope till now you would have basic understanding of SQl400. After this blog, you would be able to know more on Joins.</span></span></div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><span class="Apple-style-span"><span style="color: black; font-size: 9pt; font-weight: normal;"><br>
</span></span></div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><span class="Apple-style-span"><span style="color: black; font-size: 9pt;"><u><i>SQL JOIN Statements</i></u></span></span></div></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0in; text-align: justify;"><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><span class="Apple-style-span" style="font-weight: normal;"><br>
</span></div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><span class="Apple-style-span" style="font-weight: normal;"><span style="color: black; font-size: 9pt;">There are five types of SQL Joins</span></span></div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><span class="Apple-style-span" style="font-weight: normal;"><span style="color: black; font-size: 9pt;">1.Inner Join</span></span></div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><span class="Apple-style-span" style="font-weight: normal;"><span style="color: black; font-size: 9pt;">2.Left Outer Join</span></span></div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><span class="Apple-style-span" style="font-weight: normal;"><span style="color: black; font-size: 9pt;">3. Exception Join</span></span></div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><span class="Apple-style-span" style="font-weight: normal;"><span style="color: black; font-size: 9pt;">4. Cross Join</span></span></div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><span class="Apple-style-span" style="font-weight: normal;"><span style="color: black; font-size: 9pt;">5. Multiple Join types in one Statement</span></span></div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><span class="Apple-style-span" style="font-weight: normal;"><span style="color: black; font-size: 9pt;"></span></span><br>
</div></div></span></span></h1></div><a href="http://iseriesblogs.blogspot.com/2011/07/sql400-for-beginners-part-4.html#more">Read more »</a>Sumit Chhabrahttp://www.blogger.com/profile/05338620317642568093noreply@blogger.com1tag:blogger.com,1999:blog-2372130058947512886.post-73375488456496811422011-07-01T15:32:00.000+05:302011-07-02T11:11:36.361+05:30SQL400 for Beginners Part 3<div dir="ltr" style="text-align: left;" trbidi="on"><h1 style="margin: 0in 0in 0pt; text-align: justify; text-indent: 0in; text-justify: inter-ideograph;"><span class="Apple-style-span" style="font-family: Calibri, sans-serif;"><span class="Apple-style-span" style="font-size: 12px;"><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; text-align: justify;"><span class="Apple-style-span" style="font-weight: normal;">Hi Guys,</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; text-align: justify;"><span class="Apple-style-span" style="font-weight: normal;">I am back with 3rd tutorial of SQL400. After going through this blog, I hope all of you would be having basic understanding of SQL400</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; text-align: justify;"><u><span style="color: black; font-size: 9pt; font-weight: normal;"><br>
</span></u></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; text-align: justify;"><u><span style="color: black; font-size: 9pt;">SQL ORDER BY</span></u><b><u><span style="color: black; font-family: 'Times New Roman', serif; font-size: 18pt;"><o:p></o:p></span></u></b></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; text-align: justify;"><span style="color: black; font-size: 9pt;"><br>
</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; text-align: justify;"><span style="color: black; font-size: 9pt;">‘<span class="Apple-style-span" style="font-weight: normal;">ORDER BY’ is used to list the output in a particular order. This could be in ascending order, in descending order, or could be based on either numerical value or text value.</span></span><span style="color: black; font-family: 'Times New Roman', serif; font-size: 18pt; font-weight: normal;"><o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; text-align: justify;"><span class="Apple-style-span" style="font-weight: normal;"><span style="color: black; font-size: 9pt;"><br>
</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; text-align: justify;"><span class="Apple-style-span" style="font-weight: normal;"><span style="color: black; font-size: 9pt;">The syntax for an ORDER BY statement is as follows:</span><span style="color: black; font-family: 'Times New Roman', serif; font-size: 18pt;"><o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; text-align: justify;"><span class="Apple-style-span" style="font-weight: normal;"><span style="color: black; font-size: 9pt;"><i>SELECT "column_name" FROM "table_name" [WHERE "condition"] ORDER BY"column_name" [ASC, DESC]</i></span><span style="color: black; font-family: 'Times New Roman', serif; font-size: 18pt;"><o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; text-align: justify;"><span class="Apple-style-span" style="font-weight: normal;"><br>
</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; text-align: justify;"><span class="Apple-style-span" style="font-weight: normal;"><span style="color: black; font-size: 9pt;">It is possible to order by more than one column.</span><span style="color: black; font-family: 'Times New Roman', serif; font-size: 18pt;"><o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; text-align: justify;"><span class="Apple-style-span" style="font-weight: normal;"><span style="color: black; font-size: 9pt;"><br>
</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; text-align: justify;"><span class="Apple-style-span" style="font-weight: normal;"><span style="color: black; font-size: 9pt;">ORDER BY "column_name1" [ASC, DESC], "column_name2" [ASC, DESC]</span><span style="color: black; font-family: 'Times New Roman', serif; font-size: 18pt;"><o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; text-align: justify;"><span class="Apple-style-span" style="font-weight: normal;"><br>
</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; text-align: justify;"><span style="color: black; font-size: 9pt;"><i>SELECT * FROM TBLSTR ORDER BY STRNAM</i></span><span style="color: black; font-family: 'Times New Roman', serif; font-size: 18pt;"><o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; text-align: justify;"><span class="Apple-style-span" style="font-weight: normal;"><span style="color: black; font-size: 9pt;"><br>
</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; text-align: justify;"><span class="Apple-style-span" style="font-weight: normal;"><span style="color: black; font-size: 9pt;">The above statement will select all records from file TBLSTR and sort them based upon the ascending values of column STRNAM</span><span style="color: black; font-family: 'Times New Roman', serif; font-size: 18pt;"><o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; mso-outline-level: 2; text-align: justify;"><br>
</div></span></span></h1></div><a href="http://iseriesblogs.blogspot.com/2011/07/sql400-for-beginners-part-3.html#more">Read more »</a>Sumit Chhabrahttp://www.blogger.com/profile/05338620317642568093noreply@blogger.com0tag:blogger.com,1999:blog-2372130058947512886.post-41808179158240955422011-06-30T16:02:00.000+05:302011-07-02T11:12:54.798+05:30SQL400 for Beginners Part 2<div dir="ltr" style="text-align: left;" trbidi="on"><br>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="font-family: Calibri, sans-serif; font-size: 14px;">Hi Guys....<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br>
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="font-family: Calibri, sans-serif; font-size: 14px;">Welcome back… Today I would share more in SQL400….<o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br>
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><b><i><u><span style="color: black; font-family: Calibri, sans-serif; font-size: 14px;">Getting Started<o:p></o:p></span></u></i></b></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br>
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="font-family: Calibri, sans-serif; font-size: 14px;">Interactive SQL/400 can be run from the Command line. Invoke STRSQL {Start SQL/400} and get at what looks like another command line, where we can key in SQL statements for immediate execution, and/or use the F4 Prompt function to navigate IBM DB2/400 data base, which is a version of UDB<o:p></o:p></span><br>
<span style="font-family: Calibri, sans-serif; font-size: 14px;"><br>
</span><br>
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhaU-jnOgGgjsBPrs3ltW1qIZkQtmPV02hF5bWUUf3ZqfptKIHh9I0HMuof0wlSZ8EzcyGAJDBYKgMRJSPKLvKvLM6_jyhxIpU4HacxN6HEezRjys7qyECQKH6jkbjfF8NrWbHDN8Zle0U/s1600/Blog2_img1.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="173" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhaU-jnOgGgjsBPrs3ltW1qIZkQtmPV02hF5bWUUf3ZqfptKIHh9I0HMuof0wlSZ8EzcyGAJDBYKgMRJSPKLvKvLM6_jyhxIpU4HacxN6HEezRjys7qyECQKH6jkbjfF8NrWbHDN8Zle0U/s320/Blog2_img1.JPG" width="320"></a></div><span style="font-family: Calibri, sans-serif; font-size: 14px;"><br>
</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><br>
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><b><i><u><span style="color: black; font-family: Calibri, sans-serif; font-size: 14px;">SQL CONCEPTS<o:p></o:p></span></u></i></b></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -.25in;"><span style="color: black; font-family: Calibri, sans-serif; font-size: 14px;">•<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span></span><span style="color: black; font-family: Calibri, sans-serif; font-size: 14px;">Using basic SQL statements and clauses <o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -.25in;"><span style="color: black; font-family: Calibri, sans-serif; font-size: 14px;">•<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span></span><span style="color: black; font-family: Calibri, sans-serif; font-size: 14px;">The SELECT clause <o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -.25in;"><span style="color: black; font-family: Calibri, sans-serif; font-size: 14px;">•<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span></span><span style="color: black; font-family: Calibri, sans-serif; font-size: 14px;">Specifying a search condition using the WHERE clause <o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -.25in;"><span style="color: black; font-family: Calibri, sans-serif; font-size: 14px;">•<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span></span><span style="color: black; font-family: Calibri, sans-serif; font-size: 14px;">ORDER BY clause <o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -.25in;"><span style="color: black; font-family: Calibri, sans-serif; font-size: 14px;">•<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span></span><span style="color: black; font-family: Calibri, sans-serif; font-size: 14px;">Date/Time arithmetic <o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -.25in;"><span style="color: black; font-family: Calibri, sans-serif; font-size: 14px;">•<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span></span><span style="color: black; font-family: Calibri, sans-serif; font-size: 14px;">Creating and using ALIAS names <o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -.25in;"><span style="color: black; font-family: Calibri, sans-serif; font-size: 14px;">•<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span></span><span style="color: black; font-family: Calibri, sans-serif; font-size: 14px;">Creating descriptive labels using the LABEL ON statement <o:p></o:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; tab-stops: list .5in; text-indent: -.25in;"><span style="color: black; font-family: Calibri, sans-serif; font-size: 14px;">•<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span></span><span style="color: black; font-family: Calibri, sans-serif; font-size: 14px;">Describing an SQL object using COMMENT ON </span><br>
<span style="color: black; font-family: Calibri, sans-serif; font-size: 14px;"></span><br>
</div></div><a href="http://iseriesblogs.blogspot.com/2011/06/sql400-for-beginners-part-2.html#more">Read more »</a>Sumit Chhabrahttp://www.blogger.com/profile/05338620317642568093noreply@blogger.com0tag:blogger.com,1999:blog-2372130058947512886.post-25830968141636193252011-06-29T21:40:00.000+05:302011-07-02T11:11:09.364+05:30SQL400 For Beginners Part - 1<div dir="ltr" style="text-align: left;" trbidi="on"><div dir="ltr" style="text-align: left;" trbidi="on"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;"><span style="font-size: 10.5pt;"><span style="font-family: Calibri;">Hi Guys....Today i just thought of sharing something on SQL400 for beginners...</span></span></div><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;"><br>
</div><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;"><span style="font-family: Calibri;"><b><i><u><span style="color: black; font-size: 10.5pt;">SQL Relational Database </span></u></i></b><span style="color: black; font-size: 10.5pt;">In the relational model of data, all data is perceived as existing in tables. DB2 UDB for AS/400 objects are created and maintained as AS/400 system objects. </span><span style="font-size: 10.5pt;"></span></span></div><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-line-break-override: restrictions; punctuation-wrap: simple; text-indent: -0.3in; vertical-align: baseline;"><br>
</div><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;"><span style="font-family: Calibri;"><b><i><u><span style="color: black; font-size: 10.5pt;">AS/400 System Terms and SQL Relational Database terms</span></u></i></b><span style="font-size: 10.5pt;"></span></span></div><table border="0" cellpadding="0" cellspacing="0" class="MsoNormalTable" style="border-collapse: collapse; mso-padding-alt: 0in 0in 0in 0in; mso-yfti-tbllook: 1184; width: 475px;"><tbody>
<tr style="height: 25.6pt; mso-yfti-firstrow: yes; mso-yfti-irow: 0;"><td style="background: yellow; border-bottom: black 1pt solid; border-left: black 1pt solid; border-right: black 1pt solid; border-top: black 1pt solid; height: 25.6pt; mso-border-bottom-alt: .5pt; mso-border-color-alt: black; mso-border-left-alt: 1.0pt; mso-border-right-alt: .5pt; mso-border-style-alt: solid; mso-border-top-alt: 1.0pt; padding-bottom: 0in; padding-left: 0.4pt; padding-right: 0.4pt; padding-top: 0.4pt; width: 162.5pt;" valign="bottom" width="217"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; vertical-align: bottom;"><span style="font-family: Calibri;"><b><span style="color: black; font-size: 10.5pt;">System Terms </span></b><span style="font-size: 10.5pt;"></span></span></div></td><td style="background: yellow; border-bottom: black 1pt solid; border-left: #f0f0f0; border-right: black 1pt solid; border-top: black 1pt solid; height: 25.6pt; mso-border-bottom-alt: .5pt; mso-border-color-alt: black; mso-border-left-alt: .5pt; mso-border-right-alt: 1.0pt; mso-border-style-alt: solid; mso-border-top-alt: 1.0pt; padding-bottom: 0in; padding-left: 0.4pt; padding-right: 0.4pt; padding-top: 0.4pt; width: 193.5pt;" valign="bottom" width="258"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; vertical-align: bottom;"><span style="font-family: Calibri;"><b><span style="color: black; font-size: 10.5pt;">SQL Terms </span></b><span style="font-size: 10.5pt;"></span></span></div></td></tr>
<tr style="height: 35.1pt; mso-yfti-irow: 1;"><td style="background-color: transparent; border-bottom: black 1pt solid; border-left: black 1pt solid; border-right: black 1pt solid; border-top: #f0f0f0; height: 35.1pt; mso-border-alt: solid black .5pt; mso-border-top-alt: solid black .5pt; padding-bottom: 0in; padding-left: 0.4pt; padding-right: 0.4pt; padding-top: 0.4pt; width: 162.5pt;" valign="top" width="217"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; vertical-align: top;"><span style="font-family: Calibri;"><b><span style="color: black; font-size: 10.5pt;">Library</span></b><span style="color: black; font-size: 10.5pt;">. Groups related objects and allows you to find the objects by name. </span><span style="font-size: 10.5pt;"></span></span></div></td><td style="background-color: transparent; border-bottom: black 1pt solid; border-left: #f0f0f0; border-right: black 1pt solid; border-top: #f0f0f0; height: 35.1pt; mso-border-alt: solid black .5pt; mso-border-left-alt: solid black .5pt; mso-border-top-alt: solid black .5pt; padding-bottom: 0in; padding-left: 0.4pt; padding-right: 0.4pt; padding-top: 0.4pt; width: 193.5pt;" valign="top" width="258"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; vertical-align: top;"><span style="font-family: Calibri;"><b><span style="color: black; font-size: 10.5pt;">Collection</span></b><span style="color: black; font-size: 10.5pt;">. Consists of a library, a journal, a journal receiver, an SQL catalog, and optionally a data dictionary. A collection groups related objects and allows you to find the objects by name. </span><span style="font-size: 10.5pt;"></span></span></div></td></tr>
<tr style="height: 17.1pt; mso-yfti-irow: 2;"><td style="background-color: transparent; border-bottom: black 1pt solid; border-left: black 1pt solid; border-right: black 1pt solid; border-top: #f0f0f0; height: 17.1pt; mso-border-alt: solid black .5pt; mso-border-top-alt: solid black .5pt; padding-bottom: 0in; padding-left: 0.4pt; padding-right: 0.4pt; padding-top: 0.4pt; width: 162.5pt;" valign="top" width="217"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; vertical-align: top;"><span style="font-family: Calibri;"><b><span style="color: black; font-size: 10.5pt;">Physical file</span></b><span style="color: black; font-size: 10.5pt;">. A set of records. </span><span style="font-size: 10.5pt;"></span></span></div></td><td style="background-color: transparent; border-bottom: black 1pt solid; border-left: #f0f0f0; border-right: black 1pt solid; border-top: #f0f0f0; height: 17.1pt; mso-border-alt: solid black .5pt; mso-border-left-alt: solid black .5pt; mso-border-top-alt: solid black .5pt; padding-bottom: 0in; padding-left: 0.4pt; padding-right: 0.4pt; padding-top: 0.4pt; width: 193.5pt;" valign="top" width="258"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; vertical-align: top;"><span style="font-family: Calibri;"><b><span style="color: black; font-size: 10.5pt;">Table</span></b><span style="color: black; font-size: 10.5pt;">. A set of columns and rows. </span><span style="font-size: 10.5pt;"></span></span></div></td></tr>
<tr style="height: 17.1pt; mso-yfti-irow: 3;"><td style="background-color: transparent; border-bottom: black 1pt solid; border-left: black 1pt solid; border-right: black 1pt solid; border-top: #f0f0f0; height: 17.1pt; mso-border-alt: solid black .5pt; mso-border-top-alt: solid black .5pt; padding-bottom: 0in; padding-left: 0.4pt; padding-right: 0.4pt; padding-top: 0.4pt; width: 162.5pt;" valign="top" width="217"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; vertical-align: top;"><span style="font-family: Calibri;"><b><span style="color: black; font-size: 10.5pt;">Record</span></b><span style="color: black; font-size: 10.5pt;">. A set of fields. </span><span style="font-size: 10.5pt;"></span></span></div></td><td style="background-color: transparent; border-bottom: black 1pt solid; border-left: #f0f0f0; border-right: black 1pt solid; border-top: #f0f0f0; height: 17.1pt; mso-border-alt: solid black .5pt; mso-border-left-alt: solid black .5pt; mso-border-top-alt: solid black .5pt; padding-bottom: 0in; padding-left: 0.4pt; padding-right: 0.4pt; padding-top: 0.4pt; width: 193.5pt;" valign="top" width="258"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; vertical-align: top;"><span style="font-family: Calibri;"><b><span style="color: black; font-size: 10.5pt;">Row</span></b><span style="color: black; font-size: 10.5pt;">. The horizontal part of a table containing a serial set of columns. </span><span style="font-size: 10.5pt;"></span></span></div></td></tr>
<tr style="height: 26.1pt; mso-yfti-irow: 4;"><td style="background-color: transparent; border-bottom: black 1pt solid; border-left: black 1pt solid; border-right: black 1pt solid; border-top: #f0f0f0; height: 26.1pt; mso-border-alt: solid black .5pt; mso-border-top-alt: solid black .5pt; padding-bottom: 0in; padding-left: 0.4pt; padding-right: 0.4pt; padding-top: 0.4pt; width: 162.5pt;" valign="top" width="217"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; vertical-align: top;"><span style="font-family: Calibri;"><b><span style="color: black; font-size: 10.5pt;">Field</span></b><span style="color: black; font-size: 10.5pt;">. One or more characters of related information of one data type. </span><span style="font-size: 10.5pt;"></span></span></div></td><td style="background-color: transparent; border-bottom: black 1pt solid; border-left: #f0f0f0; border-right: black 1pt solid; border-top: #f0f0f0; height: 26.1pt; mso-border-alt: solid black .5pt; mso-border-left-alt: solid black .5pt; mso-border-top-alt: solid black .5pt; padding-bottom: 0in; padding-left: 0.4pt; padding-right: 0.4pt; padding-top: 0.4pt; width: 193.5pt;" valign="top" width="258"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; vertical-align: top;"><span style="font-family: Calibri;"><b><span style="color: black; font-size: 10.5pt;">Column</span></b><span style="color: black; font-size: 10.5pt;">. The vertical part of a table of one data type. </span><span style="font-size: 10.5pt;"></span></span></div></td></tr>
<tr style="height: 0.3in; mso-yfti-irow: 5;"><td style="background-color: transparent; border-bottom: black 1pt solid; border-left: black 1pt solid; border-right: black 1pt solid; border-top: #f0f0f0; height: 0.3in; mso-border-alt: solid black .5pt; mso-border-top-alt: solid black .5pt; padding-bottom: 0in; padding-left: 0.4pt; padding-right: 0.4pt; padding-top: 0.4pt; width: 162.5pt;" valign="top" width="217"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; vertical-align: top;"><span style="font-family: Calibri;"><b><span style="color: black; font-size: 10.5pt;">Logical file</span></b><span style="color: black; font-size: 10.5pt;">. A subset of fields and records of one or more physical files. </span><span style="font-size: 10.5pt;"></span></span></div></td><td style="background-color: transparent; border-bottom: black 1pt solid; border-left: #f0f0f0; border-right: black 1pt solid; border-top: #f0f0f0; height: 0.3in; mso-border-alt: solid black .5pt; mso-border-left-alt: solid black .5pt; mso-border-top-alt: solid black .5pt; padding-bottom: 0in; padding-left: 0.4pt; padding-right: 0.4pt; padding-top: 0.4pt; width: 193.5pt;" valign="top" width="258"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; vertical-align: top;"><span style="font-family: Calibri;"><b><span style="color: black; font-size: 10.5pt;">View</span></b><span style="color: black; font-size: 10.5pt;">. A subset of columns and rows of one or more tables. </span><span style="font-size: 10.5pt;"></span></span></div></td></tr>
<tr style="height: 26.1pt; mso-yfti-irow: 6;"><td style="background-color: transparent; border-bottom: black 1pt solid; border-left: black 1pt solid; border-right: black 1pt solid; border-top: #f0f0f0; height: 26.1pt; mso-border-alt: solid black .5pt; mso-border-top-alt: solid black .5pt; padding-bottom: 0in; padding-left: 0.4pt; padding-right: 0.4pt; padding-top: 0.4pt; width: 162.5pt;" valign="top" width="217"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; vertical-align: top;"><span style="font-family: Calibri;"><b><span style="color: black; font-size: 10.5pt;">SQL Package</span></b><span style="color: black; font-size: 10.5pt;">. An object type that is used to run SQL statements. </span><span style="font-size: 10.5pt;"></span></span></div></td><td style="background-color: transparent; border-bottom: black 1pt solid; border-left: #f0f0f0; border-right: black 1pt solid; border-top: #f0f0f0; height: 26.1pt; mso-border-alt: solid black .5pt; mso-border-left-alt: solid black .5pt; mso-border-top-alt: solid black .5pt; padding-bottom: 0in; padding-left: 0.4pt; padding-right: 0.4pt; padding-top: 0.4pt; width: 193.5pt;" valign="top" width="258"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; vertical-align: top;"><span style="font-family: Calibri;"><b><span style="color: black; font-size: 10.5pt;">Package</span></b><span style="color: black; font-size: 10.5pt;">. An object type that is used to run SQL statements. </span><span style="font-size: 10.5pt;"></span></span></div></td></tr>
<tr style="height: 12.6pt; mso-yfti-irow: 7; mso-yfti-lastrow: yes;"><td style="background-color: transparent; border-bottom: black 1pt solid; border-left: black 1pt solid; border-right: black 1pt solid; border-top: #f0f0f0; height: 12.6pt; mso-border-bottom-alt: 1.0pt; mso-border-color-alt: black; mso-border-left-alt: 1.0pt; mso-border-right-alt: .5pt; mso-border-style-alt: solid; mso-border-top-alt: .5pt; padding-bottom: 0in; padding-left: 0.4pt; padding-right: 0.4pt; padding-top: 0.4pt; width: 162.5pt;" valign="top" width="217"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; vertical-align: top;"><span style="font-family: Calibri;"><b><span style="color: black; font-size: 10.5pt;">User Profile</span></b><span style="color: black; font-size: 10.5pt;"> </span><span style="font-size: 10.5pt;"></span></span></div></td><td style="background-color: transparent; border-bottom: black 1pt solid; border-left: #f0f0f0; border-right: black 1pt solid; border-top: #f0f0f0; height: 12.6pt; mso-border-left-alt: solid black .5pt; mso-border-top-alt: solid black .5pt; padding-bottom: 0in; padding-left: 0.4pt; padding-right: 0.4pt; padding-top: 0.4pt; width: 193.5pt;" valign="top" width="258"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; vertical-align: top;"><span style="font-family: Calibri;"><b><span style="color: black; font-size: 10.5pt;">Authorization name or Authorization ID.</span></b><span style="color: black; font-size: 10.5pt;"> </span><span style="font-size: 10.5pt;"></span></span></div></td></tr>
</tbody></table><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;"><br>
</div><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 10pt;"><span style="font-family: Calibri;"><b style="mso-bidi-font-weight: normal;"><i><u><span style="font-size: 10.5pt;"></span></u></i></b></span><br>
</div></div></div><a href="http://iseriesblogs.blogspot.com/2011/06/sql400-for-beginners-part-1.html#more">Read more »</a>Sumit Chhabrahttp://www.blogger.com/profile/05338620317642568093noreply@blogger.com0