Subscribe:
RSS feed
-
Subscribe
Subscribed
Already have a WordPress.com account? Log in now.
STRING CONCAT (this is an alt/preferred version of wm_concat), can also use LISTAGG as of Oracle 11.2 SELECT i.imacs_id, SUBSTR(MAX(SYS_CONNECT_BY_PATH(ia.long_val1,’,’)),2) ImpactAgents FROM imacs.unique_rec i LEFT OUTER JOIN ( SELECT impa.imacs_id, dia.long_val1, row_number() OVER (PARTITION BY impa.imacs_id ORDER BY dia.long_val1) rn FROM imacs.part_a_impact_agents impa LEFT OUTER JOIN imacs.decoder dia ON dia.decoder_id = impa.impact_agents_cd ) ia ON […]
January 18, 2012
Oracle’s DECODE() function is pretty slick for returning a specific value based on a particular field, such as in the following: SELECT supplier_code, DECODE(supplier_id, 10000, ‘IBM’, 10001, ‘Microsoft’, 10002, ‘Hewlett Packard’, ‘Gateway’) supplier_name FROM suppliers; However, what happens when you need to specify a result based on the difference between two numbers. This can be […]
January 18, 2012
DECODE EXAMPLE: SELECT supplier_code, decode(supplier_id, 10000, ‘IBM’, 10001, ‘Microsoft’, 10002, ‘Hewlett Packard’, ‘Gateway’) supplier_name FROM suppliers; The DECODE() function accepts a field name to evaluate, then allows you to specify what result should be returned based on the value in that field. The above example is evaluating the “supplier_id” field in a table, and specifying […]
January 18, 2012
** SQL version *** UPDATE erp.rse SET expardesc = r.expardesc || ‘ – ‘ || p.parname FROM erp.rse r INNER JOIN erp.omqc_paramdesc p ON p.rsid = r.rsid AND r.rtype = ‘PSVP’ *** Oracle vesrion *** UPDATE erp.rse SET rse.expardesc = rse.expardesc || ‘ – ‘ || (SELECT p.parname FROM erp.omqc_paramdesc p WHERE p.exparid = rse.exparid) […]
January 18, 2012
Oracle doesn’t have some of the handy short-hand functions that Microsoft has embedded into it’s VB programming languages and into SQL Server but, of course, provides a similar way to return the same result. The key, is Oracle’s SUBSTR() function! In Microsoft’s SQL Server, and in VB, you have the following: MID(YourStringHere, StartFrom, NumCharsToGrab) MID(“birthday”,1,5) […]
maverick web 
January 18, 2012
0