Tag: sql
Snowflake generate sequence without gaps
This example shows how to use ROW_NUMBER to generate a sequence without gaps:
Snowflake JSON
Example SQL reading native JSON data:
Oracle: Create Database Link
Create Database Link example: Or even better, because you don’t need to know the TNS_NAME_ENTRY as defined on the server you are creating the DBLINK from:
Oracle DUMP function
Use oracle DUMP function to see internal representation of a character field, e.g. to see hidden trailing characters as in example below: ENTERPRISE_ID EMPLOYER_ID SOURCE_BUSINESS_ID SOURCE_FRIENDLY_ID GOVERMENT_AGENCY_FG SETUP_COMPLETED_FG ————- ———– ————————————————– ——————– ——————- —————— 27905692 13034613 3C02DB52-52C1-43B1-98DE-EA75CA9AB7AC BULS_6930 N Y …
Oracle: CUBE and ROLLUP aggregations
Use CUBE and ROLLUP to calculate aggregates at many levels in one statement: http://docs.oracle.com/cd/E11882_01/server.112/e25554/aggreg.htm#DWHSG8613 ROLLUP example ============== SELECT channel_desc, calendar_month_desc, countries.country_iso_code, TO_CHAR(SUM(amount_sold), ‘9,999,999,999’) SALES$ FROM sales, customers, times, channels, countries WHERE sales.time_id=times.time_id AND sales.cust_id=customers.cust_id AND customers.country_id = countries.country_id AND sales.channel_id= …