Parameterized Expression: fields

Showing posts with label fields. Show all posts

Friday, March 30, 2012

Partial searches over a lot of fields

Hi All,
I have the following scenario. I have a table called Invoice. This
has around 30 columns of which i have to do a retrieval based on
filter conditions on 10 columns. These filters need to be partial
searches i.e. for e.g the Customer name could be 'Arun', 'Parthiv',
'Aaron', now i should be able to search the customer as 'ar' and it
should return 'Arun' and 'Parthiv'. My concern is there are 10 columns
on which this like '%x%' search has to be done and there will
practically be hudreds of thousands of rows. can anybody suggest me to
improve the performance of such a query. Currently what i am thinkin
of is
select Id, Memo, .. FROM Invoice where CustomerName like '%' + @.Name +
'%' and etc.
P.S. am using ASP.Net as the front end.ArunPrakash (arunprakashb@.yahoo.com) writes:
> I have the following scenario. I have a table called Invoice. This
> has around 30 columns of which i have to do a retrieval based on
> filter conditions on 10 columns. These filters need to be partial
> searches i.e. for e.g the Customer name could be 'Arun', 'Parthiv',
> 'Aaron', now i should be able to search the customer as 'ar' and it
> should return 'Arun' and 'Parthiv'. My concern is there are 10 columns
> on which this like '%x%' search has to be done and there will
> practically be hudreds of thousands of rows. can anybody suggest me to
> improve the performance of such a query. Currently what i am thinkin
> of is
> select Id, Memo, .. FROM Invoice where CustomerName like '%' + @.Name +
> '%' and etc.

You can use SELECT TOP or SET ROWCOUNT to restrict the number of rows
returned. A good value is probably 2000. If you get 2000 rows, you tell
the user to refine his conditions.

Also keep in mind, that there could be a great difference in performance
when searching for names that start with 'Ar', or have 'ar' anywhere in
the name. If the search column is indexed, that index can be used
for the case "starts with", but not "contains".

There are also a couple of considerations of how to compose the query
to make the search effective. You may be interested in the article
http://www.sommarskog.se/dyn-search.html on my web site.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

partial DISTINCT...

Hi,
I need a way to do a 'partial' DISTINCT. What I try to say with that is:
some fields should be distinct, others not. For the non-distinct-fields is
should take the first occuring value or something like that.
For exepmle: I'm havng this query:
SELECT DISTINCT tblIsabel.IsabelID, tblIsabel.NameIsabel,
tblIsabelChild.IsabelChildID, tblIsabelChild.ChildNumber,
tblExported.ExportModeID
FROM tblIsabel JOIN tblIsabelChild ON tblIsabel.IsabelID = tblIsabelChild.IsabelID
LEFT JOIN tblExported ON tblIsabel.IsabelID = tblExported.IsabelID
ORDER BY tblIsabel.NameIsabel DESC, tblIsabelChild.IsabelChildID ASC
What the application has to do is:
It has to show all the tblIsabelChild-records ONLY ONCE, with the name of
the parent (tblIsabel), and an indication that shows me how the
tblIsabel-record has been exported (depeninding on the
tblExported.ExportModeID).
Yhe query I used works fine if the tblIsabel-records has not been exported,
or when it was exported only once.
But, when it is exported twice or more: it will find two or more
ExportModeID's for the tblIsabel-records, and like this show the
tblIsabelChild-records two or more times!
So what I need is something like this: a query that says that every
tblIsabelChildID should be unique (DISTINCT) in the recordset returned, and
for the ExportModeID it should take the first occurence or something liek
that.
It tryed something like this:
SELECT DISTINCT(tblIsabel.IsabelID, tblIsabel.NameIsabel,
tblIsabelChild.IsabelChildID, tblIsabelChild.ChildNumber),
tblExported.ExportModeID
hoping that it would use the distinct only for these values, but that
doesn't work unfortunately, hehe. I tryed using subquery's, but I didn't
manage to fidn something I need. Although I really think it shoudl be
possible to do with subquery's.
Anybody got a brilliant idea overhere?
Thanks a lot in advance,
PieterUse GROUP BY.
> It has to show all the tblIsabelChild-records ONLY ONCE, with the name of
> the parent (tblIsabel), and an indication that shows me how the
> tblIsabel-record has been exported (depeninding on the
> tblExported.ExportModeID).
If there is more than one value of ExportModeID then which one do you want
to show? I'll assume you just want to see the maximum value:
SELECT I.isabelid, I.nameisabel,
C.isabelchildid, C.childnumber,
MAX(E.exportmodeid) AS exportmodeid
FROM tblIsabel AS I
JOIN tblIsabelChild AS C
ON I.isabelid = C.isabelid
LEFT JOIN tblExported AS E
ON I.isabelid = E.isabelid
GROUP BY I.isabelid, I.nameisabel, C.isabelchildid, C.childnumber
ORDER BY I.nameisabel DESC, C.isabelchildid ASC
--
David Portas
SQL Server MVP
--|||Thanks,
I just found another solution:
adding the folowing code before the ORDER BY clause:
AND ((tblExported.ExportModeID IN
(SELECT MIN(tblExported.ExportModeID) FROM tblExported WHERE
tblExported.IsabelID = tblIsabel.IsabelID)
) OR (tblExported.ExportModeID IS NULL))
But your solution looks much nicer, hehe :-)
Funny (and stupid) thing is: I tryed the GROUP BY before, but it gave ma a
whole bunch of errors, just because I forgot to put all the fields in the
GROUP BY :-( And I just didn't try further :-(
Thanks a lot!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:DtCdnfRr557s6b7d4p2dnA@.giganews.com...
> Use GROUP BY.
> > It has to show all the tblIsabelChild-records ONLY ONCE, with the name
of
> > the parent (tblIsabel), and an indication that shows me how the
> > tblIsabel-record has been exported (depeninding on the
> > tblExported.ExportModeID).
> If there is more than one value of ExportModeID then which one do you want
> to show? I'll assume you just want to see the maximum value:
> SELECT I.isabelid, I.nameisabel,
> C.isabelchildid, C.childnumber,
> MAX(E.exportmodeid) AS exportmodeid
> FROM tblIsabel AS I
> JOIN tblIsabelChild AS C
> ON I.isabelid = C.isabelid
> LEFT JOIN tblExported AS E
> ON I.isabelid = E.isabelid
> GROUP BY I.isabelid, I.nameisabel, C.isabelchildid, C.childnumber
> ORDER BY I.nameisabel DESC, C.isabelchildid ASC
> --
> David Portas
> SQL Server MVP
> --
>

partial DISTINCT...

Hi,
I need a way to do a 'partial' DISTINCT. What I try to say with that is:
some fields should be distinct, others not. For the non-distinct-fields is
should take the first occuring value or something like that.
For exepmle: I'm havng this query:
SELECT DISTINCT tblIsabel.IsabelID, tblIsabel.NameIsabel,
tblIsabelChild.IsabelChildID, tblIsabelChild.ChildNumber,
tblExported.ExportModeID
FROM tblIsabel JOIN tblIsabelChild ON tblIsabel.IsabelID =
tblIsabelChild.IsabelID
LEFT JOIN tblExported ON tblIsabel.IsabelID = tblExported.IsabelID
ORDER BY tblIsabel.NameIsabel DESC, tblIsabelChild.IsabelChildID ASC
What the application has to do is:
It has to show all the tblIsabelChild-records ONLY ONCE, with the name of
the parent (tblIsabel), and an indication that shows me how the
tblIsabel-record has been exported (depeninding on the
tblExported.ExportModeID).
Yhe query I used works fine if the tblIsabel-records has not been exported,
or when it was exported only once.
But, when it is exported twice or more: it will find two or more
ExportModeID's for the tblIsabel-records, and like this show the
tblIsabelChild-records two or more times!
So what I need is something like this: a query that says that every
tblIsabelChildID should be unique (DISTINCT) in the recordset returned, and
for the ExportModeID it should take the first occurence or something liek
that.
It tryed something like this:
SELECT DISTINCT(tblIsabel.IsabelID, tblIsabel.NameIsabel,
tblIsabelChild.IsabelChildID, tblIsabelChild.ChildNumber),
tblExported.ExportModeID
hoping that it would use the distinct only for these values, but that
doesn't work unfortunately, hehe. I tryed using subquery's, but I didn't
manage to fidn something I need. Although I really think it shoudl be
possible to do with subquery's.
Anybody got a brilliant idea overhere?
Thanks a lot in advance,
PieterUse GROUP BY.

> It has to show all the tblIsabelChild-records ONLY ONCE, with the name of
> the parent (tblIsabel), and an indication that shows me how the
> tblIsabel-record has been exported (depeninding on the
> tblExported.ExportModeID).
If there is more than one value of ExportModeID then which one do you want
to show? I'll assume you just want to see the maximum value:
SELECT I.isabelid, I.nameisabel,
C.isabelchildid, C.childnumber,
MAX(E.exportmodeid) AS exportmodeid
FROM tblIsabel AS I
JOIN tblIsabelChild AS C
ON I.isabelid = C.isabelid
LEFT JOIN tblExported AS E
ON I.isabelid = E.isabelid
GROUP BY I.isabelid, I.nameisabel, C.isabelchildid, C.childnumber
ORDER BY I.nameisabel DESC, C.isabelchildid ASC
David Portas
SQL Server MVP
--|||Thanks,
I just found another solution:
adding the folowing code before the ORDER BY clause:
AND ((tblExported.ExportModeID IN
(SELECT MIN(tblExported.ExportModeID) FROM tblExported WHERE
tblExported.IsabelID = tblIsabel.IsabelID)
) OR (tblExported.ExportModeID IS NULL))
But your solution looks much nicer, hehe :-)
Funny (and stupid) thing is: I tryed the GROUP BY before, but it gave ma a
whole bunch of errors, just because I forgot to put all the fields in the
GROUP BY :-( And I just didn't try further :-(
Thanks a lot!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:DtCdnfRr557s6b7d4p2dnA@.giganews.com...
> Use GROUP BY.
>
of
> If there is more than one value of ExportModeID then which one do you want
> to show? I'll assume you just want to see the maximum value:
> SELECT I.isabelid, I.nameisabel,
> C.isabelchildid, C.childnumber,
> MAX(E.exportmodeid) AS exportmodeid
> FROM tblIsabel AS I
> JOIN tblIsabelChild AS C
> ON I.isabelid = C.isabelid
> LEFT JOIN tblExported AS E
> ON I.isabelid = E.isabelid
> GROUP BY I.isabelid, I.nameisabel, C.isabelchildid, C.childnumber
> ORDER BY I.nameisabel DESC, C.isabelchildid ASC
> --
> David Portas
> SQL Server MVP
> --
>

Wednesday, March 28, 2012

parsing varchar fields

InvoiceNumber@.VendorAcronym

There'd be a lot of vendors.

Additional issue: sometimes these values would be referred to in the
description field, and I'd need to distinguish them as referrals rather
than as original recorded instances of the values. For that, I imagined
either:

InvoiceNumber@.@.VendorAcronym

InvoiceNumber&VendorAcronym
InvoiceNumber//VendorAcronym

etc. -- something like that.

I'm just wondering if there's best practice for doing anything this stupid
(hey, I'm stuck with this as our only option just now; hopefully it's only
temporary). How to parse out whatever I end up implementing -- well, it
needs to be tractable.

Thoughts?

ScottScott,

Instead of changing the existing product's tables why not create an
additional table(s) to store these values and then write your queries using
joins.

HTH

Jerry
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539.cb0c208b60e605e1a298926bee12e52f@.t eranews...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Hi

It is not clear what you are trying to achieve, but creating a concanenated
field is probably not a good idea. Separate fields and a status will be alot
easier to maintain and understand.

John

"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539.cb0c208b60e605e1a298926bee12e52f@.t eranews...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Hi

It is not clear what you are trying to do, but creating a concatenated field
is probably not a good idea. Separate fields and a status will be far easier
to maintain and understand.

John

Jerry, John -- sorry for the lack of clarity.

We have a web app from a vendor, and in this particular case I don't want
to hack anything (I've done some hacks where practical, and will be doing
more, but there's some intractable stuff in view of upgrade management and
my available time).

Our people need to reference invoice numbers and vendors in a description
field, so I'm wanting to accommodate them until such time comes along that
the software supports something like that. But they're wanting to be able
to generate reports on the vendor and invoice numbers, so I'm wanting to do
this kludgey thing as elegantly as kludges can be. I figured if I specified
that they enter the two fields in some string format that was simple enough
for them this would be practical from their standpoint, and if I could
parse it it'd be practical from mine. The concatenation John's concerned
about is mostly as a way of avoiding separate ways of identifying the
"fields". That would be a bit onerous for the staff entering the
information, even if it did make parsing easier.

So imagine a lot of records with brief descriptions of invoices, with
vendor and invoice #s embedded in the descriptions. Oh, yeah, almost forgot
-- no chance of their being more than one of these per description (per
record).

Scott|||Hi

I am not sure why you think the users would want to enter some cryptic code
rather than exact values in two separate fields. That is more of a usability
with the UI, for instance if your parts were both numeric you may be slowing
your users down forcing them to use non-keypad characters that require
shifting. You should also look at the business processes, for instance if
you processed everything for one vendor together then retaining the vendor
information would remove the need to type it in again.

If you need to concatenate the values for display purposes then you can do
that in the code for your stored procedure or create a view.

John

"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127546922.53434faa9cedec41603cbc091e9f06a8@.t eranews...
> Jerry Spivey opined thusly on Sep 24:
>> Scott,
>>
>> Instead of changing the existing product's tables why not create an
>> additional table(s) to store these values and then write your queries
>> using
>> joins.
> Jerry, John -- sorry for the lack of clarity.
> We have a web app from a vendor, and in this particular case I don't want
> to hack anything (I've done some hacks where practical, and will be doing
> more, but there's some intractable stuff in view of upgrade management and
> my available time).
> Our people need to reference invoice numbers and vendors in a description
> field, so I'm wanting to accommodate them until such time comes along that
> the software supports something like that. But they're wanting to be able
> to generate reports on the vendor and invoice numbers, so I'm wanting to
> do
> this kludgey thing as elegantly as kludges can be. I figured if I
> specified
> that they enter the two fields in some string format that was simple
> enough
> for them this would be practical from their standpoint, and if I could
> parse it it'd be practical from mine. The concatenation John's concerned
> about is mostly as a way of avoiding separate ways of identifying the
> "fields". That would be a bit onerous for the staff entering the
> information, even if it did make parsing easier.
> So imagine a lot of records with brief descriptions of invoices, with
> vendor and invoice #s embedded in the descriptions. Oh, yeah, almost
> forgot
> -- no chance of their being more than one of these per description (per
> record).
> --
> Scott|||John Bell opined thusly on Sep 24:
> Hi
> I am not sure why you think the users would want to enter some cryptic code
> rather than exact values in two separate fields.

Developing more fields in this N-tier application whose upgrades are
controlled by a vendor whose dev I'm not privy to, isn't an option. If I
could do that, I would. I'm not posting to inquire about better
alternatives; I'm wanting to wring juice out of an unfortunate turnip.

> That is more of a usability
> with the UI, for instance if your parts were both numeric you may be slowing
> your users down forcing them to use non-keypad characters that require
> shifting. You should also look at the business processes, for instance if
> you processed everything for one vendor together then retaining the vendor
> information would remove the need to type it in again.
> If you need to concatenate the values for display purposes then you can do
> that in the code for your stored procedure or create a view.

Again, beyond my control. I've done a few necessary hacks of the software
(both the asp and some sprocs), but I can't expand the number of such edits
beyond reason. Next vendor upgrade, I'd be flailing about.

The users are more worried about being able to have reports on data they're
unfortunately left to enter in a free-form description field, type varchar.
My posting is to inquire whether a string formatting convention I'm
considering is the best I can do under that circumstance. I'd be glad of
any hints at how to most efficiently query the data as well.

Scott|||Hi

If you are not writing a new interface then you are probably better using a
fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part is
a fixed length then you don't need a separator, but it may help the users
differentiate the different parts. I would have expect that you can
determine the fact that an invoice is a referral by a different means.

John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127575738.f429cee989a6daeb985244c70f0bb497@.t eranews...
> John Bell opined thusly on Sep 24:
>> Hi
>>
>> I am not sure why you think the users would want to enter some cryptic
>> code
>> rather than exact values in two separate fields.
> Developing more fields in this N-tier application whose upgrades are
> controlled by a vendor whose dev I'm not privy to, isn't an option. If I
> could do that, I would. I'm not posting to inquire about better
> alternatives; I'm wanting to wring juice out of an unfortunate turnip.
>> That is more of a usability
>> with the UI, for instance if your parts were both numeric you may be
>> slowing
>> your users down forcing them to use non-keypad characters that require
>> shifting. You should also look at the business processes, for instance if
>> you processed everything for one vendor together then retaining the
>> vendor
>> information would remove the need to type it in again.
>>
>> If you need to concatenate the values for display purposes then you can
>> do
>> that in the code for your stored procedure or create a view.
> Again, beyond my control. I've done a few necessary hacks of the software
> (both the asp and some sprocs), but I can't expand the number of such
> edits
> beyond reason. Next vendor upgrade, I'd be flailing about.
> The users are more worried about being able to have reports on data
> they're
> unfortunately left to enter in a free-form description field, type
> varchar.
> My posting is to inquire whether a string formatting convention I'm
> considering is the best I can do under that circumstance. I'd be glad of
> any hints at how to most efficiently query the data as well.
>
> --
> Scott|||John Bell opined thusly on Sep 24:
> Hi
> If you are not writing a new interface then you are probably better using a
> fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part is
> a fixed length then you don't need a separator, but it may help the users
> differentiate the different parts. I would have expect that you can
> determine the fact that an invoice is a referral by a different means.

Well, we could get into "I would have expected" exchanges, or we can skip
the stuff I already know -- and posted in the original post. With all due
respect, "duh."

Good grief. Outta here.

Scott|||John Bell (jbellnewsposts@.hotmail.com) writes:
> If you are not writing a new interface then you are probably better
> using a fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If
> each part is a fixed length then you don't need a separator, but it may
> help the users differentiate the different parts. I would have expect
> that you can determine the fact that an invoice is a referral by a
> different means.

Now, how is that for a user interface? This is free text, and users will
mangle it anyway, but they have better odds with a separator than fixed
length.

I have not intervened into this thread before, because I don't have
much advice to offer Steve in this dire situation.

Hm, possibly could have a trigger on the table that loaded a new table
with the fields split in pieces. Still iffy with regards to the vendor,
but at least no existing object is changed. (Then again, being on the
vendor side of the fence, I know what I would think if I found that one
of our customers had added a trigger to one of our tables.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Scott Marquardt opined thusly on Sep 24:
> John Bell opined thusly on Sep 24:
>> Hi
>>
>> If you are not writing a new interface then you are probably better using a
>> fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part is
>> a fixed length then you don't need a separator, but it may help the users
>> differentiate the different parts. I would have expect that you can
>> determine the fact that an invoice is a referral by a different means.
> Well, we could get into "I would have expected" exchanges, or we can skip
> the stuff I already know -- and posted in the original post. With all due
> respect, "duh."
> Good grief. Outta here.

John., I'm going to apologize for a short temper there. It was born of
impatience.

My principal interest was in hearing how anyone else might PARSE the field
to recover the data. Erland's trigger idea isn't bad for WHAT I do with it.

"Invoice for 1/2 inch puce widgets and a can of beans, 100-3123@.WidgetMart"

"Refund for can of beans, 100-3123&WidgetMart"

The second one would need to be tagged as a referral to the first one. But
if that's my basic technique, they're parse out the same way.

Scott|||>> My principal interest was in hearing how anyone else might PARSE the field to recover the data. Erland's trigger idea isn't bad for WHAT I do with it. "Invoice for 1/2 inch puce widgets and a can of beans, 100-3123@.WidgetMart" <<

Since free text requires human intelligence, we get people to encode
it. If the text is short and styled the same way, you can find AI
programs that are pretty good these days. Of course this has nothing
to do with RDBMS and nobody in their right mind would try to do it in
SQL.|||Scott,

Have you considered an ActiveX script in a DTS package to shred the
data periodically (you can use the SQL Scheduler to run it once a
minute). We do this to parse out syslog tables; it's kludgy, but its
consistent. While not as immediate as a trigger to populate a table,
it does keep you from modifying the vendor database (which, if your
vendor has the ability to update their database, could be a big
benefit).

Obviously, your users would have to be trained on how to enter data
consistently, and you'd probably want some sort of validation to pick
up on coded entries that don't meant your
data-followed-by-a-comma-followed-by-more-data rules. You may even
look at Regular Expressions inside your DTS package to help with that.

Just thinking aloud,
Stu|||Hi Erland/Steve

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96DBF14D14D5AYazorman@.127.0.0.1...
> John Bell (jbellnewsposts@.hotmail.com) writes:
>> If you are not writing a new interface then you are probably better
>> using a fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If
>> each part is a fixed length then you don't need a separator, but it may
>> help the users differentiate the different parts. I would have expect
>> that you can determine the fact that an invoice is a referral by a
>> different means.
>
> Now, how is that for a user interface? This is free text, and users will
> mangle it anyway, but they have better odds with a separator than fixed
> length.

That was my point, guess I should have said it that way! The users will
(probably) not like having to enter a coded string, even if you keep it as
simple! They may even deliberately mangle it if there is no value in it for
themselves.

> I have not intervened into this thread before, because I don't have
> much advice to offer Steve in this dire situation.
It seems to me, that if this an off the shelf package the benefits has been
lost some time ago, if this is a bespoke development then it has not been
specified thoroughly enough.

> Hm, possibly could have a trigger on the table that loaded a new table
> with the fields split in pieces. Still iffy with regards to the vendor,
> but at least no existing object is changed. (Then again, being on the
> vendor side of the fence, I know what I would think if I found that one
> of our customers had added a trigger to one of our tables.)

Writing a trigger may still invalidate the contract as the transactions will
be effected, the vendor may have the right to charge improportate fees to
fix a problem even if a problem is not related to this table. But if Steve
has already tweeked things that could already be the case! A trigger would
be useful for validation even if the replication route is not taken. Then
again Steve was worried about upgrades breaking things.

Dire may be an understatement!

Steve has my sympathy.

John

> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Nothing seems to be going right in this thread!

Who is Steve?

Sorry Scott!

John

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:43364302$0$14057$da0feed9@.news.zen.co.uk...
> Hi Erland/Steve
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns96DBF14D14D5AYazorman@.127.0.0.1...
>> John Bell (jbellnewsposts@.hotmail.com) writes:
>>> If you are not writing a new interface then you are probably better
>>> using a fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If
>>> each part is a fixed length then you don't need a separator, but it may
>>> help the users differentiate the different parts. I would have expect
>>> that you can determine the fact that an invoice is a referral by a
>>> different means.
>>
>>
>> Now, how is that for a user interface? This is free text, and users will
>> mangle it anyway, but they have better odds with a separator than fixed
>> length.
> That was my point, guess I should have said it that way! The users will
> (probably) not like having to enter a coded string, even if you keep it as
> simple! They may even deliberately mangle it if there is no value in it
> for themselves.
>>
>> I have not intervened into this thread before, because I don't have
>> much advice to offer Steve in this dire situation.
>>
> It seems to me, that if this an off the shelf package the benefits has
> been lost some time ago, if this is a bespoke development then it has not
> been specified thoroughly enough.
>> Hm, possibly could have a trigger on the table that loaded a new table
>> with the fields split in pieces. Still iffy with regards to the vendor,
>> but at least no existing object is changed. (Then again, being on the
>> vendor side of the fence, I know what I would think if I found that one
>> of our customers had added a trigger to one of our tables.)
> Writing a trigger may still invalidate the contract as the transactions
> will be effected, the vendor may have the right to charge improportate
> fees to fix a problem even if a problem is not related to this table. But
> if Steve has already tweeked things that could already be the case! A
> trigger would be useful for validation even if the replication route is
> not taken. Then again Steve was worried about upgrades breaking things.
> Dire may be an understatement!
> Steve has my sympathy.
> John
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>>
>> Books Online for SQL Server SP3 at
>> http://www.microsoft.com/sql/techin.../2000/books.asp
>>|||Hi Scott

Non taken!

You can't always give good advice, sometimes the best you can do is throw
ideas into the pot.

Using a scheduled job may be less of an preblem when considering
invalidating the contract! Although running it once a minute may cause
issues such as blocking.

I have assumed that the information you are entering is not available on
this system, therefore it is not possible to pick it up at report time from
other fields.

John

"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127610853.841b341db0737c3d0f8b5850b289ecae@.t eranews...
> Scott Marquardt opined thusly on Sep 24:
>> John Bell opined thusly on Sep 24:
>>> Hi
>>>
>>> If you are not writing a new interface then you are probably better
>>> using a
>>> fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part
>>> is
>>> a fixed length then you don't need a separator, but it may help the
>>> users
>>> differentiate the different parts. I would have expect that you can
>>> determine the fact that an invoice is a referral by a different means.
>>
>> Well, we could get into "I would have expected" exchanges, or we can skip
>> the stuff I already know -- and posted in the original post. With all due
>> respect, "duh."
>>
>> Good grief. Outta here.
> John., I'm going to apologize for a short temper there. It was born of
> impatience.
> My principal interest was in hearing how anyone else might PARSE the field
> to recover the data. Erland's trigger idea isn't bad for WHAT I do with
> it.
> "Invoice for 1/2 inch puce widgets and a can of beans,
> 100-3123@.WidgetMart"
> "Refund for can of beans, 100-3123&WidgetMart"
> The second one would need to be tagged as a referral to the first one. But
> if that's my basic technique, they're parse out the same way.
> --
> Scott|||--CELKO-- opined thusly on Sep 24:
>>> My principal interest was in hearing how anyone else might PARSE the field to recover the data. Erland's trigger idea isn't bad for WHAT I do with it. "Invoice for 1/2 inch puce widgets and a can of beans, 100-3123@.WidgetMart" <<
> Since free text requires human intelligence, we get people to encode
> it. If the text is short and styled the same way, you can find AI
> programs that are pretty good these days. Of course this has nothing
> to do with RDBMS and nobody in their right mind would try to do it in
> SQL.

Hey! How'd you find out about my condition?

;-)

Scott|||Stu opined thusly on Sep 24:
> Scott,
> Have you considered an ActiveX script in a DTS package to shred the
> data periodically (you can use the SQL Scheduler to run it once a
> minute). We do this to parse out syslog tables; it's kludgy, but its
> consistent. While not as immediate as a trigger to populate a table,
> it does keep you from modifying the vendor database (which, if your
> vendor has the ability to update their database, could be a big
> benefit).
> Obviously, your users would have to be trained on how to enter data
> consistently, and you'd probably want some sort of validation to pick
> up on coded entries that don't meant your
> data-followed-by-a-comma-followed-by-more-data rules. You may even
> look at Regular Expressions inside your DTS package to help with that.
> Just thinking aloud,
> Stu

Less intrusive, yeah. I'll be needing to roll my own shredder, though --
unless you'd care to name some good controls. Haven't tried any, ever,
really. It's been nice to have never needed to!

I anticipate we'll see now more than 3000 such records a year, so this is
probably something that could be done at runtime. Users won't be running
anything that queries a result of this, so parsing it all out when running
reports (not sure where to implement that, yet, but it won't be in the
application itself) is an inelegant but tenable option.

Scott|||John Bell opined thusly on Sep 25:

> That was my point, guess I should have said it that way! The users will
> (probably) not like having to enter a coded string, even if you keep it as
> simple! They may even deliberately mangle it if there is no value in it for
> themselves.

In this case, the only users needing to enter these have the only stake in
these particular data points. This isn't something they'll grumble about;
in this case, they're the ones demanding it. ;-)

The vendor's likely to be cool with this. I'm documenting things pretty
well, and a few of my other hacks to the system may end up as features.
They have an aggressive development cycle and are responsive. Best of
worlds for trying stuff.

Steve ;-)|||Scott Marquardt at wit's-end:

> Less intrusive, yeah. I'll be needing to roll my own shredder, though --
> unless you'd care to name some good controls. Haven't tried any, ever,
> really. It's been nice to have never needed to!

> I anticipate we'll see now more than 3000 such records a year, so this is
> probably something that could be done at runtime. Users won't be running
> anything that queries a result of this, so parsing it all out when running
> reports (not sure where to implement that, yet, but it won't be in the
> application itself) is an inelegant but tenable option.

More stuff to drive you even crazier - Log Parser from Microsoft:
http://www.microsoft.com/technet/sc...er/default.mspx

It'll either drive you over the edge, or restore your sanity.

GeoSynch|||GeoSynch opined thusly on Sep 27:
> Scott Marquardt at wit's-end:
>> Less intrusive, yeah. I'll be needing to roll my own shredder, though --
>> unless you'd care to name some good controls. Haven't tried any, ever,
>> really. It's been nice to have never needed to!
>> I anticipate we'll see now more than 3000 such records a year, so this is
>> probably something that could be done at runtime. Users won't be running
>> anything that queries a result of this, so parsing it all out when running
>> reports (not sure where to implement that, yet, but it won't be in the
>> application itself) is an inelegant but tenable option.
> More stuff to drive you even crazier - Log Parser from Microsoft:
> http://www.microsoft.com/technet/sc...er/default.mspx
> It'll either drive you over the edge, or restore your sanity.

No. You don't understand. We're so understaffed and overbooked that I could
have this operating right now somewhere, in a scheduled task or a script,
and I might not even know about it. I can't begin to count the number of
things we've been grateful for and implemented without documenting it.

That's the world of education, folks! ;-)

Hey! Download to the usual folder asks whether to overwrite. Guess I've got
it already! ;-)

Scott|||Scott Marquardt opined thusly on Sep 25:
> John Bell opined thusly on Sep 25:
>> That was my point, guess I should have said it that way! The users will
>> (probably) not like having to enter a coded string, even if you keep it as
>> simple! They may even deliberately mangle it if there is no value in it for
>> themselves.
> In this case, the only users needing to enter these have the only stake in
> these particular data points. This isn't something they'll grumble about;
> in this case, they're the ones demanding it. ;-)

OK, this is maybe (maybe) the kludgiest thing I've done in a while. If
anyone has any ideas for cleaning it up, I'm all ears. It works, though.

| select
| case
| when charindex(' ',ltrim(rtrim(comment))) < charindex('@.',ltrim(rtrim(comment))) and charindex(' ',ltrim(rtrim(comment))) <> 0 then
| right(left(comment, charindex('@.',ltrim(rtrim(comment)))-1) ,charindex(' ', reverse(left( ltrim(rtrim(comment)), charindex('@.',ltrim(rtrim(comment)))-2))))
| else
| left( ltrim(rtrim(comment)), charindex('@.',ltrim(rtrim(comment)))-1)
| end
| as InvoiceNumber,
| case
| when charindex(' ',ltrim(rtrim(reverse(comment)))) < charindex('@.',ltrim(rtrim(reverse(comment)))) and charindex(' ',ltrim(rtrim(comment))) <> 0 then
| left(right(comment, charindex('@.',ltrim(rtrim(reverse(comment))))-1),charindex(' ',ltrim(rtrim(reverse(comment)))))
| else
| reverse(left( ltrim(rtrim(reverse(comment))), charindex('@.',ltrim(rtrim(reverse(comment))))-1))
| end
| as VendorID, case when charindex('@.@.',comment) > 0 then 'True' else 'False' end as Reference, comment
| from bill_lineitems
| where comment like '%@.%'

That translates into this:

This allows for putting an invoice@.vendor (or invoice@.@.vendor) anywhere in
a description field, so long as it's set off on either side by spaces or
appears at the very beginning or end of the string. You can't, for example,
use it at the end of a grammatical sentence and append a period.

These work:

invoice@.vendor yada yada yada
yada yada yada invoice@.vendor
yada yada yada invoice@.vendor yada yada yada
yada yada yada invoice@.vendor yada yada yada
invoice@.vendor
invoice@.vendor [with spaces at the end as well]

These will not work:

There are problems with this invoice@.vendor!
Use the invoice that was not paid (invoice@.vendor)

Also, you can't use more than one such thing in a single record's
description.

Scott

parsing varchar fields

What are some good strategic approaches to using freeform text fields for
data that needs to be queried? We have a product whose tables we can't
change, and I need to count on a "description" field for storing a value.
Two, actually. I'm thinking of adopting this convention:
InvoiceNumber@.VendorAcronym
There'd be a lot of vendors.
Additional issue: sometimes these values would be referred to in the
description field, and I'd need to distinguish them as referrals rather
than as original recorded instances of the values. For that, I imagined
either:
InvoiceNumber@.@.VendorAcronym
or
InvoiceNumber&VendorAcronym
InvoiceNumber//VendorAcronym
etc. -- something like that.
I'm just wondering if there's best practice for doing anything this stupid
(hey, I'm stuck with this as our only option just now; hopefully it's only
temporary). How to parse out whatever I end up implementing -- well, it
needs to be tractable.
Thoughts?
ScottScott,
Instead of changing the existing product's tables why not create an
additional table(s) to store these values and then write your queries using
joins.
HTH
Jerry
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539. cb0c208b60e605e1a298926bee12e52f@.teranew
s...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Hi
It is not clear what you are trying to do, but creating a concatenated field
is probably not a good idea. Separate fields and a status will be far easier
to maintain and understand.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539. cb0c208b60e605e1a298926bee12e52f@.teranew
s...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Hi
It is not clear what you are trying to achieve, but creating a concanenated
field is probably not a good idea. Separate fields and a status will be alot
easier to maintain and understand.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539. cb0c208b60e605e1a298926bee12e52f@.teranew
s...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Jerry Spivey opined thusly on Sep 24:
> Scott,
> Instead of changing the existing product's tables why not create an
> additional table(s) to store these values and then write your queries usin
g
> joins.
Jerry, John -- sorry for the lack of clarity.
We have a web app from a vendor, and in this particular case I don't want
to hack anything (I've done some hacks where practical, and will be doing
more, but there's some intractable stuff in view of upgrade management and
my available time).
Our people need to reference invoice numbers and vendors in a description
field, so I'm wanting to accommodate them until such time comes along that
the software supports something like that. But they're wanting to be able
to generate reports on the vendor and invoice numbers, so I'm wanting to do
this kludgey thing as elegantly as kludges can be. I figured if I specified
that they enter the two fields in some string format that was simple enough
for them this would be practical from their standpoint, and if I could
parse it it'd be practical from mine. The concatenation John's concerned
about is mostly as a way of avoiding separate ways of identifying the
"fields". That would be a bit onerous for the staff entering the
information, even if it did make parsing easier.
So imagine a lot of records with brief descriptions of invoices, with
vendor and invoice #s embedded in the descriptions. Oh, yeah, almost forgot
-- no chance of their being more than one of these per description (per
record).
Scott|||Hi
I am not sure why you think the users would want to enter some cryptic code
rather than exact values in two separate fields. That is more of a usability
with the UI, for instance if your parts were both numeric you may be slowing
your users down forcing them to use non-keypad characters that require
shifting. You should also look at the business processes, for instance if
you processed everything for one vendor together then retaining the vendor
information would remove the need to type it in again.
If you need to concatenate the values for display purposes then you can do
that in the code for your stored procedure or create a view.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127546922. 53434faa9cedec41603cbc091e9f06a8@.teranew
s...
> Jerry Spivey opined thusly on Sep 24:
> Jerry, John -- sorry for the lack of clarity.
> We have a web app from a vendor, and in this particular case I don't want
> to hack anything (I've done some hacks where practical, and will be doing
> more, but there's some intractable stuff in view of upgrade management and
> my available time).
> Our people need to reference invoice numbers and vendors in a description
> field, so I'm wanting to accommodate them until such time comes along that
> the software supports something like that. But they're wanting to be able
> to generate reports on the vendor and invoice numbers, so I'm wanting to
> do
> this kludgey thing as elegantly as kludges can be. I figured if I
> specified
> that they enter the two fields in some string format that was simple
> enough
> for them this would be practical from their standpoint, and if I could
> parse it it'd be practical from mine. The concatenation John's concerned
> about is mostly as a way of avoiding separate ways of identifying the
> "fields". That would be a bit onerous for the staff entering the
> information, even if it did make parsing easier.
> So imagine a lot of records with brief descriptions of invoices, with
> vendor and invoice #s embedded in the descriptions. Oh, yeah, almost
> forgot
> -- no chance of their being more than one of these per description (per
> record).
> --
> Scott|||John Bell opined thusly on Sep 24:
> Hi
> I am not sure why you think the users would want to enter some cryptic cod
e
> rather than exact values in two separate fields.
Developing more fields in this N-tier application whose upgrades are
controlled by a vendor whose dev I'm not privy to, isn't an option. If I
could do that, I would. I'm not posting to inquire about better
alternatives; I'm wanting to wring juice out of an unfortunate turnip.

> That is more of a usability
> with the UI, for instance if your parts were both numeric you may be slowi
ng
> your users down forcing them to use non-keypad characters that require
> shifting. You should also look at the business processes, for instance if
> you processed everything for one vendor together then retaining the vendor
> information would remove the need to type it in again.
> If you need to concatenate the values for display purposes then you can do
> that in the code for your stored procedure or create a view.
Again, beyond my control. I've done a few necessary hacks of the software
(both the asp and some sprocs), but I can't expand the number of such edits
beyond reason. Next vendor upgrade, I'd be flailing about.
The users are more worried about being able to have reports on data they're
unfortunately left to enter in a free-form description field, type varchar.
My posting is to inquire whether a string formatting convention I'm
considering is the best I can do under that circumstance. I'd be glad of
any hints at how to most efficiently query the data as well.
Scott|||Hi
If you are not writing a new interface then you are probably better using a
fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part is
a fixed length then you don't need a separator, but it may help the users
differentiate the different parts. I would have expect that you can
determine the fact that an invoice is a referral by a different means.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127575738. f429cee989a6daeb985244c70f0bb497@.teranew
s...
> John Bell opined thusly on Sep 24:
> Developing more fields in this N-tier application whose upgrades are
> controlled by a vendor whose dev I'm not privy to, isn't an option. If I
> could do that, I would. I'm not posting to inquire about better
> alternatives; I'm wanting to wring juice out of an unfortunate turnip.
>
> Again, beyond my control. I've done a few necessary hacks of the software
> (both the asp and some sprocs), but I can't expand the number of such
> edits
> beyond reason. Next vendor upgrade, I'd be flailing about.
> The users are more worried about being able to have reports on data
> they're
> unfortunately left to enter in a free-form description field, type
> varchar.
> My posting is to inquire whether a string formatting convention I'm
> considering is the best I can do under that circumstance. I'd be glad of
> any hints at how to most efficiently query the data as well.
>
> --
> Scott|||John Bell opined thusly on Sep 24:
> Hi
> If you are not writing a new interface then you are probably better using
a
> fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part i
s
> a fixed length then you don't need a separator, but it may help the users
> differentiate the different parts. I would have expect that you can
> determine the fact that an invoice is a referral by a different means.
Well, we could get into "I would have expected" exchanges, or we can skip
the stuff I already know -- and posted in the original post. With all due
respect, "duh."
Good grief. Outta here.
Scott|||John Bell (jbellnewsposts@.hotmail.com) writes:
> If you are not writing a new interface then you are probably better
> using a fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If
> each part is a fixed length then you don't need a separator, but it may
> help the users differentiate the different parts. I would have expect
> that you can determine the fact that an invoice is a referral by a
> different means.
Now, how is that for a user interface? This is free text, and users will
mangle it anyway, but they have better odds with a separator than fixed
length.
I have not intervened into this thread before, because I don't have
much advice to offer Steve in this dire situation.
Hm, possibly could have a trigger on the table that loaded a new table
with the fields split in pieces. Still iffy with regards to the vendor,
but at least no existing object is changed. (Then again, being on the
vendor side of the fence, I know what I would think if I found that one
of our customers had added a trigger to one of our tables.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Parse sting with quotes in SQL

If, I have a string as follows , how Do I parse these quotes, such that it gives me 3 fields.

Eg of string is

Status changes "PASS" "FAIL" By "PW" '"/3/12/2007"

Resuls set should look like:

Column 1 Column 2 Column 3

PASS FAIL 3/12/2007

Pl advise.

Use a combination of the replace() function and Jen Suessmeyer's split function.

If there are no 'spaces' in the data, a single pass replacing the double quote with an empty string would work.

Split Function (Jens Suessmeyer)
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

|||Do you really need them in separate columns? If rows will do, then you could use Itzik's split function. Check insidetsql.com for the script.

Rob|||

this is a code to break a string "first"Second"third"Forth"Fifth"Sixth"" into First Second third forth etc however u have to pass and Extra " in the end of the string

declare @.Group varchar(8000)
Create table #Temp(GroupName varchar(100))
Declare @.Cnt int,@.CharLocation int,@.Previous int,@.Extent int
Set @.Group='"First"Second"Third"Forth"Fifth"Sixth""'
Set @.Cnt=1
Set @.Previous=1
Set @.Extent=1
While @.Cnt<>Len(@.Group)
BEgin
if substring(@.Group,@.Cnt,1)='"'
BEgin
--Select Substring(@.GroupName,@.Previous,@.Extent-1)
insert into #Temp values(Substring(@.Group,@.Previous,@.Extent-1))

sET @.Previous=@.Cnt+1
Set @.Extent=0
END
Set @.Extent=@.Extent+1
Set @.Cnt=@.Cnt+1
END
select * from #temp where GroupName <>''
drop table #Temp

try doing some manipulations and see if this code is of any help

RegarDs,

Jacx

|||I need this to be split into columns, the code above splits it into rows|||Jen's Split function I offered does split into columns -but of course you didn't try it, did you?|||

I did try Jens split function. I created that in the dbo, BUT it created that function as a type='TF', Not 'FN'.

So when I actually call the function, it gives err message invalid object.

This is how I call the Split Function .

select dbo.Split ('"mod PEN" "approved" "by wagety" "3/21/2007"','""')

and it errs out with the message below, though the split object does exist in the db. Any suggestions.

Thanks much.

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.Split'.

|||

You made a simple error. The function is a table valued function (TVF or "TF") That means that you use it like a table.

Instead of:

select dbo.Split ('"mod PEN" "approved" "by wagety" "3/21/2007"','""')

Try:

SELECT * FROM dbo.Split( '"mod PEN" "approved" "by wagety" "3/21/2007"','""')

Also, recall my earlier comment about needing to use the replace() function to remove the quotes and/or commas.|||

I have 2 issues:

1. I need the string split into multiple columns, not just 1. ie COL1 Shoud have "mod PEN" ,

Col2 should hv "approv" Col3 should hv "by wagety" and Col4 "3/21/2007"'. This split function returns everthying in 1 column.

2. Also, I have these values that are to be split, stored in a table. eg there is a table called notes, with a field called status.

That field status will have that records, which will have values such as "mod PEN" "approved" "by wagety" "3/21/2007"' this entire string will be 1 row. How can I use this function, for that tbl notes?

|||

1. I thought I understood that was your request.

Jen's split function will separte into different columns, if you are having difficulty, you may not have identified and be handling the delimiters correctly.

2. You have completely confused me now.

I previously understood that you wanted to separate

"PASS" "FAIL" By "PW" '"/3/12/2007"

into separate parts -at least that is what your example seemed to indicate that you wanted.

Now, it seems that you want to store all of the parts in one field called 'Status'.

Why not just store the current string in the Status field and be done?

|||

1. This is how I call Jens split function and it return all the values In 1 Single Column: eg

select * from dbo.Split ('"mod PEN" "approved" "by wagety" "3/21/2007"','""')

When you execute this, it returns the following , into 1 Column called SplitValue . I need the values between the doubel quotes, split into different columns, ie in this case 4 diff cols, ie Col1 should have mod PEN, Col2 should have approved, Colum # 3 shd have By wagety, Col 4 should have 3/21/2007. Can you pl assist

Output returns the foll:

OccurenceId SplitValue
-- --
1 "mod PEN" "approved" "by wagety" "3/21/2007"

Parse sting with quotes in SQL

If, I have a string as follows , how Do I parse these quotes, such that it gives me 3 fields.

Eg of string is

Status changes "PASS" "FAIL" By "PW" '"/3/12/2007"

Resuls set should look like:

Column 1 Column 2 Column 3

PASS FAIL 3/12/2007

Pl advise.

Use a combination of the replace() function and Jen Suessmeyer's split function.

If there are no 'spaces' in the data, a single pass replacing the double quote with an empty string would work.

Split Function (Jens Suessmeyer)
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

|||Do you really need them in separate columns? If rows will do, then you could use Itzik's split function. Check insidetsql.com for the script.

Rob|||

this is a code to break a string "first"Second"third"Forth"Fifth"Sixth"" into First Second third forth etc however u have to pass and Extra " in the end of the string

sET @.Previous=@.Cnt+1
Set @.Extent=0
END
Set @.Extent=@.Extent+1
Set @.Cnt=@.Cnt+1
END
select * from #temp where GroupName <>''
drop table #Temp

try doing some manipulations and see if this code is of any help

RegarDs,

Jacx

|||I need this to be split into columns, the code above splits it into rows|||Jen's Split function I offered does split into columns -but of course you didn't try it, did you?|||

I did try Jens split function. I created that in the dbo, BUT it created that function as a type='TF', Not 'FN'.

So when I actually call the function, it gives err message invalid object.

This is how I call the Split Function .

select dbo.Split ('"mod PEN" "approved" "by wagety" "3/21/2007"','""')

and it errs out with the message below, though the split object does exist in the db. Any suggestions.

Thanks much.

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.Split'.

|||

You made a simple error. The function is a table valued function (TVF or "TF") That means that you use it like a table.

Instead of:

select dbo.Split ('"mod PEN" "approved" "by wagety" "3/21/2007"','""')

Try:

SELECT * FROM dbo.Split( '"mod PEN" "approved" "by wagety" "3/21/2007"','""')

Also, recall my earlier comment about needing to use the replace() function to remove the quotes and/or commas.|||

I have 2 issues:

1. I need the string split into multiple columns, not just 1. ie COL1 Shoud have "mod PEN" ,

Col2 should hv "approv" Col3 should hv "by wagety" and Col4 "3/21/2007"'. This split function returns everthying in 1 column.

2. Also, I have these values that are to be split, stored in a table. eg there is a table called notes, with a field called status.

|||

1. I thought I understood that was your request.

Jen's split function will separte into different columns, if you are having difficulty, you may not have identified and be handling the delimiters correctly.

2. You have completely confused me now.

I previously understood that you wanted to separate

"PASS" "FAIL" By "PW" '"/3/12/2007"

into separate parts -at least that is what your example seemed to indicate that you wanted.

Now, it seems that you want to store all of the parts in one field called 'Status'.

Why not just store the current string in the Status field and be done?

|||

1. This is how I call Jens split function and it return all the values In 1 Single Column: eg

select * from dbo.Split ('"mod PEN" "approved" "by wagety" "3/21/2007"','""')

Output returns the foll:

OccurenceId SplitValue
-- --
1 "mod PEN" "approved" "by wagety" "3/21/2007"

Wednesday, March 21, 2012

Parse fields

Hi there,

I am re-organizing the database. We used to have field 'names' in our
table for our first name and last name. However, I want to have those
names in different field.

FYI, I have two different tables in different databases. The A
database contains A table with 'names" field. The B database contains
B table with 'fname' and 'lname' I want to copy data A table to B
table.

How can I parse names field into first name and last name fields?

Here are some examples which are in the database.
(
id names
01 John Doe
02 John & Jane Doe
03 Mr & Mrs Doe
) something like this. It might contain '&' and two names. If there
are two names, then use first name.

Thanks guys,Do something like this
declare @.tbla table ( id int ,names varchar(100))
insert into @.tbla values (1,'John Doe')
insert into @.tbla values (1,'John & Jane Doe')
insert into @.tbla values (1,'Mr & Mrs Doe ')

select
id,
firstname = case when names like '%&%' then names
else
case when charindex(' ',names) 0
then substring(names,1,charindex(' ',names) - 1)
else names
end
end ,
lastname = case when names like '%&%' then ''
else
case when charindex(' ',names) 0
then substring(names,charindex(' ',names)+ 1,len(names) -
charindex(' ',names))
else names
end
end

from @.tbla

M A Srinivas

On Mar 9, 1:20 pm, "portCo" <woos...@.gmail.comwrote:

Quote:

Originally Posted by

Hi there,
>
I am re-organizing the database. We used to have field 'names' in our
table for our first name and last name. However, I want to have those
names in different field.
>
FYI, I have two different tables in different databases. The A
database contains A table with 'names" field. The B database contains
B table with 'fname' and 'lname' I want to copy data A table to B
table.
>
How can I parse names field into first name and last name fields?
>
Here are some examples which are in the database.
(
id names
01 John Doe
02 John & Jane Doe
03 Mr & Mrs Doe
) something like this. It might contain '&' and two names. If there
are two names, then use first name.
>
Thanks guys,

|||On Mar 9, 10:46 am, masri...@.gmail.com wrote:

Quote:

Originally Posted by

Do something like this
declare @.tbla table ( id int ,names varchar(100))
insert into @.tbla values (1,'John Doe')
insert into @.tbla values (1,'John & Jane Doe')
insert into @.tbla values (1,'Mr & Mrs Doe ')
>
select
id,
firstname = case when names like '%&%' then names
else
case when charindex(' ',names) 0
then substring(names,1,charindex(' ',names) - 1)
else names
end
end ,
lastname = case when names like '%&%' then ''
else
case when charindex(' ',names) 0
then substring(names,charindex(' ',names)+ 1,len(names) -
charindex(' ',names))
else names
end
end
>
from @.tbla
>
M A Srinivas
>
On Mar 9, 1:20 pm, "portCo" <woos...@.gmail.comwrote:
>
>
>

Quote:

Originally Posted by

Hi there,

Quote:

Originally Posted by

I am re-organizing the database. We used to have field 'names' in our
table for our first name and last name. However, I want to have those
names in different field.

Quote:

Originally Posted by

Quote:

Originally Posted by

How can I parse names field into first name and last name fields?

Quote:

Originally Posted by

Quote:

Originally Posted by

Thanks guys,- Hide quoted text -

>
- Show quoted text -

The following will also work:

SELECTid,
names,
RTRIM(SUBSTRING(names, 1, CHARINDEX(' ', names) - 1)) fname,
CASE
WHEN CHARINDEX('&', names) 0 THEN
LTRIM(SUBSTRING(names, CHARINDEX('&', names) + 1, LEN(names)))
ELSE
LTRIM(SUBSTRING(names, CHARINDEX(' ', names), LEN(names)))
END lname
FROMtblA|||On Mar 9, 12:20 am, "portCo" <woos...@.gmail.comwrote:

Quote:

Originally Posted by

Thanks guys|||>How can I parse names field [sic] into first name and last name fields [sic]? <<

Don't do it. Name handling is an ugly problem and if you have to do
this on a regular basis get a package designed for this kind of work.
Some companies are Group 1 Software, SSA (used to have a great booklet
on this topic), Melissa Data Corporation and Promark Software Inc.

Their software handles mailing lists and you can get a review copy
from Melissa Data. They do not choke on names like "John Paul van der
Poon" and worse.|||On Mar 9, 2:20 pm, "portCo" <woos...@.gmail.comwrote:

Quote:

Originally Posted by

hi,

select id,
fname = substring(ltrim(rtrim(names)),1,charindex('
',ltrim(rtrim(names))) - 1),
lname = reverse(substring(reverse(ltrim(rtrim(names))),
1,charindex(' ',reverse(ltrim(rtrim(names)))) - 1))
from Atable

HTH

Tuesday, March 20, 2012

parametized inserts in a loop

Hi,

I have a for next loop that I am using to write some data into a table.

The problem I have is one of the fields is an @.parameter

The relevant bit of VB.net code is:

Dim dbpm_theDateTimeAs System.Data.IDataParameter =New System.Data.SqlClient.SqlParameter

dbConnection.Open()

For intCounterAsInteger = 0To ds.Tables(0).Rows.Count - 1

querystring ="Insert into tblActionsToDo ([TheDT], [username]) VALUES (@.theDateTime, '" & username &"')"

dbpm_theDateTime =New System.Data.SqlClient.SqlParameter

dbpm_theDateTime.ParameterName ="@.theDateTime"

dbpm_theDateTime.Value = tmpstrDT

dbpm_theDateTime.DbType = System.Data.DbType.DateTime

dbCommand.Parameters.Add(dbpm_theDateTime)

dbCommand.CommandText = querystring

rowsAffected = dbCommand.ExecuteNonQuery

The first time through the for next loop, everything works fine. The next time though I get an error that @.theDateTime has already been defined. To be fair, it is true; but how do I get round this? I've tried making the @.parameter a string so that I can manipulate it as "@.parameter" & intcounter, and thus have a uniquely-named varaible for every loop, but it doesn't work.

thanks for any pointers

I fixed this one out on my own.

All I had to do was move everything outside of the loop except for

dbpm_theDateTime.Value = tmpstrDT

and it works just fine :)

Friday, March 9, 2012

Parameters input field size

Hi,

is it possible to change the appearence of input fields for parameters on the report server? My parameter is Multi-value with quite large amount of available values. On report server, user can (without scrolling) see only the first value. Parameter values are quite long, so user has to move alternally with both vertical and horizontal scrollbars to find the right value.

Thanks

Janca

I don't believe there is a way to do that directly, but you can build your own UI and include the ReportViewer control on the page. Take a look at the example at:

http://technet.microsoft.com/en-us/library/aa179197(SQL.80).aspx

Larry

parameters in sql server 2000

Hi!

I need how to pass variables struct witt n fields and n records
to procedures in transac sql in one bbdd sql server 2000,
this parameters are in/out.

Thanks.Sounds like an array:

http://www.sommarskog.se/arrays-in-sql.html

--
David Portas
SQL Server MVP
--

Wednesday, March 7, 2012

Parameters for sp that depend on the current row fields

How can I specify parameters to a store procedure that depend on a row
without using ado.net in code behind?
The problem is that I use 5 parameters for my store procedure. 2 are report
parameters, and I want other 3 to be specified by the fields of the current
row, in this way the returned value by the store procedure depends on certain
values of each row.
I currently use code behind with Ado.Net to specify the parameters but
opening and closing the connection takes some time (depending on the # of
rows), if I somewhat could call the sp from the given field in the row in
reporting services it would be way faster. Does someone knows of a way to do
this?
Thanks.You can embed a subreport in a field of the current row and pass those
fields and parameters to the subreport (I do this). Give it a try, should be
a lot easier and cleaner.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"gchinl" <gchinl@.discussions.microsoft.com> wrote in message
news:64EF7852-D7B0-4479-8B64-971E93E88B9A@.microsoft.com...
> How can I specify parameters to a store procedure that depend on a row
> without using ado.net in code behind?
> The problem is that I use 5 parameters for my store procedure. 2 are
> report
> parameters, and I want other 3 to be specified by the fields of the
> current
> row, in this way the returned value by the store procedure depends on
> certain
> values of each row.
> I currently use code behind with Ado.Net to specify the parameters but
> opening and closing the connection takes some time (depending on the # of
> rows), if I somewhat could call the sp from the given field in the row in
> reporting services it would be way faster. Does someone knows of a way to
> do
> this?
> Thanks.

Saturday, February 25, 2012

Parameters

Hi all,
Is it possible to group parameters fields into logical groups in report
view?
eg.
filters:
start date:
end date:
colors:
table border:
chart background:
...
TIA,
KamelThere would be nice to have separate control to manage parameters
presentation and logic.
Sometimes there is a need to have a lot of parameters (over 20) and
there is a presentation problem in reportviewer.
Kamel
kamel wrote:
> Hi all,
> Is it possible to group parameters fields into logical groups in report
> view?
> eg.
> filters:
> start date:
> end date:
> colors:
> table border:
> chart background:
> ...
> TIA,
> Kamel

Monday, February 20, 2012

parameterized update sp_executesql

I am trying to limit network traffic by only sending fields that have actually changed to an update stored procedure. I am obviously not doing this correctly as I am rather new to this. This is a small version of my sproc that I am using to test the workings. I am getting 'Error converting data type nvarchar to int'. If you can help me get this easy query working, I would very much like your help to use the EXEC (sql) where sql is an sp_executesql statement because some of my update fields are much larger than 4000 nvarchar. The following statement was working when I was using the second version. If I am trying to update an integer foreign key field, shouldn't I try to use the parameters to send an integer value for that field, rather than an nvarchar? As you can see, I really need to get some not so basic info on this. I have done hours of research on the net and can't find some of these simple background principals. I also question whether I am saving enough network traffic to validate the cost in processing for building this query and executing it. Any help you can provide will be greatly appreciated.

Non working

CREATE PROCEDURE tspJobs_Updatetest
(
@.JobID int=0,
@.AddressID int=0,
@.AddressChg int=0,
@.CustomerID int=0,
@.CustomerChg int=0,
@.ContactID int=0,
@.ContactChg int=0,
@.PlanID int=0,
@.PlanChg int=0,

)
AS

DECLARE @.updateClause nvarchar(1000);
DECLARE @.whereClause nvarchar(500);
DECLARE @.sqlStatement nvarchar(4000);
DECLARE @.paramDefinition nvarchar(1000);
DECLARE @.valuesDefinition nvarchar(1500);

--Set Where Clause and parameter statement

IF @.JobID > 0
BEGIN
SET @.whereClause = ' WHERE job_id = @.update_job_id '
SET @.paramDefinition = N'@.update_job_id int '
SET @.valuesDefinition = N'@.update_job_id=@.JobID '

--Build Update Statement

SET @.updateClause = null

IF @.AddressChg > 0
BEGIN
SET @.paramDefinition = @.paramDefinition + N', @.update_address_id int'
SET @.valuesDefinition = @.valuesDefinition + N', @.update_address_id = @.AddressID'
IF @.updateClause IS NULL
SET @.updateClause = N'address_id = @.update_address_id'
ELSE
SET @.updateClause = @.updateClause + N', address_id = @.update_address_id'
END

IF @.CustomerChg > 0
BEGIN
SET @.paramDefinition = @.paramDefinition + N', @.update_customer_id int'
SET @.valuesDefinition = @.valuesDefinition + N', @.update_customer_id = @.CustomerID'
IF @.updateClause IS NULL
SET @.updateClause = N'customer_id = @.update_customer_id'
ELSE
SET @.updateClause = @.updateClause + N', customer_id = @.update_customer_id'
END

IF @.ContactChg > 0
BEGIN
SET @.paramDefinition = @.paramDefinition + N', @.update_contact_id int'
SET @.valuesDefinition = @.valuesDefinition + N', @.update_contact_id = @.ContactID'
IF @.updateClause IS NULL
BEGIN
IF @.ContactID = 0
SET @.updateClause = N'contact_id = NULL'
ELSE
SET @.updateClause = N'contact_id = @.update_contact_id'
END
ELSE
BEGIN
IF @.ContactID = 0
SET @.updateClause = @.updateClause + N', contact_id = NULL'
ELSE
SET @.updateClause = @.updateClause + N', contact_id = @.update_contact_id'
END
END

IF @.PlanChg > 0
BEGIN
SET @.paramDefinition = @.paramDefinition + N', @.update_plan_id int'
SET @.valuesDefinition = @.valuesDefinition + N', @.update_plan_id = @.PlanID'
IF @.updateClause IS NULL
BEGIN
IF @.PlanID = 0
SET @.updateClause = N'plan_id = NULL'
ELSE
SET @.updateClause = N'plan_id = @.update_plan_id'
END
ELSE
BEGIN
IF @.PlanID = 0
SET @.updateClause = @.updateClause + N', plan_id = NULL'
ELSE
SET @.updateClause = @.updateClause + N', plan_id = @.update_plan_id'
END
END

-- Complete SQL statement

IF NOT @.updateClause IS NULL
BEGIN
SET @.sqlStatement = 'UPDATE [dbo].[Jobs] SET ' + @.updateClause + @.whereClause
EXEC sp_executesql @.sqlStatement,@.paramDefinition, @.valuesDefinition
END
END
GO

Working, but using nvarchar for integer foreign key field updates

CREATE PROCEDURE tspJobs_Update
(
@.JobID int=0,
@.AddressID int=0,
@.AddressChg int=0,
@.CustomerID int=0,
@.CustomerChg int=0,
@.ContactID int=0,
@.ContactChg int=0,
@.PlanID int=0,
@.PlanChg int=0

)
AS

DECLARE @.updateClause varchar(3000);
DECLARE @.whereClause varchar(1000);
DECLARE @.sqlStatement nvarchar(4000);
DECLARE @.paramDefinition nvarchar(500)

--Set Where Clause and parameter statement

IF @.JobID > 0
BEGIN
SET @.whereClause = ' WHERE job_id = @.update_job_id'
SET @.paramDefinition = N'@.update_job_id int'

--Build Update Statement

SET @.updateClause = null

IF @.AddressChg > 0
BEGIN
DECLARE @.update_address_id varchar(15)
SET @.update_address_id = @.AddressID
IF @.updateClause IS NULL
SET @.updateClause = 'address_id = ' + @.update_address_id
ELSE
SET @.updateClause = @.updateClause + ', address_id = ' + @.update_address_id
END

IF @.CustomerChg > 0
BEGIN
DECLARE @.update_customer_id varchar(15)
SET @.update_customer_id = @.CustomerID
IF @.updateClause IS NULL
SET @.updateClause = 'customer_id = ' + @.update_customer_id
ELSE
SET @.updateClause = @.updateClause + ', customer_id = ' + @.update_customer_id
END

IF @.ContactChg > 0
BEGIN
DECLARE @.update_contact_id varchar(15)
SET @.update_contact_id = @.ContactID
IF @.updateClause IS NULL
BEGIN
IF @.update_contact_id IS NULL
SET @.updateClause = 'contact_id = NULL'
ELSE
SET @.updateClause = 'contact_id = ' + @.update_contact_id
END
ELSE
BEGIN
IF @.update_contact_id IS NULL
SET @.updateClause = @.updateClause + ', contact_id = NULL'
ELSE
SET @.updateClause = @.updateClause + ', contact_id = ' + @.update_contact_id
END
END

IF @.PlanChg > 0
BEGIN
DECLARE @.update_plan_id varchar(15)
SET @.update_plan_id = @.PlanID
IF @.updateClause IS NULL
BEGIN
IF @.update_plan_id IS NULL
SET @.updateClause = 'plan_id = NULL'
ELSE
SET @.updateClause = 'plan_id = ' + @.update_plan_id
END
ELSE
BEGIN
IF @.update_plan_id IS NULL
SET @.updateClause = @.updateClause + ', plan_id = NULL'
ELSE
SET @.updateClause = @.updateClause + ', plan_id = ' + @.update_plan_id
END
END-- Complete SQL statement

IF @.updateClause <> null
BEGIN
SET @.sqlStatement = 'UPDATE [dbo].[Jobs] SET ' + @.updateClause + @.whereClause
EXEC sp_executesql @.sqlStatement,@.paramDefinition, @.update_job_id=@.JobID
END
END
GO

I know that this is a huge post, but I have pretty much exhausted my resources.

Thanks,

I didn't analyze the logic of the SP. But you are making the SP more complicated than necessary. Here are the reasons:

1. You are using dynamic SQL which can hurt performance

2. Your dynamic SQL code doesn't protect against SQL injection. So you can compromise your database/server if you haave more SPs like this that take string parameters

3. Since the UPDATE statement is executed dynamically you need to grant UPDATE permissions to all callers of the SP. This increases the attack surface of the database and grants more permissions to users than necessary. And it kind of defeats the purpose of having a SP. You might as well form the UPDATE statement on the client-side and execute it directly. You will get the same or probably better performance

4. Debugging code using dynamic SQL can be hard and difficult to maintain also as you have encountered

5. Lastly, you don't really gain that much by updating only the necessary columns. SQL Server has to do lot of work to locate the row to update and after you have located the row for update it doesn't matter in most cases if you update one or all of the columns. The only case where it matters is if you have lot of variable length columns that can overflow or increase the row size which will require more work. Otherwise you might as well update the entire row. You don't save anything in terms of performance or efficiency.

If you are not passing the old values (i.e., columns that were not changed) then you can use alternate approach below which doesn't require dynamic SQL:

UPDATE dbo.Jobs

SET address_id = CASE WHEN AddressChg > 0 THEN @.AddressID ELSE address_id END

, ...

WHERE job_id = @.update_job_id

The main point to note is that keeping transactions/calls light-weight gives the best performance and improves overall utilization of the server.

|||

Thank you very much for your reply. I agree that the dynamic SQL use opened us up to some danger, although this is a windows form application. I would love to get rid of it, however, I don't understand how the statement that you have shown works. The case I understand of course, it's the 'ELSE address_id END' that I don't understand. I don't have this value (address_id) from my application, only the 'THEN AddressID'. Is this address_id coming from the table update mechanism? If so, this is definitely the answer to most of my problems.

|||

The address_id in the ELSE clause comes from the row you are updating. It refers to the column in the table. So it is a way to use the current value of address_id if there is no change. SQL is a set-based language so statements like UPDATE has logically a before and after image for each row & the operations happen in one shot. This allows you to swap values in two columns using an UPDATE statement without any intermediate storage:

update t

set a = b, b = a

The database engine handles all the dirty work for you.

|||It works beautifully. Thank you so much for this elegant, simple solution.

Parameterized queries - works in Access but not SQLS2k?

I have an application where users can enter data into any (or all) of 6 search fields,
to produce a filtered query.

This works fine using my Access version(see code below),
but as SQLS2k cannot use "IIF", I tried to replace these bits with
"CASE/WHEN/THEN/ELSE" lines, which does not work with numeric fields
as these cannot be "wild-carded" in the same way as Access allows.

Can anyone suggest a way forward that does not involve coding all the
possible permutations of "SELECT" blocks driven by lots of nested "IF/THEN/ELSE"s?

Hoping you can help
Alex

PARAMETERS
CurrentType Text,
CurrentCategoryID Long,
CurrentProductID Long,
CurrentClientID Long,
CurrentContractID Long,
FromDate DateTime,
ToDate DateTime;

SELECT
tAudit.AuditID,
tAudit.ActionType,
tAudit.ClientID,
tClients.ContactCompanyName,
tAudit.ContractID,
tContracts.ClientRef,
tAudit.ProductID,
tProducts.ProductName,
tAudit.CategoryID,
tCategories.CategoryName,
tAudit.Acknowledged,
tAudit.ValueAmount,
tAudit.DateStamp

FROM (((tAudit
LEFT JOIN tCategories
ON tAudit.CategoryID = tCategories.CategoryID)
LEFT JOIN tClients ON tAudit.ClientID = tClients.ClientID)
LEFT JOIN tContracts ON tAudit.ContractID = tContracts.ContractID)
LEFT JOIN tProducts ON tAudit.ProductID = tProducts.ProductID

WHERE (((tAudit.ActionType) Like IIf(IsNull([CurrentType]),"*",[CurrentType]))
AND ((tAudit.ClientID) Like IIf(IsNull([CurrentClientID]),"*",[CurrentClientID]))
AND ((tAudit.ContractID) Like IIf(IsNull([CurrentContractID]),"*",[CurrentContractID]))
AND ((tAudit.ProductID) Like IIf(IsNull([CurrentProductID]),"*",[CurrentProductID]))
AND ((tAudit.CategoryID) Like IIf(IsNull([CurrentCategoryID]),"*",[CurrentCategoryID]))
AND (([tAudit].[DateStamp]) Between [FromDate] And [ToDate]));(tAudit.ContractID) Like IIf(IsNull([CurrentContractID]),"*",[CurrentContractID])
Mdaaa...
tAudit.ContractID = isnull([CurrentContractID],tAudit.ContractID)
???|||Thanks Buser - works fine - much obliged to you!

Regards
Alex

Friday, March 30, 2012

Partial searches over a lot of fields

partial DISTINCT...

partial DISTINCT...

Wednesday, March 28, 2012

parsing varchar fields

parsing varchar fields

Parse sting with quotes in SQL

Parse sting with quotes in SQL

Wednesday, March 21, 2012

Parse fields

Tuesday, March 20, 2012

parametized inserts in a loop

Friday, March 9, 2012

Parameters input field size

parameters in sql server 2000

Wednesday, March 7, 2012

Parameters for sp that depend on the current row fields

Saturday, February 25, 2012

Parameters

Monday, February 20, 2012

parameterized update sp_executesql

Parameterized queries - works in Access but not SQLS2k?

Parameterized Expression

Blog Archive

About Me