I'm soliciting any suggestions from the readers of the group on how
best to tackle this task. I need to determine if trouble tickets
opened get closed within 4 hours. Yes, I already know about
datediff(hh, date1, date2, but it's not that simple. The thing I'm
struggle with is that I need to take inaccount business hours (8:00am
to 4:00pm). If date1 is 10/27/05 2:00pm then the 4 hour window ends at
10/28/05 10:00am. Any suggestions are much appreciated.Try,
if (convert(char(8), @.sd, 112) = convert(char(8), @.ed, 112) and datediff(hh,
@.sd, @.ed) <= 4) or (convert(char(8), @.sd, 112) < convert(char(8), @.ed, 112)
and (datediff(hh, @.sd, @.ed) - 8) <= 4) print 'may be in 4 hours'
else
'more than 4 hours'
AMB
"BobD" wrote:
> I'm soliciting any suggestions from the readers of the group on how
> best to tackle this task. I need to determine if trouble tickets
> opened get closed within 4 hours. Yes, I already know about
> datediff(hh, date1, date2, but it's not that simple. The thing I'm
> struggle with is that I need to take inaccount business hours (8:00am
> to 4:00pm). If date1 is 10/27/05 2:00pm then the 4 hour window ends at
> 10/28/05 10:00am. Any suggestions are much appreciated.
>|||Correction:
if (convert(char(8), @.sd, 112) = convert(char(8), @.ed, 112) and datediff(hh,
@.sd, @.ed) <= 4) or (convert(char(8), @.sd, 112) < convert(char(8), @.ed, 112)
and (datediff(hh, @.sd, @.ed) - 20) <= 4)
print 'may be in 4 hours'
else
'more than 4 hours'
go
AMB
"Alejandro Mesa" wrote:
> Try,
> if (convert(char(8), @.sd, 112) = convert(char(8), @.ed, 112) and datediff(h
h,
> @.sd, @.ed) <= 4) or (convert(char(8), @.sd, 112) < convert(char(8), @.ed, 112
)
> and (datediff(hh, @.sd, @.ed) - 8) <= 4) print 'may be in 4 hours'
> else
> 'more than 4 hours'
>
> AMB
> "BobD" wrote:
>|||Correction: (sorry guys)
if (convert(char(8), @.sd, 112) = convert(char(8), @.ed, 112) and datediff(hh,
@.sd, @.ed) <= 4) or (convert(char(8), @.sd, 112) < convert(char(8), @.ed, 112)
and (datediff(hh, @.sd, @.ed) - 16) <= 4)
print 'may be in 4 hours'
else
'more than 4 hours'
go
AMB
"Alejandro Mesa" wrote:
> Correction:
> if (convert(char(8), @.sd, 112) = convert(char(8), @.ed, 112) and datediff(h
h,
> @.sd, @.ed) <= 4) or (convert(char(8), @.sd, 112) < convert(char(8), @.ed, 112
)
> and (datediff(hh, @.sd, @.ed) - 20) <= 4)
> print 'may be in 4 hours'
> else
> 'more than 4 hours'
> go
>
> AMB
> "Alejandro Mesa" wrote:
>|||Bob,
Try this:
SELECT
CASE WHEN cast(convert(varchar(12),date1,114) as datetime) < '08:00'
THEN dateadd(day,datediff(day,0,date1),'12:00
')
WHEN cast(convert(varchar(12),date1,114) as datetime) < '12:00'
THEN dateadd(hour,4,date1)
ELSE dateadd(hour,20,date1) END
FROM yourTable
Steve Kass
Drew University
BobD wrote:
>I'm soliciting any suggestions from the readers of the group on how
>best to tackle this task. I need to determine if trouble tickets
>opened get closed within 4 hours. Yes, I already know about
>datediff(hh, date1, date2, but it's not that simple. The thing I'm
>struggle with is that I need to take inaccount business hours (8:00am
>to 4:00pm). If date1 is 10/27/05 2:00pm then the 4 hour window ends at
>10/28/05 10:00am. Any suggestions are much appreciated.
>
>|||Bod
try this
SELECT CASE WHEN DATE1 = DATE2 THEN DATEDIFF(HOUR, DATE1,DATE2) ELSE
DATEDIFF(HOUR, DATE1,(DATEADD(HOUR,-16,DATE2)))
--
Regards
R.D
--Knowledge gets doubled when shared
"BobD" wrote:
> I'm soliciting any suggestions from the readers of the group on how
> best to tackle this task. I need to determine if trouble tickets
> opened get closed within 4 hours. Yes, I already know about
> datediff(hh, date1, date2, but it's not that simple. The thing I'm
> struggle with is that I need to take inaccount business hours (8:00am
> to 4:00pm). If date1 is 10/27/05 2:00pm then the 4 hour window ends at
> 10/28/05 10:00am. Any suggestions are much appreciated.
>
Showing posts with label trouble. Show all posts
Showing posts with label trouble. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
Computed Fields/Multiple Datasources
I am getting started with Reporting Services.
I am having trouble with computed fields. I have two issues:
1. I have a special dataset that I use to read parameters from a
database table. This table has only one row. I would like to add a
computed field that divides one of the columns in this row by another.
I created a computed field called ProRatedMultiplier with the following
definition:
=Fields!OPERATINGDAYSINMONTH.Value/Fields!INVOICINGDAY.Value
The problem is that when I insert this computed field into my report,
it wants to sum the output:
=Sum(Fields!DailyInvoiceProRateMultiplier.Value, "InputDataDS")
If I remove the SUM() function, I get compile errors.
I need to use this computed value in many places on the report and thus
would like to have the formula defined only once (instead of repeating
it in each field).
It seems SRS sees it as returning one or more rows and thus wants to
aggregate. Am I going about this the wrong way? Can I make any changes
to make this work?
2. Assuming I get the above to work, I will have this new computed
field on my dataset. I would like to create a computed field on another
dataset that uses this first computed field value.
This new, second computed field would multiply the first computed field
value (which is a pro-rata multiplier) by the sum() aggregate of a
column in the second dataset.
The definition would like something like this:
=SUM( Fields!INVOICEAMOUNT.Value) *
Fields!DailyInvoiceProRateMultiplier.Value, "InputDataDS"
This also will not compile.
Am I going about this the wrong way?For #1:
Use the First aggregate function:
=First(Fields!DailyInvoiceProRateMultiplier.Value, "InputDataDS")
So even if your InputDataDS dataset has more than one rows (for whatever
reasons), you will still get the expected result. Since you use the
calculated field value in another dataset, you cannot omit the aggregate
function (otherwise the fields collection would be scoped to the wrong
dataset).
For #2:
You could write an expression in a textbox like this:
=SUM( Fields!INVOICEAMOUNT.Value) * First(
Fields!DailyInvoiceProRateMultiplier.Value, "InputDataDS")
However, you cannot define a calculated field which uses aggregate
functions.
Probably a better solution for #1 & #2:
Since you have only 1 row - did you look into hidden textboxes, which
calculate the formulas instead of calculated fields? You could then
reference the value of the formula textbox in other textboxes by using an
expression like
=ReportItems!FormulaTextbox.Value
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hunter Hillegas" <hunter.hillegas@.gmail.com> wrote in message
news:chqjea$pq0@.odak26.prod.google.com...
> I am getting started with Reporting Services.
> I am having trouble with computed fields. I have two issues:
> 1. I have a special dataset that I use to read parameters from a
> database table. This table has only one row. I would like to add a
> computed field that divides one of the columns in this row by another.
> I created a computed field called ProRatedMultiplier with the following
> definition:
> =Fields!OPERATINGDAYSINMONTH.Value/Fields!INVOICINGDAY.Value
> The problem is that when I insert this computed field into my report,
> it wants to sum the output:
> =Sum(Fields!DailyInvoiceProRateMultiplier.Value, "InputDataDS")
> If I remove the SUM() function, I get compile errors.
> I need to use this computed value in many places on the report and thus
> would like to have the formula defined only once (instead of repeating
> it in each field).
> It seems SRS sees it as returning one or more rows and thus wants to
> aggregate. Am I going about this the wrong way? Can I make any changes
> to make this work?
> 2. Assuming I get the above to work, I will have this new computed
> field on my dataset. I would like to create a computed field on another
> dataset that uses this first computed field value.
> This new, second computed field would multiply the first computed field
> value (which is a pro-rata multiplier) by the sum() aggregate of a
> column in the second dataset.
> The definition would like something like this:
> =SUM( Fields!INVOICEAMOUNT.Value) *
> Fields!DailyInvoiceProRateMultiplier.Value, "InputDataDS"
> This also will not compile.
> Am I going about this the wrong way?
>
I am having trouble with computed fields. I have two issues:
1. I have a special dataset that I use to read parameters from a
database table. This table has only one row. I would like to add a
computed field that divides one of the columns in this row by another.
I created a computed field called ProRatedMultiplier with the following
definition:
=Fields!OPERATINGDAYSINMONTH.Value/Fields!INVOICINGDAY.Value
The problem is that when I insert this computed field into my report,
it wants to sum the output:
=Sum(Fields!DailyInvoiceProRateMultiplier.Value, "InputDataDS")
If I remove the SUM() function, I get compile errors.
I need to use this computed value in many places on the report and thus
would like to have the formula defined only once (instead of repeating
it in each field).
It seems SRS sees it as returning one or more rows and thus wants to
aggregate. Am I going about this the wrong way? Can I make any changes
to make this work?
2. Assuming I get the above to work, I will have this new computed
field on my dataset. I would like to create a computed field on another
dataset that uses this first computed field value.
This new, second computed field would multiply the first computed field
value (which is a pro-rata multiplier) by the sum() aggregate of a
column in the second dataset.
The definition would like something like this:
=SUM( Fields!INVOICEAMOUNT.Value) *
Fields!DailyInvoiceProRateMultiplier.Value, "InputDataDS"
This also will not compile.
Am I going about this the wrong way?For #1:
Use the First aggregate function:
=First(Fields!DailyInvoiceProRateMultiplier.Value, "InputDataDS")
So even if your InputDataDS dataset has more than one rows (for whatever
reasons), you will still get the expected result. Since you use the
calculated field value in another dataset, you cannot omit the aggregate
function (otherwise the fields collection would be scoped to the wrong
dataset).
For #2:
You could write an expression in a textbox like this:
=SUM( Fields!INVOICEAMOUNT.Value) * First(
Fields!DailyInvoiceProRateMultiplier.Value, "InputDataDS")
However, you cannot define a calculated field which uses aggregate
functions.
Probably a better solution for #1 & #2:
Since you have only 1 row - did you look into hidden textboxes, which
calculate the formulas instead of calculated fields? You could then
reference the value of the formula textbox in other textboxes by using an
expression like
=ReportItems!FormulaTextbox.Value
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hunter Hillegas" <hunter.hillegas@.gmail.com> wrote in message
news:chqjea$pq0@.odak26.prod.google.com...
> I am getting started with Reporting Services.
> I am having trouble with computed fields. I have two issues:
> 1. I have a special dataset that I use to read parameters from a
> database table. This table has only one row. I would like to add a
> computed field that divides one of the columns in this row by another.
> I created a computed field called ProRatedMultiplier with the following
> definition:
> =Fields!OPERATINGDAYSINMONTH.Value/Fields!INVOICINGDAY.Value
> The problem is that when I insert this computed field into my report,
> it wants to sum the output:
> =Sum(Fields!DailyInvoiceProRateMultiplier.Value, "InputDataDS")
> If I remove the SUM() function, I get compile errors.
> I need to use this computed value in many places on the report and thus
> would like to have the formula defined only once (instead of repeating
> it in each field).
> It seems SRS sees it as returning one or more rows and thus wants to
> aggregate. Am I going about this the wrong way? Can I make any changes
> to make this work?
> 2. Assuming I get the above to work, I will have this new computed
> field on my dataset. I would like to create a computed field on another
> dataset that uses this first computed field value.
> This new, second computed field would multiply the first computed field
> value (which is a pro-rata multiplier) by the sum() aggregate of a
> column in the second dataset.
> The definition would like something like this:
> =SUM( Fields!INVOICEAMOUNT.Value) *
> Fields!DailyInvoiceProRateMultiplier.Value, "InputDataDS"
> This also will not compile.
> Am I going about this the wrong way?
>
Sunday, March 11, 2012
complicated update (for me)
hello everyone,
just having trouble with an update statement. so i started off with a
table like so:
create table foo (
id uniqueidentifier not null primary key nonclustered,
name varchar(50) not null,
type int not null,
datecreated datetime not null default getdate(),
constraint foo1 unique (name)
)
go
then i added a new column, like so:
alter table foo add column rank int null
go
and here's where the tricky update comes in. my goal is to populate
this new "rank" column with incremental values for each distinct "type"
value.
for example, i the original table looks like so:
select name, type from foo
go
name type
-- --
john 1
jane 1
jim 1
jake 2
jeff 2
kyle 2
keli 2
kim 2
i would want the update statement to populate the table so that AFTER
the update, it looks like so:
select name, type, rank from foo
name type rank
-- -- --
john 1 1
jane 1 2
jim 1 3
jake 2 1
jeff 2 2
kyle 2 3
keli 2 4
kim 2 5
it's important to note that the original "rank" really doesn't matter,
it is just something that needs to be kept track of moving forward, so
i don't even particularly have to rank them alphabetically, or any
other way. just need an update statement that can put in incremental
values, but that sort of "resets" the incrementation for each changing
value of another field.
if that's possible.
thanks in advance for any help!
jasonYou have a few problems with this. First, a uniqueidentifier cannot be
a relational key, so this is not a table by definition. Where does it
occur in the reality of the data model? Next, Rows are not records;
fields are not columns; tables are not files. Totally differerent
concepts. You are building a sequential file in SQL
DEFAULT comes after the data type in STANDARD SQL, and you can use
CURRENT_TIMESTAMP instead of the proprietary getdate(). But you should
not put audit trail information in the table (ask your accountant about
proper procedures).
There is no sequential access or ordering in an RDBMS, so "first",
"next" and "last" are totally meaningless. So what are you using to
assign the rank values? The data model should have some rule so you
can validate the data.
Granted this is a sample table, but all of the column names are
incomplete (type of what? Name of what? Etc.) Your sample design
should look more like this:
CREATE TABLE NewFoo
(foo_name VARCHAR(50) NOT NULL PRIMARY KEY,
foo_type INTEGER NOT NULL,
bar_rank INTEGER NOT NULL,
UNIQUE (foo_type, bar_rank)); -- is this the PK?
Now, let's kill the old one and get things into an RDBMS:
INSERT INTO NewFoo (foo_name, foo_type, bar_rank)
SELECT name, type,
(SELECT COUNT(F1.*)
FROM Foo AS F1
WHERE F1.type = Foo.type
AND F1.name = Foo.name)
FROM Foo;
Then drop Foo and rename NewFoo.|||> First, a uniqueidentifier cannot be a relational key, so this
> is not a table by definition. Where does it occur in the
> reality of the data model?
i call bs on this one, CELKO. a uniqueidentifier is a perfectly valid
artificial key for any record. what makes you think it can't be a
relational key?
> But you should not put audit trail information in the table
i call bs on this one too, i'm afraid. row-level auditing is a
perfectly valid practice for a number of reasons.
as for the rest, yeah, i was lazy, i didn't actually try the create
table statement before posting :) and i'll try to be more descriptive
in future examples.
as for the rank, it's definitely not an attempt to make the table a
sequential file, so you guessed wrong there i'm afraid. i use clustered
indexes if order is important for something, which has specific,
uncommon purposes in my opinion.
the rank is a meaningful value that a subscriber application needs to
know. you can think of it like your standing in a contest, you're
either in first, second, third, etc. place. the rank can't actually be
determined by any values in the database, it is determined by the
providers of the data, and we just store the rank. it's meaningful, not
physically sequential, etc.
and lastly, regarding the insert statement: THANK YOU. exactly what i
was looking for. i look forward to trying it out when i get back to the
office tomorrow.
thankful as always,
jason|||>> call bs on this one, CELKO. a uniqueidentifier is a perfectly valid artificial
key for any record [sic]. what makes you think it can't be a relational key? <<
The very definition of a relationall key, the stuff Dr. Codd wrote and
basic data model concepts. A relational key is subset of the
attributes of an entity that is unique. A uniqueidentifier is derived
from physical storage and has nothing whatsoever to do with the entity.
Newbies who do not know that a row (logical construct) is nothing like
a record (physical storage) constantly make this mistake and build file
systems in SQL.
An artificial key has to have validation and verification rules, and a
uniqueidentifier does not.
That is fine, but putting the audit trail into the table that is being
audited is not a proper accounting practice. The changes need to be
caught outside of the table. Talk to the accounting department or the
SOX guy for your company. This is like letting developers do their own
QA.|||assuming (name, type) is unique
update foo set rank = (select count(*) from foo f1 where f1.type =
foo.type and f1.name<foo.name)+1
if (name, type) is NOT unique, it it still doable but more complex|||> A uniqueidentifier is derived from physical storage
well, I guess whoever says this at a job interview is less likely to
get hired ;)|||> A relational key is subset of the
> attributes of an entity that is unique.
actually, if i'm reading you correctly, that's a called NATURAL key.
relational keys do not have the necessary condition of being natural
elements of an entity. the only necessary condition of a relational key
is that it be a column or columns whose values are gauranteed to be
unique across all occurrences in a given table. that's it.
by this definition, a relational key can be natural OR artificial. what
you're describing is a natural relational key, and good for you, that's
totally fine. and so are artificial relational keys.
> An artificial key has to have validation and verification rules, and a
> uniqueidentifier does not.
the only "validation and verification" rule required to act as a
relational key is that it be UNIQUE. and uniqueidentifiers, when
properly used, are certainly that.
i presume that you would have just as many objections about using an
identity integer as a relational key? if that's true, then you're
grossly misrepresenting your argument. you're not arguing the
definition of a relational key, you're arguing the validity of
artificial versus natural keys AS relational keys. totally different
argument.
> That is fine, but putting the audit trail into the table that is being
> audited is not a proper accounting practice.
i don't see your logic here. what is the difference between attaching
such a column to the entity it is auditing and putting it in another
table, and relating it to the entity it is auditing? the only
difference i can think of is that you could apply different user
permissions to each table. that's fine and well, but there are plenty
of other places to handle security, and other considerations, such as
performance.|||this worked like a charm, thank you very much!
jason|||yeah, i wasn't sure where that was coming from either. aren't they
derived from like a bunch of crazy variables? datetime, cpu serial
number, mac address, your mother's maiden name, the position of the
every valence electron in your body ...|||>> what is the difference between attaching such a column to the entity it i
s auditing and putting it in another table, and relating it to the entity it
is auditing? <<
You do not have to put the audit information in the schema at all. It
can be in an external file system or other RDBMS.
Separation is a basic accounting principle, like double entry
bookkeeping. For example, when I submit an article to a publisher, I
send the editor one copy of the invoice and another copy to Accounts
Payable. The A/P clerk has to match both copies of the invoice before
they issue a check to me.
My editor deletes their copies of my invoice. The A/P clerk now has an
invoice without a mate at the end of the payment cycle, so they know to
start calling people.
The A/P department deletes their copies of my invoice. The editor now
has an invoice which was no paid at the end of the payment cycle, so
they know to call the A/P department.
Both editor and A/P delete their copies of my invoice. Accounting sees
a missing invoice number at the end of the payment cycle, because they
designed an invoice number that can be validated and verified rather
than a meaningless, hardware generated number. Accounting makes life
hell for everyone until they can trace that missing invoice number.
.
just having trouble with an update statement. so i started off with a
table like so:
create table foo (
id uniqueidentifier not null primary key nonclustered,
name varchar(50) not null,
type int not null,
datecreated datetime not null default getdate(),
constraint foo1 unique (name)
)
go
then i added a new column, like so:
alter table foo add column rank int null
go
and here's where the tricky update comes in. my goal is to populate
this new "rank" column with incremental values for each distinct "type"
value.
for example, i the original table looks like so:
select name, type from foo
go
name type
-- --
john 1
jane 1
jim 1
jake 2
jeff 2
kyle 2
keli 2
kim 2
i would want the update statement to populate the table so that AFTER
the update, it looks like so:
select name, type, rank from foo
name type rank
-- -- --
john 1 1
jane 1 2
jim 1 3
jake 2 1
jeff 2 2
kyle 2 3
keli 2 4
kim 2 5
it's important to note that the original "rank" really doesn't matter,
it is just something that needs to be kept track of moving forward, so
i don't even particularly have to rank them alphabetically, or any
other way. just need an update statement that can put in incremental
values, but that sort of "resets" the incrementation for each changing
value of another field.
if that's possible.
thanks in advance for any help!
jasonYou have a few problems with this. First, a uniqueidentifier cannot be
a relational key, so this is not a table by definition. Where does it
occur in the reality of the data model? Next, Rows are not records;
fields are not columns; tables are not files. Totally differerent
concepts. You are building a sequential file in SQL
DEFAULT comes after the data type in STANDARD SQL, and you can use
CURRENT_TIMESTAMP instead of the proprietary getdate(). But you should
not put audit trail information in the table (ask your accountant about
proper procedures).
There is no sequential access or ordering in an RDBMS, so "first",
"next" and "last" are totally meaningless. So what are you using to
assign the rank values? The data model should have some rule so you
can validate the data.
Granted this is a sample table, but all of the column names are
incomplete (type of what? Name of what? Etc.) Your sample design
should look more like this:
CREATE TABLE NewFoo
(foo_name VARCHAR(50) NOT NULL PRIMARY KEY,
foo_type INTEGER NOT NULL,
bar_rank INTEGER NOT NULL,
UNIQUE (foo_type, bar_rank)); -- is this the PK?
Now, let's kill the old one and get things into an RDBMS:
INSERT INTO NewFoo (foo_name, foo_type, bar_rank)
SELECT name, type,
(SELECT COUNT(F1.*)
FROM Foo AS F1
WHERE F1.type = Foo.type
AND F1.name = Foo.name)
FROM Foo;
Then drop Foo and rename NewFoo.|||> First, a uniqueidentifier cannot be a relational key, so this
> is not a table by definition. Where does it occur in the
> reality of the data model?
i call bs on this one, CELKO. a uniqueidentifier is a perfectly valid
artificial key for any record. what makes you think it can't be a
relational key?
> But you should not put audit trail information in the table
i call bs on this one too, i'm afraid. row-level auditing is a
perfectly valid practice for a number of reasons.
as for the rest, yeah, i was lazy, i didn't actually try the create
table statement before posting :) and i'll try to be more descriptive
in future examples.
as for the rank, it's definitely not an attempt to make the table a
sequential file, so you guessed wrong there i'm afraid. i use clustered
indexes if order is important for something, which has specific,
uncommon purposes in my opinion.
the rank is a meaningful value that a subscriber application needs to
know. you can think of it like your standing in a contest, you're
either in first, second, third, etc. place. the rank can't actually be
determined by any values in the database, it is determined by the
providers of the data, and we just store the rank. it's meaningful, not
physically sequential, etc.
and lastly, regarding the insert statement: THANK YOU. exactly what i
was looking for. i look forward to trying it out when i get back to the
office tomorrow.
thankful as always,
jason|||>> call bs on this one, CELKO. a uniqueidentifier is a perfectly valid artificial
key for any record [sic]. what makes you think it can't be a relational key? <<
The very definition of a relationall key, the stuff Dr. Codd wrote and
basic data model concepts. A relational key is subset of the
attributes of an entity that is unique. A uniqueidentifier is derived
from physical storage and has nothing whatsoever to do with the entity.
Newbies who do not know that a row (logical construct) is nothing like
a record (physical storage) constantly make this mistake and build file
systems in SQL.
An artificial key has to have validation and verification rules, and a
uniqueidentifier does not.
That is fine, but putting the audit trail into the table that is being
audited is not a proper accounting practice. The changes need to be
caught outside of the table. Talk to the accounting department or the
SOX guy for your company. This is like letting developers do their own
QA.|||assuming (name, type) is unique
update foo set rank = (select count(*) from foo f1 where f1.type =
foo.type and f1.name<foo.name)+1
if (name, type) is NOT unique, it it still doable but more complex|||> A uniqueidentifier is derived from physical storage
well, I guess whoever says this at a job interview is less likely to
get hired ;)|||> A relational key is subset of the
> attributes of an entity that is unique.
actually, if i'm reading you correctly, that's a called NATURAL key.
relational keys do not have the necessary condition of being natural
elements of an entity. the only necessary condition of a relational key
is that it be a column or columns whose values are gauranteed to be
unique across all occurrences in a given table. that's it.
by this definition, a relational key can be natural OR artificial. what
you're describing is a natural relational key, and good for you, that's
totally fine. and so are artificial relational keys.
> An artificial key has to have validation and verification rules, and a
> uniqueidentifier does not.
the only "validation and verification" rule required to act as a
relational key is that it be UNIQUE. and uniqueidentifiers, when
properly used, are certainly that.
i presume that you would have just as many objections about using an
identity integer as a relational key? if that's true, then you're
grossly misrepresenting your argument. you're not arguing the
definition of a relational key, you're arguing the validity of
artificial versus natural keys AS relational keys. totally different
argument.
> That is fine, but putting the audit trail into the table that is being
> audited is not a proper accounting practice.
i don't see your logic here. what is the difference between attaching
such a column to the entity it is auditing and putting it in another
table, and relating it to the entity it is auditing? the only
difference i can think of is that you could apply different user
permissions to each table. that's fine and well, but there are plenty
of other places to handle security, and other considerations, such as
performance.|||this worked like a charm, thank you very much!
jason|||yeah, i wasn't sure where that was coming from either. aren't they
derived from like a bunch of crazy variables? datetime, cpu serial
number, mac address, your mother's maiden name, the position of the
every valence electron in your body ...|||>> what is the difference between attaching such a column to the entity it i
s auditing and putting it in another table, and relating it to the entity it
is auditing? <<
You do not have to put the audit information in the schema at all. It
can be in an external file system or other RDBMS.
Separation is a basic accounting principle, like double entry
bookkeeping. For example, when I submit an article to a publisher, I
send the editor one copy of the invoice and another copy to Accounts
Payable. The A/P clerk has to match both copies of the invoice before
they issue a check to me.
My editor deletes their copies of my invoice. The A/P clerk now has an
invoice without a mate at the end of the payment cycle, so they know to
start calling people.
The A/P department deletes their copies of my invoice. The editor now
has an invoice which was no paid at the end of the payment cycle, so
they know to call the A/P department.
Both editor and A/P delete their copies of my invoice. Accounting sees
a missing invoice number at the end of the payment cycle, because they
designed an invoice number that can be validated and verified rather
than a meaningless, hardware generated number. Accounting makes life
hell for everyone until they can trace that missing invoice number.
.
Complicated SELECT Problem
I'm having some trouble coming up with the correct select statement.
Lets say I have the following two tables:
Lets say I have the following two tables:
------ ------
Orders OrderItem
------ ------
PK OrderID (int) PK OrderItemID (int)
OrderDate (datetime) FK OrderID (int)
CustomerName (nvarchar) Priority (int)
My search would basically return a list of all the orders placed within
the last week. The select statement would include the entire contents
of the Orders table. The tricky part is that I also need to return the
number of items within each order that have a priority of 1 (so they
can be displayed more prominently).
The first part of the select statement is easy enough, but I'm stuck on
the count part. I'd appreciate any help. Here's what I have so far:
SELECT
Orders.OrderID,
Orders.OrderDate,
Orders.CustomerName
FROM
Orders
WHERE
DATEDIFF(dd, Orders.OrderDate, GetDate()) < 7
--
JasonMaybe I am missing something. I didn't understand how priority of 1
mattered. The field wasn't in your database structure.
SELECT
Orders.OrderID,
Orders.OrderDate,
Orders.CustomerName,
(select count(orderid) from orderitem where
orders.orderid=orderitem.orderid) as Count
FROM Orders
WHERE DATEDIFF(dd, Orders.OrderDate, GetDate()) < 7
Jason wrote:
> I'm having some trouble coming up with the correct select statement.
> Lets say I have the following two tables:
> ------ ------
> Orders OrderItem
> ------ ------
> PK OrderID (int) PK OrderItemID (int)
> OrderDate (datetime) FK OrderID (int)
> CustomerName (nvarchar) Priority (int)
> My search would basically return a list of all the orders placed
within
> the last week. The select statement would include the entire
contents
> of the Orders table. The tricky part is that I also need to return
the
> number of items within each order that have a priority of 1 (so they
> can be displayed more prominently).
> The first part of the select statement is easy enough, but I'm stuck
on
> the count part. I'd appreciate any help. Here's what I have so far:
> SELECT
> Orders.OrderID,
> Orders.OrderDate,
> Orders.CustomerName
> FROM
> Orders
> WHERE
> DATEDIFF(dd, Orders.OrderDate, GetDate()) < 7
> --
> Jason|||Thanks. That should work just fine. I was unaware that you could
include a select statement inside another select like that. You learn
something new every day.
--
Jason|||AS400 Guru (hazen@.candid.com) writes:
> Maybe I am missing something. I didn't understand how priority of 1
> mattered. The field wasn't in your database structure.
It is in OrderItem.
> SELECT
> Orders.OrderID,
> Orders.OrderDate,
> Orders.CustomerName,
> (select count(orderid) from orderitem where
> orders.orderid=orderitem.orderid) as Count
> FROM Orders
> WHERE DATEDIFF(dd, Orders.OrderDate, GetDate()) < 7
This is likely to perform better:
SELECT o.OrderID, o.OrderDate, o.CustomerName, coalesce(oi.cnt, 0)
FROM Orders o
LEFT JOIN (SELECT OrderId, cnt = COUNT(*)
FROM OrderItem
WHERE Priority = 1
GROUP BY OrderId) oi ON o.OrderID = oi.OrderID
WHERE DATEDIFF(dd, Orders.OrderDate, GetDate()) < 7
It's always a good idea to benchmark different solutions. But my experience
is that a derived table gives better performance that sub-selects in the
SELECT list. The latter are usally computed for each row, whereas the
derived table permits the optimizer to do all at once.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Thursday, March 8, 2012
Complex variable length string combination problem
Hi,
I'm having trouble finding a solution for the following problem:
Suppose you have the following string as input 'sig, ltw, onss'
I would like to generate the following result set:
sig
ltw
onss
sig, ltw
sig, onss
ltw, onns
sig, ltw, onss
This should also work for any number of names (each time seperated by a
comma) within the string ex.:
input = 'sig, ltw, onss, plk'
output =
sig
ltw
onss
plk
sig, ltw
sig, onss
sig, plk
ltw, onns
ltw, plk
onss, plk
sig, ltw, onss
sig, onss, plk
ltw, onss, plk
sig, ltw, onss, plk
Anyone an idea how to resolve this problem?
Thanx in advance...Why don't you put this data in a table rather than a delimited string? TSQL
really isn't a language for string manipulation and delimited data just
shouldn't exist in the database.
The permutations you want to output could be achieved with a self- CROSS
JOIN once you've put the data in a table.
David Portas
SQL Server MVP
--|||You can also do it with the ROLLUP operator:
CREATE TABLE foo (x VARCHAR(5) PRIMARY KEY)
INSERT INTO foo VALUES ('sig')
INSERT INTO foo VALUES ('ltw')
INSERT INTO foo VALUES ('onss')
SELECT A.x, B.x, C.x
FROM foo AS A
JOIN foo AS B
ON B.x <> A.x
JOIN foo AS C
ON C.x <> A.x
AND C.x <> B.x
GROUP BY A.x, B.x, C.x WITH ROLLUP
David Portas
SQL Server MVP
--|||Hi,
I agree that string manipulation is not so easy in T-SQL.
Suppose I put the words as rows in a table; the problem with the crossjoin
is that it returns too many possiblities that are not usefull to me:
given a, b, c
the crossjoin would generate:
a, a, a
a, b, a
a, c, a
b, a, a => already exists but in another order
etc...
As you see whereas in my case I generate 14 possibilities for a 4 names
comma seperated string in your case this will be 27 possibilities; in other
words almost twice as many.
A solution would be to elimate from the crossjoin result the same occurences
of the names but in another order. But how?
"David Portas" wrote:
> Why don't you put this data in a table rather than a delimited string? TSQ
L
> really isn't a language for string manipulation and delimited data just
> shouldn't exist in the database.
> The permutations you want to output could be achieved with a self- CROSS
> JOIN once you've put the data in a table.
> --
> David Portas
> SQL Server MVP
> --|||This is pretty close; if we could just eliminate the "doubles" (in this case
the same names but in a different order) than I would be a happy man... ;-)
"David Portas" wrote:
> You can also do it with the ROLLUP operator:
> CREATE TABLE foo (x VARCHAR(5) PRIMARY KEY)
> INSERT INTO foo VALUES ('sig')
> INSERT INTO foo VALUES ('ltw')
> INSERT INTO foo VALUES ('onss')
> SELECT A.x, B.x, C.x
> FROM foo AS A
> JOIN foo AS B
> ON B.x <> A.x
> JOIN foo AS C
> ON C.x <> A.x
> AND C.x <> B.x
> GROUP BY A.x, B.x, C.x WITH ROLLUP
> --
> David Portas
> SQL Server MVP
> --
>|||Try this one then:
CREATE TABLE foo (x VARCHAR(5) PRIMARY KEY)
INSERT INTO foo VALUES ('sig')
INSERT INTO foo VALUES ('ltw')
INSERT INTO foo VALUES ('onss')
INSERT INTO foo VALUES ('')
SELECT A.x, NULLIF(B.x,''), NULLIF(C.x,'')
FROM foo AS A
LEFT JOIN foo AS B
ON B.x < A.x
LEFT JOIN foo AS C
ON C.x < B.x
WHERE A.x > ''
David Portas
SQL Server MVP
--|||David,
Great!!!! this is exactly what I need.
Is there an easy way to make this code dynamic as to receive any number of
input rows?
This already is great to work on...
thanx man! :-)
"David Portas" wrote:
> Try this one then:
> CREATE TABLE foo (x VARCHAR(5) PRIMARY KEY)
> INSERT INTO foo VALUES ('sig')
> INSERT INTO foo VALUES ('ltw')
> INSERT INTO foo VALUES ('onss')
> INSERT INTO foo VALUES ('')
> SELECT A.x, NULLIF(B.x,''), NULLIF(C.x,'')
> FROM foo AS A
> LEFT JOIN foo AS B
> ON B.x < A.x
> LEFT JOIN foo AS C
> ON C.x < B.x
> WHERE A.x > ''
> --
> David Portas
> SQL Server MVP
> --
>|||The following is really slow, (cause it's resursive), but it works...
First step would be to get the Indiv Strings into a table.. You can use the
following function for that... It's a generally useful function to have
around anyway...
Create Function dbo.ParseString (
@.S VarChar(8000),
@.delim Char(1))
Returns @.tOut Table (ValNum Integer Primary Key Identity, sVal VarChar(500)
)
As
Begin
Declare @.sVal VarChar(80)
Declare @.deLimPos Integer
If right(@.S,1) <> @.Delim Set @.S = @.S + @.Delim
While Len(@.S) > 0
Begin
Set @.deLimPos = CharIndex(@.delim, @.S)
Set @.sVal = Left(@.S,@.deLimPos -1)
Set @.S = Right(@.S,Len(@.S) - @.deLimPos)
Insert @.tOut (sVal) Values (@.sVal)
End
Return
End
-- ----
This function parses a string, using the supplied delimiter, and returns a
table with one row for each entry in the string.
Next, you need a function to "alphabeticize" a delimited string... This one
does the trick...
Create FUNCTION dbo.SortString
(@.S VarChar(1000), @.Delim Char(1))
RETURNS VarChar(1000)
AS
BEGIN
Declare @.Out VarChar(1000) Set @.Out = ''
Declare @.Vals Table (Val VarChar(100))
Declare @.Val VarChar(100)
Insert @.Vals(Val)
Select LTrim(sVal)
From dbo.ParseString(@.S, @.Delim)
While Exists(Select * From @.Vals) Begin
Select @.Val = Min(Val) From @.Vals
Set @.Out = @.Out + @.Val + ','
Delete @.Vals Where Val = @.Val
End
Return Substring(@.Out, 1, Len(@.Out) -1)
END
-- ----
Using these 2 functions, you can dynamically do what you want wit hteh
following:
assuming the variable @.Input contains the original concatenated string...
Declare @.Input VarChar(100) Set @.Input = 'a, b, c'
Declare @.Items Table (ItmNo Integer Primary Key Identity, item varchar(50))
Declare @.Permutations Table(Permutation VarChar(1000))
Insert @.Items (Item)
Select sVal
From dbo.ParseString(@.Input , ',')
Order By sVal
-- ---
--Select * From @.Items
Declare @.NumItems TinyInt
Declare @.Size TinyInt Set @.Size = 0
Select @.NumItems = Count(*) From @.Items
While @.Size <= @.NumItems Begin
Insert @.Permutations (Permutation)
Select Distinct dbo.SortString(IsNull(P.Permutation + ', ', '') +
I.Item, ',')
From @.Items I Left Join @.Permutations P
On I.Item <> IsNull(P.Permutation,'')
Where Not Exists(Select * From @.Permutations
Where Permutation =
dbo.SortString(IsNull(P.Permutation + ', ', '') +
I.Item, ','))
And CharIndex(I.Item, IsNull(P.Permutation,'')) = 0
Set @.Size = @.Size + 1
End
Insert @.Permutations(Permutation) Values('')
Select * From @.Permutations|||Thanks man for your effort.
Will stick to the answer of David Portas found it very elegant.
"CBretana" wrote:
> The following is really slow, (cause it's resursive), but it works...
> First step would be to get the Indiv Strings into a table.. You can use th
e
> following function for that... It's a generally useful function to have
> around anyway...
> Create Function dbo.ParseString (
> @.S VarChar(8000),
> @.delim Char(1))
> Returns @.tOut Table (ValNum Integer Primary Key Identity, sVal VarChar(50
0))
> As
> Begin
> Declare @.sVal VarChar(80)
> Declare @.deLimPos Integer
> If right(@.S,1) <> @.Delim Set @.S = @.S + @.Delim
> While Len(@.S) > 0
> Begin
> Set @.deLimPos = CharIndex(@.delim, @.S)
> Set @.sVal = Left(@.S,@.deLimPos -1)
> Set @.S = Right(@.S,Len(@.S) - @.deLimPos)
> Insert @.tOut (sVal) Values (@.sVal)
> End
> Return
> End
> -- ----
> This function parses a string, using the supplied delimiter, and returns a
> table with one row for each entry in the string.
> Next, you need a function to "alphabeticize" a delimited string... This on
e
> does the trick...
> Create FUNCTION dbo.SortString
> (@.S VarChar(1000), @.Delim Char(1))
> RETURNS VarChar(1000)
> AS
> BEGIN
> Declare @.Out VarChar(1000) Set @.Out = ''
> Declare @.Vals Table (Val VarChar(100))
> Declare @.Val VarChar(100)
> Insert @.Vals(Val)
> Select LTrim(sVal)
> From dbo.ParseString(@.S, @.Delim)
> While Exists(Select * From @.Vals) Begin
> Select @.Val = Min(Val) From @.Vals
> Set @.Out = @.Out + @.Val + ','
> Delete @.Vals Where Val = @.Val
> End
> Return Substring(@.Out, 1, Len(@.Out) -1)
> END
> -- ----
> Using these 2 functions, you can dynamically do what you want wit hteh
> following:
> assuming the variable @.Input contains the original concatenated string...
> Declare @.Input VarChar(100) Set @.Input = 'a, b, c'
> Declare @.Items Table (ItmNo Integer Primary Key Identity, item varchar(50)
)
> Declare @.Permutations Table(Permutation VarChar(1000))
> Insert @.Items (Item)
> Select sVal
> From dbo.ParseString(@.Input , ',')
> Order By sVal
> -- ---
> --Select * From @.Items
> Declare @.NumItems TinyInt
> Declare @.Size TinyInt Set @.Size = 0
> Select @.NumItems = Count(*) From @.Items
>
> While @.Size <= @.NumItems Begin
> Insert @.Permutations (Permutation)
> Select Distinct dbo.SortString(IsNull(P.Permutation + ', ', '') +
> I.Item, ',')
> From @.Items I Left Join @.Permutations P
> On I.Item <> IsNull(P.Permutation,'')
> Where Not Exists(Select * From @.Permutations
> Where Permutation =
> dbo.SortString(IsNull(P.Permutation + ', ', '')
+
> I.Item, ','))
> And CharIndex(I.Item, IsNull(P.Permutation,'')) = 0
> Set @.Size = @.Size + 1
> End
> Insert @.Permutations(Permutation) Values('')
> Select * From @.Permutations
>|||No problemo Peter,
David's solution is much more elegant, and much faster, but as you noticed,
it not dynamic as to the number of items in the imput string...
But as I'm sure you have already figured out, the required processing to
solve this problem increases very very fast as the number of items increases
.
Regards,
Charly
"PeterM" wrote:
> Thanks man for your effort.
> Will stick to the answer of David Portas found it very elegant.
> "CBretana" wrote:
>
I'm having trouble finding a solution for the following problem:
Suppose you have the following string as input 'sig, ltw, onss'
I would like to generate the following result set:
sig
ltw
onss
sig, ltw
sig, onss
ltw, onns
sig, ltw, onss
This should also work for any number of names (each time seperated by a
comma) within the string ex.:
input = 'sig, ltw, onss, plk'
output =
sig
ltw
onss
plk
sig, ltw
sig, onss
sig, plk
ltw, onns
ltw, plk
onss, plk
sig, ltw, onss
sig, onss, plk
ltw, onss, plk
sig, ltw, onss, plk
Anyone an idea how to resolve this problem?
Thanx in advance...Why don't you put this data in a table rather than a delimited string? TSQL
really isn't a language for string manipulation and delimited data just
shouldn't exist in the database.
The permutations you want to output could be achieved with a self- CROSS
JOIN once you've put the data in a table.
David Portas
SQL Server MVP
--|||You can also do it with the ROLLUP operator:
CREATE TABLE foo (x VARCHAR(5) PRIMARY KEY)
INSERT INTO foo VALUES ('sig')
INSERT INTO foo VALUES ('ltw')
INSERT INTO foo VALUES ('onss')
SELECT A.x, B.x, C.x
FROM foo AS A
JOIN foo AS B
ON B.x <> A.x
JOIN foo AS C
ON C.x <> A.x
AND C.x <> B.x
GROUP BY A.x, B.x, C.x WITH ROLLUP
David Portas
SQL Server MVP
--|||Hi,
I agree that string manipulation is not so easy in T-SQL.
Suppose I put the words as rows in a table; the problem with the crossjoin
is that it returns too many possiblities that are not usefull to me:
given a, b, c
the crossjoin would generate:
a, a, a
a, b, a
a, c, a
b, a, a => already exists but in another order
etc...
As you see whereas in my case I generate 14 possibilities for a 4 names
comma seperated string in your case this will be 27 possibilities; in other
words almost twice as many.
A solution would be to elimate from the crossjoin result the same occurences
of the names but in another order. But how?
"David Portas" wrote:
> Why don't you put this data in a table rather than a delimited string? TSQ
L
> really isn't a language for string manipulation and delimited data just
> shouldn't exist in the database.
> The permutations you want to output could be achieved with a self- CROSS
> JOIN once you've put the data in a table.
> --
> David Portas
> SQL Server MVP
> --|||This is pretty close; if we could just eliminate the "doubles" (in this case
the same names but in a different order) than I would be a happy man... ;-)
"David Portas" wrote:
> You can also do it with the ROLLUP operator:
> CREATE TABLE foo (x VARCHAR(5) PRIMARY KEY)
> INSERT INTO foo VALUES ('sig')
> INSERT INTO foo VALUES ('ltw')
> INSERT INTO foo VALUES ('onss')
> SELECT A.x, B.x, C.x
> FROM foo AS A
> JOIN foo AS B
> ON B.x <> A.x
> JOIN foo AS C
> ON C.x <> A.x
> AND C.x <> B.x
> GROUP BY A.x, B.x, C.x WITH ROLLUP
> --
> David Portas
> SQL Server MVP
> --
>|||Try this one then:
CREATE TABLE foo (x VARCHAR(5) PRIMARY KEY)
INSERT INTO foo VALUES ('sig')
INSERT INTO foo VALUES ('ltw')
INSERT INTO foo VALUES ('onss')
INSERT INTO foo VALUES ('')
SELECT A.x, NULLIF(B.x,''), NULLIF(C.x,'')
FROM foo AS A
LEFT JOIN foo AS B
ON B.x < A.x
LEFT JOIN foo AS C
ON C.x < B.x
WHERE A.x > ''
David Portas
SQL Server MVP
--|||David,
Great!!!! this is exactly what I need.
Is there an easy way to make this code dynamic as to receive any number of
input rows?
This already is great to work on...
thanx man! :-)
"David Portas" wrote:
> Try this one then:
> CREATE TABLE foo (x VARCHAR(5) PRIMARY KEY)
> INSERT INTO foo VALUES ('sig')
> INSERT INTO foo VALUES ('ltw')
> INSERT INTO foo VALUES ('onss')
> INSERT INTO foo VALUES ('')
> SELECT A.x, NULLIF(B.x,''), NULLIF(C.x,'')
> FROM foo AS A
> LEFT JOIN foo AS B
> ON B.x < A.x
> LEFT JOIN foo AS C
> ON C.x < B.x
> WHERE A.x > ''
> --
> David Portas
> SQL Server MVP
> --
>|||The following is really slow, (cause it's resursive), but it works...
First step would be to get the Indiv Strings into a table.. You can use the
following function for that... It's a generally useful function to have
around anyway...
Create Function dbo.ParseString (
@.S VarChar(8000),
@.delim Char(1))
Returns @.tOut Table (ValNum Integer Primary Key Identity, sVal VarChar(500)
)
As
Begin
Declare @.sVal VarChar(80)
Declare @.deLimPos Integer
If right(@.S,1) <> @.Delim Set @.S = @.S + @.Delim
While Len(@.S) > 0
Begin
Set @.deLimPos = CharIndex(@.delim, @.S)
Set @.sVal = Left(@.S,@.deLimPos -1)
Set @.S = Right(@.S,Len(@.S) - @.deLimPos)
Insert @.tOut (sVal) Values (@.sVal)
End
Return
End
-- ----
This function parses a string, using the supplied delimiter, and returns a
table with one row for each entry in the string.
Next, you need a function to "alphabeticize" a delimited string... This one
does the trick...
Create FUNCTION dbo.SortString
(@.S VarChar(1000), @.Delim Char(1))
RETURNS VarChar(1000)
AS
BEGIN
Declare @.Out VarChar(1000) Set @.Out = ''
Declare @.Vals Table (Val VarChar(100))
Declare @.Val VarChar(100)
Insert @.Vals(Val)
Select LTrim(sVal)
From dbo.ParseString(@.S, @.Delim)
While Exists(Select * From @.Vals) Begin
Select @.Val = Min(Val) From @.Vals
Set @.Out = @.Out + @.Val + ','
Delete @.Vals Where Val = @.Val
End
Return Substring(@.Out, 1, Len(@.Out) -1)
END
-- ----
Using these 2 functions, you can dynamically do what you want wit hteh
following:
assuming the variable @.Input contains the original concatenated string...
Declare @.Input VarChar(100) Set @.Input = 'a, b, c'
Declare @.Items Table (ItmNo Integer Primary Key Identity, item varchar(50))
Declare @.Permutations Table(Permutation VarChar(1000))
Insert @.Items (Item)
Select sVal
From dbo.ParseString(@.Input , ',')
Order By sVal
-- ---
--Select * From @.Items
Declare @.NumItems TinyInt
Declare @.Size TinyInt Set @.Size = 0
Select @.NumItems = Count(*) From @.Items
While @.Size <= @.NumItems Begin
Insert @.Permutations (Permutation)
Select Distinct dbo.SortString(IsNull(P.Permutation + ', ', '') +
I.Item, ',')
From @.Items I Left Join @.Permutations P
On I.Item <> IsNull(P.Permutation,'')
Where Not Exists(Select * From @.Permutations
Where Permutation =
dbo.SortString(IsNull(P.Permutation + ', ', '') +
I.Item, ','))
And CharIndex(I.Item, IsNull(P.Permutation,'')) = 0
Set @.Size = @.Size + 1
End
Insert @.Permutations(Permutation) Values('')
Select * From @.Permutations|||Thanks man for your effort.
Will stick to the answer of David Portas found it very elegant.
"CBretana" wrote:
> The following is really slow, (cause it's resursive), but it works...
> First step would be to get the Indiv Strings into a table.. You can use th
e
> following function for that... It's a generally useful function to have
> around anyway...
> Create Function dbo.ParseString (
> @.S VarChar(8000),
> @.delim Char(1))
> Returns @.tOut Table (ValNum Integer Primary Key Identity, sVal VarChar(50
0))
> As
> Begin
> Declare @.sVal VarChar(80)
> Declare @.deLimPos Integer
> If right(@.S,1) <> @.Delim Set @.S = @.S + @.Delim
> While Len(@.S) > 0
> Begin
> Set @.deLimPos = CharIndex(@.delim, @.S)
> Set @.sVal = Left(@.S,@.deLimPos -1)
> Set @.S = Right(@.S,Len(@.S) - @.deLimPos)
> Insert @.tOut (sVal) Values (@.sVal)
> End
> Return
> End
> -- ----
> This function parses a string, using the supplied delimiter, and returns a
> table with one row for each entry in the string.
> Next, you need a function to "alphabeticize" a delimited string... This on
e
> does the trick...
> Create FUNCTION dbo.SortString
> (@.S VarChar(1000), @.Delim Char(1))
> RETURNS VarChar(1000)
> AS
> BEGIN
> Declare @.Out VarChar(1000) Set @.Out = ''
> Declare @.Vals Table (Val VarChar(100))
> Declare @.Val VarChar(100)
> Insert @.Vals(Val)
> Select LTrim(sVal)
> From dbo.ParseString(@.S, @.Delim)
> While Exists(Select * From @.Vals) Begin
> Select @.Val = Min(Val) From @.Vals
> Set @.Out = @.Out + @.Val + ','
> Delete @.Vals Where Val = @.Val
> End
> Return Substring(@.Out, 1, Len(@.Out) -1)
> END
> -- ----
> Using these 2 functions, you can dynamically do what you want wit hteh
> following:
> assuming the variable @.Input contains the original concatenated string...
> Declare @.Input VarChar(100) Set @.Input = 'a, b, c'
> Declare @.Items Table (ItmNo Integer Primary Key Identity, item varchar(50)
)
> Declare @.Permutations Table(Permutation VarChar(1000))
> Insert @.Items (Item)
> Select sVal
> From dbo.ParseString(@.Input , ',')
> Order By sVal
> -- ---
> --Select * From @.Items
> Declare @.NumItems TinyInt
> Declare @.Size TinyInt Set @.Size = 0
> Select @.NumItems = Count(*) From @.Items
>
> While @.Size <= @.NumItems Begin
> Insert @.Permutations (Permutation)
> Select Distinct dbo.SortString(IsNull(P.Permutation + ', ', '') +
> I.Item, ',')
> From @.Items I Left Join @.Permutations P
> On I.Item <> IsNull(P.Permutation,'')
> Where Not Exists(Select * From @.Permutations
> Where Permutation =
> dbo.SortString(IsNull(P.Permutation + ', ', '')
+
> I.Item, ','))
> And CharIndex(I.Item, IsNull(P.Permutation,'')) = 0
> Set @.Size = @.Size + 1
> End
> Insert @.Permutations(Permutation) Values('')
> Select * From @.Permutations
>|||No problemo Peter,
David's solution is much more elegant, and much faster, but as you noticed,
it not dynamic as to the number of items in the imput string...
But as I'm sure you have already figured out, the required processing to
solve this problem increases very very fast as the number of items increases
.
Regards,
Charly
"PeterM" wrote:
> Thanks man for your effort.
> Will stick to the answer of David Portas found it very elegant.
> "CBretana" wrote:
>
Saturday, February 25, 2012
Complex Query
Hello,
I am having trouble in getting the expected results. I have two tables as below. I need Idno,transdt,cd,cycdt,amt from joining two tables. The criteria is that if the transdt greater than same month of cycdt then we need get the next month cycdt and corresponding amount for that, if it is less than or equal to same months cycdt then get the same months cycdt and amt. Cd is dummy field which can be anything. I am using sqlserver 8.0
table1
idno,trandt,cd
12345,04/15/2005,cd1
12345,04/15/2005,cd2
12345,04/22/2005,cd3
12345,07/03/2005,cd4
12345,09/10/2005,cd5
3421,03/05/2005,cd6
3421,05/06/2005,cd7
3421,07/04/2005,cd8
3421,07/15/2005,cd9
3421,09/15/2005,cd10
idno,cycdt,amt
12345,02/10/2005,15.43
12345,03/13/2005,40.84
12345,04/18/2005,10.10
12345,05/24/2005,13.00
12345,06/16/2005,20.89
12345,07/18/2005,12.12
12345,08/17/2005,10.89
12345,09/17/2005,12.87
12345,10/16/2005,13.89
3421,05/10/2005,15.00
3421,06/11/2005,20.00
3421,07/11/2005,14.15
3421,08/12/2005,15.54
Expected result.
12345,04/15/2005,cd1,04/18/2005,10.10
12345,04/15/2005,cd2,04/18/2005,10.10
12345,04/22/2005,cd3,05/24/2005,13.00
12345,07/03/2005,cd4,07/18/2005,12.12
12345,09/10/2005,cd5,09/17/2005,12.87
3421,05/06/2005,cd7,05/10/2005,15.00
3421,07/04/2005,cd8,07/11/2005,14.15
3421,07/15/2005,cd9,08/12/2005,15.54
I really appreciate if someone can give solution for this using a query (SQL server,Access,Foxpro) is fine.
I am able to do this oracle using count(trandt) over(partition trandt order by 1) as cnt.
and cnt=1.
If u want I can post that too for getting any idea.
I need more information. I would assume that idno is the primary key on both tables, except that it is not unique for each row. If the key is the idno and the date column on each table, then you will not be able to perform this query as you won't be able to join the two tables together. I will attempt the query, but I am not sure it is what you want without knowing more information. Please reply to let me know whether this answere your question, or whether you need more help. As you either want the current month's cycdt or the next months, I suggest join to two copies of the second table, one joining on the same month, one joining on the subsequent month:
select
case t1.transdt > t2a.cycdt
then t2b.cycdt -- get next month's cycdt
else t2a.cycdt -- get this month's cycdt
end as cycdt,
case t1.transdt > t2a.cycdt
then t2b.amt -- get next month's amt
else t2a.amt -- get next month's amt
end as amt
from table1 as t1
join table2 as t2a -- current month join
on t2a.idno = t1.idno and month(t2a.cycdt) = month(t1.transdt)
join table2 as t2b -- joins to next month.
on t2b.idno = t1.idno and month(t2b.cycdt) = month(t1.transdt) + 1
-- The second join may need to be an outer join, as will restrict result set to
-- include only dates upto the month before the latest month. I don't know if
-- this is going to be a problem for you
For more T-SQL tips and advice, visit my blog:
|||Whoops, forgot the first three columns.
select
t1.Idno,
t1.transdt,
t1.cd,
case
when t1.transdt > t2a.cycdt then t2b.cycdt -- get next month's cycdt
else t2a.cycdt -- get this month's cycdt
end as cycdt,
case when t1.transdt > t2a.cycdt then t2b.amt -- get next month's amt
else t2a.amt -- get next month's amt
end as amt
from table1 as t1
join table2 as t2a -- current month join
on t2a.idno = t1.idno and month(t2a.cycdt) = month(t1.transdt)
join table2 as t2b -- joins to next month.
on t2b.idno = t1.idno and month(t2b.cycdt) = month(t1.transdt) + 1
|||First, I really appreciate you in taking time to write the query.
Basically, its not a formal table but just set of two result set and need to produce report out of it as one time. so, I am not making it as any primary key or something. But Idno is the main link between the tables.
Also, this is test tables as my original tables have 3million in table1 and 500k in table2.
The solution you had given works fine as long as there are no missing cycdt in between. But if one cycdt misses the query is not pulling the proper records.
(E.g) try removing the record 12345,05/24/05 from table2.
Let me know if you need more info.
I have this in Oracle but not able to use "Over - Partition" thing since I am using SQL server 8.0
Here it is in Oracle.
SELECT IDNO,TRANDT,IDNO1,CYCDT,AMT FROM
(SELECT IDNO,TRANDT,IDNO1,CYCDT,AMT,COUNT(TRANDT) OVER(PARTITION BY TRANDT ORDER BY 1) CNT
FROM
( SELECT T1.IDNO IDNO,T1.TRANDT,T2.IDNO IDNO1,T2.CYCDT,T2.AMT
FROM tab1 T1,tab2 T2 WHERE T1.IDNO=T2.IDNO )
WHERE (TO_CHAR(TRANDT,'MM') = TO_CHAR(CYCDT,'MM') AND
TO_CHAR(TRANDT,'DD') < TO_CHAR(CYCDT,'DD')) OR
(TO_NUMBER(TO_CHAR(TRANDT,'MM'))+1 = TO_CHAR(CYCDT,'MM'))
ORDER BY 1,2,4
)
WHERE TO_CHAR(TRANDT,'MM') = TO_CHAR(CYCDT,'MM') OR CNT=1ORDER BY 1 DESC,2,4
Friday, February 17, 2012
compile [really link] question
Hi,
I'm having trouble linking a simple example using the bcp functions...
The error:
************************************************
test.obj : error LNK2001: unresolved external symbol _bcp_init@.20
************************************************
Environment: Windows 2000, SQL Server 2000, VS C++ 6.0 Enterprise
I know my environment and connection handles are working...but my bcp_init()
call is driving me crazy...
Headers I'm including:
////////////////////////////////
//C HEADERS
#include <stdio.h>
#include <stdlib.h>
//WINDOWS
#include <windows.h>
//ODBC HEADERS
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
//BCP HEADER
#include <Odbcss.h>
////////////////////////////////
Library I'm including
Odbcbcp.lib
I use both
a) /libpath:"C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Lib"
and
b) LIB environmental variable points there too
I've checked the folder and the file is def. there!
Thinking I might not be using the ODBC 3.0 headers and lib...
I've check my LIB path and "Odbcbcp.lib" only exists once!
If I comment out the bcp_init() call, my program compiles and links fine
The call to bcp_exec() is FINE...
Anyone have a clue as to what I'm doing wrong?
Included below is my c file (test.c) pasted just in case I'm doing something
else wrong.
// START test.c
#include <stdio.h>
#include <stdlib.h>
#include <windows.h>
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
#include <Odbcss.h>
#define MAXBUFLEN 255
#define SQLSERVER1
#defineDSN2
int createHandles();
void destroyHandles();
SQLHENV henv = SQL_NULL_HENV;//Environment Handle
SQLHDBC hcn = SQL_NULL_HDBC;//Connection Handle
main()
{
intret;//internal return codes
RETCODEretcode;//ODBC return codes
UCHARszDSN[SQL_MAX_DSN_LENGTH+1] = "cnABL",//DSN info
szDSN_UID[MAXNAME] = "sa",
szDSN_PSWD[MAXNAME] = "";
SQLCHARconnectionStringIn[MAXBUFLEN] = "";//SQL SERVER driver
connection string
SQLCHAR connectionStringOut[MAXBUFLEN];//SQL SERVER driver
connection string out
SQLSMALLINT lengthOfConnectionStringOut = 0;
// Bulk copy variables.
SDWORD cRows;
intmyType = DSN;
//Create Handles
if ( ( ret = createHandles() ) )
{
destroyHandles();
returnEXIT_FAILURE;
}
switch (myType)
{
case SQLSERVER:
//Build up the ConnStrIn...
sprintf(connectionStringIn, "DRIVER={SQL
Server};SERVER=%s;UID=%s;PWD=%s;DATABASE=%s;", "", "sa", "", "abldb");
printf("\nCONNECTION STRING->%s<-", connectionStringIn);
//connect
retcode = SQLDriverConnect(hcn,// Connection handle
NULL,// Window handle
connectionStringIn,// Input connect string
SQL_NTS,// Null-terminated string
connectionStringOut,// Address of output buffer
MAXBUFLEN,// Size of output buffer
&lengthOfConnectionStringOut,// Address of output length
SQL_DRIVER_NOPROMPT);
//check it
if (retcode == SQL_ERROR)
//if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
// Connect failed, call SQLGetDiagRec for errors.
printf("\nSQL SERVER CONNECTION FAILED!");
destroyHandles();
return EXIT_FAILURE;
}
else
{
// Connects to SQL Server always return
// informational messages. These messages can be
// retrieved by calling SQLGetDiagRec.
printf("\nSQL SERVER CONNECTION SUCCESS!");
}
break;
case DSN:
//connect
retcode = SQLConnect(hcn, szDSN, (SWORD)strlen(szDSN), szDSN_UID,
(SWORD)strlen(szDSN_UID), szDSN_PSWD, (SWORD)strlen(szDSN_PSWD));
//check it
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
// Connect failed, call SQLGetDiagRec for errors.
printf("\nDSN CONNECTION FAILED!");
destroyHandles();
return EXIT_FAILURE;
}
else
{
// Connects to SQL Server always return
// informational messages. These messages can be
// retrieved by calling SQLGetDiagRec.
printf("\nDSN CONNECTION SUCCESS!");
}
break;
}
// Initialize the bulk copy.
//retcode = bcp_init(hcn, "abldb..trendtable", "c:\\ben\\BCPODBC.bcp",
"c:\\ben\\BCPERROR.out", DB_OUT);
// Note that the test is for the bulk copy return of SUCCEED,
// not the ODBC return of SQL_SUCCESS.
if ( (retcode != SUCCEED) )
{
printf("bcp_init(hcn) Failed\n\n");
destroyHandles();
return EXIT_FAILURE;
}
// Execute the bulk copy.
retcode = bcp_exec(hcn, &cRows);
if ( (retcode != SUCCEED) )
{
printf("bcp_exec(hcn) Failed\n\n");
destroyHandles();
return EXIT_FAILURE;
}
printf("Number of rows bulk copied out = %d.\n", cRows);
//clean up
destroyHandles();
return EXIT_SUCCESS;
}
int createHandles()
{
RETCODE retcode;
// Allocate the ODBC Environment and save handle.
retcode = SQLAllocHandle (SQL_HANDLE_ENV,
SQL_NULL_HANDLE,
&henv);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
// Notify ODBC that this is an ODBC 3.0 application.
retcode = SQLSetEnvAttr(henv,
SQL_ATTR_ODBC_VERSION,
(SQLPOINTER)SQL_OV_ODBC3,
SQL_IS_INTEGER);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
// Allocate an ODBC connection handle
retcode = SQLAllocHandle(SQL_HANDLE_DBC,
henv,
&hcn);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
return 1;
}
void destroyHandles()
{
SQLDisconnect(hcn);
SQLFreeHandle(SQL_HANDLE_DBC, hcn);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
// END test.c
thanks in advance,
georgejetson
Nevermind...
Although I had my environmental variables pointing to the more recent dirs
first, it was not working...I'm still learning ms vc dev environment...
FYI: To compile ODBC using BCP in VC6 Enterprise...
My Solution:
a) Downloaded latest and greatest mdac sdk
b) Added mdac lib & include dirs to LIB & INCLUDE environmental variable
list at front of list
c) For all configurations (Release/Active...) in VC6
1) Made sure all required libs existed
Odbc32.lib Odbccp32.lib Odbcbcp.lib
(probably don't need Odbccp32.lib)
2) Set Additional Library Path to FIRST use new mdac/lib folder, then
sqlserver/lib folder
ie:C:\mdacSdk28\Libs\x86,C:\Program Files\Microsoft SQL
Server\80\Tools\DevTools\Lib
d)Did the same thing for additional include directories
ie:C:\mdacSdk28\Inc,C:\Program Files\Microsoft SQL
Server\80\Tools\DevTools\Include
In my source code, I included the following...
//ALL ODBC CALLS NEED
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
//ODBC UNICODE
#include <Sqlucode.h>
//ODBC BCP
#include <Odbcss.h>
e) Finally, I took OUT the /nologo option so I could see where the compiler
and linker were really looking
the result below
Compiling...
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 12.00.8168 for 80x86
Copyright (C) Microsoft Corp 1984-1998. All rights reserved.
cl /MLd /W3 /Gm /GX /ZI /Od /I "C:\mdacSdk28\Inc" /I "C:\Program
Files\Microsoft SQL Server\80\Tools\DevTools\Include" /D "WIN32" /D "_DEBUG"
/D "_CONSOLE" /D "_MBCS" /FR"Debug/" /Fp"Debug/prjOdbcTest.pch" /YX
/Fo"Debug/" /Fd"Debug/" /FD /GZ /c
"C:\sqlutil\odbcTest\test.c"
test.c
Note: Using precompiled header
Linking...
Creating browse info file...
prjOdbcTest.exe - 0 error(s), 0 warning(s)
thanks in advance,
georgejetson
I'm having trouble linking a simple example using the bcp functions...
The error:
************************************************
test.obj : error LNK2001: unresolved external symbol _bcp_init@.20
************************************************
Environment: Windows 2000, SQL Server 2000, VS C++ 6.0 Enterprise
I know my environment and connection handles are working...but my bcp_init()
call is driving me crazy...
Headers I'm including:
////////////////////////////////
//C HEADERS
#include <stdio.h>
#include <stdlib.h>
//WINDOWS
#include <windows.h>
//ODBC HEADERS
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
//BCP HEADER
#include <Odbcss.h>
////////////////////////////////
Library I'm including
Odbcbcp.lib
I use both
a) /libpath:"C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Lib"
and
b) LIB environmental variable points there too
I've checked the folder and the file is def. there!
Thinking I might not be using the ODBC 3.0 headers and lib...
I've check my LIB path and "Odbcbcp.lib" only exists once!
If I comment out the bcp_init() call, my program compiles and links fine
The call to bcp_exec() is FINE...
Anyone have a clue as to what I'm doing wrong?
Included below is my c file (test.c) pasted just in case I'm doing something
else wrong.
// START test.c
#include <stdio.h>
#include <stdlib.h>
#include <windows.h>
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
#include <Odbcss.h>
#define MAXBUFLEN 255
#define SQLSERVER1
#defineDSN2
int createHandles();
void destroyHandles();
SQLHENV henv = SQL_NULL_HENV;//Environment Handle
SQLHDBC hcn = SQL_NULL_HDBC;//Connection Handle
main()
{
intret;//internal return codes
RETCODEretcode;//ODBC return codes
UCHARszDSN[SQL_MAX_DSN_LENGTH+1] = "cnABL",//DSN info
szDSN_UID[MAXNAME] = "sa",
szDSN_PSWD[MAXNAME] = "";
SQLCHARconnectionStringIn[MAXBUFLEN] = "";//SQL SERVER driver
connection string
SQLCHAR connectionStringOut[MAXBUFLEN];//SQL SERVER driver
connection string out
SQLSMALLINT lengthOfConnectionStringOut = 0;
// Bulk copy variables.
SDWORD cRows;
intmyType = DSN;
//Create Handles
if ( ( ret = createHandles() ) )
{
destroyHandles();
returnEXIT_FAILURE;
}
switch (myType)
{
case SQLSERVER:
//Build up the ConnStrIn...
sprintf(connectionStringIn, "DRIVER={SQL
Server};SERVER=%s;UID=%s;PWD=%s;DATABASE=%s;", "", "sa", "", "abldb");
printf("\nCONNECTION STRING->%s<-", connectionStringIn);
//connect
retcode = SQLDriverConnect(hcn,// Connection handle
NULL,// Window handle
connectionStringIn,// Input connect string
SQL_NTS,// Null-terminated string
connectionStringOut,// Address of output buffer
MAXBUFLEN,// Size of output buffer
&lengthOfConnectionStringOut,// Address of output length
SQL_DRIVER_NOPROMPT);
//check it
if (retcode == SQL_ERROR)
//if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
// Connect failed, call SQLGetDiagRec for errors.
printf("\nSQL SERVER CONNECTION FAILED!");
destroyHandles();
return EXIT_FAILURE;
}
else
{
// Connects to SQL Server always return
// informational messages. These messages can be
// retrieved by calling SQLGetDiagRec.
printf("\nSQL SERVER CONNECTION SUCCESS!");
}
break;
case DSN:
//connect
retcode = SQLConnect(hcn, szDSN, (SWORD)strlen(szDSN), szDSN_UID,
(SWORD)strlen(szDSN_UID), szDSN_PSWD, (SWORD)strlen(szDSN_PSWD));
//check it
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
// Connect failed, call SQLGetDiagRec for errors.
printf("\nDSN CONNECTION FAILED!");
destroyHandles();
return EXIT_FAILURE;
}
else
{
// Connects to SQL Server always return
// informational messages. These messages can be
// retrieved by calling SQLGetDiagRec.
printf("\nDSN CONNECTION SUCCESS!");
}
break;
}
// Initialize the bulk copy.
//retcode = bcp_init(hcn, "abldb..trendtable", "c:\\ben\\BCPODBC.bcp",
"c:\\ben\\BCPERROR.out", DB_OUT);
// Note that the test is for the bulk copy return of SUCCEED,
// not the ODBC return of SQL_SUCCESS.
if ( (retcode != SUCCEED) )
{
printf("bcp_init(hcn) Failed\n\n");
destroyHandles();
return EXIT_FAILURE;
}
// Execute the bulk copy.
retcode = bcp_exec(hcn, &cRows);
if ( (retcode != SUCCEED) )
{
printf("bcp_exec(hcn) Failed\n\n");
destroyHandles();
return EXIT_FAILURE;
}
printf("Number of rows bulk copied out = %d.\n", cRows);
//clean up
destroyHandles();
return EXIT_SUCCESS;
}
int createHandles()
{
RETCODE retcode;
// Allocate the ODBC Environment and save handle.
retcode = SQLAllocHandle (SQL_HANDLE_ENV,
SQL_NULL_HANDLE,
&henv);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
// Notify ODBC that this is an ODBC 3.0 application.
retcode = SQLSetEnvAttr(henv,
SQL_ATTR_ODBC_VERSION,
(SQLPOINTER)SQL_OV_ODBC3,
SQL_IS_INTEGER);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
// Allocate an ODBC connection handle
retcode = SQLAllocHandle(SQL_HANDLE_DBC,
henv,
&hcn);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
return 1;
}
void destroyHandles()
{
SQLDisconnect(hcn);
SQLFreeHandle(SQL_HANDLE_DBC, hcn);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
// END test.c
thanks in advance,
georgejetson
Nevermind...
Although I had my environmental variables pointing to the more recent dirs
first, it was not working...I'm still learning ms vc dev environment...
FYI: To compile ODBC using BCP in VC6 Enterprise...
My Solution:
a) Downloaded latest and greatest mdac sdk
b) Added mdac lib & include dirs to LIB & INCLUDE environmental variable
list at front of list
c) For all configurations (Release/Active...) in VC6
1) Made sure all required libs existed
Odbc32.lib Odbccp32.lib Odbcbcp.lib
(probably don't need Odbccp32.lib)
2) Set Additional Library Path to FIRST use new mdac/lib folder, then
sqlserver/lib folder
ie:C:\mdacSdk28\Libs\x86,C:\Program Files\Microsoft SQL
Server\80\Tools\DevTools\Lib
d)Did the same thing for additional include directories
ie:C:\mdacSdk28\Inc,C:\Program Files\Microsoft SQL
Server\80\Tools\DevTools\Include
In my source code, I included the following...
//ALL ODBC CALLS NEED
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
//ODBC UNICODE
#include <Sqlucode.h>
//ODBC BCP
#include <Odbcss.h>
e) Finally, I took OUT the /nologo option so I could see where the compiler
and linker were really looking
the result below
Compiling...
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 12.00.8168 for 80x86
Copyright (C) Microsoft Corp 1984-1998. All rights reserved.
cl /MLd /W3 /Gm /GX /ZI /Od /I "C:\mdacSdk28\Inc" /I "C:\Program
Files\Microsoft SQL Server\80\Tools\DevTools\Include" /D "WIN32" /D "_DEBUG"
/D "_CONSOLE" /D "_MBCS" /FR"Debug/" /Fp"Debug/prjOdbcTest.pch" /YX
/Fo"Debug/" /Fd"Debug/" /FD /GZ /c
"C:\sqlutil\odbcTest\test.c"
test.c
Note: Using precompiled header
Linking...
Creating browse info file...
prjOdbcTest.exe - 0 error(s), 0 warning(s)
thanks in advance,
georgejetson
compile [really link] question
Hi,
I'm having trouble linking a simple example using the bcp functions...
The error:
****************************************
********
test.obj : error LNK2001: unresolved external symbol _bcp_init@.20
****************************************
********
Environment: Windows 2000, SQL Server 2000, VS C++ 6.0 Enterprise
I know my environment and connection handles are working...but my bcp_init()
call is driving me crazy...
Headers I'm including:
////////////////////////////////
//C HEADERS
#include <stdio.h>
#include <stdlib.h>
//WINDOWS
#include <windows.h>
//ODBC HEADERS
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
//BCP HEADER
#include <Odbcss.h>
////////////////////////////////
Library I'm including
Odbcbcp.lib
I use both
a) /libpath:"C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Lib"
and
b) LIB environmental variable points there too
I've checked the folder and the file is def. there!
Thinking I might not be using the ODBC 3.0 headers and lib...
I've check my LIB path and "Odbcbcp.lib" only exists once!
If I comment out the bcp_init() call, my program compiles and links fine
The call to bcp_exec() is FINE...
Anyone have a clue as to what I'm doing wrong?
Included below is my c file (test.c) pasted just in case I'm doing something
else wrong.
// START test.c
#include <stdio.h>
#include <stdlib.h>
#include <windows.h>
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
#include <Odbcss.h>
#define MAXBUFLEN 255
#define SQLSERVER 1
#define DSN 2
int createHandles();
void destroyHandles();
SQLHENV henv = SQL_NULL_HENV; //Environment Handle
SQLHDBC hcn = SQL_NULL_HDBC; //Connection Handle
main()
{
int ret; // internal return codes
RETCODE retcode; // ODBC return codes
UCHAR szDSN[SQL_MAX_DSN_LENGTH+1] = "cnABL", //DSN info
szDSN_UID[MAXNAME] = "sa",
szDSN_PSWD[MAXNAME] = "";
SQLCHAR connectionStringIn[MAXBUFLEN] = ""; //SQL SERVER driver
connection string
SQLCHAR connectionStringOut[MAXBUFLEN]; //SQL SERVER driver
connection string out
SQLSMALLINT lengthOfConnectionStringOut = 0;
// Bulk copy variables.
SDWORD cRows;
int myType = DSN;
//Create Handles
if ( ( ret = createHandles() ) )
{
destroyHandles();
return EXIT_FAILURE;
}
switch (myType)
{
case SQLSERVER:
//Build up the ConnStrIn...
sprintf(connectionStringIn, "DRIVER={SQL
Server};SERVER=%s;UID=%s;PWD=%s;DATABASE
=%s;", "", "sa", "", "abldb");
printf("\nCONNECTION STRING->%s<-", connectionStringIn);
//connect
retcode = SQLDriverConnect( hcn, // Connection handle
NULL, // Window handle
connectionStringIn, // Input connect string
SQL_NTS, // Null-terminated string
connectionStringOut, // Address of output buffer
MAXBUFLEN, // Size of output buffer
&lengthOfConnectionStringOut, // Address of output length
SQL_DRIVER_NOPROMPT);
//check it
if (retcode == SQL_ERROR)
//if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
// Connect failed, call SQLGetDiagRec for errors.
printf("\nSQL SERVER CONNECTION FAILED!");
destroyHandles();
return EXIT_FAILURE;
}
else
{
// Connects to SQL Server always return
// informational messages. These messages can be
// retrieved by calling SQLGetDiagRec.
printf("\nSQL SERVER CONNECTION SUCCESS!");
}
break;
case DSN:
//connect
retcode = SQLConnect(hcn, szDSN, (SWORD)strlen(szDSN), szDSN_UID,
(SWORD)strlen(szDSN_UID), szDSN_PSWD, (SWORD)strlen(szDSN_PSWD));
//check it
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
// Connect failed, call SQLGetDiagRec for errors.
printf("\nDSN CONNECTION FAILED!");
destroyHandles();
return EXIT_FAILURE;
}
else
{
// Connects to SQL Server always return
// informational messages. These messages can be
// retrieved by calling SQLGetDiagRec.
printf("\nDSN CONNECTION SUCCESS!");
}
break;
}
// Initialize the bulk copy.
//retcode = bcp_init(hcn, "abldb..trendtable", "c:\\ben\\BCPODBC.bcp",
"c:\\ben\\BCPERROR.out", DB_OUT);
// Note that the test is for the bulk copy return of SUCCEED,
// not the ODBC return of SQL_SUCCESS.
if ( (retcode != SUCCEED) )
{
printf("bcp_init(hcn) Failed\n\n");
destroyHandles();
return EXIT_FAILURE;
}
// Execute the bulk copy.
retcode = bcp_exec(hcn, &cRows);
if ( (retcode != SUCCEED) )
{
printf("bcp_exec(hcn) Failed\n\n");
destroyHandles();
return EXIT_FAILURE;
}
printf("Number of rows bulk copied out = %d.\n", cRows);
//clean up
destroyHandles();
return EXIT_SUCCESS;
}
int createHandles()
{
RETCODE retcode;
// Allocate the ODBC Environment and save handle.
retcode = SQLAllocHandle ( SQL_HANDLE_ENV,
SQL_NULL_HANDLE,
&henv);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
// Notify ODBC that this is an ODBC 3.0 application.
retcode = SQLSetEnvAttr( henv,
SQL_ATTR_ODBC_VERSION,
(SQLPOINTER)SQL_OV_ODBC3,
SQL_IS_INTEGER);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
// Allocate an ODBC connection handle
retcode = SQLAllocHandle( SQL_HANDLE_DBC,
henv,
&hcn);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
return 1;
}
void destroyHandles()
{
SQLDisconnect(hcn);
SQLFreeHandle(SQL_HANDLE_DBC, hcn);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
// END test.c
thanks in advance,
georgejetsonNevermind...
Although I had my environmental variables pointing to the more recent dirs
first, it was not working...I'm still learning ms vc dev environment...
FYI: To compile ODBC using BCP in VC6 Enterprise...
My Solution:
a) Downloaded latest and greatest mdac sdk
b) Added mdac lib & include dirs to LIB & INCLUDE environmental variable
list at front of list
c) For all configurations (Release/Active...) in VC6
1) Made sure all required libs existed
Odbc32.lib Odbccp32.lib Odbcbcp.lib
(probably don't need Odbccp32.lib)
2) Set Additional Library Path to FIRST use new mdac/lib folder, then
sqlserver/lib folder
ie:C:\mdacSdk28\Libs\x86,C:\Program Files\Microsoft SQL
Server\80\Tools\DevTools\Lib
d)Did the same thing for additional include directories
ie:C:\mdacSdk28\Inc,C:\Program Files\Microsoft SQL
Server\80\Tools\DevTools\Include
In my source code, I included the following...
//ALL ODBC CALLS NEED
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
//ODBC UNICODE
#include <Sqlucode.h>
//ODBC BCP
#include <Odbcss.h>
e) Finally, I took OUT the /nologo option so I could see where the compiler
and linker were really looking
the result below
--
Compiling...
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 12.00.8168 for 80x86
Copyright (C) Microsoft Corp 1984-1998. All rights reserved.
cl /MLd /W3 /Gm /GX /ZI /Od /I "C:\mdacSdk28\Inc" /I "C:\Program
Files\Microsoft SQL Server\80\Tools\DevTools\Include" /D "WIN32" /D "_DEBUG"
/D "_CONSOLE" /D "_MBCS" /FR"Debug/" /Fp"Debug/prjOdbcTest.pch" /YX
/Fo"Debug/" /Fd"Debug/" /FD /GZ /c
"C:\sqlutil\odbcTest\test.c"
test.c
Note: Using precompiled header
Linking...
Creating browse info file...
prjOdbcTest.exe - 0 error(s), 0 warning(s)
--
thanks in advance,
georgejetson
I'm having trouble linking a simple example using the bcp functions...
The error:
****************************************
********
test.obj : error LNK2001: unresolved external symbol _bcp_init@.20
****************************************
********
Environment: Windows 2000, SQL Server 2000, VS C++ 6.0 Enterprise
I know my environment and connection handles are working...but my bcp_init()
call is driving me crazy...
Headers I'm including:
////////////////////////////////
//C HEADERS
#include <stdio.h>
#include <stdlib.h>
//WINDOWS
#include <windows.h>
//ODBC HEADERS
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
//BCP HEADER
#include <Odbcss.h>
////////////////////////////////
Library I'm including
Odbcbcp.lib
I use both
a) /libpath:"C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Lib"
and
b) LIB environmental variable points there too
I've checked the folder and the file is def. there!
Thinking I might not be using the ODBC 3.0 headers and lib...
I've check my LIB path and "Odbcbcp.lib" only exists once!
If I comment out the bcp_init() call, my program compiles and links fine
The call to bcp_exec() is FINE...
Anyone have a clue as to what I'm doing wrong?
Included below is my c file (test.c) pasted just in case I'm doing something
else wrong.
// START test.c
#include <stdio.h>
#include <stdlib.h>
#include <windows.h>
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
#include <Odbcss.h>
#define MAXBUFLEN 255
#define SQLSERVER 1
#define DSN 2
int createHandles();
void destroyHandles();
SQLHENV henv = SQL_NULL_HENV; //Environment Handle
SQLHDBC hcn = SQL_NULL_HDBC; //Connection Handle
main()
{
int ret; // internal return codes
RETCODE retcode; // ODBC return codes
UCHAR szDSN[SQL_MAX_DSN_LENGTH+1] = "cnABL", //DSN info
szDSN_UID[MAXNAME] = "sa",
szDSN_PSWD[MAXNAME] = "";
SQLCHAR connectionStringIn[MAXBUFLEN] = ""; //SQL SERVER driver
connection string
SQLCHAR connectionStringOut[MAXBUFLEN]; //SQL SERVER driver
connection string out
SQLSMALLINT lengthOfConnectionStringOut = 0;
// Bulk copy variables.
SDWORD cRows;
int myType = DSN;
//Create Handles
if ( ( ret = createHandles() ) )
{
destroyHandles();
return EXIT_FAILURE;
}
switch (myType)
{
case SQLSERVER:
//Build up the ConnStrIn...
sprintf(connectionStringIn, "DRIVER={SQL
Server};SERVER=%s;UID=%s;PWD=%s;DATABASE
=%s;", "", "sa", "", "abldb");
printf("\nCONNECTION STRING->%s<-", connectionStringIn);
//connect
retcode = SQLDriverConnect( hcn, // Connection handle
NULL, // Window handle
connectionStringIn, // Input connect string
SQL_NTS, // Null-terminated string
connectionStringOut, // Address of output buffer
MAXBUFLEN, // Size of output buffer
&lengthOfConnectionStringOut, // Address of output length
SQL_DRIVER_NOPROMPT);
//check it
if (retcode == SQL_ERROR)
//if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
// Connect failed, call SQLGetDiagRec for errors.
printf("\nSQL SERVER CONNECTION FAILED!");
destroyHandles();
return EXIT_FAILURE;
}
else
{
// Connects to SQL Server always return
// informational messages. These messages can be
// retrieved by calling SQLGetDiagRec.
printf("\nSQL SERVER CONNECTION SUCCESS!");
}
break;
case DSN:
//connect
retcode = SQLConnect(hcn, szDSN, (SWORD)strlen(szDSN), szDSN_UID,
(SWORD)strlen(szDSN_UID), szDSN_PSWD, (SWORD)strlen(szDSN_PSWD));
//check it
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
// Connect failed, call SQLGetDiagRec for errors.
printf("\nDSN CONNECTION FAILED!");
destroyHandles();
return EXIT_FAILURE;
}
else
{
// Connects to SQL Server always return
// informational messages. These messages can be
// retrieved by calling SQLGetDiagRec.
printf("\nDSN CONNECTION SUCCESS!");
}
break;
}
// Initialize the bulk copy.
//retcode = bcp_init(hcn, "abldb..trendtable", "c:\\ben\\BCPODBC.bcp",
"c:\\ben\\BCPERROR.out", DB_OUT);
// Note that the test is for the bulk copy return of SUCCEED,
// not the ODBC return of SQL_SUCCESS.
if ( (retcode != SUCCEED) )
{
printf("bcp_init(hcn) Failed\n\n");
destroyHandles();
return EXIT_FAILURE;
}
// Execute the bulk copy.
retcode = bcp_exec(hcn, &cRows);
if ( (retcode != SUCCEED) )
{
printf("bcp_exec(hcn) Failed\n\n");
destroyHandles();
return EXIT_FAILURE;
}
printf("Number of rows bulk copied out = %d.\n", cRows);
//clean up
destroyHandles();
return EXIT_SUCCESS;
}
int createHandles()
{
RETCODE retcode;
// Allocate the ODBC Environment and save handle.
retcode = SQLAllocHandle ( SQL_HANDLE_ENV,
SQL_NULL_HANDLE,
&henv);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
// Notify ODBC that this is an ODBC 3.0 application.
retcode = SQLSetEnvAttr( henv,
SQL_ATTR_ODBC_VERSION,
(SQLPOINTER)SQL_OV_ODBC3,
SQL_IS_INTEGER);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
// Allocate an ODBC connection handle
retcode = SQLAllocHandle( SQL_HANDLE_DBC,
henv,
&hcn);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
return 1;
}
void destroyHandles()
{
SQLDisconnect(hcn);
SQLFreeHandle(SQL_HANDLE_DBC, hcn);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
// END test.c
thanks in advance,
georgejetsonNevermind...
Although I had my environmental variables pointing to the more recent dirs
first, it was not working...I'm still learning ms vc dev environment...
FYI: To compile ODBC using BCP in VC6 Enterprise...
My Solution:
a) Downloaded latest and greatest mdac sdk
b) Added mdac lib & include dirs to LIB & INCLUDE environmental variable
list at front of list
c) For all configurations (Release/Active...) in VC6
1) Made sure all required libs existed
Odbc32.lib Odbccp32.lib Odbcbcp.lib
(probably don't need Odbccp32.lib)
2) Set Additional Library Path to FIRST use new mdac/lib folder, then
sqlserver/lib folder
ie:C:\mdacSdk28\Libs\x86,C:\Program Files\Microsoft SQL
Server\80\Tools\DevTools\Lib
d)Did the same thing for additional include directories
ie:C:\mdacSdk28\Inc,C:\Program Files\Microsoft SQL
Server\80\Tools\DevTools\Include
In my source code, I included the following...
//ALL ODBC CALLS NEED
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
//ODBC UNICODE
#include <Sqlucode.h>
//ODBC BCP
#include <Odbcss.h>
e) Finally, I took OUT the /nologo option so I could see where the compiler
and linker were really looking
the result below
--
Compiling...
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 12.00.8168 for 80x86
Copyright (C) Microsoft Corp 1984-1998. All rights reserved.
cl /MLd /W3 /Gm /GX /ZI /Od /I "C:\mdacSdk28\Inc" /I "C:\Program
Files\Microsoft SQL Server\80\Tools\DevTools\Include" /D "WIN32" /D "_DEBUG"
/D "_CONSOLE" /D "_MBCS" /FR"Debug/" /Fp"Debug/prjOdbcTest.pch" /YX
/Fo"Debug/" /Fd"Debug/" /FD /GZ /c
"C:\sqlutil\odbcTest\test.c"
test.c
Note: Using precompiled header
Linking...
Creating browse info file...
prjOdbcTest.exe - 0 error(s), 0 warning(s)
--
thanks in advance,
georgejetson
Subscribe to:
Posts (Atom)