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]
> <javascript:>>:
>
>> 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] <javascript:>.
>> 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.
