Hide label if no data

Hi all,
is it possible to hide a textbox (a normal label) if in the adiacent cell
there is no data in tha dataset? (for example a null value).
Thanks a lot.
--
Luigi
http://blogs.dotnethell.it/ciupaz/Yes it is. You can add this expression in your text box;
iif(Fields!TextBoxData.Value <0, Fields!TextBox.Value, nothing)
"Luigi" wrote:
> Hi all,
> is it possible to hide a textbox (a normal label) if in the adiacent cell
> there is no data in tha dataset? (for example a null value).
> Thanks a lot.
> --
> Luigi
> http://blogs.dotnethell.it/ciupaz/

Hide an entire row if data in one firld is 0 or null

I want to be able to remove rows from the report if certain data in the row is 0. If a sales division has no sales for a district. there is no reason to show the district name or even to show an empty space for the row. I want to suppress the entire row without the user being able to see it or unhide it.

Is this possible?

You can accomplish this using filters. You can filter at the dataset, data region and grouping levels. You could also modify the SQL query to filter the data using the WHERE clause.

Here is more information on filter data in a Report.

http://msdn2.microsoft.com/en-us/library/ms157307.aspx

Ian|||This will not work in the current context. There are multiple fields that get grouped at a division/region level

hi their

EMP_ID Attend ID
E0001 AT0001
E0002 NULL
E0003 AT0005
E0004 NULL

This is the normal out put i got
just what i want to do is if there any NULL on the Attend id its display as NOT ATTEND
This is what i need get

EMP_ID Attend ID
E0001 AT0001
E0002 NOT ATTEND
E0003 AT0005
E0004 NOT ATTEND

Any IdeaGive a look to either COALESCE or ISNULL in books online. Change where you are selecting "Attend ID
" to ISNULL( [Attend ID], 'NOT ATTEND') or COALESCE( [Attend ID], 'NOT ATTEND')

Heterogeneous queries, ANSI_NULLS, ANSI_WARNINGS

I have stored procedure:

EXEC sp_addlinkedsrvlogin @.FailedRegionServerName, 'false', NULL, 'sa', 'pass'

DECLARE @.a varchar(100)
SET @.a = @.FailedRegionServerName + '.Ithalat.dbo.Product'

DECLARE @.s varchar(100)
SET @.s = ' SELECT * FROM ' + @.a
EXEC ( @.s )

When I execute it I get the error:

Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

Then I put
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON lines into the procedure. Also checked "Ansi Nulls" and "Ansi Warnings" in the properties of SQL Server. It didn't work

Then I tried:

DECLARE @.s varchar(300)
SET @.s = 'SET ANSI_WARNINGS ON; SET ANSI_NULLS ON; SELECT * FROM ' + @.a
EXEC ( @.s )

I still got the error.

WHAT SHOULD I DO? HOW CAN I GET A TABLE CONTENT FROM A LINKED SERVER? Any will be appreciated, thanks a lot...

You will have to create the stored procedures with those ANSI Settings:

SET ANSI_WARNINGS ON
SET ANSI_NULLS ON

CREATE PROCEDURE Name (...)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Well, your suggestion had been tried and didn't work

All I need is to select some data from another server and insert into local server

|||

In Query Analyzer

Set ANSI_NULLS ON;
Set ANSI_WARNINGS ON;

Execute and then remove these two lines of code.

You can now write your create procedure code in Analyzer

SQL server will remember these ansi settings evertime your procedure is subsequently called

|||

hi,

did u get the solution for ur problem? i am facing the same error..

|||

Jens suggestion will work - I think you just need to use a GO between the SETs and the Create statement. Your stored procedure needs to be created with the settings on. So you just set those on in your session and then create the stored procedure.

Set ansi_nulls on
Set ansi_warnings on
go
Create Procedure YourProcedure ....

-Sue

Heterogeneous queries, ANSI_NULLS, ANSI_WARNINGS

I have stored procedure:

EXEC sp_addlinkedsrvlogin @.FailedRegionServerName, 'false', NULL, 'sa', 'pass'

DECLARE @.a varchar(100)
SET @.a = @.FailedRegionServerName + '.Ithalat.dbo.Product'

DECLARE @.s varchar(100)
SET @.s = ' SELECT * FROM ' + @.a
EXEC ( @.s )

When I execute it I get the error:

Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

Then I put
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON lines into the procedure. Also checked "Ansi Nulls" and "Ansi Warnings" in the properties of SQL Server. It didn't work

Then I tried:

DECLARE @.s varchar(300)
SET @.s = 'SET ANSI_WARNINGS ON; SET ANSI_NULLS ON; SELECT * FROM ' + @.a
EXEC ( @.s )

I still got the error.

WHAT SHOULD I DO? HOW CAN I GET A TABLE CONTENT FROM A LINKED SERVER? Any will be appreciated, thanks a lot...

You will have to create the stored procedures with those ANSI Settings:

SET ANSI_WARNINGS ON
SET ANSI_NULLS ON

CREATE PROCEDURE Name (...)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Well, your suggestion had been tried and didn't work

All I need is to select some data from another server and insert into local server

|||

In Query Analyzer

Set ANSI_NULLS ON;
Set ANSI_WARNINGS ON;

Execute and then remove these two lines of code.

You can now write your create procedure code in Analyzer

SQL server will remember these ansi settings evertime your procedure is subsequently called

|||

hi,

did u get the solution for ur problem? i am facing the same error..

|||

Jens suggestion will work - I think you just need to use a GO between the SETs and the Create statement. Your stored procedure needs to be created with the settings on. So you just set those on in your session and then create the stored procedure.

Set ansi_nulls on
Set ansi_warnings on
go
Create Procedure YourProcedure ....

-Sue

Here is the query

select -- DebitNote
1as Tag,
null as Parent,
null as [DebitNote!1],
null as [DebitNoteDocumentDetails!2],
null as [DebitNoteDocumentDate!3],
null as [DebitNoteDocumentNumber!4],
null as [DocumentStatus!5],
null as [InvoiceReference!6],
null as [InvoiceDocumentNumber!7],
null as [OrderReference!8],-- col 10
null as [PurchaseOrderNumber!9],
null as [Buyer!10],
null as [BuyerGLN!11],
null as [Address!12],
null as [BuildingIdentifier!13],
null as [PostCode!14],
null as [Seller!15],
null as [SellerGLN!16],
null as [Address!17],
null as [BuildingIdentifier!18],-- col 20
null as [PostCode!19],
null as [ShipTo!20],
null as [ShipToGLN!21],
null as [TaxPointDateTime!22],
null as [DebitItem!23],
null as [DebitItem!23!ChangeReasonCoded],
null as [LineItemNumber!24],
null as [ItemIdentifier!25],
null as [GTIN!26],
null as [InvoiceQuantity!27],-- col 30
null as [DebitQuantity!28],
null as [UnitPrice!29],
null as [LineItemPrice!30],
null as [LineItemDiscount!31],
null as [DiscountValue!32],
null as [DiscountRate!33],
null as [VATDetails!34],
null as [TaxCategory!35],
null as [TaxRate!36],
null as [CreditLineIndicator!37], -- col 40
null as [DebitTotals!38],
null as [DocumentDiscountRate!39],
null as [SettlementDiscountRate!40],
null as [VATRateTotals!41],
null as [VATDetails!42],
null as [TaxCategory!43],
null as [TaxRate!44],
null as [DiscountedLineTotals!45],
null as [DocumentDiscountValue!46],
null as [SettlementDiscountValue!47], -- col 50
null as [TaxableAmount!48],
null as [VATPayable!49],
null as [SettlementSubTotal!50],
null as [DebitNoteSubTotal!51],
null as [VATTotal!52],
null as [SettlementDebitTotal!53],
null as [TotalPayable!54]-- col 57
union select-- DebitNoteDocumentDetails
2,
1,
null, -- DebitNote
null,-- DebitNoteDocumentDetails
null,null, null, null, null,null, null, null, null, null,null, null,
null, null, null,null, null, null,null, null, null,
null, null,null, null, null, null, null,null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null
union select-- DebitNoteDocumentDate
3,
2,
null, null,
DebitNoteDocumentDate,
null,null, null, null, null,null, null, null, null, null,null, null,
null, null, null,null, null, null,null, null, null,
null, null,null, null, null, null, null,null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select-- DebitNoteDocumentNumber
4,
2,
null, null, null,
DebitNoteDocumentNumber,
null,null, null, null, null,null, null, null, null, null,null, null,
null, null, null,null, null, null,null, null, null,
null, null,null, null, null, null, null,null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select-- DocumentStatus
5,
2,
null, null, null, null,
DocumentStatus,
null,null, null, null, null,null, null, null, null, null,null, null,
null, null, null,null, null, null,null, null, null,
null, null,null, null, null, null, null,null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select-- InvoiceReference
6,
1,
null, null, null,null, null,
null,
null,null, null, null, null,null, null, null, null, null,null, null,
null, null, null,null, null, null,null, null, null,
null, null,null, null, null, null, null,null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null
union select-- InvoiceDocumentNumber
7,
6,
null, null, null,null, null, null,
InvoiceDocumentNumber,
null,null, null, null, null,null, null, null, null, null,null, null,
null, null, null,null, null, null,null, null, null,
null, null,null, null, null, null, null,null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select-- OrderReference
8,
1,
null, null, null,null, null, null, null,
null,
null,null, null, null, null,null, null, null, null, null,null, null,
null, null, null,null, null, null,null, null, null,
null, null,null, null, null, null, null,null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null
union select-- PurchaseOrderNumber
9,
8,
null, null, null,null, null, null, null, null,
PurchaseOrderNumber,
null,null, null, null, null,null, null, null, null, null,null, null,
null, null, null,null, null, null,null, null, null,
null, null,null, null, null, null, null,null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null
from DebitNotes_XmlView(1)
union select-- Buyer
10,
1,
null, null, null,null, null, null, null, null, null,
null,
null,null, null, null, null,null, null, null, null, null,null, null,
null, null, null,null, null, null,null, null, null,
null, null,null, null, null, null, null,null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null
union select-- BuyerGLN
11,
10,
null, null, null,null, null, null, null, null, null, null,
BuyerGLN,
null,null, null, null, null,null, null, null, null, null,null, null,
null, null, null,null, null, null,null, null, null,
null, null,null, null, null, null, null,null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null
from DebitNotes_XmlView(1)
union select-- Address
12,
10,
null, null, null,null, null, null, null, null, null, null, null,
null,
null,null, null, null, null,null, null, null, null, null,null, null,
null, null, null,null, null, null,null, null, null,
null, null,null, null, null, null, null,null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null
union select-- BuyerBuildingIdentifier
13,
12,
null, null, null,null, null, null, null, null, null, null, null, null,
BuyerBuildingIdentifier,
null,null, null, null, null,null, null, null, null, null,null, null,
null, null, null,null, null, null,null, null, null,
null, null,null, null, null, null, null,null, null, null, null, null,
null, null, null, null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select-- BuyerPostCode
14,
12,
null, null, null,null, null, null, null, null, null, null, null, null,
null,
BuyerPostCode,
null,null, null, null, null,null, null, null, null, null,null, null,
null, null, null,null, null, null,null, null, null,
null, null,null, null, null, null, null,null, null, null, null, null,
null, null, null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select-- Seller
15,
1,
null, null, null,null, null, null, null, null, null, null, null, null,
null,null,
null,
null,null, null, null, null,null, null, null, null, null,null, null,
null, null, null,null, null, null,null, null, null,
null, null,null, null, null, null, null,null, null, null, null, null,
null, null, null, null, null, null, null
union select-- SellerGLN
16,
15,
null, null, null,null, null, null, null, null, null, null, null, null,
null,null, null,
SellerGLN,
null,null, null, null, null,null, null, null, null, null,null, null,
null, null, null,null, null, null,null, null, null,
null, null,null, null, null, null, null,null, null, null, null, null,
null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select-- Address
17,
15,
null, null, null,null, null, null, null, null, null, null, null, null,
null,null, null,null,
null,
null,null, null, null, null,null, null, null, null, null,null, null,
null, null, null,null, null, null,null, null, null,
null, null,null, null, null, null, null,null, null, null, null, null,
null, null, null, null, null
union select-- SellerBuildingIdentifier
18,
17,
null, null, null,null, null, null, null, null, null, null, null, null,
null,null, null,null, null,
SellerBuildingIdentifier,
null,null, null, null, null,null, null, null, null, null,null, null,
null, null, null,null, null, null,null, null, null,
null, null,null, null, null, null, null,null, null, null, null, null,
null, null, null, null
from DebitNotes_XmlView(1)
union select-- SellerPostCode
19,
17,
null, null, null,null, null, null, null, null, null, null, null, null,
null,null, null,null, null,null,
SellerPostCode,
null,null, null, null, null,null, null, null, null, null,null, null,
null, null, null,null, null, null,null, null, null,
null, null,null, null, null, null, null,null, null, null, null, null,
null, null, null
from DebitNotes_XmlView(1)
union select-- ShipTo
20,
1,
null, null, null,null, null, null, null, null, null, null, null, null,
null,null, null,null, null,null, null,
null,
null,null, null, null, null,null, null, null, null, null,null, null,
null, null, null,null, null, null,null, null, null,
null, null,null, null, null, null, null,null, null, null, null, null,
null, null
union select-- ShipToGLN
21,
20,
null, null, null,null, null, null, null, null, null, null, null, null,
null,null, null,null, null,null, null,null,
ShipToGLN,
null,null, null, null, null,null, null, null, null, null,null, null,
null, null, null,null, null, null,null, null, null,
null, null,null, null, null, null, null,null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select-- TaxPointDateTime
22,
1,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
TaxPointDateTime,
null,null, null, null, null,null, null, null, null, null,null, null,
null, null, null,null, null, null,null, null, null,
null, null,null, null, null, null, null,null, null, null, null, null
from DebitNotes_XmlView(1)
union select-- DebitItem
23,
1,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null,
null, '5',
null,null, null, null, null,null, null, null, null, null,null, null,
null, null, null,null, null, null,null, null, null,
null, null,null, null, null, null, null,null, null, null
union select-- LineItemNumber
24,
23,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,
LineItemNumber,
null,null, null, null, null,null, null, null, null, null,null, null,
null, null, null,null, null, null,null, null, null,
null, null,null, null, null, null, null,null, null
from DebitNotes_XmlView(1)
union select -- ItemIdentifier
25,
23,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,null,
null,
null,null, null, null, null,null, null, null, null, null,null, null,
null, null, null,null, null, null,null, null, null,
null, null,null, null, null, null, null,null
union select -- GTIN
26,
25,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,null, null,
GTIN,
null,null, null, null, null,null, null, null, null, null,null, null,
null, null, null,null, null, null,null, null, null,
null, null,null, null, null, null, null
from DebitNotes_XmlView(1)
union select -- InvoiceQuantity
27,
23,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null, null,
null, null, null,null, null, null,
InvoiceQuantity,
null,null, null, null, null,null, null, null, null, null,null, null,
null, null, null,null, null, null,null, null, null,
null, null,null, null, null, null
from DebitNotes_XmlView(1)
union select -- DebitQuantity
28,
23,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,null, null, null, null,
DebitQuantity,
null, null,null, null, null, null, null,null, null, null, null, null,
null, null,null, null, null, null, null,null, null,
null, null, null,null,null
from DebitNotes_XmlView(1)
union select -- UnitPrice
29,
23,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,null, null, null, null, null,
UnitPrice,
null,null, null,null, null, null, null, null,null, null, null, null,
null, null, null,null, null, null, null, null,null,
null, null, null, null
from DebitNotes_XmlView(1)
union select -- LineItemPrice
30,
23,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,null, null, null, null, null, null,
LineItemPrice,
null,null, null,null, null, null, null, null,null, null, null, null,
null, null, null,null, null, null, null, null,null,
null, null, null
from DebitNotes_XmlView(1)
union select -- LineItemDiscount
31,
23,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,null, null, null, null, null, null, null,
null,
null,null, null,null, null, null, null, null,null, null, null, null,
null, null, null,null, null, null, null, null,null,
null, null
union select -- DiscountValue
32,
31,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,null, null, null, null, null, null, null, null,
DiscountValue,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null,null, null, null, null, null,null,
null
from DebitNotes_XmlView(1)
union select -- DiscountRate
33,
31,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,null, null, null, null, null, null, null,null, null,
DiscountRate,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null,null, null, null, null, null,null
from DebitNotes_XmlView(1)
union select -- VATDetails
34,
23,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,null, null, null, null, null, null, null,null, null,
null,
null,
null, null, null, null,null, null, null, null, null,null, null,
null,null, null, null, null, null,null, null, null
union select -- ItemTaxCategory
35,
34,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,null, null, null, null, null, null, null,null, null,
null, null,
ItemTaxCategory,
null, null, null, null, null,null, null, null, null,null, null, null,
null, null,null, null, null,null, null
from DebitNotes_XmlView(1)
union select -- ItemTaxRate
36,
34,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,null, null, null, null, null, null, null,null, null,
null, null, null,
ItemTaxRate,
null, null, null, null, null,null, null, null, null,null, null, null,
null, null,null, null, null,null
from DebitNotes_XmlView(1)
union select -- CreditLineIndicator
37,
23,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,null, null, null, null, null, null, null,null, null,
null, null, null, null,
CreditLineIndicator,
null, null, null, null, null,null, null, null, null,null, null, null,
null, null,null, null, null
from DebitNotes_XmlView(1)
union select -- DebitTotals
38,
1,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,null, null, null, null, null, null, null,null, null,
null, null, null, null, null,
null,
null, null, null, null,null, null, null, null,null, null, null, null,
null,null, null, null
union select -- DocumentDiscountRate
39,
38,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,null, null, null, null, null, null, null,null, null,
null, null, null, null, null, null,
DocumentDiscountRate,
null, null,null, null, null, null, null, null, null, null,null, null,
null,null, null
from DebitNotes_XmlView(1)
union select -- SettlementDiscountRate
40,
38,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,null, null, null, null, null, null, null,null, null,
null, null, null, null, null, null, null,
SettlementDiscountRate,
null, null,null, null, null, null, null, null, null, null,null, null,
null,null
from DebitNotes_XmlView(1)
union select -- VATRateTotals
41,
38,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,null, null, null, null, null, null, null,null, null,
null, null, null, null, null, null, null,
null,
null,
null, null,null, null, null, null, null, null, null, null,null, null, null
union select -- VATDetails
42,
41,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,null, null, null, null, null, null, null,null, null,
null, null, null, null, null, null, null,
null, null,
null,
null, null,null, null, null, null, null, null, null, null,null, null
union select -- TotalsTaxCategory
43,
42,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,null, null, null, null, null, null, null,null, null,
null, null, null, null, null, null, null,
null, null, null,
TotalsTaxCategory,
null, null,null, null, null, null, null, null, null, null,null
from DebitNotes_XmlView(1)
union select -- TotalsTaxRate
44,
42,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,null, null, null, null, null, null, null,null, null,
null, null, null, null, null, null, null,
null, null, null, null,
TotalsTaxRate,
null, null,null, null, null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select -- DiscountedLineTotals
45,
41,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,null, null, null, null, null, null, null,null, null,
null, null, null, null, null, null, null,
null, null, null, null, null,
DiscountedLineTotals,
null,null, null, null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select -- DocumentDiscountValue
46,
41,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,null, null, null, null, null, null, null,null, null,
null, null, null, null, null, null, null,
null, null, null, null, null, null,
DocumentDiscountValue,
null, null,null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select -- SettlementDiscountValue
47,
41,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,null, null, null, null, null, null, null,null, null,
null, null, null, null, null, null, null,
null, null, null, null, null, null, null,
SettlementDiscountValue,
null,null,null, null, null, null, null
from DebitNotes_XmlView(1)
union select -- TaxableAmount
48,
41,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,null, null, null, null, null, null, null,null, null,
null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null,
TaxableAmount,
null, null,null, null, null, null
from DebitNotes_XmlView(1)
union select -- VATPayable
49,
41,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,null, null, null, null, null, null, null,null, null,
null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
VATPayable,
null,null,null, null, null
from DebitNotes_XmlView(1)
union select -- SettlementSubTotal
50,
38,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,null, null, null, null, null, null, null,null, null,
null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null,
SettlementSubTotal ,
null, null, null, null
from DebitNotes_XmlView (1)
union select -- DebitNoteSubTotal
51,
38,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,null, null, null, null, null, null, null,null, null,
null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null,
DebitNoteSubTotal,
null, null, null
from DebitNotes_XmlView(1)
union select -- VATTotal
52,
38,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,null, null, null, null, null, null, null,null, null,
null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
VATTotal,
null, null
from DebitNotes_XmlView(1)
union select -- SettlementDebitTotal
53,
38,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,null, null, null, null, null, null, null,null, null,
null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null,
SettlementDebitTotal,
null
from DebitNotes_XmlView(1)
union select -- TotalPayable
54,
38,
null, null, null,null, null, null, null, null,null, null, null, null,
null, null, null, null, null, null,null, null,null,
null, null, null,null, null, null, null, null, null, null,null, null,
null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null,
TotalPayable
from DebitNotes_XmlView(1)
order by tag
for xml explicit
Looks like a SQL Server bug. If you explicitly use the element
directive for the last five columns, it appears to work,
i.e. change
[SettlementSubTotal!50] to [SettlementSubTotal!50!element]
BTW your ORDER BY clause is correct and required, also you may
want to use UNION ALL instead of UNION (more efficient).
|||Thanks for your reply.
If I do change change [SettlementSubTotal!50] to
[SettlementSubTotal!50!element] I get <SettlementSubTotal element="0.00" />
instead of <SettlementSubTotal>0.00</SettlementSubTotal>.
If I do put [DebitTotals!38!SettlementSubTotal!element] to say that
SettlementSubTotal is an element of DebitTotals, it will come after the
VATRateTotals element (that contains other sub-elements) -- and order is
important.
This is how the <DebitTotals> bit should look like...
<DebitTotals>
<DocumentDiscountRate>0.00</DocumentDiscountRate>
<SettlementDiscountRate>0.00</SettlementDiscountRate>
<VATRateTotals>
<VATDetails>
<TaxCategory>S</TaxCategory>
<TaxRate>17.50</TaxRate>
</VATDetails>
<DiscountedLineTotals>0.00</DiscountedLineTotals>
<DocumentDiscountValue>0.00</DocumentDiscountValue>
<SettlementDiscountValue>0.00</SettlementDiscountValue>
<TaxableAmount>20.00</TaxableAmount>
<VATPayable>3.50</VATPayable>
</VATRateTotals>
<SettlementSubTotal>0.00</SettlementSubTotal>
<DebitNoteSubTotal>23.50</DebitNoteSubTotal>
<VATTotal>3.50</VATTotal>
<SettlementDebitTotal>0.00</SettlementDebitTotal>
<TotalPayable>23.50</TotalPayable>
</DebitTotals>
Is there something I can do with ORDER BY ? I'm a bit stuck now.
|||Apologies, typo
[SettlementSubTotal!50] to [SettlementSubTotal!50!!element=AD]
(two exclamation marks not one)
|||That's it ! Thanks for helping me. By the way, I have started using UNION ALL
instead of UNION
- Craig
|||Please disregard my earlier posting asking for the repro.
using [name!1] had a bug that we fixed in at least SQL Server 2005, don't
remember at the moment whether it is fixed in SP4 as well. Please use
[name!1!] instead.
Best regards
Michael
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:DA9542C4-1546-4814-BE5D-54259729A4F8@.microsoft.com...
> That's it ! Thanks for helping me. By the way, I have started using UNION
> ALL
> instead of UNION
> - Craig
|||Yes, it is fixed in SQL Server 2000 SP4. So, either install SP4 or use the
workaround Michael provided.
Regards,
Eugene
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:e1AW8F%23kFHA.320@.TK2MSFTNGP09.phx.gbl...
> Please disregard my earlier posting asking for the repro.
> using [name!1] had a bug that we fixed in at least SQL Server 2005, don't
> remember at the moment whether it is fixed in SP4 as well. Please use
> [name!1!] instead.
> Best regards
> Michael
>
> "Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
> news:DA9542C4-1546-4814-BE5D-54259729A4F8@.microsoft.com...
>

Here is the query

select -- DebitNote
1 as Tag,
null as Parent,
null as [DebitNote!1],
null as [DebitNoteDocumentDetails!2],
null as [DebitNoteDocumentDate!3],
null as [DebitNoteDocumentNumber!4],
null as [DocumentStatus!5],
null as [InvoiceReference!6],
null as [InvoiceDocumentNumber!7],
null as [OrderReference!8], -- col 10
null as [PurchaseOrderNumber!9],
null as [Buyer!10],
null as [BuyerGLN!11],
null as [Address!12],
null as [BuildingIdentifier!13],
null as [PostCode!14],
null as [Seller!15],
null as [SellerGLN!16],
null as [Address!17],
null as [BuildingIdentifier!18], -- col 20
null as [PostCode!19],
null as [ShipTo!20],
null as [ShipToGLN!21],
null as [TaxPointDateTime!22],
null as [DebitItem!23],
null as [DebitItem!23!ChangeReasonCoded],
null as [LineItemNumber!24],
null as [ItemIdentifier!25],
null as [GTIN!26],
null as [InvoiceQuantity!27], -- col 30
null as [DebitQuantity!28],
null as [UnitPrice!29],
null as [LineItemPrice!30],
null as [LineItemDiscount!31],
null as [DiscountValue!32],
null as [DiscountRate!33],
null as [VATDetails!34],
null as [TaxCategory!35],
null as [TaxRate!36],
null as [CreditLineIndicator!37], -- col 40
null as [DebitTotals!38],
null as [DocumentDiscountRate!39],
null as [SettlementDiscountRate!40],
null as [VATRateTotals!41],
null as [VATDetails!42],
null as [TaxCategory!43],
null as [TaxRate!44],
null as [DiscountedLineTotals!45],
null as [DocumentDiscountValue!46],
null as [SettlementDiscountValue!47], -- col 50
null as [TaxableAmount!48],
null as [VATPayable!49],
null as [SettlementSubTotal!50],
null as [DebitNoteSubTotal!51],
null as [VATTotal!52],
null as [SettlementDebitTotal!53],
null as [TotalPayable!54] -- col 57
union select -- DebitNoteDocumentDetails
2,
1,
null, -- DebitNote
null, -- DebitNoteDocumentDetails
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null
union select -- DebitNoteDocumentDate
3,
2,
null, null,
DebitNoteDocumentDate,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select -- DebitNoteDocumentNumber
4,
2,
null, null, null,
DebitNoteDocumentNumber,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select -- DocumentStatus
5,
2,
null, null, null, null,
DocumentStatus,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select -- InvoiceReference
6,
1,
null, null, null, null, null,
null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null
union select -- InvoiceDocumentNumber
7,
6,
null, null, null, null, null, null,
InvoiceDocumentNumber,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select -- OrderReference
8,
1,
null, null, null, null, null, null, null,
null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null
union select -- PurchaseOrderNumber
9,
8,
null, null, null, null, null, null, null, null,
PurchaseOrderNumber,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null
from DebitNotes_XmlView(1)
union select -- Buyer
10,
1,
null, null, null, null, null, null, null, null, null,
null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null
union select -- BuyerGLN
11,
10,
null, null, null, null, null, null, null, null, null, null,
BuyerGLN,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null
from DebitNotes_XmlView(1)
union select -- Address
12,
10,
null, null, null, null, null, null, null, null, null, null, null,
null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null
union select -- BuyerBuildingIdentifier
13,
12,
null, null, null, null, null, null, null, null, null, null, null, null,
BuyerBuildingIdentifier,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select -- BuyerPostCode
14,
12,
null, null, null, null, null, null, null, null, null, null, null, null,
null,
BuyerPostCode,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select -- Seller
15,
1,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null,
null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null
union select -- SellerGLN
16,
15,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null,
SellerGLN,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select -- Address
17,
15,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null,
null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null
union select -- SellerBuildingIdentifier
18,
17,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null,
SellerBuildingIdentifier,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null
from DebitNotes_XmlView(1)
union select -- SellerPostCode
19,
17,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null,
SellerPostCode,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null
from DebitNotes_XmlView(1)
union select -- ShipTo
20,
1,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null,
null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null
union select -- ShipToGLN
21,
20,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null,
ShipToGLN,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select -- TaxPointDateTime
22,
1,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
TaxPointDateTime,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select -- DebitItem
23,
1,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null,
null, '5',
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null
union select -- LineItemNumber
24,
23,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null,
LineItemNumber,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select -- ItemIdentifier
25,
23,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null,
null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null
union select -- GTIN
26,
25,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null,
GTIN,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select -- InvoiceQuantity
27,
23,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null,
InvoiceQuantity,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select -- DebitQuantity
28,
23,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null,
DebitQuantity,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null
from DebitNotes_XmlView(1)
union select -- UnitPrice
29,
23,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null,
UnitPrice,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null
from DebitNotes_XmlView(1)
union select -- LineItemPrice
30,
23,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
LineItemPrice,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null
from DebitNotes_XmlView(1)
union select -- LineItemDiscount
31,
23,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null,
null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null
union select -- DiscountValue
32,
31,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null,
DiscountValue,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null
from DebitNotes_XmlView(1)
union select -- DiscountRate
33,
31,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
DiscountRate,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select -- VATDetails
34,
23,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null,
null,
null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null
union select -- ItemTaxCategory
35,
34,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null,
ItemTaxCategory,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select -- ItemTaxRate
36,
34,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null,
ItemTaxRate,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select -- CreditLineIndicator
37,
23,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null,
CreditLineIndicator,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null
from DebitNotes_XmlView(1)
union select -- DebitTotals
38,
1,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null,
null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null
union select -- DocumentDiscountRate
39,
38,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null,
DocumentDiscountRate,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null
from DebitNotes_XmlView(1)
union select -- SettlementDiscountRate
40,
38,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null,
SettlementDiscountRate,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null
from DebitNotes_XmlView(1)
union select -- VATRateTotals
41,
38,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null,
null,
null,
null, null, null, null, null, null, null, null, null, null, null, null, null
union select -- VATDetails
42,
41,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null,
null, null,
null,
null, null, null, null, null, null, null, null, null, null, null, null
union select -- TotalsTaxCategory
43,
42,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null,
null, null, null,
TotalsTaxCategory,
null, null, null, null, null, null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select -- TotalsTaxRate
44,
42,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null,
null, null, null, null,
TotalsTaxRate,
null, null, null, null, null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select -- DiscountedLineTotals
45,
41,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null,
null, null, null, null, null,
DiscountedLineTotals,
null, null, null, null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select -- DocumentDiscountValue
46,
41,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null,
null, null, null, null, null, null,
DocumentDiscountValue,
null, null, null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select -- SettlementDiscountValue
47,
41,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null,
null, null, null, null, null, null, null,
SettlementDiscountValue,
null, null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select -- TaxableAmount
48,
41,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null,
TaxableAmount,
null, null, null, null, null, null
from DebitNotes_XmlView(1)
union select -- VATPayable
49,
41,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
VATPayable,
null, null, null, null, null
from DebitNotes_XmlView(1)
union select -- SettlementSubTotal
50,
38,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null,
SettlementSubTotal ,
null, null, null, null
from DebitNotes_XmlView (1)
union select -- DebitNoteSubTotal
51,
38,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null,
DebitNoteSubTotal,
null, null, null
from DebitNotes_XmlView(1)
union select -- VATTotal
52,
38,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
VATTotal,
null, null
from DebitNotes_XmlView(1)
union select -- SettlementDebitTotal
53,
38,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null,
SettlementDebitTotal,
null
from DebitNotes_XmlView(1)
union select -- TotalPayable
54,
38,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null,
null, null,
TotalPayable
from DebitNotes_XmlView(1)
order by tag
for xml explicitLooks like a SQL Server bug. If you explicitly use the element
directive for the last five columns, it appears to work,
i.e. change
[SettlementSubTotal!50] to [SettlementSubTotal!50!element]
BTW your ORDER BY clause is correct and required, also you may
want to use UNION ALL instead of UNION (more efficient).|||Thanks for your reply.
If I do change change [SettlementSubTotal!50] to
[SettlementSubTotal!50!element] I get <SettlementSubTotal element="0.00" />
instead of <SettlementSubTotal>0.00</SettlementSubTotal>.
If I do put [DebitTotals!38!SettlementSubTotal!eleme
nt] to say that
SettlementSubTotal is an element of DebitTotals, it will come after the
VATRateTotals element (that contains other sub-elements) -- and order is
important.
This is how the <DebitTotals> bit should look like...
<DebitTotals>
<DocumentDiscountRate>0.00</DocumentDiscountRate>
<SettlementDiscountRate>0.00</SettlementDiscountRate>
<VATRateTotals>
<VATDetails>
<TaxCategory>S</TaxCategory>
<TaxRate>17.50</TaxRate>
</VATDetails>
<DiscountedLineTotals>0.00</DiscountedLineTotals>
<DocumentDiscountValue>0.00</DocumentDiscountValue>
<SettlementDiscountValue>0.00</SettlementDiscountValue>
<TaxableAmount>20.00</TaxableAmount>
<VATPayable>3.50</VATPayable>
</VATRateTotals>
<SettlementSubTotal>0.00</SettlementSubTotal>
<DebitNoteSubTotal>23.50</DebitNoteSubTotal>
<VATTotal>3.50</VATTotal>
<SettlementDebitTotal>0.00</SettlementDebitTotal>
<TotalPayable>23.50</TotalPayable>
</DebitTotals>
Is there something I can do with ORDER BY ? I'm a bit stuck now.|||Apologies, typo
[SettlementSubTotal!50] to [SettlementSubTotal!50!!element=AD]
(two exclamation marks not one)|||That's it ! Thanks for helping me. By the way, I have started using UNION AL
L
instead of UNION
- Craig|||Please disregard my earlier posting asking for the repro.
using [name!1] had a bug that we fixed in at least SQL Server 2005, don't
remember at the moment whether it is fixed in SP4 as well. Please use
[name!1!] instead.
Best regards
Michael
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:DA9542C4-1546-4814-BE5D-54259729A4F8@.microsoft.com...
> That's it ! Thanks for helping me. By the way, I have started using UNION
> ALL
> instead of UNION
> - Craig|||Yes, it is fixed in SQL Server 2000 SP4. So, either install SP4 or use the
workaround Michael provided.
Regards,
Eugene
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:e1AW8F%23kFHA.320@.TK2MSFTNGP09.phx.gbl...
> Please disregard my earlier posting asking for the repro.
> using [name!1] had a bug that we fixed in at least SQL Server 2005, don't
> remember at the moment whether it is fixed in SP4 as well. Please use
> [name!1!] instead.
> Best regards
> Michael
>
> "Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
> news:DA9542C4-1546-4814-BE5D-54259729A4F8@.microsoft.com...
>

Help: Query

Hi,
I have 2 tables like below
CREATE TABLE Location
(
LocationID int NOT NULL,
LocationName char (30) NOT NULL UNIQUE,
CONSTRAINT PK_Location PRIMARY KEY (LocationID)
)
CREATE TABLE Parts
(
PartID int NOT NULL,
LocationID int NOT NULL,
PartName char (30) NOT NULL,
CONSTRAINT PK_Parts PRIMARY KEY (PartID),
CONSTRAINT FK_PartsLocation FOREIGN KEY (Location ID)
REFERENCES Location (LocationID)
)
I have created a stored procedure to delete all of partid on a certain
location as shown below.
CREATE PROCEDURE sp_DeleteLocation @.LocName char(30) AS
BEGIN
DECLARE @.PartID int
DECLARE crs_Parts CURSOR FOR
SELECT p.PartID
FROM Parts AS p INNER JOIN Location AS 1
ON p.LocationID = @.LocName
WHERE l.LocationName = @.LocName
OPEN crs_Parts
FETCH NEXT FROM crs_Parts INTO @.PartID
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
DELETE Parts WHERE CURRENT OF crs_Parts
FETCH NEXT FROM crs_Parts INTO @.PartID
END
CLOSE crs_Parts
DEALLOCATE crs_Parts
DELETE Location WHERE LocationName = @.LocName
END
My Question is how to replace the cursor operation in the stored
procedure by using single DELETE statement?
Thanks
Robert LieHi Robert
Jusr the query this way,
DELETE Parts
FROM Parts INNER JOIN Location
ON ON Parts.LocationID = Location.LocationID
WHERE Location.LocationName = @.LocName
hope this is the one that you are looking for:
thanks and regards
Chandra
"Robert Lie" wrote:
> Hi,
> I have 2 tables like below
> CREATE TABLE Location
> (
> LocationID int NOT NULL,
> LocationName char (30) NOT NULL UNIQUE,
> CONSTRAINT PK_Location PRIMARY KEY (LocationID)
> )
>
> CREATE TABLE Parts
> (
> PartID int NOT NULL,
> LocationID int NOT NULL,
> PartName char (30) NOT NULL,
> CONSTRAINT PK_Parts PRIMARY KEY (PartID),
> CONSTRAINT FK_PartsLocation FOREIGN KEY (Location ID)
> REFERENCES Location (LocationID)
> )
>
> I have created a stored procedure to delete all of partid on a certain
> location as shown below.
> CREATE PROCEDURE sp_DeleteLocation @.LocName char(30) AS
> BEGIN
> DECLARE @.PartID int
> DECLARE crs_Parts CURSOR FOR
> SELECT p.PartID
> FROM Parts AS p INNER JOIN Location AS 1
> ON p.LocationID = @.LocName
> WHERE l.LocationName = @.LocName
> OPEN crs_Parts
> FETCH NEXT FROM crs_Parts INTO @.PartID
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> DELETE Parts WHERE CURRENT OF crs_Parts
> FETCH NEXT FROM crs_Parts INTO @.PartID
> END
> CLOSE crs_Parts
> DEALLOCATE crs_Parts
> DELETE Location WHERE LocationName = @.LocName
> END
> My Question is how to replace the cursor operation in the stored
> procedure by using single DELETE statement?
> Thanks
> Robert Lie
>

Help: Messages stuck in sys.transmission_queue

For some reason the messages are stuck at sys.transmission_queue. The transmission status seems to be null. I verified all the Queues and they hae the values of (is_receive_enables = 1, is_enqueue_enabled = 1, is_activation_enabled = 0)

I have set up the service broker on different domain and have bidirectional ports open to receive messages.

I can send and receive messages, when I run the same service broker setup scripts at the machines that belongs to same domain .

any help appreciated.

Do you have anything picking up the messages from the queues? You have is_activation_enabled = 0. Have you tried associating an sp with a queue ?

Tim|||

Thanks Tim... I figured out the issue. The 2 servers we used are in different domain and have firewall. The ports open for service broker were not bidirectional.

Help: Messages stuck in sys.transmission_queue

For some reason the messages are stuck at sys.transmission_queue. The transmission status seems to be null. I verified all the Queues and they hae the values of (is_receive_enables = 1, is_enqueue_enabled = 1, is_activation_enabled = 0)

I have set up the service broker on different domain and have bidirectional ports open to receive messages.

I can send and receive messages, when I run the same service broker setup scripts at the machines that belongs to same domain .

any help appreciated.

Do you have anything picking up the messages from the queues? You have is_activation_enabled = 0. Have you tried associating an sp with a queue ?

Tim|||

Thanks Tim... I figured out the issue. The 2 servers we used are in different domain and have firewall. The ports open for service broker were not bidirectional.