Comparing multiple values in an iif clause

Hello,

Just want to ask..

Does anyone have an idea how i can compare multiple values in the iif() clause like

IDType IN (1, 2, 3, 4)

This one doesnt work

iif(IDType IN (1, 2, 3, 4), true, false)

Please help..

Thanks..

One option is to use an IIF function call like this:

=iif(Fields!IDType.Value = 1 OR Fields!IDType.Value = 2 OR Fields!IDType.Value = 3 OR ..., true, false)

In some cases (e.g. numeric values), you could use alternatives to the IIF function:
* =Choose(...)
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctchoose.asp
* =Switch(...)
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctswitch.asp

-- Robert

Comparing fields from multiple datasets

I have one report with two dataset (one from a table and one from a Cube).
Within my report are two tables, one uses the â'Tableâ' dataset and one uses
the â'Cubeâ' dataset. The two dataset are similar (i.e. they return similar
data/fields).
I would like to compare some of the fields from the first dataset with some
of the fields from the second dataset and then use conditional formatting to
turn the text fields red if the two fields do not match.
I know how to write a conditional formatting expression for a cell within a
table but I do not know how to compare a value from one dataset with a value
from another dataset. Note: I do not want to compare aggregates; I want the
comparisons to be at the detail row level. Is it even possible? If so, how
is it done?
Would one possibility be to compare the values/contents of textboxes at the
textbox level? If so, how is that done?
Any info on this topic would be greatâ?¦ Thanks=IIF(ReportItems!txtTextBox1.Value = ReportItems!txtTextBox2.Value,
"BLACK", "RED")
Matt A|||That's a nice idea but it won't work. I'm trying to compare values from two
different datasets that reside in two different tables. I tried your code
(plugging in my textbox names) and it errors out.
Error 2 [rsReportItemReference] The Color expression for the textbox
â'BalanceAmtâ' refers to the report item â'CompositBalanceâ'. Report item
expressions can only refer to other report items within the same grouping
scope or a containing grouping scope.
I welcome any more suggestions you may have though.
Thanks,
Bill
"reportdude" wrote:
> =IIF(ReportItems!txtTextBox1.Value = ReportItems!txtTextBox2.Value,
> "BLACK", "RED")
> Matt A
>|||Try placing each dataset in the same control such as a listbox to
encompass scope-

Comparing contents of one row to multiple rows in another table?

Hi,

I've a bunch of records that may contain data that I'm after. For example:

This is a fake title [electronic resource]. 1997.

I have a very small table (~10 rows) of things like '[electronic resource]'

Is there any way to see if my record contains any of the 'target' items in the other table?

Need more information. Can't able to follow you. Can you post more info pls...

Comparing all fields of two rows in few tables.

The problem scenario is this:
I have an application which uses few tables.
user can post data and can submit the form multiple times.
each posting is saved as each version.
Now that I have all data, I need to find the difference between 2
versions of the saved data
and report that he has modified these fields in the current submission
to the older.
what is the best way to compare all the fields(except for Primary key)
of 2 rows in a table.
I am thinking of doing this -
select
case when oldversion.Field1 <> newversion.Field1 then 'changed' as
Field1 end,
case when oldversion.Field2 <> newversion.Field2 then 'changed' as
Field2 end
from
(select * from Table1 where TablePKField = oldPKID ) oldversion
inner join
(select * from Table1 where TablePKField = NewPKID ) newversion
where newversion.commonField = newversion.CommonField
Please suggest me the best way of doing this without much performance
loss...
Thanks for your time.
G.Gees
if (select checksum_agg(checksum(*)) from t1)
<> (select checksum_agg(checksum(*)) from t2)
print 'different'
else
print 'probably the same'
"Gees" <gayathri.s@.gmail.com> wrote in message
news:1141032918.046365.85170@.j33g2000cwa.googlegroups.com...
> The problem scenario is this:
> I have an application which uses few tables.
> user can post data and can submit the form multiple times.
> each posting is saved as each version.
> Now that I have all data, I need to find the difference between 2
> versions of the saved data
> and report that he has modified these fields in the current submission
> to the older.
> what is the best way to compare all the fields(except for Primary key)
> of 2 rows in a table.
> I am thinking of doing this -
> select
> case when oldversion.Field1 <> newversion.Field1 then 'changed' as
> Field1 end,
> case when oldversion.Field2 <> newversion.Field2 then 'changed' as
> Field2 end
> from
> (select * from Table1 where TablePKField = oldPKID ) oldversion
> inner join
> (select * from Table1 where TablePKField = NewPKID ) newversion
> where newversion.commonField = newversion.CommonField
> Please suggest me the best way of doing this without much performance
> loss...
> Thanks for your time.
> G.
>|||Thanks Uri Dimant.
My problem also includes, quering those fields where the data is
changed and show only the changes.
something like In Table1 ,
Field1- Field 2 - Field3
Row1 A - B - C
Row2 A - X - Y
I need to show that,
>From Row1 to Row2
Values of Field2 , B to X
and Values of Field3 C to Y
are the changes.
any help ?
Thanks again!
G
Uri Dimant wrote:
> Gees
> if (select checksum_agg(checksum(*)) from t1)
> <> (select checksum_agg(checksum(*)) from t2)
> print 'different'
> else
> print 'probably the same'
>
>
> "Gees" <gayathri.s@.gmail.com> wrote in message
> news:1141032918.046365.85170@.j33g2000cwa.googlegroups.com...|||Gees
CREATE TABLE [dbo].Audit (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Col1] [varchar] (50) NOT NULL ,
[Col2] [int] NOT NULL ,
[Col3] [varchar] (255) NOT NULL ,
[Col4] [int] NOT NULL
) ON [PRIMARY]
And it has the following records:
INSERT INTO Audit VALUES ('Andy', 3, 'Oxford', 21)
INSERT INTO Audit VALUES ('Andy', 4, 'Oxford', 21)
INSERT INTO Audit VALUES ('Andy', 4, 'Cambridge', 21)
INSERT INTO Audit VALUES ('Andy', 6, 'Cambridge', 29)
INSERT INTO Audit VALUES ('Andy', 4, 'Manchester', 21)
ID ChangedColumn NewVal
2 Col2 4
3 Col3 Cambridge
4 Col2 6
4 Col4 29
5 Col3 Manchester
select a2.id,'col2' as colchng, cast(a2.col2 as varchar(255)) as newvalue
from audit a1 join audit a2 on a1.id=a2.id-1
where a1.col2<>a2.col2
union all
select a2.id,'col3', a2.col3
from audit a1 join audit a2 on a1.id=a2.id-1
where a1.col3<>a2.col3
union all
select a2.id,'col4', cast(a2.col4 as varchar(255))
from audit a1 join audit a2 on a1.id=a2.id-1
where a1.col4<>a2.col4
order by 1,2
"Gees" <gayathri.s@.gmail.com> wrote in message
news:1141042195.525248.161020@.i39g2000cwa.googlegroups.com...
> Thanks Uri Dimant.
> My problem also includes, quering those fields where the data is
> changed and show only the changes.
> something like In Table1 ,
> Field1- Field 2 - Field3
> Row1 A - B - C
> Row2 A - X - Y
> I need to show that,
> Values of Field2 , B to X
> and Values of Field3 C to Y
> are the changes.
> any help ?
> Thanks again!
> G
> Uri Dimant wrote:
>
>|||Hi Uri Dimant,
Thanks for the quick and nice reply.
Thats very useful.
Thanks a ton!
Best,
G

Company with Address Question

I have a database that allows a client to have multiple addresses, could be a home, business, vacation, etc... I'm trying to deside if I should put the Company Name and Title fileds in the tblClientAddresses table or keep it with the tblClient table. We do mailings to clients and if the mail is going to a home address the company and title should be excluded. If I put them in the tblClients table I really have no way of knowing if we are mailing to a home or a company. If i put the company and title with the address it makes sence that this would solve the issue.

Looking for any thoughts or suggestions?

Thank you,Standard normalization would say if there are multiple addresses per client, put it in a separate table. The only time I would recommend against this is for performance reasons, but I'm guessing you don't have any performance problems yet.|||The Company Name and Title fields belong in tblClient.

You could do something like this to make them blank if the address is a Home address:

SELECT
 CASE
 WHEN A.type = 'Home' THEN ''
 ELSE C.Name
 END AS Name,
 CASE
 WHEN A.type = 'Home' THEN ''
 ELSE C.Title
 END AS Title,
 A.Address1,
 A.Address2,
 A.City,
 A.State,
 A.ZIP
FROM
 tblClient C
INNER JOIN
 tblClientAddress A ON C.ClientID = A.ClientID

As the OP said, though, sometimes you give up correct normalization for performance -- especially if the data is static and the tables are extremely large.

Terri

Company Dimension

We deal with multiple vendors who provide us information via text/xml files. Vendor A may provide financial data, vendor b provides litigation data, vendor c provides ratings data. Our current structure has databases for each vendor with its own company table which basically makes all this data disconnected. Of course each vendor has its own proprietary company id to make records unique.

All of the data is based on companies so the grain of data would be at a company level. I would like to be able to link this information together by creating a dimensional model that has a single company table (DimCompany) and has facts populated based on the type of data we receive. Would this be the right sequence of events?

1. My initial load (historical) would have to look at all these data sources and create one company record in my DimCompany table. This table would then link to all other fact tables to provide a single view of company info. I would imagine this would have to be a fuzzy lookup since one company will be in all sources.

2. On subsequent loads (incremental) I would probably have to do a lookup of companies in the dimension via the proprietary code and add if the company wasn't there.

Any advice on tackling this issue would be greatly appreciated especially if SSIS was used in the process.

Hi jrp210,

Based on my interpretation of your schema, I would design DimCompany as you describe. One row per company, unique IDs, and you may wish to add surrogate keys - they will make your data mart / warehouse easier to scale.

I would also add a DimInformationType for the different types of data you receive.

Another option is to snowflake the InformationType table off of DimCompany. This is common when the referenced lookup table is relatively small and doesn't change often.

There are performance implications but in smaller data warehouses (~1 - 2G) you will probably never notice. And if you do, an index will likely clear up any performance issue.

I don't follow the need for a fuzzy lookup. These are expensive in SSIS and should only be used when necessary.

You are correct: incremental loading does require a lookup (or a merge join) to detect new records.

There's lots of good information online about how to do this. I suggest picking up one of the Kimball books for help in designing data warehouses - I particularly like the Data Warehoue Toolkit which is updated to include information on using SSIS for ETL.

Hope this helps,

Andy

|||

Here are my 2 cents:

Having a conformed 'Company' dimension is the way to 'join' all fact rows. Not sure about using a Fuzzy lookup as it may give you non-expected results; so evaluate yourself the margin of error your company can tolerate. In general you may want to asses the level of cleanness of your data, so you can anticipate the results

About how to organize the fact data; I would say that depends on the way the data is going to be used during analysis and reporting, and on the nature of the data. You may want to spend some time with the end users to get that feedback; draw a data model and review it with them again; this may take a few iterations.

Andy suggestion about Kimball’s book is also a good idea.

Good luck with that

|||

Thanks for the response. I did purchase the book you referenced. It has been very helpful but doesn't delve into the issue of trying to create one dimension table from multiple sources with different primary keys.

I will definitely be creating surrogate keys because more data sources will be introduced over the long haul.

I don't follow the DimInformationType table. Is this more of a helper table to map the surrogate company key to the proprietary key used by the vendor? For example:

The first time I run the historical load I will have to insert companies (take Microsoft for example) into my DimCompany table. This will be done by using vendor A's company info, vendor B, and so on. Microsoft's companyId in vendor A's system might be 123456, in vendor B's system it may be 789101. I want one instance of Microsoft in my DimCompany table so I will have to do a lookup to make sure it isn't already in the table before inserting. If it is not in the DimCompany table then I will add to DimCompany and then add to another table that has the surrogate key/vendor a key combination.

When its time to access vendor B's file, most of the companies will be in the DimCompany table. If not, follow the same procedure as above. If they are in the DimCompany table then I will have to add a row to the vendor b helper table with the surrogate key/vendor b key.

It all stems from the fact that each vendor has its own proprietary (different) key for the same company. I don't know how I would get around not using fuzzy logic or some sort of text mapping. Text mapping could be dangerous as well since the names may be slightly different.

|||

There are 2 other books from Kimball’s group:

The Data Warehouse ETL tool kit

The Microsoft Data Warehouse Toolkit

The first one cover how to conform dimension (coming from different sources).; the second one covers Kimball's warehouse methodology using SQL Server 2005 tools.

|||

jrp210 wrote:

The first time I run the historical load I will have to insert companies (take Microsoft for example) into my DimCompany table. This will be done by using vendor A's company info, vendor B, and so on. Microsoft's companyId in vendor A's system might be 123456, in vendor B's system it may be 789101. I want one instance of Microsoft in my DimCompany table so I will have to do a lookup to make sure it isn't already in the table before inserting. If it is not in the DimCompany table then I will add to DimCompany and then add to another table that has the surrogate key/vendor a key combination.

Hi jrp210,

That's different from what I understood previously. Don't feel bad, this happens to me a lot.

Maybe your schema looks like this:

CompanySK (surrogate key) CompanyName (business key)
1 Microsoft

VendorSK (surrogate key) VendorName (business key)
1 Vendor A
2 Vendor B

VendorCompanySK VendorCompany_CompanySK VendorCompany_VendorSK VendorCompany_ID
1 1 1 123456
2 1 2 789101

This is a snowflake that allows you to utilize Company and Vendor separately in facts, and also use VendorCompany in facts. There are foreign key relationships between DimCompany and DimVendorCompany, and DimVendor and DimVendorCompany.

Hope this helps,

Andy

|||

Andy,

My goal would be to create one company record from multiple sources. I don't necessarily need a vendor or vendorcompany table but will probably need their proprietary company id as an attribute in my company dimension table so that one could link back using their key.

I would assume something like this:

CompanySK
VendorA CompanyId
VendorB CompanyId
VendorC CompanyId
Company Name

For the most part one row should contain an Id for vendor a,b, and c but there are times when that is not the case. Is that why the company name would be the business/natural key?

|||

The first thing you need to do if to define the grain of your dimension; it looks to me like the grain is one row for each company (even when that company exists in several vendor data sources); so if you have more than one 'version' of a company, like in your example of Microsoft company, you would need some kind of auxiliary table to keep that 1:many relationship between the many rows/company in the source and the 1 row per company n your dimension.

BTW, this has nothing to do with SSIS...but I hope it helps

|||

You are right about the SSIS - probably more geared toward dimensional modeling/DW. But, I am using SSIS to do that so this is where I originally posted to.

Yes, grain of the dimension is the company. The really isn't more than one version of the company. It is the same company coming from different source (vendor) systems. The vendor systems have different unique keys in which they tag a company. Because of this there isn't one natural key to use across all. If I understand what you are saying is that this auxillary table would "create" the natural key that will be used in the dimension table?

|||

jrp210 wrote:

If I understand what you are saying is that this auxillary table would "create" the natural key that will be used in the dimension table?

That is right. You could create a surrogate key in the dimension and then that auxiliary table will keep the relationship between source system keys(many for a company) and the Dimension surrogate key (one per company).

|||

At first it makes sense but to initially load the database how would you keep the company unique in the DimCompany table without a key that would link them together? Or better yet put, which comes first loading the auxillary table or Dimension table?

I was assuming the auxillary table would be loaded first:
VendorId
VendorCompanyCode
CompanyName
etc.

But then I would have to create a unique key that would be used in the DimCompany table. There would have to be another table that then creates this key (identity column) that would be used in the DimCompany table.

Communications between Datasets

Is there any way to put together data from multiple datasets into one in
order to generate a single report?
ThanksHi,
You can define multiple datasets into the same report definition in order to
put their data in the same report.
In the other hand, if you want to put data from different datasets in the
same table or list, you need to use subreports (See post tittled "Fields
from Multiple Datasets" into this news group.
Hope this helps,
Mónica
"markgoldin" <markgoldin_2000@.yahoo.com> escribió en el mensaje
news:364A7754-9D67-4F8A-8528-CEEE5A924C46@.microsoft.com...
> Is there any way to put together data from multiple datasets into one in
> order to generate a single report?
> Thanks|||On Dec 12, 6:23 am, "markgoldin" <markgoldin_2...@.yahoo.com> wrote:
> Is there any way to put together data from multiple datasets into one in
> order to generate a single report?
> Thanks
You may put all your data into text boxes from several datasets. You
can also use tables, but one dataset per table. So if you don't mind
having separate datasets on separate tables then go for it.

Common DTS Source & Multiple Destinations

I want to run multiple DTS packages which export data into text files.
There is only one Data Source ..and multiple destinations.
When i write a code for this in VB ,for each Package i need to define the source connectioninividually. Can't i use the same Source connection which i used for the first package in the subsequent packages?http://www.sqldts.com/default.aspx?200

Common dataset for Multiple reports

Hi..
I have few reports that uses a very similar sql query in its own dataset, i
would like to have this sql query in a common place (a common dataset) so if
there a change i dont have to go thru all the report change the query.
Thank you very much,
Mahen.Unfortunately, that is not possible in Reporting Services 2000. Not sure
about 2005.
What you could do was to create a Stored Procedure, and use this in your
reports. If you change the content, you change the stored procedure, not 10
reports. But you still have to create a dataset connected to it in each
report. Also, if you change the parameters for your stored procedure, you
need to edit all reports using that stored procedure too.
Kaisa M. Lindahl Lervik
"Mahen" <Mahen@.discussions.microsoft.com> wrote in message
news:DDF3483F-4C61-4895-9DEE-40F383A655C0@.microsoft.com...
> Hi..
> I have few reports that uses a very similar sql query in its own dataset,
> i
> would like to have this sql query in a common place (a common dataset) so
> if
> there a change i dont have to go thru all the report change the query.
> Thank you very much,
> Mahen.

comma delimited list update stored procedure

I have a stored procedure that I want to use to update multiple records. I'm using ASP and the request form collection is returning values in a comma delimited list.
Example:
name1 = value1, value2, value3, etc.
name2 = value1, value2, value3, etc.
name3 = value1, value2, value3, etc.

Here is how I wrote my stored procedure:

CREATE PROCEDURE dbo.Sp_Update_ABR_Record
(
@.abrID int,
@.ddo varchar(50),
@.ay varchar(50),
@.strategy varchar(10),
@.budgacct varchar(10),
@.budgobj varchar(10),
@.origamt
varchar(50),
@.incrdecr varchar(50),
@.review char(10),
@.abrdetlsID varchar(50)
)
AS
UPDATE DIM_ABR_REQ_HDR
SET ABR_review = @.review
WHERE ABR_ID = @.abrID

UPDATE DIM_ABR_REQ_DETLS
SET ABR_DETLS_DDO = @.ddo, ABR_DETLS_AY = @.ay,
ABR_DETLS_STRATEGY = @.strategy, ABR_DETLS_BUDG_ACCT = @.budgacct,
ABR_DETLS_BUDG_OBJ = @.budgobj, ABR_DETLS_FUND_ORIG_AMT = convert(money, @.origamt), ABR_DETLS_FUND_INCR_DECR = convert(money, @.incrdecr)
WHERE
ABR_DETLS_ID = @.abrdetlsID
GO

The second update is where the comma delimited list needs to be handled. The first update is only updating one field once.

Is there a way to write the procedure to handle the comma delimited list? Or, is the way I have the stored procedure okay and I just need to handle the comma delimited list within the ASP code? I'm not sure which way I can accomplish this?

Thanks for any help.
-D-Hi,
I think providing values in XML format rather than comma delimited will deliver more flexibility to retrieve values from that.
Still you can write User Defined Functions to pass the comma delimited string into that and get particular value.
Regards,
Leila|||Which parameter is the list, and how did you intend to use it?

-PatP|||The request form collection will return each of these parameters in a comma delimited list, which the second update in the stored procedure would handle:

@.ddo varchar(50),
@.ay varchar(50),
@.strategy varchar(10),
@.budgacct varchar(10),
@.budgobj varchar(10),
@.origamt varchar(50),
@.incrdecr varchar(50),
@.abrdetlsID varchar(50)

So, should I use the split function and pass the information into the procedure that way? I've used the split funciton for one parameter, but not multiple parameters. So, I wasn't sure how to code for that?

Thank you for your help.
-D-|||If the number of parameters the same across the set then do parse them and execute the procedure once for each combination. Otherwise, you need to review this design and get away from doing thing this way.|||Yes, there are the same number of parameters for each variable in the set. So, if I use the split function:

ddolist = Split(Request.Form("ddo"),", "
strategylist = Split(Request.Form("strategy"),", "
aylist = Split(Request.Form("ay"),", "
budgobjlist = Split(Request.Form("budgobj"),", "
budgacctlist = Split(Request.Form("budgacct"),", "
incrdecrlist = Split(Request.Form("incrdecr"),", "
abrdetlsIDlist = Split(Request.Form("abrdetlsID"),", "

I can use any of the parameters to determine the number of loops by using Ubound?

i.e.:

Loop_Max = UBound(abrdetlsIDlist)
For x = 0 to Loop_Max
Command_Name.Parameters.Item("@.ddo").Value = ddolist(x)
Command_Name.Parameters.Item("@.strategy").Value = strategylist(x)
Command_Name.Parameters.Item("@.ay").Value = aylist(x)
Command_Name.Parameters.Item("@.budgobj").Value = budgobjlist(x)
Command_Name.Parameters.Item("@.budgacct").Value = budgacctlist(x)
Command_Name.Parameters.Item("@.incrdecr").Value = incrdecrlist(x)
Command_Name.Parameters.Item("@.abrdetlsID").Value = abrdetlsIDlist(x)
Command_Name.Execute()
Next

Would that be correct?

Thank you for your help.
Regards,
-D-

Comma Delimited Fields

Hello,
I have a table. Among the fields are UID (key) and errorCodes. ErrorCodes has multiple values separated by commas. A typical record would look like:
UID errorCodes
3245 1145 (1), 1051 (7), 0015 (13)
I'd like to query the table based on the UID. Ideally, the query would return a recordset with each errorCode as its own field. If the newly generated field values could have the same name as the errorCode, that would be good as well.
Thanks,
Stew
I suggest a change in design, so that there is a row in a related table for
every UID <-> ErrorCode combination. What you're doing here is a very
common non-relational mistake; entities should be single entities, not lists
of entities.
If you keep it the way it is, you're going to have to use ugly brute force
string parsing (LIKE, PATINDEX, etc) to search each column for instances of
an errorcode.
e.g.
WHERE ',' + errorCodes + ',' LIKE '%,1145 (1),%'
Not very efficient at all, and rather cumbersome to program... especially
when you are looking for more than one matching value.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Stewart Partington" <anonymous@.discussions.microsoft.com> wrote in message
news:6D489FF4-E04A-4793-91E5-BCD202805CD8@.microsoft.com...
> Hello,
> I have a table. Among the fields are UID (key) and errorCodes. ErrorCodes
> has multiple values separated by commas. A typical record would look like:
> UID errorCodes
> 3245 1145 (1), 1051 (7), 0015 (13)
> I'd like to query the table based on the UID. Ideally, the query would
> return a recordset with each errorCode as its own field. If the newly
> generated field values could have the same name as the errorCode, that
> would be good as well.
> Thanks,
> Stew
|||Aaron, I understand your suggestion and gave it some consideration. However, I'm unaware of all the possible error codes which may be encountered. Meaning I'd be entering new errorcodes as they are encountered - which I'm not interested in. Ideally, I'd l
ike the project to be dynamic enough to run itself.
I guess I could automate having the new error codes input into the table. However if there is a query that would satisfy my original request, I'd still be interested in that.
Thanks,
Stew
|||> encountered. Meaning I'd be entering new errorcodes as they are
> encountered - which I'm not interested in. Ideally, I'd like the project
> to be dynamic enough to run itself.
How is this impacted by whether you store new errorcodes in a list or as a
separate value in a column?

> I guess I could automate having the new error codes input into the table.
> However if there is a query that would satisfy my original request, I'd
> still be interested in that.
I thought I gave you one (at least the WHERE clause)... looking back on it
you might have to play with spaces, depending on how you're appending new
values to your existing list.
IMHO, you're still approaching this the wrong way and I don't understand
your complaints about my suggested change.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||Check out the RAC utility for S2k.
See transposing and then creating a crosstab of
the UID as the row and the errorCodes as the
pivot columns.No sql coding required.
RAC v2.2 and QALite @.
www.rac4sql.net

Comma Delimited Fields

Hello,
I have a table. Among the fields are UID (key) and errorCodes. ErrorCodes ha
s multiple values separated by commas. A typical record would look like:
UID errorCodes
3245 1145 (1), 1051 (7), 0015 (13)
I'd like to query the table based on the UID. Ideally, the query would retur
n a recordset with each errorCode as its own field. If the newly generated f
ield values could have the same name as the errorCode, that would be good as
well.
Thanks,
StewI suggest a change in design, so that there is a row in a related table for
every UID <-> ErrorCode combination. What you're doing here is a very
common non-relational mistake; entities should be single entities, not lists
of entities.
If you keep it the way it is, you're going to have to use ugly brute force
string parsing (LIKE, PATINDEX, etc) to search each column for instances of
an errorcode.
e.g.
WHERE ',' + errorCodes + ',' LIKE '%,1145 (1),%'
Not very efficient at all, and rather cumbersome to program... especially
when you are looking for more than one matching value.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Stewart Partington" <anonymous@.discussions.microsoft.com> wrote in message
news:6D489FF4-E04A-4793-91E5-BCD202805CD8@.microsoft.com...
> Hello,
> I have a table. Among the fields are UID (key) and errorCodes. ErrorCodes
> has multiple values separated by commas. A typical record would look like:
> UID errorCodes
> 3245 1145 (1), 1051 (7), 0015 (13)
> I'd like to query the table based on the UID. Ideally, the query would
> return a recordset with each errorCode as its own field. If the newly
> generated field values could have the same name as the errorCode, that
> would be good as well.
> Thanks,
> Stew|||Aaron, I understand your suggestion and gave it some consideration. However,
I'm unaware of all the possible error codes which may be encountered. Meani
ng I'd be entering new errorcodes as they are encountered - which I'm not in
terested in. Ideally, I'd l
ike the project to be dynamic enough to run itself.
I guess I could automate having the new error codes input into the table. Ho
wever if there is a query that would satisfy my original request, I'd still
be interested in that.
Thanks,
Stew|||> encountered. Meaning I'd be entering new errorcodes as they are
> encountered - which I'm not interested in. Ideally, I'd like the project
> to be dynamic enough to run itself.
How is this impacted by whether you store new errorcodes in a list or as a
separate value in a column?

> I guess I could automate having the new error codes input into the table.
> However if there is a query that would satisfy my original request, I'd
> still be interested in that.
I thought I gave you one (at least the WHERE clause)... looking back on it
you might have to play with spaces, depending on how you're appending new
values to your existing list.
IMHO, you're still approaching this the wrong way and I don't understand
your complaints about my suggested change.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Check out the RAC utility for S2k.
See transposing and then creating a crosstab of
the UID as the row and the errorCodes as the
pivot columns.No sql coding required.
RAC v2.2 and QALite @.
www.rac4sql.net

COMException when execute SQLXMLBulkLoad3Class

I haven't been able to find any information on the error:
"Schema: multiple base for a derived type on when is not supported"
I am trying to run:
Dim bulkLoadObj As New SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class()
bulkLoadObj.Execute(".xsd", ".xml")
If this doesn't work I will need to work on finding a alternative way to get
an xml document into Sql Server.
Thank you for any help provided.
Here is the xsd code for when:
<xs:complexType name="COEvent">
<xs:sequence>
<xs:element name="user" type="COShortUser" minOccurs="0"/>
</xs:sequence>
<xs:attribute name="when" type="CODateOrDateTime"/>
</xs:complexType>
And then at the top of the file
<xs:simpleType name="CODateOrDateTime">
<xs:union memberTypes="xs:dateTime xs:date"/>
</xs:simpleType>
Thanks for any help.
"AshleyT" wrote:

> I haven't been able to find any information on the error:
> "Schema: multiple base for a derived type on when is not supported"
> I am trying to run:
> Dim bulkLoadObj As New SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class()
> bulkLoadObj.Execute(".xsd", ".xml")
> If this doesn't work I will need to work on finding a alternative way to get
> an xml document into Sql Server.
> Thank you for any help provided.
>
|||Hello,
Could you please send me the schema and the data file so i can give it a try?
Thanks,
Monica Frintu
"AshleyT" wrote:
[vbcol=seagreen]
> Here is the xsd code for when:
> <xs:complexType name="COEvent">
> <xs:sequence>
> <xs:element name="user" type="COShortUser" minOccurs="0"/>
> </xs:sequence>
> <xs:attribute name="when" type="CODateOrDateTime"/>
> </xs:complexType>
> And then at the top of the file
> <xs:simpleType name="CODateOrDateTime">
> <xs:union memberTypes="xs:dateTime xs:date"/>
> </xs:simpleType>
> Thanks for any help.
> "AshleyT" wrote:

COMException when execute SQLXMLBulkLoad3Class

I haven't been able to find any information on the error:
"Schema: multiple base for a derived type on when is not supported"
I am trying to run:
Dim bulkLoadObj As New SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class()
bulkLoadObj.Execute(".xsd", ".xml")
If this doesn't work I will need to work on finding a alternative way to get
an xml document into Sql Server.
Thank you for any help provided.Here is the xsd code for when:
<xs:complexType name="COEvent">
<xs:sequence>
<xs:element name="user" type="COShortUser" minOccurs="0"/>
</xs:sequence>
<xs:attribute name="when" type="CODateOrDateTime"/>
</xs:complexType>
And then at the top of the file
<xs:simpleType name="CODateOrDateTime">
<xs:union memberTypes="xs:dateTime xs:date"/>
</xs:simpleType>
Thanks for any help.
"AshleyT" wrote:

> I haven't been able to find any information on the error:
> "Schema: multiple base for a derived type on when is not supported"
> I am trying to run:
> Dim bulkLoadObj As New SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class()
> bulkLoadObj.Execute(".xsd", ".xml")
> If this doesn't work I will need to work on finding a alternative way to g
et
> an xml document into Sql Server.
> Thank you for any help provided.
>|||Hello,
Could you please send me the schema and the data file so i can give it a try
?
Thanks,
Monica Frintu
"AshleyT" wrote:
> Here is the xsd code for when:
> <xs:complexType name="COEvent">
> <xs:sequence>
> <xs:element name="user" type="COShortUser" minOccurs="0"/>
> </xs:sequence>
> <xs:attribute name="when" type="CODateOrDateTime"/>
> </xs:complexType>
> And then at the top of the file
> <xs:simpleType name="CODateOrDateTime">
> <xs:union memberTypes="xs:dateTime xs:date"/>
> </xs:simpleType>
> Thanks for any help.
> "AshleyT" wrote:
>