{"type":"doc","content":[{"type":"heading","attrs":{"level":5},"content":[{"text":"Writing Power SQL","type":"text"}]},{"type":"paragraph","content":[{"text":"When you switch from the ","type":"text"},{"text":"Design","type":"text","marks":[{"type":"strong"}]},{"text":" tab to the ","type":"text"},{"text":"Edit","type":"text","marks":[{"type":"strong"}]},{"text":" tab, the SQL in the ","type":"text"},{"text":"Design","type":"text","marks":[{"type":"strong"}]},{"text":" tab is constructed and displayed as a complete SQL statement in the later. Using the ","type":"text"},{"text":"Edit","type":"text","marks":[{"type":"strong"}]},{"text":" tab, you can view and write complex SQL statements that cannot be defined using the ","type":"text"},{"text":"Design","type":"text","marks":[{"type":"strong"}]},{"text":" tab.","type":"text"}]},{"type":"paragraph","content":[{"text":"During Query writing, you can make use of ","type":"text"},{"text":"Context Help","type":"text","marks":[{"type":"strong"}]},{"text":". It assists you in correct code (syntax) formation. As you type the code, you can view the context help to select the options. A window pops up for you to choose the right option and disappears upon choosing the option.","type":"text"}]},{"type":"paragraph","content":[{"text":"Figure 8: Editing SQL Statement","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":250,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":905,"alt":"editing SQL Statement","type":"external","url":"https://docs.intellicus.com/wp-content/uploads/2019/03/fig-8-SQL.png","height":314}}]},{"type":"paragraph","content":[{"text":"To prevent a complex query (defined in the Edit tab) from being overwritten, when you switch over to the Design tab, make some changes, and get back to Edit tab; you will be prompted with a dialog.","type":"text"}]},{"type":"paragraph","content":[{"text":"Figure 9: SQL Overwrite Prompt","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":250,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":594,"alt":"SQL Overwrite Prompt","type":"external","url":"https://docs.intellicus.com/wp-content/uploads/2019/03/fig-9-SQL.png","height":223}}]},{"type":"paragraph","content":[{"text":"If you select ","type":"text"},{"text":"Yes","type":"text","marks":[{"type":"strong"}]},{"text":" your changes in the ","type":"text"},{"text":"Edit","type":"text","marks":[{"type":"strong"}]},{"text":" tab will be overwritten, because the SQL in the ","type":"text"},{"text":"Design","type":"text","marks":[{"type":"strong"}]},{"text":" tab will be reconstructed. If you select ‘No’ then the SQL in the ","type":"text"},{"text":"Edit","type":"text","marks":[{"type":"strong"}]},{"text":" tab will remain intact and shall be used as the final SQL.","type":"text"}]},{"type":"blockquote","content":[{"type":"paragraph","content":[{"text":"Note","type":"text","marks":[{"type":"strong"}]},{"text":": The SQL statement in the Edit tab will be used as the final SQL for compilation.","type":"text"}]},{"type":"paragraph","content":[{"text":"Important","type":"text","marks":[{"type":"strong"}]},{"text":": If SQL is manually specified in Edit tab (not constructed using design tab), its field details will not be available in Filter tab.","type":"text"}]}]},{"type":"heading","attrs":{"level":5},"content":[{"text":"Dynamic SQL Queries using parameters","type":"text"}]},{"type":"paragraph","content":[{"text":"To include a parameter in the SQL Query, specify a parameter enclosed within <% and %>.","type":"text"}]},{"type":"paragraph","content":[{"text":"Example: To dynamically decide the ","type":"text"},{"text":"where","type":"text","marks":[{"type":"em"}]},{"text":" clause:","type":"text"}]},{"type":"blockquote","content":[{"type":"paragraph","content":[{"text":"SELECT * FROM EmpTbl WHERE <%PrmWhrCls%>","type":"text"}]}]},{"type":"heading","attrs":{"level":5},"content":[{"text":"Dynamic SQL javascript code block","type":"text"}]},{"type":"paragraph","content":[{"text":"Use javascript code block enclosed by <@% and %@> to create a dynamic SQL Query. After execution, the code block should return a string that will be replaced in the SQL query.","type":"text"}]},{"type":"paragraph","content":[{"text":"You can use following in the code block:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"if else","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Parameter value and parameter data type","type":"text"}]}]}]},{"type":"heading","attrs":{"level":6},"content":[{"text":"Syntax of Code block","type":"text"}]},{"type":"blockquote","content":[{"type":"paragraph","content":[{"text":"<@% >","type":"text"},{"type":"hardBreak"},{"text":"[Executable block, which returns a string]","type":"text"},{"type":"hardBreak"},{"text":"<%@>","type":"text"}]}]},{"type":"heading","attrs":{"level":6},"content":[{"text":"Syntax to use parameter in code block","type":"text"}]},{"type":"blockquote","content":[{"type":"paragraph","content":[{"text":"params(“parameterName”).getAttribute()","type":"text"}]}]},{"type":"paragraph","content":[{"text":"Attributes","type":"text","marks":[{"type":"strong"}]},{"text":": “Value” to get value of the parameter and “DataType” to get data type of the parameter.","type":"text"}]},{"type":"heading","attrs":{"level":6},"content":[{"text":"Examples","type":"text"}]},{"type":"paragraph","content":[{"text":"Example 1","type":"text","marks":[{"type":"strong"}]},{"text":". Check if parameter “prmEmpNo” exists. If it does and its value is not blank, return the value, else return nothing.","type":"text"}]},{"type":"blockquote","content":[{"type":"paragraph","content":[{"text":"Select * from emp where 1=1","type":"text"},{"type":"hardBreak"},{"text":"<@%","type":"text"},{"type":"hardBreak"},{"text":"if(params(“prmEmpNo”) != null &&","type":"text"},{"type":"hardBreak"},{"text":"params(“prmEmpNo”).getValue() != “”)","type":"text"},{"type":"hardBreak"},{"text":"{","type":"text"},{"type":"hardBreak"},{"text":"return ” AND empno in <%prmEmpNo%>”;","type":"text"},{"type":"hardBreak"},{"text":"}","type":"text"},{"type":"hardBreak"},{"text":"else","type":"text"},{"type":"hardBreak"},{"text":"{","type":"text"},{"type":"hardBreak"},{"text":"return “”;","type":"text"},{"type":"hardBreak"},{"text":"}","type":"text"},{"type":"hardBreak"},{"text":"%@>","type":"text"}]}]},{"type":"paragraph","content":[{"text":"Example 2","type":"text","marks":[{"type":"strong"}]},{"text":". Check if parameter “prmSelectTable” exists. If it does not, return table name as “emp” else (if it exists) value of the parameter must be used as table name.","type":"text"}]},{"type":"blockquote","content":[{"type":"paragraph","content":[{"text":"Select * from","type":"text"},{"type":"hardBreak"},{"text":"<@% if(params(“prmSelectTable”) == null) { return “emp”; else return params(“prmSelectTable”).getValue(); } %@>","type":"text"}]}]},{"type":"paragraph","content":[{"text":"Example 3","type":"text","marks":[{"type":"strong"}]},{"text":". Construct WHERE clause dynamically (for LOCATIONTYPEID and REGNID fields).","type":"text"}]},{"type":"blockquote","content":[{"type":"paragraph","content":[{"text":"select LOCTIONID, LOCTIONNAME,LOCTYPEID,REGNID from LOCATIONMASTER where 1=1","type":"text"},{"type":"hardBreak"},{"text":"<@%","type":"text"},{"type":"hardBreak"},{"text":"var v_Str;","type":"text"},{"type":"hardBreak"},{"text":"v_Str = ”;","type":"text"},{"type":"hardBreak"},{"text":"var v_Str1;","type":"text"},{"type":"hardBreak"},{"text":"var v_Str2;","type":"text"},{"type":"hardBreak"},{"text":"var v_Str1=”;","type":"text"},{"type":"hardBreak"},{"text":"var v_Str2=”;","type":"text"},{"type":"hardBreak"},{"text":"if(params(“prmloctype”) != null &&","type":"text"},{"type":"hardBreak"},{"text":"params(“prmloctype”).getValue() != “”)","type":"text"},{"type":"hardBreak"},{"text":"{","type":"text"},{"type":"hardBreak"},{"text":"v_Str1 = ” AND LOCATIONTYPEID in (<%prmloctype%>)”;","type":"text"},{"type":"hardBreak"},{"text":"}","type":"text"},{"type":"hardBreak"},{"text":"else","type":"text"},{"type":"hardBreak"},{"text":"{","type":"text"},{"type":"hardBreak"},{"text":"v_Str1 = “”;","type":"text"},{"type":"hardBreak"},{"text":"}","type":"text"}]},{"type":"paragraph","content":[{"text":"if(params(“prmRegion”) != null &&","type":"text"},{"type":"hardBreak"},{"text":"params(“prmRegion”).getValue() != “”)","type":"text"},{"type":"hardBreak"},{"text":"{","type":"text"},{"type":"hardBreak"},{"text":"v_Str2 = ” AND REGNID = <%prmRegion%> “;","type":"text"},{"type":"hardBreak"},{"text":"}","type":"text"},{"type":"hardBreak"},{"text":"else","type":"text"},{"type":"hardBreak"},{"text":"{","type":"text"},{"type":"hardBreak"},{"text":"v_Str2 = “”;","type":"text"},{"type":"hardBreak"},{"text":"}","type":"text"}]},{"type":"paragraph","content":[{"text":"v_Str = v_Str1 +v_Str2;","type":"text"},{"type":"hardBreak"},{"text":"return v_Str;","type":"text"},{"type":"hardBreak"},{"text":"%@>","type":"text"}]}]},{"type":"heading","attrs":{"level":6},"content":[{"text":"Find Button","type":"text"}]},{"type":"paragraph","content":[{"text":"The Edit tab has a find button. For long queries (refer to the figure on previous page), you can search the query for a search string to reach the location quickly.","type":"text"}]}],"version":1}

Browser not supported