Data Randomization Function in Oracle PL/SQLhttps://www.joellipman.com/articles/database/pl-sql/data-randomization-function-in-oracle-plsql.html

22 August 2012 in the name of "seamless integration"... How? Previously I had tried to write a smart function which could determine the datatype automatically but as you may know, Oracle has a funny way of working with dates which made this need to be changed. The...

• Type: Article
• Author: Joel Lipman
• Category: Oracle PL/SQL
• Language: en-GB

SSIS Script: convert UPPERCASE to Mixed-Case using TitleCasehttps://www.joellipman.com/articles/microsoft/ssis/ssis-script-convert-uppercase-to-mixed-case-using-titlecase.html

04 October 2013 Validate() { bool pbCancel = false; IDTSInput100 input = ComponentMetaData.InputCollection[0]; string errorMsg1 = "Invalid datatype for {0}. Inputs must be DT_STR or DT_WSTR"; string errorMsg2 = "Invalid usage type for {0}. Inputs must be READWRITE"; //...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Integration Services
• Language: en-GB

Zoho Deluge - Counting in a Map dataTypehttps://www.joellipman.com/articles/crm/zoho/zoho-deluge-counting-in-a-map.html

02 September 2019 What? A quick article on how to count within a map. I didn't have too much difficulty getting this to work and I'm not sure if there are better ways of counting so I'm documenting it to see if I can refine the code or find a some short codes that will...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: en-GB

SSIS Convert a string into a datehttps://www.joellipman.com/articles/microsoft/ssis/ssis-convert-a-string-into-a-date.html

14 March 2013 account for NULLs. -- Standard TRIM(This_Date) == "" ? NULL(DT_DATE) : (DT_DATE)This_Date -- if data source column is of datatype string and european date format: return SQL date (TRIM(This_Date) == "") ? (DT_DATE)"1900-01-01" :...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Integration Services
• Language: en-GB

ZohoCRM & Xero Real-Time Invoices: Receive Webhookhttps://www.joellipman.com/articles/crm/zoho/zohocrm-xero-real-time-invoices-receive-webhook.html

10 January 2022 // // receive parameter crmAPIRequest (of datatype string in your function) m_Payload = crmAPIRequest; // // initialize response variable m_Response = Map(); // // get xero signature v_XeroSignature = "SIGNATURE_FAIL";...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

Zoho Deluge: Regex Rounding and/or Removing Trailing Zeroshttps://www.joellipman.com/articles/crm/zoho/zoho-deluge-regex-rounding-and-or-removing-trailing-zeros.html

08 February 2022 admit that round() needs to be used for currencies. The issue is that round() only rounds up decimals to integers when the datatype is an integer and not a decimal: // syntax: // v_Test9.round(2); // round(v_Test9, 2); v_Test9 = 10.0; info...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

ZohoCRM: Using Criteria with Module Record Counthttps://www.joellipman.com/articles/crm/zoho/zohocrm-using-criteria-with-module-record-count.html

14 November 2023 info v_Pages; Call with datetime criteria: NB: You can't compare a date time value to a field that is simply of datatype: date v_PerPage = 200; v_CountCriteria = "((Quote_Stage:equals:" + encodeUrl("Draft") + ")AND(Created_Time:less_than:" +...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

Joes Quicklist Weblinks (JQW)https://www.joellipman.com/component/content/article/joes-quicklist-weblinks-jqw.html?catid=40

03 July 2011 something like the following error after upgrading/installing this module: Warning: in_array() [function.in-array]: Wrong datatype for second argument in /public_html/modules/mod_quicklistweblinks/helper.php on line 214 This is because you have not...

• Type: Article
• Author: Joel Lipman
• Category: Product Documentation
• Language: *

SSRS Dropdown parameter cannot be blank!https://www.joellipman.com/articles/microsoft/ssrs/ssrs-dropdown-to-allow-blank-values.html

14 December 2011 rather than the value field) to make this all work. Notes: My convert part of my SQL query is because I had to match the datatype used by our reporting server for the ItemID field which is some sort of hexadecimal ID value that Microsoft are so very...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• Language: en-GB

Basic Oracle Function Structurehttps://www.joellipman.com/articles/database/pl-sql/basic-oracle-function-structure.html

15 January 2012 AD name as user parameters. Syntax: CREATE [OR REPLACE] FUNCTION function_name [ (parameter [,parameter]) ] RETURN return_datatype IS | AS [declaration_section] BEGIN executable_section [EXCEPTION exception_section] END [function_name]; Example: My...

• Type: Article
• Author: Joel Lipman
• Category: Oracle PL/SQL
• Language: en-GB

DataJumble - Shuffling characters in a data valuehttps://www.joellipman.com/articles/database/t-sql/data-shuffling-function.html

01 August 2012 time; DECLARE @DateMatch int; DECLARE @TimeMatch int; DECLARE @ColumnTypeIsDate int; -- Format given value (prevents CHAR datatype issue) SET @OrigVal = LTRIM(RTRIM(@OrigVal)); -- Set variable default values SET @NewVal = ''; SET @OrigLen =...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: *

DataScramble - Randomizing data rowshttps://www.joellipman.com/articles/database/t-sql/datascramble-randomizing-data-rows.html

07 December 2012 @ColumnTypeIsDate int; DECLARE @IntOptions int; DECLARE @CharOptions varchar(max); -- Format given value (prevents CHAR datatype issue) SET @OrigVal = LTRIM(RTRIM(@OrigVal)); -- Set variable default values SET @NewVal = ''; SET @OrigLen =...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

T-SQL Conversion failed when converting the varchar to data type inthttps://www.joellipman.com/articles/database/t-sql/t-sql-conversion-failed-when-converting-the-varchar-to-data-type-int.html

14 May 2013 a number and a string together; then remembers it's a Microsoft by-product and realises it can't. Solved by checking the datatypes for each field in the concatenation string: LTRIM(RTRIM(rm.Name + ', ' + rm.[Zone] + ', ' + CONVERT(VARCHAR(4),...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

T-SQL: Parse an XML valuehttps://www.joellipman.com/articles/database/t-sql/t-sql-parse-an-xml-value.html

27 March 2014 AS Gender -- Returns records where GENDER = "Male" More Examples (Note that Event_XML is of datatype XML in the following examples): -- exist() returns 1 or 0 if node exists or not respectively SELECT Event_XML.exist('/STAFF/EMPLOYEE_NUMBER') FROM...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

Zoho Creator: Button on Report for Merging Multiple Selected Recordshttps://www.joellipman.com/articles/crm/zoho/zoho-creator-button-on-report-for-merging-multiple-selected-records.html

06 December 2020 first? Well when I create a workflow, it will ask me to run a function, the Creator workflow process will then read what datatype of a parameter I'm sending to the function. So in "Edit this application" > go to Workflow > Functions > New Function: give...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

ZohoCRM: Process all records of a modulehttps://www.joellipman.com/articles/crm/zoho/zohocrm-process-all-records-of-a-module.html

24 August 2021 that I now use frequently in client systems. How? The gist is that we add a checkbox called "Processed" which will have a datatype Boolean. Our function will then loop through each record and do what it has to do. The workaround here is that we order...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

Zoho Deluge: Calculate Days, Hours, Minutes, Seconds between two Timestampshttps://www.joellipman.com/articles/crm/zoho/zoho-deluge-calculate-days,-hours,-minutes,-seconds-between-two-timestamps.html

21 September 2021 other systems but here's one in Zoho Deluge. How? We're going to make use of the .toLong() function applied to a datetime datatype variable which will return the Unix seconds. // the now time v_NowTime = zoho.currenttime; // // correction: I want to...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

Zoho Creator / eBay: Get all Active Productshttps://www.joellipman.com/articles/crm/zoho/zoho-creator-ebay-get-all-active-products.html

13 October 2021 is a function which, if given the page number and the number of entries per page, returns these in JSON as a Zoho Map datatype. How? I'm not going to go into detail on how I create an access token to query the eBay Trading API as you can read this in my...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

Zoho Inventory & eBay Picture Services: UploadSiteHostedPictureshttps://www.joellipman.com/articles/crm/zoho/zoho-inventory-ebay-picture-services-uploadsitehostedpictures.html

31 March 2022 on the tin Category: Standalone Click on Create Give it the parameter/argument p_InventoryitemID as an Integer/Long/Number datatype As the first part of your code, enter the following as code (you'll need to speify your own App Owner and App Name): //...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

ZohoCRM: Daily Follow Up and Remind Record Owner to Convert Leadhttps://www.joellipman.com/articles/crm/zoho/zohocrm-follow-up-and-remind-record-owner-to-convert-lead.html

14 May 2024 get created time (no need to check if null?) v_LeadCreatedTime = r_LeadDetails.get("Created_Time"); // // convert to time datatype v_TimeOffset = (v_LeadCreatedTime.subString(0,10) + " " + v_LeadCreatedTime.subString(11,25)).toString("yyyy-MM-dd...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *