Invoice Sample

Anyone know where I can find a sample RDL that is styled like an invoice
form?
Header - contains Sold To/Ship To
Detail - Invoice Line Items
Footer - contains invoice totals
TIA
TinaTina,
Reporting Services installs sample reports. There is one particular report
you could probably use as template.
The report is called Sales Order Detail report. It resembles an invoice. You
should be able to access this report in http://<server>/ReportServer in the
SampleReports folder. You can access the design of this report in Visual
Studio .Net.
Dave
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:%23OhIxkM9EHA.2192@.TK2MSFTNGP14.phx.gbl...
> Anyone know where I can find a sample RDL that is styled like an invoice
> form?
> Header - contains Sold To/Ship To
> Detail - Invoice Line Items
> Footer - contains invoice totals
> TIA
> Tina
>|||Thanks David, I'll have a look at the sample.
"David Pope" <dpope@.nospam.satx.rr.com> wrote in message
news:OMPQ7sM9EHA.2540@.TK2MSFTNGP09.phx.gbl...
> Tina,
> Reporting Services installs sample reports. There is one particular report
> you could probably use as template.
> The report is called Sales Order Detail report. It resembles an invoice.
You
> should be able to access this report in http://<server>/ReportServer in
the
> SampleReports folder. You can access the design of this report in Visual
> Studio .Net.
> Dave
> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> news:%23OhIxkM9EHA.2192@.TK2MSFTNGP14.phx.gbl...
> > Anyone know where I can find a sample RDL that is styled like an invoice
> > form?
> >
> > Header - contains Sold To/Ship To
> > Detail - Invoice Line Items
> > Footer - contains invoice totals
> >
> > TIA
> > Tina
> >
> >
>|||That's a start but I need to repeat the BillTo & ShipTo on every page.
"David Pope" <dpope@.nospam.satx.rr.com> wrote in message
news:OMPQ7sM9EHA.2540@.TK2MSFTNGP09.phx.gbl...
> Tina,
> Reporting Services installs sample reports. There is one particular report
> you could probably use as template.
> The report is called Sales Order Detail report. It resembles an invoice.
You
> should be able to access this report in http://<server>/ReportServer in
the
> SampleReports folder. You can access the design of this report in Visual
> Studio .Net.
> Dave
> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> news:%23OhIxkM9EHA.2192@.TK2MSFTNGP14.phx.gbl...
> > Anyone know where I can find a sample RDL that is styled like an invoice
> > form?
> >
> > Header - contains Sold To/Ship To
> > Detail - Invoice Line Items
> > Footer - contains invoice totals
> >
> > TIA
> > Tina
> >
> >
>|||Tina,
You can put the Bill To and Ship To information in the Table Header and have
it repeat.
Dave
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:uPYifJP9EHA.2680@.TK2MSFTNGP09.phx.gbl...
> That's a start but I need to repeat the BillTo & ShipTo on every page.
> "David Pope" <dpope@.nospam.satx.rr.com> wrote in message
> news:OMPQ7sM9EHA.2540@.TK2MSFTNGP09.phx.gbl...
>> Tina,
>> Reporting Services installs sample reports. There is one particular
>> report
>> you could probably use as template.
>> The report is called Sales Order Detail report. It resembles an invoice.
> You
>> should be able to access this report in http://<server>/ReportServer in
> the
>> SampleReports folder. You can access the design of this report in Visual
>> Studio .Net.
>> Dave
>> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
>> news:%23OhIxkM9EHA.2192@.TK2MSFTNGP14.phx.gbl...
>> > Anyone know where I can find a sample RDL that is styled like an
>> > invoice
>> > form?
>> >
>> > Header - contains Sold To/Ship To
>> > Detail - Invoice Line Items
>> > Footer - contains invoice totals
>> >
>> > TIA
>> > Tina
>> >
>> >
>>
>|||That's what I'm working on now but I loose the free-form ability when
working in a table.
"David Pope" <dpope@.nospam.satx.rr.com> wrote in message
news:uw4KOPP9EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Tina,
> You can put the Bill To and Ship To information in the Table Header and
have
> it repeat.
> Dave
> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> news:uPYifJP9EHA.2680@.TK2MSFTNGP09.phx.gbl...
> > That's a start but I need to repeat the BillTo & ShipTo on every page.
> >
> > "David Pope" <dpope@.nospam.satx.rr.com> wrote in message
> > news:OMPQ7sM9EHA.2540@.TK2MSFTNGP09.phx.gbl...
> >> Tina,
> >>
> >> Reporting Services installs sample reports. There is one particular
> >> report
> >> you could probably use as template.
> >>
> >> The report is called Sales Order Detail report. It resembles an
invoice.
> > You
> >> should be able to access this report in http://<server>/ReportServer in
> > the
> >> SampleReports folder. You can access the design of this report in
Visual
> >> Studio .Net.
> >>
> >> Dave
> >>
> >> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> >> news:%23OhIxkM9EHA.2192@.TK2MSFTNGP14.phx.gbl...
> >> > Anyone know where I can find a sample RDL that is styled like an
> >> > invoice
> >> > form?
> >> >
> >> > Header - contains Sold To/Ship To
> >> > Detail - Invoice Line Items
> >> > Footer - contains invoice totals
> >> >
> >> > TIA
> >> > Tina
> >> >
> >> >
> >>
> >>
> >
> >
>|||Welcome to Reporting Services. I am a Crystal Reports developer myself but
starting to understand Reporting Services.
Tip: You can select multiple cells and merge them. That is a huge help.
Dave
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:exmn0TP9EHA.3588@.TK2MSFTNGP10.phx.gbl...
> That's what I'm working on now but I loose the free-form ability when
> working in a table.
> "David Pope" <dpope@.nospam.satx.rr.com> wrote in message
> news:uw4KOPP9EHA.1404@.TK2MSFTNGP11.phx.gbl...
>> Tina,
>> You can put the Bill To and Ship To information in the Table Header and
> have
>> it repeat.
>> Dave
>> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
>> news:uPYifJP9EHA.2680@.TK2MSFTNGP09.phx.gbl...
>> > That's a start but I need to repeat the BillTo & ShipTo on every page.
>> >
>> > "David Pope" <dpope@.nospam.satx.rr.com> wrote in message
>> > news:OMPQ7sM9EHA.2540@.TK2MSFTNGP09.phx.gbl...
>> >> Tina,
>> >>
>> >> Reporting Services installs sample reports. There is one particular
>> >> report
>> >> you could probably use as template.
>> >>
>> >> The report is called Sales Order Detail report. It resembles an
> invoice.
>> > You
>> >> should be able to access this report in http://<server>/ReportServer
>> >> in
>> > the
>> >> SampleReports folder. You can access the design of this report in
> Visual
>> >> Studio .Net.
>> >>
>> >> Dave
>> >>
>> >> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
>> >> news:%23OhIxkM9EHA.2192@.TK2MSFTNGP14.phx.gbl...
>> >> > Anyone know where I can find a sample RDL that is styled like an
>> >> > invoice
>> >> > form?
>> >> >
>> >> > Header - contains Sold To/Ship To
>> >> > Detail - Invoice Line Items
>> >> > Footer - contains invoice totals
>> >> >
>> >> > TIA
>> >> > Tina
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>

Invert rows and columns (PIVOT)

Hi everybody.

I have this table that contains how many items were sold (and their value) into two departments:

SellerID(PK)	Year(PK)	ItemsSoldDPT1	ItemsSoldDPT2	ValueSoldDPT1	ValueSoldDPT2
1	2002	10	20	300.00	400.00
1	2003	13	71	450.00	320.00
1	2004	8	4	350.00	640.00
1	2005	2	15	110.00	680.00
2	2001	3	1	130.00	100.00
2	2005	1	7	190.00	200.00
2	2006	6	9	170.00	500.00
...	...	...	...	...	...

I'm trying to write a query that puts the data present in the "Year" column as if they were in a row (column definitions)..making sums of pieces and values.. or.. to be more clear..

I want to obtain this:

SellerID	2001 Items	2001 Values	2002 Items	2002 Values	2003 Items	2003 Values	2004 Items	2004 Values	2005 Items	2005 Values	2006 Items	2006 Values	...
1	(NULL)	(NULL)	30	700.00	84	770.00	12	990.00	17	790.00	(NULL)	(NULL)	...
2	4	230.00	(NULL)	(NULL)	(NULL)	(NULL)	(NULL)	(NULL)	8	390.00	15	670.00	...
...	...	..	...	...	...	...	...	...	...	...	...	...	...

Any ideas? I think I should use the PIVOT keyword to write the sql but I can't figure how it works and how can I do that sums.

Please note: I don't know how many distinct values of "Year" exists and the min and max year can be specified by the user.

Thank you for your help.

It is best to use the standard SQL approach of using GROUP BY and CASE expressions. Pivot can only be used to pivot one set of values. You have to write more complex query if you want to just use PIVOT operator and performance will be bad for those approaches.

select t.SellerID

, sum(case t.Year when 2001 then t.ItemsSoldDPT1 + t.ItemsSoldDPT2 end) as 2001_Values

, sum(case t.Year when 2001 then t.ValuesSoldDPT1 + t.ValuesSoldDPT2 end) as 2001_Values

, sum(case t.Year when 2002 then t.ItemsSoldDPT1 + t.ItemsSoldDPT2 end) as 2002_Values

, sum(case t.Year when 2002 then t.ValuesSoldDPT1 + t.ValuesSoldDPT2 end) as 2002_Values

...

from tbl as t

group by t.SellerID

|||Thank you for your interest and your time.
I already thought at this solution (it was the first approach I used) but this means that I must build a query that contains a couple of "sum(...) as ..." for each year I want to consider.. and I know neither how many different years are contained in the table nor how big is the date interval..
Moreover I discarted this approach because I can only replace the initial and final year value in the SQL query (something like a "tag replace"). We use a particular reporting tecnology that accepts queries at design time and replaces variables at runtime (with typed parameters).

So I have to write something like
SELECT.... xxx...xxx..xxx
WHERE...
and Year>=[%shortInitialYear] and Year<=[%shortFinalYear]

Even if I could use the PIVOT keyword I have a similar problem because the pivot keyword assumes that the user has to specify each value to pivot "IN ([value1], [value2], [value3])" and not a range (something like "BETWEEN [%shortInitialYear] and [%shortFinalYear]" )|||

hi

i understand exactly what you mean.

here is a short description on what i did to solve the problem, i apologize for not having the time to go into more detail but you'll figure this out quickly

first do a query where you would get the distinct date information with the amounts (sum it if you want), in a view would be best

then when you have got the distinct dates create a new temporary table, dynamically of course. i created the tabel and in a cursor added the columns (which was a result from the view)

then insert into your dynamic table the values into columns corresponding to the view you have created.

this whole approach works if your dynamic sql logic is sound, best is that you don't have to specify any thing other than what you want in the original view.

hope this helps

|||

It is a known annoyance :) You can use pivot by flattening out the set: http://drsql.spaces.msn.com/blog/cns!80677FB08B3162E4!758.entry but like Umachandar said, the "classic" style is easier/faster. If you want to help change this, vote here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127071

The easiest thing to do is to just make your SUM clauses dynamic:

create table year
(
year char(4) primary key
)
insert into year
select 2001
union all
select 2002
go
declare @.query varchar(8000)
select @.query = 'select t.SellerID ' + char(10) + (
SELECT distinct
', sum(case t.Year when ''' + year + ''' then t.ItemsSoldDPT1 + t.ItemsSoldDPT2 end) as ' + year + '_Values' + char(10) +
', sum(case t.Year when ''' + year + ''' then t.ValuesSoldDPT1 + t.ValuesSoldDPT2 end) as ' + year + '_Values' + char(10)
AS [text()]
FROM
year y
FOR XML PATH('') ) + char(10) + ' from tbl as t group by t.SellerID'

select @.query

I used 2005 syntax to build the sum columns, since I think you have 2005, in this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=488720&SiteID=1 there is another full example.

sql

Inventory update problem

I am trying to update a master inventory table from an order details table, the query below works fine except when the order details table contains the same code number multiple times. When this occurs the update only updates for the first instance of the code number.

How do I make the update work for all the records not just the unique records?

UPDATE Inventory.Inventory
SET Qty = Inventory.Inventory.Qty - Retail.OrderDetails.Qty FROM Inventory.Inventory INNER JOIN
Retail.OrderDetails ON Inventory.Inventory.Code = Retail.OrderDetails.Code
WHERE (Retail.OrderDetails.Invoice = 207070202)

Thanks

Quote:

Originally Posted by Kliot

I am trying to update a master inventory table from an order details table, the query below works fine except when the order details table contains the same code number multiple times. When this occurs the update only updates for the first instance of the code number.

How do I make the update work for all the records not just the unique records?

UPDATE Inventory.Inventory
SET Qty = Inventory.Inventory.Qty - Retail.OrderDetails.Qty FROM Inventory.Inventory INNER JOIN
Retail.OrderDetails ON Inventory.Inventory.Code = Retail.OrderDetails.Code
WHERE (Retail.OrderDetails.Invoice = 207070202)

Thanks

I think it is not possible in SQL Server. But it will work in MS Access.

You have to fetch record and then update it|||hi

i have gone through ur query, but if possible just send me 1 or two records of each table and tell me exactily what u want|||Here is an example,

Invoice table

Code|||Here is an example,

Invoice table

Code Quantity
DM01 2
LG02 2
DM01 3
QP76 1

The update query will update the Inventory table quantity for DM01 by 2 not 5, the second DM01 is not updated

I can get around this by doing a sum query inside the select but it's not ideal.

UPDATE Inventory.Inventory
set RQty = Inventory.Inventory.RQty - od.Quantity
FROM (SELECT Code, SUM(Quantity) AS Quantity FROM Retail.OrderDetails WHERE Invoice = 207022101
GROUP BY Code) as od WHERE(Inventory.inventory.code = od.code)

Invalid object name 'information_schema.constraint_table_usage'

I created a new database on a SQL 2005 server, which also contains other
databases. I just tried to run "select * from
information_schema.constraint_table_usage" and got an error Msg 208, Level
16, State 1, Line 1
Invalid object name 'information_schema.constraint_table_usage'.
However I can run the select statement on the other databases and get the
information back.
Anyone have any idea why it wouldn't work on one database but does on other's.
Thanks in advance.
Possibly a permissions issue?
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:4835E0D8-D980-4C60-A2D2-A6BDFAD2D91D@.microsoft.com...
>I created a new database on a SQL 2005 server, which also contains other
> databases. I just tried to run "select * from
> information_schema.constraint_table_usage" and got an error Msg 208, Level
> 16, State 1, Line 1
> Invalid object name 'information_schema.constraint_table_usage'.
> However I can run the select statement on the other databases and get the
> information back.
> Anyone have any idea why it wouldn't work on one database but does on
> other's.
> Thanks in advance.
|||> My guess is that the database is case-sensitive. The info schema view
> names are all upper-case.
Of course, duh. I was going to suggest compatibility level next. :-)
|||That was the issue, thought I created it case-insensitive...
"Tibor Karaszi" wrote:

> My guess is that the database is case-sensitive. The info schema view names are all upper-case.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "scuba79" <scuba79@.discussions.microsoft.com> wrote in message
> news:4835E0D8-D980-4C60-A2D2-A6BDFAD2D91D@.microsoft.com...
>
>

Invalid object name 'information_schema.constraint_table_usage'

I created a new database on a SQL 2005 server, which also contains other
databases. I just tried to run "select * from
information_schema.constraint_table_usage" and got an error Msg 208, Level
16, State 1, Line 1
Invalid object name 'information_schema.constraint_table_usage'.
However I can run the select statement on the other databases and get the
information back.
Anyone have any idea why it wouldn't work on one database but does on other's.
Thanks in advance.Possibly a permissions issue?
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:4835E0D8-D980-4C60-A2D2-A6BDFAD2D91D@.microsoft.com...
>I created a new database on a SQL 2005 server, which also contains other
> databases. I just tried to run "select * from
> information_schema.constraint_table_usage" and got an error Msg 208, Level
> 16, State 1, Line 1
> Invalid object name 'information_schema.constraint_table_usage'.
> However I can run the select statement on the other databases and get the
> information back.
> Anyone have any idea why it wouldn't work on one database but does on
> other's.
> Thanks in advance.|||My guess is that the database is case-sensitive. The info schema view names are all upper-case.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:4835E0D8-D980-4C60-A2D2-A6BDFAD2D91D@.microsoft.com...
>I created a new database on a SQL 2005 server, which also contains other
> databases. I just tried to run "select * from
> information_schema.constraint_table_usage" and got an error Msg 208, Level
> 16, State 1, Line 1
> Invalid object name 'information_schema.constraint_table_usage'.
> However I can run the select statement on the other databases and get the
> information back.
> Anyone have any idea why it wouldn't work on one database but does on other's.
> Thanks in advance.|||> My guess is that the database is case-sensitive. The info schema view
> names are all upper-case.
Of course, duh. I was going to suggest compatibility level next. :-)|||That was the issue, thought I created it case-insensitive...
"Tibor Karaszi" wrote:
> My guess is that the database is case-sensitive. The info schema view names are all upper-case.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "scuba79" <scuba79@.discussions.microsoft.com> wrote in message
> news:4835E0D8-D980-4C60-A2D2-A6BDFAD2D91D@.microsoft.com...
> >I created a new database on a SQL 2005 server, which also contains other
> > databases. I just tried to run "select * from
> > information_schema.constraint_table_usage" and got an error Msg 208, Level
> > 16, State 1, Line 1
> > Invalid object name 'information_schema.constraint_table_usage'.
> >
> > However I can run the select statement on the other databases and get the
> > information back.
> >
> > Anyone have any idea why it wouldn't work on one database but does on other's.
> >
> > Thanks in advance.
>
>sql

Invalid object name 'information_schema.constraint_table_usage'

I created a new database on a SQL 2005 server, which also contains other
databases. I just tried to run "select * from
information_schema.constraint_table_usage" and got an error Msg 208, Level
16, State 1, Line 1
Invalid object name 'information_schema.constraint_table_usage'.
However I can run the select statement on the other databases and get the
information back.
Anyone have any idea why it wouldn't work on one database but does on other'
s.
Thanks in advance.Possibly a permissions issue?
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:4835E0D8-D980-4C60-A2D2-A6BDFAD2D91D@.microsoft.com...
>I created a new database on a SQL 2005 server, which also contains other
> databases. I just tried to run "select * from
> information_schema.constraint_table_usage" and got an error Msg 208, Level
> 16, State 1, Line 1
> Invalid object name 'information_schema.constraint_table_usage'.
> However I can run the select statement on the other databases and get the
> information back.
> Anyone have any idea why it wouldn't work on one database but does on
> other's.
> Thanks in advance.|||My guess is that the database is case-sensitive. The info schema view names
are all upper-case.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:4835E0D8-D980-4C60-A2D2-A6BDFAD2D91D@.microsoft.com...
>I created a new database on a SQL 2005 server, which also contains other
> databases. I just tried to run "select * from
> information_schema.constraint_table_usage" and got an error Msg 208, Level
> 16, State 1, Line 1
> Invalid object name 'information_schema.constraint_table_usage'.
> However I can run the select statement on the other databases and get the
> information back.
> Anyone have any idea why it wouldn't work on one database but does on othe
r's.
> Thanks in advance.|||> My guess is that the database is case-sensitive. The info schema view
> names are all upper-case.
Of course, duh. I was going to suggest compatibility level next. :-)

Invalid locator de-referenced

I am trying to copy data from one SQL server to another. The data contains big image or texts. After SQLGetData, we used the SQLPrepare, SQLBindParameter, SQLParam and SQLPutData, and SQL_LEN_DATA_AT_EXEC to insert the data.

Our application is unicode based, but the data type in the SQL server is text, so we bind the paramter using SQL_C_WCHAR as C type and SQL_LONGVARCHAR as sql type. But we got the error:

SQLParamData: RETCODE= -1; State=37000, Native Error=7143

Microsoft claims it has this problem when using the MDAC2.7, see http://support.microsoft.com/kb/325765, but with a different error message.

The SQL server ODBC driver we are using is 2000.85.1117.00, but I got "Invalid locator de-referenced", Not sure if it has been resolved completely or not?

Any possible workaround?

Thank

Strong

It should have been fixed in MDAC 2.7 SP1. Can you try MDAC 2.7 SP1 or after?

From some source, it mentioned two workarounds that you might want to try as well.

1. Instead of calling SQL_LEN_DATA_AT_EXEC(409602), if we call SQL_LEN_DATA_AT_EXEC(204801), then it works. Please note that according to ODBC Spec, SQL_LEN_DATA_AT_EXEC expects client to bind number of BYTES, so this is not actually an workaround.

2. Instead of binding SQL_C_WCHAR, if we bind it as SQL_C_CHAR, then we are able to insert more than 400k. This workaround is not an option for the customer as they are using Access Linked table to Sql server, and access always bind it as SQL_C_WCHAR. Customer does not have control over how access binds it.

|||

Thanks for the reply. Actually I am using the MDAC 2.8 and SQL driver version 2000.85.1117.0 which should include the fix for the SQL_LEN_DATA_AT_EXEC problem. I am wondering if there are any new upper limits in the driver for SQLPutData, SQLParamData and SQL_LEN_DATA_AT_EXEC?

What are the differences between SQL_LEN_DATA_AT_EXEC(length) and SQL_DATA_AT_EXEC? the 'length' in SQL_LEN_DATA_AT_EXEC means the TOTAL bytes will be sent? or just the byte number sent in chunks when calling SQLPutData? What does the SQL_LEN_DATA_AT_EXEC(0) means?

Thanks

Strong

|||

SQL_LEN_DATA_AT_EXEC(length), where length is the total length of the text, ntext, or image parameter data in bytes. So, you can call SQLPutData one or more times as long as the total number of bytes to send is equal to length.

The difference between SQL_DATA_AT_EXEC and SQL_LEN_DATA_AT_EXEC(length) is that if a data source needs to know how many bytes of long data will be sent so that it can preallocate space, then SQL_LEN_DATA_AT_EXEC(length) must be used, otherwise SQL_DATA_AT_EXEC can be used. To determine if a data source requires this value, the application can call SQLGetInfo with the SQL_NEED_LONG_DATA_LEN option.

|||

Hi! Junfeng,

I figured out how I got the error "Invalid locator de-referenced", I rather think it is the issue (or limitation) from SQL Driver. The version of SQL driver I am using is 2000.85.1117.00 and the SQL Server is 2000 - 8.00.2039.

I was trying to insert 2 more than 400,000 bytes (200,000 wchar_t) data into 2 text columns using SQLParamData and SQLPutData, the code likes following but exactly, but quite sure it is correct:

*******************

Table: create table test (col1 text, col2 text)

sql: insert into test values (?, ?)

wchar_t * param1 (more than 200,000 wide chars)

wchar_t* param2 (more than 200,000 wide chars)

The ODBC API calls include:

SQLPrepare

strlen1 = SQL_LEN_DATA_AT_EXEC(param1widecharlength * sizeof(wchar_t)) -- convert to byte length

strlen2 = SQL_LEN_DATA_AT_EXEC(param2widecharlength * sizeof(wchar_t))

SQLBindParameter(stmt, 1, SQ_PARAM_INPUT, SQL_C_WCHAR, SQL_LONGVARCHAR, 214748367, 0, param1, 0, &strlen1); -- OK

SQLBindParameter(stmt, 2, SQ_PARAM_INPUT, SQL_C_WCHAR, SQL_LONGVARCHAR, 214748367, 0, param2, 0, &strlen2); -- OK

....

SQLExecute -- returns SQL_NEED_DATA, OK

SQLPOINTER retPtr1;

SQLPOINTER retPtr2;

SQLParamData(stmt, &retPtr1) -- returns SQL_NEED_DATA (OK) but the retPtr1 points to param2 (is it any probem here?)

while loop to SQLPutData

RETCODE rc = SQLPutData(stmt, (SQLPOINTER)((wchar_t*)retPtr1+ sent), 2000 * sizeof(wchar_t));

SQLParamData(stmt, &retPtr2) -- get error "Invalid locator de-referenced"

-

Supposed that the first 2 SQLParamData calls return SQL_NEED_DATA with returned retPtr1 and retPtr2 pointing to param1 and param2,

then the 3rd call of SQLParamData returns SQL_SUCCESS

-

If I did not used the SQLParamData and SQLPutData, it seems work fine.

Strong

Interpreting the percentage in decision-tree model

Hi,

I used a decision-tree mining-model to describe and predict fraud. The table contains 1039 records with 775 distinct value of A-number (the calling party). I used 9 columns in the model. SQL Server reports that only 3 columns are significant in predicting the fraud

- BPN_is_too_short (called party-number is too short)
- Duration_is_zero
- Invalid_area_code

The key-column in A-number, and the predicted column is Is_Fraud with the range of values are only 0 and 1. There's no record with NULL (missing-value) in the column Is_Fraud.

Mining Legend shows in the first split
[-] 625 cases of fraud
[-] 150 cases of non-fraud
[-] 0 cases of missing

In addition to that, Mining Legend shows
[-] 79.69% of fraud
[-] 19.64% of non-fraud
[-] 0.67% Missing

Now when I compare those values, they don't match.
(A) 625/775 is 80.645%, not 79.69%
(B) 150/775 is 19.355%, not 19.64%
(C) 0 cases of NULL (missing value) should imply 0% of missing, not 0.67% of missing

Furthermore in one node (with the split on duration_is_zero), there are 541 cases of fraud and 0 cases of non-fraud. This implies the node is leaf-node. However, Mining Legend shows

514 cases of fraud, 99.35%

0 cases of non-fraud, 0.33%

[F] 0 cases of missing, 0.33%

My questions
(1) Why the values don't match like in cases A through C ?
(2) Why the values don't match even in cases D through F when we have no subtree at all ?

I've searched explanation by reading the mathematical reasoning, entropy, Gini index; but it does not answer the discrepancies of those values and percentages in the Mining Legend.

Regards,

BernaridhoOur DT algorithm uses a baysian prior in all calculations. This means that it assumes that all possible states have equal probability at the beginning. This prior gets distributed throughout the tree. Therefore, what you are seeing are the prior-adjusted probabilities and the actual support.|||

Hi..

I need this information more details for doing my thesis which also use DT.

Would you like to give examples how to calculate this percentage ? I have tried to calculate using baysian prior, but the result isn't match which the percentage.

Thank you for your answer..

Interpreting the percentage in decision-tree model

Hi,

I used a decision-tree mining-model to describe and predict fraud. The table contains 1039 records with 775 distinct value of A-number (the calling party). I used 9 columns in the model. SQL Server reports that only 3 columns are significant in predicting the fraud

- BPN_is_too_short (called party-number is too short)
- Duration_is_zero
- Invalid_area_code

The key-column in A-number, and the predicted column is Is_Fraud with the range of values are only 0 and 1. There's no record with NULL (missing-value) in the column Is_Fraud.

Mining Legend shows in the first split
[-] 625 cases of fraud
[-] 150 cases of non-fraud
[-] 0 cases of missing

In addition to that, Mining Legend shows
[-] 79.69% of fraud
[-] 19.64% of non-fraud
[-] 0.67% Missing

Now when I compare those values, they don't match.
(A) 625/775 is 80.645%, not 79.69%
(B) 150/775 is 19.355%, not 19.64%
(C) 0 cases of NULL (missing value) should imply 0% of missing, not 0.67% of missing

Furthermore in one node (with the split on duration_is_zero), there are 541 cases of fraud and 0 cases of non-fraud. This implies the node is leaf-node. However, Mining Legend shows

514 cases of fraud, 99.35%
0 cases of non-fraud, 0.33%
[F] 0 cases of missing, 0.33%
My questions
(1) Why the values don't match like in cases A through C ?
(2) Why the values don't match even in cases D through F when we have no subtree at all ?

I've searched explanation by reading the mathematical reasoning, entropy, Gini index; but it does not answer the discrepancies of those values and percentages in the Mining Legend.

Regards,

Bernaridho
Our DT algorithm uses a baysian prior in all calculations. This means that it assumes that all possible states have equal probability at the beginning. This prior gets distributed throughout the tree. Therefore, what you are seeing are the prior-adjusted probabilities and the actual support.|||

Hi..

I need this information more details for doing my thesis which also use DT.

Would you like to give examples how to calculate this percentage ? I have tried to calculate using baysian prior, but the result isn't match which the percentage.

Thank you for your answer..

Interpreting the percentage in decision-tree model

Hi,

I used a decision-tree mining-model to describe and predict fraud. The table contains 1039 records with 775 distinct value of A-number (the calling party). I used 9 columns in the model. SQL Server reports that only 3 columns are significant in predicting the fraud

- BPN_is_too_short (called party-number is too short)
- Duration_is_zero
- Invalid_area_code

The key-column in A-number, and the predicted column is Is_Fraud with the range of values are only 0 and 1. There's no record with NULL (missing-value) in the column Is_Fraud.

Mining Legend shows in the first split
[-] 625 cases of fraud
[-] 150 cases of non-fraud
[-] 0 cases of missing

In addition to that, Mining Legend shows
[-] 79.69% of fraud
[-] 19.64% of non-fraud
[-] 0.67% Missing

Now when I compare those values, they don't match.
(A) 625/775 is 80.645%, not 79.69%
(B) 150/775 is 19.355%, not 19.64%
(C) 0 cases of NULL (missing value) should imply 0% of missing, not 0.67% of missing

Furthermore in one node (with the split on duration_is_zero), there are 541 cases of fraud and 0 cases of non-fraud. This implies the node is leaf-node. However, Mining Legend shows

514 cases of fraud, 99.35%
0 cases of non-fraud, 0.33%
[F] 0 cases of missing, 0.33%
My questions
(1) Why the values don't match like in cases A through C ?
(2) Why the values don't match even in cases D through F when we have no subtree at all ?

I've searched explanation by reading the mathematical reasoning, entropy, Gini index; but it does not answer the discrepancies of those values and percentages in the Mining Legend.

Regards,

Bernaridho
Our DT algorithm uses a baysian prior in all calculations. This means that it assumes that all possible states have equal probability at the beginning. This prior gets distributed throughout the tree. Therefore, what you are seeing are the prior-adjusted probabilities and the actual support.|||

Hi..

I need this information more details for doing my thesis which also use DT.

Would you like to give examples how to calculate this percentage ? I have tried to calculate using baysian prior, but the result isn't match which the percentage.

Thank you for your answer..