Command Import - Revision history

Chanson: /* MS SQL to know */

2024-02-27T13:25:27Z

MS SQL to know

← Older revision		Revision as of 13:25, 27 February 2024
Line 23:		Line 23:

	===MS SQL to know===		===MS SQL to know===
			[[Microsoft SQL Server Query Features]]
	*ltrim,rtrim		*ltrim,rtrim
	*replace		*replace

Chanson: /* MS SQL to know */

2024-02-27T13:23:13Z

MS SQL to know

← Older revision		Revision as of 13:23, 27 February 2024
Line 29:		Line 29:
	*iif		*iif
	*rank		*rank
			*with (derived queries)

	===Import Examples===		===Import Examples===

Chanson at 15:12, 22 February 2024

2024-02-22T15:12:22Z

← Older revision		Revision as of 15:12, 22 February 2024
Line 21:		Line 21:
	*Some records, especially prices, have what appear to be duplicates. You need special queries that pull just the latest version of the record.		*Some records, especially prices, have what appear to be duplicates. You need special queries that pull just the latest version of the record.
	*Start with Small Tables and Build up (Like any Import)		*Start with Small Tables and Build up (Like any Import)

			===MS SQL to know===
			*ltrim,rtrim
			*replace
			*left, right
			*case
			*iif
			*rank


	===Import Examples===		===Import Examples===

Chanson: /* Know This First */

2024-02-20T16:21:18Z

Know This First

← Older revision		Revision as of 16:21, 20 February 2024
Line 19:		Line 19:
	...		...
	WHEN 'CAS' THEN 'CA' ELSE '' END CUST_TERMS</pre>		WHEN 'CAS' THEN 'CA' ELSE '' END CUST_TERMS</pre>
			*Some records, especially prices, have what appear to be duplicates. You need special queries that pull just the latest version of the record.
	*Start with Small Tables and Build up (Like any Import)		*Start with Small Tables and Build up (Like any Import)

Chanson: /* Know This First */

2024-02-20T16:18:01Z

Know This First

← Older revision		Revision as of 16:18, 20 February 2024
Line 5:		Line 5:
	*There is NO data integrity.		*There is NO data integrity.
	:*Expect sub tables, prices etc. to have records that do have matching main tables. (e.g. prices for customers that don't exist).		:*Expect sub tables, prices etc. to have records that do have matching main tables. (e.g. prices for customers that don't exist).
	*~~All~~ fields must be trimmed - especially key fields. Expect both left and right padding on Customer and product codes.		*Alpha fields must be trimmed - especially key fields. Expect both left and right padding on Customer and product codes.
	:MS SQL hasn't had the "TRIM" statement for long, so most often you must select as:		:MS SQL hasn't had the "TRIM" statement for long, so most often you must select as:
	:<code>LTRIM(RTRIM((PRJP.PROJ_CODE)) JOB_PO_LOC,</code>		:<code>LTRIM(RTRIM((PRJP.PROJ_CODE)) JOB_PO_LOC,</code>
	:in some cases, such as Oracle you only have trim and must select as:		:in some cases, such as Oracle you only have trim and must select as:
	:<code>TRIM(PRJP.PROJ_CODE) JOB_PO_LOC,</code>		:<code>TRIM(PRJP.PROJ_CODE) JOB_PO_LOC,</code>
			*Numeric Fields may be null. Use coalesce... <code>COALESCE(Finc_Chrg_amt,0) Finc_chrg_amt</code>
	*Field IDs are longer - especially Tax Authorities, sales people and classes.		*Field IDs are longer - especially Tax Authorities, sales people and classes.
	:*Keep a list of mappings when setting up the class type fields to use in case statements...		*Keep a list of mappings when setting up the class type fields to use in case statements...
	<pre>		<pre>
	case TERMS		case TERMS
Line 19:		Line 20:
	WHEN 'CAS' THEN 'CA' ELSE '' END CUST_TERMS</pre>		WHEN 'CAS' THEN 'CA' ELSE '' END CUST_TERMS</pre>
	*Start with Small Tables and Build up (Like any Import)		*Start with Small Tables and Build up (Like any Import)


	===Import Examples===		===Import Examples===

Chanson at 12:57, 19 February 2024

2024-02-19T12:57:10Z

← Older revision		Revision as of 12:57, 19 February 2024
Line 18:		Line 18:
	...		...
	WHEN 'CAS' THEN 'CA' ELSE '' END CUST_TERMS</pre>		WHEN 'CAS' THEN 'CA' ELSE '' END CUST_TERMS</pre>
			*Start with Small Tables and Build up (Like any Import)


	===Import Examples===		===Import Examples===

Chanson: /* Know This First */

2024-02-19T12:50:56Z

Know This First

← Older revision		Revision as of 12:50, 19 February 2024
Line 5:		Line 5:
	*There is NO data integrity.		*There is NO data integrity.
	:*Expect sub tables, prices etc. to have records that do have matching main tables. (e.g. prices for customers that don't exist).		:*Expect sub tables, prices etc. to have records that do have matching main tables. (e.g. prices for customers that don't exist).
	~~MS SQL hasn't had the "TRIM" statement for long, so most often you must select as:~~
	~~<code>LTRIM(RTRIM((PRJP.PROJ_CODE)) JOB_PO_LOC,</code>~~
	~~But in some cases, such as Oracle you only have trim and must select as:~~
	~~<code>TRIM(PRJP.PROJ_CODE) JOB_PO_LOC,</code>~~
	*All fields must be trimmed - especially key fields. Expect both left and right padding on Customer and product codes.		*All fields must be trimmed - especially key fields. Expect both left and right padding on Customer and product codes.
			:MS SQL hasn't had the "TRIM" statement for long, so most often you must select as:
			:<code>LTRIM(RTRIM((PRJP.PROJ_CODE)) JOB_PO_LOC,</code>
			:in some cases, such as Oracle you only have trim and must select as:
			:<code>TRIM(PRJP.PROJ_CODE) JOB_PO_LOC,</code>
	*Field IDs are longer - especially Tax Authorities, sales people and classes.		*Field IDs are longer - especially Tax Authorities, sales people and classes.
	:*Keep a list of mappings when setting up the class type fields to use in case statements...		:*Keep a list of mappings when setting up the class type fields to use in case statements...
Line 18:		Line 18:
	...		...
	WHEN 'CAS' THEN 'CA' ELSE '' END CUST_TERMS</pre>		WHEN 'CAS' THEN 'CA' ELSE '' END CUST_TERMS</pre>


	===Import Examples===		===Import Examples===

Chanson at 12:49, 19 February 2024

2024-02-19T12:49:27Z

← Older revision		Revision as of 12:49, 19 February 2024
Line 1:		Line 1:
	There is not ONE way to import Command Data - each situation is unique.		There is not ONE way to import Command Data - each situation is unique.

			===Know This First===
	What you need to know before importing Command's Data		What you need to know before importing Command's Data
	*There is NO data integrity.		*There is NO data integrity.
Line 12:		Line 13:
	:*Keep a list of mappings when setting up the class type fields to use in case statements...		:*Keep a list of mappings when setting up the class type fields to use in case statements...
	<pre>		<pre>
			case TERMS
			WHEN '15N' THEN '15'
			WHEN 'N20' THEN '20'
			...
			WHEN 'CAS' THEN 'CA' ELSE '' END CUST_TERMS</pre>


			===Import Examples===


	Here are some samples used in the past...		Here are some samples used in the past...
Line 24:		Line 29:


	Command Tables:		===Command Tables===

	*'''ACCT:''' GL Accounts		*'''ACCT:''' GL Accounts

Chanson at 12:43, 19 February 2024

2024-02-19T12:43:20Z

← Older revision		Revision as of 12:43, 19 February 2024
Line 1:		Line 1:
	There is not ONE way to import Command Data - each situation is unique.		There is not ONE way to import Command Data - each situation is unique.

			What you need to know before importing Command's Data
			*There is NO data integrity.
			:*Expect sub tables, prices etc. to have records that do have matching main tables. (e.g. prices for customers that don't exist).
			MS SQL hasn't had the "TRIM" statement for long, so most often you must select as:
			<code>LTRIM(RTRIM((PRJP.PROJ_CODE)) JOB_PO_LOC,</code>
			But in some cases, such as Oracle you only have trim and must select as:
			<code>TRIM(PRJP.PROJ_CODE) JOB_PO_LOC,</code>
			*All fields must be trimmed - especially key fields. Expect both left and right padding on Customer and product codes.
			*Field IDs are longer - especially Tax Authorities, sales people and classes.
			:*Keep a list of mappings when setting up the class type fields to use in case statements...
			<pre>





	Here are some samples used in the past...		Here are some samples used in the past...

Chanson at 22:27, 18 February 2024

2024-02-18T22:27:16Z

← Older revision		Revision as of 22:27, 18 February 2024
Line 2:		Line 2:

	Here are some samples used in the past...		Here are some samples used in the past...
			:[[Examples by Table]]
	:[[Command Import: Hedger Work Notes]]		:[[Command Import: Hedger Work Notes]]
	:[[Command Import: Rain MO Work Notes]]		:[[Command Import: Rain MO Work Notes]]