SQL Overview
- SQL stands for Structured Query Language
- It is essentially the language used to communicate with databases
- Since in our case, we are dealing with Saved Searches, we can use SQL to parang
communicate with the Netsuite database so that we can furter refine and manipulate
yung search results natin.
------------------------------------------------------------
SQL Expressions
- An SQL expression is a combination of one or more values, operators and SQL
functions that evaluate to a value.
- SQL Expressions are like formulas written in query language.
- ang iba lang is it is written in query language para maintindihan ng database ano
ba yung gusto natin mangyari
-- So think of SQL Expressions as parang functions that we use in Excel, medyo
ibang syntax lang dito sa NetSuite
- CONCAT, TRUNCATE, etc
-similar to Excel functions, we input it as a formula then we get a result
------------------------------------------------------------
SQL Functions
- so a part of
------------------------------------------------------------
So we can incorporate SQL expressions in search results by using a formula
- According to SuiteAnswers 8451, we can define formulas in searches in 3 ways
- I'll be focusing on the 3rd way since yun yung pinapagawa sa Hands on
- CRITERIA SUB TAB
------------------------------------------------------------
so focusing on the 3rd way,
Using a Formula in Search Results
- So we all get a better understanding of using formulas, let's all navigate using
our test accounts.
- Let's use nalang the Saved Search we used yesterday during the hands on
- so Lists > Search > Saved Searches
- so since SQL enables us to manipulate data, for this example, let's add the field
Name tas try natin manipulate siya
- so after natin ma-add yung name field, add tayo ulit ng isa pa, but this time,
Formula naman
- As you can see, there are several types of formula... currency, date/time, text.
we should select the formula type based on the calculation that the formula will
perform.
- so for us, since ang gusto natin manipulate is yung Name, ang piliin natin is
yung text since LOWER({name})
- then In the Formula field, dito po natin i-define yung formula. pwedeng by typing
directly in the textbox or by clicking yung Set icon.
- ang main difference nilang dalawa is if directly sa textbox mag type, parang
blinded tayo since wala tayong way to find out ano yung
so here, sa pop up window, may dropdown for function and field.
- here in the function dropdown, you can select the SQL function you want to use
- here in the field dropdown, you can select the fields na gusto natin ilagay sa
formula
- so for our example, lets select LOWER.
- this function basically replaces the text into all lower case letters
- then sa field, lets select NAME naman po.
- lets replace the char inside the paranthesis with the field name of name since
yun yung gusto natin mapalitan into all lowercase letters
------------------------------------------------------------
so since for our hands on, we were instructed to create a saved search and
incorporate a CASE Function. Discuss ko po muna briefly ano yung case function
CASE function
- pwedeng multiple WHEN
- ELSE is optional kasi even without it, magtutuloy pa rin. pag di nasatisfy yung
conditions, null lang ang return niya.
-- so to try naman yung CASE function, gamitin nalang natin ulit yung saved search
kanina
- navigte to results subtab again, but this time add natin yung shipping
state/province
- pagka add, let's preview the results first
- so as you can see, yung shipping states abbreviated, for this example, let's use
a CASE function that will return the whole name of the state so for example, NY =
New York, CA = California, etc.
- so to do that, let's navigate back
- then add a formula text field ulit
CASE WHEN {shipstate} = 'NY' THEN 'New York' WHEN {shipstate} = 'CA' THEN
'California' ELSE 'No State defined' END
------------------------------------------------------------