ok, tks Lukas
2016-01-21 11:04 GMT+01:00 Lukas Eder <[email protected]>:
> Hi Denis,
>
> Thanks for your additional feedback.
> I'm thinking too that there is probably some misunderstanding in how SQL
> Server's CROSS JOIN wit [xml-column].nodes() really works. I'm equally
> surprised by this issue.
>
> Unfortunately, there is not much jOOQ can do when you use plain SQL like
> you did:
>
> Table<Record> FatturaPA =
> //TODO: fix per il type-safe
> DSL.table("{0}.nodes({1})", DSL.inline("fatturaPA"), "declare
> namespace
> ns3=\"http://www.fatturapa.gov.it/sdi/fatturapa/v1.1\";/ns3:FatturaElettronica";)
> .as("FATT", "MAIN");
>
> Whatever you're passing in as QueryPart argument {0} will be rendered at
> that position.
>
> If jOOQ had formal support for this nodes() method, things might look
> differently, as we could then avoid rendering the schema name. I'll be
> happy to look at this in the context of
> https://github.com/jOOQ/jOOQ/issues/4947
>
> Hope this helps,
> Lukas
>
> 2016-01-18 8:23 GMT+01:00 Denis Miorandi <[email protected]>:
>
>> Hi Lukas,
>> all in all the issue seems not to be on jooq but in sqlserver I
>> think but I'm not sure. Jooq generate this code that seems correct to me:
>>
>> CROSS APPLY* [dbo].[FattOut].[fatturaPA**].*nodes('declare namespace
>> ns3="http://www.fatturapa.gov.it/sdi/fatturapa/v1.1";/ns3:FatturaElettronica')
>> [FATT]([MAIN])
>>
>>
>> but SQLServer 2012 does not execute that line. It work's just removing
>> dbo. So I'm concerning about this. If is by design on sqlserver, jooq
>> should not write [dbo], but there is no reasong to be like this.
>> I don't know if you have any informations more about this.
>>
>>
>> DDL is the following
>>
>>
>> USE [Adep_FattureXML]GO
>> /****** Object: Table [dbo].[FattOut] Script Date: 18/01/2016 08.11.19
>> ******/SET ANSI_NULLS ONGO
>> SET QUOTED_IDENTIFIER ONGO
>> CREATE TABLE [dbo].[FattOut] (
>> [IDFattOut] [int] IDENTITY(1, 1) NOT NULL
>> ,[IDStatoFattura] [smallint] NULL
>> ,[IDClasseiva] [smallint] NULL
>> ,[IDRegistroIVA] [int] NULL
>> ,[IDSede] [int] NULL
>> ,[IDVersioneSDI] [int] NULL
>> ,[FatturaPA] [xml] NULL
>> ,[NoteInterne] [nvarchar](255) NULL
>> ,[DataRegistro] [datetime] NULL
>> ,[NumRegistro] [int] NULL
>> ,[ADEPAnno] [int] NULL
>> ,[IDADEPCategoria] [smallint] NULL
>> ,[Sollecito] [bit] NULL
>> ,[DataSollecito] [date] NULL
>> ,[DataCreazione] [datetime] NULL
>> ,[DataUM] [datetime] NULL
>> ,CONSTRAINT [PK_TDocTestate] PRIMARY KEY CLUSTERED ([IDFattOut] ASC)
>> WITH (
>> PAD_INDEX = OFF
>> ,STATISTICS_NORECOMPUTE = OFF
>> ,IGNORE_DUP_KEY = OFF
>> ,ALLOW_ROW_LOCKS = ON
>> ,ALLOW_PAGE_LOCKS = ON
>> ) ON [PRIMARY]
>> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO
>> ALTER TABLE [dbo].[FattOut]
>> WITH CHECK ADD CONSTRAINT [FK_DocTestate_RegistriIVA] FOREIGN KEY
>> ([IDRegistroIVA]) REFERENCES [dbo].[RegistriIVA]([IDRegistroIVA])GO
>> ALTER TABLE [dbo].[FattOut] CHECK CONSTRAINT [FK_DocTestate_RegistriIVA]GO
>> ALTER TABLE [dbo].[FattOut]
>> WITH NOCHECK ADD CONSTRAINT [FK_DocTestate_Sedi] FOREIGN KEY ([IDSede])
>> REFERENCES [dbo].[Sedi]([IDSede]) NOTFOR REPLICATIONGO
>> ALTER TABLE [dbo].[FattOut] NOCHECK CONSTRAINT [FK_DocTestate_Sedi]GO
>> ALTER TABLE [dbo].[FattOut]
>> WITH CHECK ADD CONSTRAINT [FK_DocTestate_StatiFatture] FOREIGN KEY
>> ([IDStatoFattura]) REFERENCES [dbo].[StatiFattura]([IDStatoFattura])GO
>> ALTER TABLE [dbo].[FattOut] CHECK CONSTRAINT [FK_DocTestate_StatiFatture]GO
>> ALTER TABLE [dbo].[FattOut]
>> WITH CHECK ADD CONSTRAINT [FK_FattOut_ADEPCategorie] FOREIGN KEY
>> ([IDADEPCategoria]) REFERENCES [dbo].[ADEPCategorie]([IDADEPCategoria])GO
>> ALTER TABLE [dbo].[FattOut] CHECK CONSTRAINT [FK_FattOut_ADEPCategorie]GO
>> ALTER TABLE [dbo].[FattOut]
>> WITH CHECK ADD CONSTRAINT [FK_FattOut_ClassiIVA] FOREIGN KEY
>> ([IDClasseiva]) REFERENCES [dbo].[ClassiIVA]([IDClasseIVA])GO
>> ALTER TABLE [dbo].[FattOut] CHECK CONSTRAINT [FK_FattOut_ClassiIVA]GO
>> ALTER TABLE [dbo].[FattOut]
>> WITH CHECK ADD CONSTRAINT [FK_FattOut_VersioniSDI] FOREIGN KEY
>> ([IDVersioneSDI]) REFERENCES [dbo].[VersioniSDI]([IDVersioneSDI])GO
>> ALTER TABLE [dbo].[FattOut] CHECK CONSTRAINT [FK_FattOut_VersioniSDI]GO
>>
>>
>>
>> Il giorno domenica 17 gennaio 2016 21:31:54 UTC+1, Lukas Eder ha scritto:
>>>
>>> Hi Denis,
>>>
>>> Thank you for your feedback. I vaguely recall having run into a similar
>>> issue on SQL Server with CROSS APPLY. What does your [FattOut] table's DDL
>>> look like?
>>>
>>
>>
>>
>>
>>> Best Regards,
>>> Lukas
>>>
>>
>>
>>
>>
>>
>>> 2016-01-12 15:07 GMT+01:00 Denis Miorandi <[email protected]>:
>>>
>>>> sorry wrong sql post sql is the following. I've got an error on cross
>>>> apply, but i don't understand why. Jooq seems to generate right query.
>>>> Removing dbo from table name it works.
>>>>
>>>> Anyone knows about it?
>>>>
>>>> Msg 107, Level 15, State 1, Line 18
>>>> The column prefix 'dbo' does not match with a table name or alias name
>>>> used in the query.
>>>> Msg 9506, Level 16, State 1, Line 2
>>>> The XMLDT method 'nodes' can only be invoked on columns of type xml.
>>>>
>>>>
>>>>
>>>> SELECT* [dbo]*.*[FattOut]*.*[IDFattOut]
>>>> *,*[dbo]*.*[RegistriIVA]*.*[IDRamoAzienda]
>>>> *,*[dbo]*.*[StatiFattura]*.*[StatoFattura]
>>>> *,*[dbo]*.*[ClassiIVA]*.*[SiglaClasseIva]
>>>> *,*[dbo]*.*[RegistriIVA]*.*[NomeRegistro]
>>>> *,*[dbo]*.*[FattOut]*.*[ADEPAnno]
>>>> *,*[dbo]*.*[FattOut]*.*[DataRegistro]
>>>> *,*[dbo]*.*[FattOut]*.*[NumRegistro]
>>>> *,*[dbo]*.*[Sedi]*.*[Sede] [cliente]
>>>> *,*[dbo]*.*[ADEPCategorie]*.*[ADEPCategoriaDep]
>>>>
>>>> *,*FATT*.*MAIN*.value('FatturaElettronicaBody[1]/DatiGenerali[1]/DatiGeneraliDocumento[1]/Numero[1]',
>>>> 'varchar(80)')* [NUMERO_FATTURA]
>>>>
>>>> *,*FATT*.*MAIN*.value('FatturaElettronicaBody[1]/DatiGenerali[1]/DatiGeneraliDocumento[1]/Data[1]',
>>>> 'varchar(80)')* [DATA_FATTURA]
>>>>
>>>> *,*FATT*.*MAIN*.value('FatturaElettronicaBody[1]/DatiGenerali[1]/DatiGeneraliDocumento[1]/ImportoTotaleDocumento[1]',
>>>> 'varchar(80)')* [TOTALE_FATTURA]
>>>> *FROM* [dbo]*.*[FattOut]
>>>> *INNER JOIN* [dbo]*.*[StatiFattura] *ON*
>>>> [dbo]*.*[FattOut]*.*[IDStatoFattura] *=*
>>>> [dbo]*.*[StatiFattura]*.*[IDStatoFattura]
>>>> *INNER JOIN* [dbo]*.*[Sedi] *ON* [dbo]*.*[FattOut]*.*[IDSede] *=*
>>>> [dbo]*.*[Sedi]*.*[IDSede]
>>>> *CROSS APPLY* [dbo].[FattOut].*nodes('declare namespace
>>>> ns3="http://www.fatturapa.gov.it/sdi/fatturapa/v1.1";/ns3:FatturaElettronica')
>>>> [FATT]([MAIN])
>>>> LEFT JOIN* [dbo]*.*[ADEPCategorie] *ON*
>>>> [dbo]*.*[ADEPCategorie]*.*[IDADEPCategoria] *=*
>>>> [dbo]*.*[FattOut]*.*[IDADEPCategoria]
>>>> *LEFT JOIN* [dbo]*.*[ClassiIVA] *ON* [dbo]*.*[ClassiIVA]*.*[IDClasseiva]
>>>> *=* [dbo]*.*[FattOut]*.*[IDClasseiva]
>>>> *LEFT JOIN* [dbo]*.*[RegistriIVA] *ON*
>>>> [dbo]*.*[RegistriIVA]*.*[IDRegistroIVA] *=*
>>>> [dbo]*.*[FattOut]*.*[IDRegistroIVA]*
>>>>
>>>>
>>>>
>>>>
>>>> Il giorno martedì 12 gennaio 2016 15:00:10 UTC+1, Denis Miorandi ha
>>>> scritto:
>>>>>
>>>>> Hi,
>>>>> I've got a strange behavior running an xml query on sql-server
>>>>> 2012.
>>>>>
>>>>> Fattout e = Tables.FATTOUT;
>>>>>
>>>>> Table<Record> FatturaPA =
>>>>> //TODO: fix per il type-safe
>>>>> DSL.table("{0}.nodes({1})", DSL.inline("fatturaPA"), "declare
>>>>> namespace
>>>>> ns3=\"http://www.fatturapa.gov.it/sdi/fatturapa/v1.1\";/ns3:FatturaElettronica";)
>>>>> .as("FATT", "MAIN");
>>>>>
>>>>> Field<String> NUMERO_FATTURA =
>>>>>
>>>>> DSL.field("FATT.MAIN.value('FatturaElettronicaBody[1]/DatiGenerali[1]/DatiGeneraliDocumento[1]/Numero[1]',
>>>>> 'varchar(80)')", String.class)
>>>>> .as("NUMERO_FATTURA");
>>>>>
>>>>>
>>>>> Field<String> DATA_FATTURA =
>>>>>
>>>>> DSL.field("FATT.MAIN.value('FatturaElettronicaBody[1]/DatiGenerali[1]/DatiGeneraliDocumento[1]/Data[1]',
>>>>> 'varchar(80)')", String.class)
>>>>> .as("DATA_FATTURA");
>>>>>
>>>>> Field<String> TOTALE_FATTURA =
>>>>>
>>>>> DSL.field("FATT.MAIN.value('FatturaElettronicaBody[1]/DatiGenerali[1]/DatiGeneraliDocumento[1]/ImportoTotaleDocumento[1]',
>>>>> 'varchar(80)')", String.class)
>>>>> .as("TOTALE_FATTURA");
>>>>>
>>>>> SelectJoinStep<Record13<Integer, Integer, String, String, String,
>>>>> Integer, LocalDateTime, Integer, String, String, String, String, String>>
>>>>> query=create.select(
>>>>> FATTOUT.IDFATTOUT,
>>>>> REGISTRIIVA.IDRAMOAZIENDA,
>>>>> STATIFATTURA.STATOFATTURA,
>>>>> CLASSIIVA.SIGLACLASSEIVA,
>>>>> REGISTRIIVA.NOMEREGISTRO,
>>>>> FATTOUT.ADEPANNO,
>>>>> FATTOUT.DATAREGISTRO,
>>>>> FATTOUT.NUMREGISTRO,
>>>>> SEDI.SEDE.as("cliente"),
>>>>> ADEPCATEGORIE.ADEPCATEGORIADEP,
>>>>> NUMERO_FATTURA,
>>>>> DATA_FATTURA,
>>>>> TOTALE_FATTURA
>>>>> )
>>>>> .from(FATTOUT)
>>>>>
>>>>> .innerJoin(STATIFATTURA).on(FATTOUT.IDSTATOFATTURA.eq(STATIFATTURA.IDSTATOFATTURA))
>>>>> .innerJoin(SEDI).on(FATTOUT.IDSEDE.eq(SEDI.IDSEDE))
>>>>> .crossApply(FatturaPA)
>>>>>
>>>>> .leftJoin(ADEPCATEGORIE).on(ADEPCATEGORIE.IDADEPCATEGORIA.eq(FATTOUT.IDADEPCATEGORIA))
>>>>>
>>>>> .leftJoin(CLASSIIVA).on(CLASSIIVA.IDCLASSEIVA.eq(FATTOUT.IDCLASSEIVA))
>>>>>
>>>>> .leftJoin(REGISTRIIVA).on(REGISTRIIVA.IDREGISTROIVA.eq(FATTOUT.IDREGISTROIVA));
>>>>>
>>>>> generate the sql:
>>>>>
>>>>> SELECT [dbo].[FattOut].[IDFattOut]
>>>>> ,[dbo].[RegistriIVA].[IDRamoAzienda]
>>>>> ,[dbo].[StatiFattura].[StatoFattura]
>>>>> ,[dbo].[ClassiIVA].[SiglaClasseIva]
>>>>> ,[dbo].[RegistriIVA].[NomeRegistro]
>>>>> ,[dbo].[FattOut].[ADEPAnno]
>>>>> ,[dbo].[FattOut].[DataRegistro]
>>>>> ,[dbo].[FattOut].[NumRegistro]
>>>>> ,[dbo].[Sedi].[Sede] [cliente]
>>>>> ,[dbo].[ADEPCategorie].[ADEPCategoriaDep]
>>>>>
>>>>> ,FATT.MAIN.value('FatturaElettronicaBody[1]/DatiGenerali[1]/DatiGeneraliDocumento[1]/Numero[1]',
>>>>> 'varchar(80)') [NUMERO_FATTURA]
>>>>>
>>>>> ,FATT.MAIN.value('FatturaElettronicaBody[1]/DatiGenerali[1]/DatiGeneraliDocumento[1]/Data[1]',
>>>>> 'varchar(80)') [DATA_FATTURA]
>>>>>
>>>>> ,FATT.MAIN.value('FatturaElettronicaBody[1]/DatiGenerali[1]/DatiGeneraliDocumento[1]/ImportoTotaleDocumento[1]',
>>>>> 'varchar(80)') [TOTALE_FATTURA]FROM [dbo].[FattOut]INNER JOIN
>>>>> [dbo].[StatiFattura] ON [dbo].[FattOut].[IDStatoFattura] =
>>>>> [dbo].[StatiFattura].[IDStatoFattura]INNER JOIN [dbo].[Sedi] ON
>>>>> [dbo].[FattOut].[IDSede] = [dbo].[Sedi].[IDSede]CROSS APPLY
>>>>> 'fatturaPA'.nodes('declare namespace
>>>>> ns3="http://www.fatturapa.gov.it/sdi/fatturapa/v1.1";/ns3:FatturaElettronica')
>>>>> [FATT]([MAIN])LEFT JOIN [dbo].[ADEPCategorie] ON
>>>>> [dbo].[ADEPCategorie].[IDADEPCategoria] =
>>>>> [dbo].[FattOut].[IDADEPCategoria]LEFT JOIN [dbo].[ClassiIVA] ON
>>>>> [dbo].[ClassiIVA].[IDClasseiva] = [dbo].[FattOut].[IDClasseiva]LEFT JOIN
>>>>> [dbo].[RegistriIVA] ON [dbo].[RegistriIVA].[IDRegistroIVA] =
>>>>> [dbo].[FattOut].[IDRegistroIVA]
>>>>>
>>>>> --
>>>> You received this message because you are subscribed to the Google
>>>> Groups "jOOQ User Group" group.
>>>> To unsubscribe from this group and stop receiving emails from it, send
>>>> an email to [email protected].
>>>> For more options, visit https://groups.google.com/d/optout.
>>>>
>>>
>>> --
>> You received this message because you are subscribed to the Google Groups
>> "jOOQ User Group" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected].
>> For more options, visit https://groups.google.com/d/optout.
>>
>
> --
> You received this message because you are subscribed to a topic in the
> Google Groups "jOOQ User Group" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/jooq-user/PcYhxv6gWkI/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> [email protected].
> For more options, visit https://groups.google.com/d/optout.
>
--
Dott. Denis Miorandi
via dei Vignai 27
38060 - Nogaredo (TN)
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
