Oh LORDY...It CAN be done
A SubTable is an SQL table that allows for Updates...so the statement "
And it
will create sub tables from any associated mv attributes." Is slightly
incorrect...yes we think in terms that SubTable may refer to what is
actually created via VSG as a MV (or MS) table via the ASSOC.
i.e. EDI_INVOIC_REF_INV_MV is a child of EDI_INVOICE_HDR
The corresponding subtables are named EDI_INVOIC_REF_INV_MV_SUB and
EDI_INVOICE_HDR_SUB respectively
To update the multi-valued attributes within EDI-INVOIC_REF_INV_MV_SUB you
must include the key to the parent along with its MV value in the SQL
INSERT statement as seen below
INSERT INTO EDI_INVOIC_REF_INV_MV_SUB(EDI810_KEY,REF_01_Header_Ref_Type,
REF_02_Header_Ref, REF_03_Header_Ref_Desc, REF_INV_MV_KEY) VALUES
('4700','VN', '123458', 'Vendor Order Number','3')
In the example above EDI810_KEY is the key to the Parent and
REF_INV_MV_KEY is the Primary Key to the MV SubTable[aka the multi-value
position]. The Values are "Stuffed" in the above example to simply test
the insert and can obviously be replaced with assigned variables
[email protected] wrote on 02/18/2009 04:36:40 AM:
> Just from memory here VSG will create you a table from your udt file.
And it
> will create sub tables from any associated mv attributes. If you then
want
> to add data to the MV's you either insert a new record into the table or
> update the table with the new data, I do not think you perform an insert
> against the sub table. Is that correct ??
>
>
>
> -----Original Message-----
> From: [email protected]
> [mailto:[email protected]] On Behalf Of Debra Fedchin
> Sent: 17 February 2009 23:10
> To: [email protected]
> Subject: RE: [U2] OLEDB error
>
> I know I know...
>
> Let me put this plain and simple instead of my usual stream of
> conscienceness
>
> A) I am attempting to INSERT data FROM SQL to UNIDATA
>
> B) I have multi-valued fields that have been accounted for with ASSOC
> within the Tables which have PUBLIC permissions on READ, WRITE, EXECUTE
on
> all levels
>
> C) Tables were created thru VSG as were Views and Sub-Tables.
>
> D) I am attempting to write to the Sub-Tables
>
> E) The select statement was done in order to determine if the INSERT
would
> be valid
>
> F) All I want to know is the correct Syntax to INSERT data into a
> Multi-valued field FROM SQLServer
>
> G) I HAVE been able to INSERT simple data with NO multi-values into the
> Tables, it is the Multi-values which are now the issue. I HAVE NOT been
> able to update a multi-valued field
>
>
> This is the SQL from VSG for the Table
>
> CREATE SUBTABLE
>
EDI_INVOIC_REF_INV_MV_SUB(EDI810_KEY,REF_INV_MV_KEY,REF_02_Header_Ref,REF_03
> _
> Header_Ref_Desc,REF_01_Header_Ref_Type)
> AS SELECT
>
EDI810_KEY,NL1_KEY(REF_02_Header_Ref,REF_03_Header_Ref_Desc,REF_01_Header_Re
> f
> _Type),REF_02_Header_Ref,REF_03_Header_Ref_Desc,
> REF_01_Header_Ref_Type FROM EDI_INVOICE_HDR UNNEST NL1
> REF_02_Header_Ref,REF_03_Header_Ref_Desc,REF_01_Header_Ref_Type PRIMARY
> KEY EDI810_KEY,REF_INV_MV_KEY FOREIGN KEY EDI810_KEY REFERENCES
> EDI_INVOICE_HDR_NF_SUB;
>
>
> Now...
>
> What should the SQL statement look like for the INSERT to correctly
> populate REF_02_Header_Ref,REF_03_Header_Ref_Desc,REF_01_Header_Ref_Type
> ???
>
>
> Another ANNOYING thing I discovered....when a SELECT is issued....the
SQL
> Server Side is sent Column information over using an ORDER BY producing
a
> attribute list in alpha order rather than database placement order
>
> UCI command: SRV_SQOutParams
> Number of parameters: 11
> Parameter 1: EDI_INVOIC_REF_INV_MV_SUB
> Parameter 2:
>
EDI810_KEY~REF_INV_MV_KEY~REF_02_Header_Ref~REF_03_Header_Ref_Desc~REF_01_He
> a
> der_Ref_Type
> Parameter 3: ~MD0~~~
> Parameter 4: 10L~8R~30L~80L~2L
> Parameter 5: VARCHAR(10)~INTEGER~VARCHAR(30)~VARCHAR(80)~VARCHAR(2)
> Parameter 6: 1~2~~~
> Parameter 7: 1~~~~
> Parameter 8: EDI_INVOICE_HDR_NF_SUB
> Parameter 9: REF_INV
> Parameter 10: 0
> Parameter 11:
>
> The above is from the UNIX trace showing that it was able to retreive
the
> table information
>
>
> Yet....
>
> Debug: OLEDB driver: Insert SQL='SELECT REF_01_Header_Ref_Type,
> REF_02_Header_Ref, REF_03_Header_Ref_Desc, REF_INV_MV_KEY FROM
> daf.EDI_INVOIC_REF_INV_MV_SUB WHERE (0=1)'
> Error: Couldn't insert row into table 'EDI_INVOIC_REF_INV_MV_SUB'.
> Error: Error 0x80040e21: "Multiple-step OLE DB operation generated
errors.
> Check each OLE DB status value, if available. No work was done."
> Information: The status of the individual columns is as follows:
> Information: REF_01_Header_Ref_Type: VN
> Information: REF_02_Header_Ref: 109099
> Information: REF_03_Header_Ref_Desc: Vendor Order Number
> Warning: REF_INV_MV_KEY: DBSTATUS_E_PERMISSIONDENIED (value[I4]:1)
> Debug: OLEDB driver: Insert SQL='SELECT BIG_01_Invoice_Date,
> BIG_02_Invoice_Num, BIG_03_PO_Date, BIG_04_PO_Num, BIG_05_Rel_Num,
> BIG_07_Trans_Type, Benco_Host, CTT_01_CTT_Amt, DTM_01_Date_Qualifer,
> DTM_02_Date_Shipped, Date_Processed, EDI810_KEY, Mailbox, N1_BT_01_Code,
> N1_BT_02_Name, N1_BT_03_Qualifier, N1_BT_04_ID, N1_RE_01_Code,
> N1_RE_02_Name, N1_RE_03_Qualifier, N1_RE_04_ID, N1_ST_01_Code,
> N1_ST_02_Name, N1_ST_03_Qualifier, N1_ST_04_ID, N3_BT_01_Address1,
> N3_BT_02_Address2, N3_RE_01_Address1, N3_RE_02_Address2,
> N3_ST_01_Address1, N3_ST_02_Address2, N4_BT_01_City, N4_BT_02_State,
> N4_BT_03_Zip, N4_BT_04_Country_Code, N4_RE_01_City, N4_RE_02_State,
> N4_RE_03_Zip, N4_RE_04_Country_Code, N4_ST_01_City, N4_ST_02_State,
> N4_ST_03_Zip, N4_ST_04_Country_Code, PER_01_Contact_Code,
> PER_02_Contact_Name, PER_03_Contact_TE_Contact, PER_04_Contact_Phone,
> PER_05_Contact_FX_Contact, PER_06_Contact_Fax, TDS_01_Amount,
> Trading_Partner, Vendor_Num FROM daf.EDI_INVOICE_HDR_NF_SUB WHERE (0=1)'
> Debug: Rule #1: 810 -> Prelude_3 [create Prelude_3 (Data Link)]
>
>
> Go figure....
>
>
>
>
> "Symeon Breen" <[email protected]>
> Sent by: [email protected]
> 02/17/2009 04:48 PM
> Please respond to
> [email protected]
>
>
> To
> <[email protected]>
> cc
>
> Subject
> RE: [U2] OLEDB error
>
>
>
>
>
>
> What is the actual sql command you are performing ?
>
> It is a bit confusing as the error talks about an insert but it is a
> select
> it is showing.
>
>
>
>
>
> -----Original Message-----
> From: [email protected]
> [mailto:[email protected]] On Behalf Of Debra Fedchin
> Sent: 17 February 2009 15:59
> To: [email protected]
> Subject: RE: [U2] OLEDB error
>
> Yep
>
> All permission's are set to PUBLIC
>
> It is frustrating since most of the DOCS give examples of utilizing SQL
> statements as if you attempting to manipulate Unidata data within the
> Unidata server itself (duh -- now why would I be attempting to make
like
> more complicated for no good reason?) OR examples of how to EXTRACT data
> from a SQL server into Unidata.
>
> There are no clear examples of a standard SQL statement that would be
used
> on a Client to update exposed SQL Tables that have a parent/child
> relationship (MV) -- and a statement that goes beyond a mere snipit that
> would give you more than the smallest granular overview.
>
> It's bad enough that the XML extract does not work as documented (non
> standard and just plain butt ugly in regards to hierarchy!) without
adding
> further insult to injury. All I am trying to do is to update FROM a
> client to Unidata without having to drop-kick to flat text files!
>
> It ain't rocket science!
>
>
>
>
>
> Horacio Pellegrino <[email protected]>
> Sent by: [email protected]
> 02/12/2009 08:11 PM
> Please respond to
> [email protected]
>
>
> To
> [email protected]
> cc
>
> Subject
> RE: [U2] OLEDB error
>
>
>
>
>
>
> Username has permissions to create and insert granted?
>
> HP
>
>
> -----Original Message-----
> From: [email protected]
> [mailto:[email protected]] On Behalf Of Debra Fedchin
> Sent: Wednesday, February 11, 2009 11:08 AM
> To: [email protected]
> Cc: [email protected]; '[email protected]'
> Subject: [U2] OLEDB error
>
> ok
>
> Can someone tell me why I got this error?
>
> Debug: OLEDB driver: Insert SQL='SELECT BIG_01_Invoice_Date,
> BIG_02_Invoice_Num, BIG_03_PO_Date, BIG_04_PO_Num, BIG_05_Rel_Num,
> BIG_07_Trans_Type, Benco_Host, CTT_01_CTT_Amt, DTM_01_Date_Qualifer,
> DTM_02_Date_Shipped, Date_Processed, EDI810_ID, Mailbox, N1_BT_01_Code,
> N1_BT_02_Name, N1_BT_03_Qualifier, N1_BT_04_ID, N3_BT_01_Address1,
> N3_BT_02_Address2, N4_BT_01_City, N4_BT_02_State, N4_BT_03_Zip,
> N4_BT_04_Country_Code, TDS_01_Amount, Trading_Partner, Vendor_Num FROM
> daf.EDI_INVOICE_HDR_NF_SUB WHERE (0=1)'
> Error: Couldn't insert row into table 'EDI_INVOICE_HDR_NF_SUB'.
> Error: Error 0x80004005: "[Ardent][UniData ODBC Driver][IBM][SQL
> Client][UNIDATA]Can not insert into view EDI_INVOICE_HDR_NF_SUB"
>
>
> Now -- this table was created, had SQL.CONVERT run against it, followed
by
> going through VSG to create both Views and Subtables, all Privileges
have
> been set to PUBLIC
>
> The error states that the table is a view when in fact it is a Subtable
>
>
>
>
> =================================================
> This communication (including any attachments) is intended only for use
by
> the
> addressee(s) named herein and may contain legally privileged or
> confidential
> information. If you are not the intended recipient or an authorized
> representative of the intended recipient on this communication, you are
> hereby
> notified that any dissemination or distribution of this communication
(or
> attachments) is strictly prohibited. If you have received this
> communication
> in error, please notify us immediately by e-mail and permanently delete
> the
> communication and any attachments from your system.
> -------
> u2-users mailing list
> [email protected]
> To unsubscribe please visit http://listserver.u2ug.org/
> -------
> u2-users mailing list
> [email protected]
> To unsubscribe please visit http://listserver.u2ug.org/
>
>
>
> =================================================
> This communication (including any attachments) is intended only for use
by
> the
> addressee(s) named herein and may contain legally privileged or
> confidential
> information. If you are not the intended recipient or an authorized
> representative of the intended recipient on this communication, you are
> hereby
> notified that any dissemination or distribution of this communication
(or
> attachments) is strictly prohibited. If you have received this
> communication
> in error, please notify us immediately by e-mail and permanently delete
> the
> communication and any attachments from your system.
> -------
> u2-users mailing list
> [email protected]
> To unsubscribe please visit http://listserver.u2ug.org/
> -------
> u2-users mailing list
> [email protected]
> To unsubscribe please visit http://listserver.u2ug.org/
>
>
>
> =================================================
> This communication (including any attachments) is intended only for use
by
> the
> addressee(s) named herein and may contain legally privileged or
confidential
> information. If you are not the intended recipient or an authorized
> representative of the intended recipient on this communication, you are
> hereby
> notified that any dissemination or distribution of this communication
(or
> attachments) is strictly prohibited. If you have received this
communication
> in error, please notify us immediately by e-mail and permanently delete
the
> communication and any attachments from your system.
> -------
> u2-users mailing list
> [email protected]
> To unsubscribe please visit http://listserver.u2ug.org/
> -------
> u2-users mailing list
> [email protected]
> To unsubscribe please visit http://listserver.u2ug.org/
=================================================
This communication (including any attachments) is intended only for use by the
addressee(s) named herein and may contain legally privileged or confidential
information. If you are not the intended recipient or an authorized
representative of the intended recipient on this communication, you are hereby
notified that any dissemination or distribution of this communication (or
attachments) is strictly prohibited. If you have received this communication
in error, please notify us immediately by e-mail and permanently delete the
communication and any attachments from your system.
-------
u2-users mailing list
[email protected]
To unsubscribe please visit http://listserver.u2ug.org/
