Minor changes to field position

I've got a report with six vertical fields. Are are currency type, all are same font. All are right justified. All are from the same data source. All are the same data type on the data source. For some reason, one of these fields is approximately 1/16" indented from the rest. Normally, I would rule this as a quirk and manually adjust the field's position by 1/16" in order to compensate. Unfortunately, I don't seem able to manually move the field in increments of less that 1/8". Any ideas?Check to see if snap to grid is on. Also, is one of these a formula and the others text >?<|||You Should be able to select all the fields, and size to same size.

mining association rules using SQL

Hello everybody,

I am interested in mining association rules field

I have found some posted statements but I didn't undertand them. I need your help badly.

Just tell from where should I start?

Thanks alot

There is a tutorial using association rules for market basket analysis at http://msdn2.microsoft.com/en-us/library/ms345326.aspx

This is a good place to start.

Minimum Date in DateStamp field is not 1-Jan-100 (Silly by SQLServer guys)

Minimum Date in DateStamp field is not 1-Jan-100. Wouldn't we expect that.
In SQLServer2000 is it "1-Jan-100", if not WHY ??,
In the previous versions it is "01-Jan-1753"
bikramjeetThe problem lies with the Gregorian calendar, not the SQL software.

If you go back before 1753, you start to run into all kinds of problems with dates. Finding out which day corresponds to a particular date means that the algorithm needs to know where you want the date resolved from a political standpoint as well as a chronological standpoint. There are also a number of other problems that crop up, such as certain calender days occuring more than once. It gets ugly.

See Wolfram (http://scienceworld.wolfram.com/astronomy/GregorianCalendar.html) for more explaination.

-PatP|||The problem lies with the Gregorian calendar, not the SQL software.

If you go back before 1753, you start to run into all kinds of problems with dates. Finding out which day corresponds to a particular date means that the algorithm needs to know where you want the date resolved from a political standpoint as well as a chronological standpoint. There are also a number of other problems that crop up, such as certain calender days occuring more than once. It gets ugly.

See Wolfram (http://scienceworld.wolfram.com/astronomy/GregorianCalendar.html) for more explaination.

-PatP

I always found that to be a lame excuse...

As if there aren't enough...what the technical word they use...oh well, kludges in M$ internal code anyway...

If they really wanted to..(and who the hell said ancient dates are precise anyway)...bulid it in tho the damn date functions...why limit it?

Next they'll be telling us you can't travel fatser than the speed of light...

The Church of M$|||I always found that to be a lame excuse...Yeah, but Convert() would be kind of funky if it had arguments for your location, political affiliation, and religion!

Oracle solves this problem by simply ignoring the Gregorian reformation altogether... You simply learn to live on Oracle time!

There are other client programming languages that have library code that detects the PC's locale and uses that to make the decisions (which are frequently incorrect) about date conversions. This can be a real hoot to debug, since the exact same binary running on the exact same release of windoze would change its results based on where it thought the PC was!

That leads to the issue of lunar calendars that have leap months added on a tough to predict basis... That's even more fun!

I don't remember if you were involved in the discussion of database normalization that Rudy invited me to that was the first time I'd posted here... The issue of dates is a lot like the issue of normalization: if you know nothing about it, no problem; if you know a little bit about it, not a big problem; as you learn more than just a little, the problem gets bigger and uglier really fast. The whole thing becomes a slippery slope, so it gets tough to dance!

-PatP

min with a bit

Hi,

I'm trying to grab records with a priority over those marked as yes (-1) in
a certain field.

Trying "select id, min(bit) from tab group by id" does not work, as the min
operator doesn't work on bits.

Is there an alternative to my query?

Many thanks,
Chrismin(cast(deleted as int))

"Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
news:buoari$thc$1@.ucsnew1.ncl.ac.uk...
> Hi,
> I'm trying to grab records with a priority over those marked as yes (-1)
in
> a certain field.
> Trying "select id, min(bit) from tab group by id" does not work, as the
min
> operator doesn't work on bits.
> Is there an alternative to my query?
> Many thanks,
> Chris|||"mountain man" <hobbit@.southern_seaweed.com.op> wrote in message
news:opOPb.23515$Wa.14455@.news-server.bigpond.net.au...
> "Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
> news:buoari$thc$1@.ucsnew1.ncl.ac.uk...

> > Trying "select id, min(bit) from tab group by id" does not work, as the
> min
> > operator doesn't work on bits.
> > Is there an alternative to my query?

> min(cast(deleted as int))

Thanks for that, I do though get an error when trying it, I guess it's
because I'm using an mdb file and linked tables to the sql server... any
other ideas? Could create a quick function I guess...

Cheers,
Chris|||"Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message news:<buocfi$ub9$1@.ucsnew1.ncl.ac.uk>...
> "mountain man" <hobbit@.southern_seaweed.com.op> wrote in message
> news:opOPb.23515$Wa.14455@.news-server.bigpond.net.au...
> > "Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
> > news:buoari$thc$1@.ucsnew1.ncl.ac.uk...
> > > Trying "select id, min(bit) from tab group by id" does not work, as the
> min
> > > operator doesn't work on bits.
> > > > Is there an alternative to my query?
> > min(cast(deleted as int))
> Thanks for that, I do though get an error when trying it, I guess it's
> because I'm using an mdb file and linked tables to the sql server... any
> other ideas? Could create a quick function I guess...
> Cheers,
> Chris

Your question isn't really clear - a bit column can only hold 0,1 or
NULL. Perhaps the -1 is coming from Access, not from MSSQL? If it is
an MSSQL query, then please consider posting the CREATE TABLE
statement for your table, as well as the exact query that you're
using, and the output you expect (sample data would also be useful).

Simon|||How a bit could be (-1) ?

"Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
news:buoari$thc$1@.ucsnew1.ncl.ac.uk...
> Hi,
> I'm trying to grab records with a priority over those marked as yes (-1)
in
> a certain field.
> Trying "select id, min(bit) from tab group by id" does not work, as the
min
> operator doesn't work on bits.
> Is there an alternative to my query?
> Many thanks,
> Chris|||"Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
news:buocfi$ub9$1@.ucsnew1.ncl.ac.uk...
> "mountain man" <hobbit@.southern_seaweed.com.op> wrote in message
> news:opOPb.23515$Wa.14455@.news-server.bigpond.net.au...
> > "Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
> > news:buoari$thc$1@.ucsnew1.ncl.ac.uk...
> > > Trying "select id, min(bit) from tab group by id" does not work, as
the
> > min
> > > operator doesn't work on bits.
> > > > Is there an alternative to my query?
> > min(cast(deleted as int))
> Thanks for that, I do though get an error when trying it, I guess it's
> because I'm using an mdb file and linked tables to the sql server... any
> other ideas? Could create a quick function I guess...

How about .... min(cast(bit as varchar(1))) ?

Pete Brown
Falls Creek
Oz|||When you move a database from MS Access to SQL-Server, then do not
translate MS-Access Boolean columns into SQL-Server Bit columns, but use
Tinyint or Char(1) columns instead (and add appropriate CHECK
constraints to limit the column to (0,1) or ('Y','N')).

HTH,
Gert-Jan

Not Me wrote:
> Hi,
> I'm trying to grab records with a priority over those marked as yes (-1) in
> a certain field.
> Trying "select id, min(bit) from tab group by id" does not work, as the min
> operator doesn't work on bits.
> Is there an alternative to my query?
> Many thanks,
> Chris|||"Igor Raytsin" <n&i@.cyberus.ca> wrote in message
news:400fee3f_1@.news.cybersurf.net...
> "Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
> news:buoari$thc$1@.ucsnew1.ncl.ac.uk...
> > I'm trying to grab records with a priority over those marked as yes (-1)
> in
> > a certain field.
> > Trying "select id, min(bit) from tab group by id" does not work, as the
> min
> > operator doesn't work on bits.
> > Is there an alternative to my query?
> How a bit could be (-1) ?

Ask Bill :o)

Chris|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:60cd0137.0401220728.58b967ae@.posting.google.c om...
> "Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
news:<buocfi$ub9$1@.ucsnew1.ncl.ac.uk>...
> > "mountain man" <hobbit@.southern_seaweed.com.op> wrote in message
> > news:opOPb.23515$Wa.14455@.news-server.bigpond.net.au...
> > > "Not Me" <Not.Me@.faker.fake.fa.ke> wrote in message
> > > news:buoari$thc$1@.ucsnew1.ncl.ac.uk...
> > > > Trying "select id, min(bit) from tab group by id" does not work, as
the
> > min
> > > > operator doesn't work on bits.
> > > > > > Is there an alternative to my query?
> > > min(cast(deleted as int))
> > Thanks for that, I do though get an error when trying it, I guess it's
> > because I'm using an mdb file and linked tables to the sql server... any
> > other ideas? Could create a quick function I guess...
>
> Your question isn't really clear - a bit column can only hold 0,1 or
> NULL. Perhaps the -1 is coming from Access, not from MSSQL? If it is
> an MSSQL query, then please consider posting the CREATE TABLE
> statement for your table, as well as the exact query that you're
> using, and the output you expect (sample data would also be useful).

Thanks for your help, yes the -1 just seems to be how access likes to
display the info.

The full problem, is that I have a table of, for example careers that people
have. In the table certain people (reference numbers) may have a current
job, and a number of non-current jobs. They may have no current job at all
but some past ones.

So, a table could show

id current job
#1 yes databases
#1 no graphics
#2 no statistics
#2 no games

and I would want to return one record for each id#, with a preference of a
current job (if no current job, any non-current job will do)

So far I've only managed to do a "select all current jobs union select all
non-current jobs that don't appear in the current jobs list" The problem
here is that it becomes very very slow when performing the "jobs that don't
appear in the current jobs list" (done by where x not in (select x from y)).

So my effort was to somehow group up the reference numbers, and display the
min(current) job, which would pick the current job as a preference. But the
problem here is I can't add min(job) to the list can I? because that will
not necessary return the correct job associated with the value of
min(current)..

Hope you understand the problem!!
Any help is greatly appreciated.

Cheers,
Chris|||Not Me (Not.Me@.faker.fake.fa.ke) writes:
> So, a table could show
> id current job
> #1 yes databases
> #1 no graphics
> #2 no statistics
> #2 no games
> and I would want to return one record for each id#, with a preference of a
> current job (if no current job, any non-current job will do)
> So far I've only managed to do a "select all current jobs union select
> all non-current jobs that don't appear in the current jobs list" The
> problem here is that it becomes very very slow when performing the "jobs
> that don't appear in the current jobs list" (done by where x not in
> (select x from y)).

Here is one way that you may want to try:

DECLARE @.temp TABLE (ident int IDENTITY,
id int NOT NULL,
current bit NOT NULL,
job varchar(29) NOT NULL)

INSERT @.temp(id, current, job)
SELECT id, current, job
FROM source_table
ORDER BY id, current DESC

SELECT t.id, c.current, t.job
FROM @.temp t
JOIN (SELECT id, minident = MIN(ident)
FROM @.temp
GROUP BY id) m ON t.ident = m.minident
ORDER BY t.id

By inserting the data into a table variable with an identity column,
the rows are numbered, and the first identity value for each id is the
row you want.

I should add that this trick is not foolproof. You are not really
guaranteed that the identity values actually reflects the ORDER BY
clause, but it works most of the time. Particularly, if there is
no parallelism. Here I am relying on that INSERT into a table variable
never uses parallelism.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Mimic SQL Server Management Studio behavior inside RS Report

I want to know how to mimic the SSMS behavior inside an Reporting Services report when querying an XML field.

In SSMS, a field that is XML shows up as a link, and when the use clicks on the XML, it opens then entire XML document in a new window. I need this exact behavior in RS, but can't accomplish it.

Any smart people out there who can do this?

You would need to use a drillthrough or hyperlink action in the report. You could pass the value of the XML to the target URL.|||

Brian, thats the part that I dont understand. The target isn't in a file- it's in a field in the database. So, with one hyperlink action how do I extract the data, and present it in another IE window, formatted as XML (i.e. indentation, Expand/collapse nodes functionality, etc. like in IE or SSMS)

You say "pass the value of the XML". How do I construnct the hyperlink to do that? Just assign the field to the Drill to URL action? That seems to easy, but I'll give it a shot...

-Kory

|||

I didn't realize you were trying to get an interactive representation of the XML. You can't really accomplish what you want in a report as we don't deal with XML data hierarchically - the XML data extension will flatten it and the binding it into a textbox will simply display the string. (Native XML support is in the long term plans)

So, if you want an interactive view, I would create an ASPX page that took the XML data as a parameter and displayed the HTML then create a URL action to point to the page.

Migration from Access : AutoNumber

I have proceed to an Access Migration to SQL Server.

The Access field type "autonumber" are now
of type "int" in SQL Server.
I do not see in SQL Server a way to tell him that it is
an auto counter.
Is this transparent ?
Will SQL Server manage it alone ?

Thanks, PierreI'm not sure of your exact question, but in SQL Server the equivalent of "Autonumber" is IDENTITY. If you want to count by one, then its IDENTITY (1,1).|||I do not see the field type "identity" in SQL Server ?

My question was, in Access we have the type of field
"automatic number" which is an auto incremental integer field.
(often ID numbers)

After migration, this one become a simple "int" field.

So in my sql statement, I never fullfill this field through
the "insert", because it is automatic in ACCESS.

Now, under SQL Server, it bugs.
So I need to tell to SQL Server that this is an atomatic incremental field.

I do not see how to do that.

Thanks,
Pierre.|||Pierre,

The IDENTITY property is not a data type, but an attribute that can be assigned to INT data types (and other data types as well). From EM, you can right-click on the table and go into Design. In the design, click on the field you wish to set to auto-increment. In the area beneath the listing of columns, you will see a list of attributes (Description, Default Value, Precision, Scale, Identity, Identity Seed, Indentity Increment, etc...).

Click on the Identity attribute and set it equal to "Yes".

Alternatively, you may use a script to create the table. Here is a generic script that you may use. Note, however, that you will have issues with trying to do it this way. Obviously, you cannot create a table with the same name over the existing table. Also, if you create a temporary table and push the data from your existing table into it, you will have to enable Identity Insert (see SQL BOL). However, I thought you should see the DDL for creating a table with an Identity Column so that you might better understand what SQL is doing.

CREATE TABLE [dbo].[tbl_MyTable] (
[int] IDENTITY (1, 1) NOT NULL ,
[MyColumn1] [varchar] (50) NOT NULL ,
[MyColumn2] [varchar] (3000) NULL ,
[MyColumn3] [varchar] (50) NOT NULL
) ON [PRIMARY]

Also not the following:
1. The syntax for IDENTITY is INDENTITY (Seed, Increment)
2. Seed is a starting value. You may not want to start at 1
3. Increment is a value by which the increment the identity. You may want to increment by a value other than 1.

HTH,

Hugh Scott

See SQL BOL for more information on IDENTITY

[i]Originally posted by Plarde
I do not see the field type "identity" in SQL Server ?

My question was, in Access we have the type of field
"automatic number" which is an auto incremental integer field.
(often ID numbers)

After migration, this one become a simple "int" field.

So in my sql statement, I never fullfill this field through
the "insert", because it is automatic in ACCESS.

Now, under SQL Server, it bugs.
So I need to tell to SQL Server that this is an atomatic incremental field.

I do not see how to do that.

Thanks,
Pierre.|||Thank you very much,
that was the solution.

It does work fine.

Pierre.

Migration ACCESS --> SQL date field

Hi,
I import an access table with date field in SQL with DTS import/export
wizard.
I got error : data overflow on date field. There is another way to do it ?In the DTS package, transform that column to varchar, and then run isdate()
against the new table to determine which rows need to be repaired.
"Sylvain Provencher" <sylvain.provencher@.nobelia.com> wrote in message
news:uZybbSLpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I import an access table with date field in SQL with DTS import/export
> wizard.
> I got error : data overflow on date field. There is another way to do it ?
>