-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello, I need to create a view that returns some data and a unique value
for each returned row. What I've done is to concat each field in the
view separated with a dot, but its clumsy and I don't like it.

Is there a better way?

Thanks,
Pablo
- --
Pablo Montilla
www.odyssey.com.uy

-- How many
economists
-- does it take to change a light bulb?
None. If the government would just leave it alone, it would
screw itself in.


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (MingW32)

iD8DBQFECgqcvooSiBfQCSoRAhOoAJ9wVOXww1gLQJ/7mB4+gJkCkEK3SwCfazTb
e4u+hYqhlJ0DcQob/CTPpNI=
=2GHB
-----END PGP SIGNATURE-----

Pablo Montilla wrote:
Why can't you just return a key or keys from the base table(s)? Please
post DDL, sample data and your query. It's pretty much impossible to
answer your question properly unless we can see what you are talking
about.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

OK, sorry for the lack of info, this is the view I need to have key:
SELECT
CAST(a.bankAccountID AS varchar) + '.' +
CONVERT(varchar, t.creationDate, 112) + '.' +
CONVERT(varchar, t.transactionDate, 112) + '.' +
CAST(t.type AS varchar) + '.' +
CAST(SIGN(t.value) AS varchar) AS
transactionTotalID,
a.bankAccountID,
t.creationDate,
t.transactionDate,
t.type,
SIGN(t.value) AS sign,
SUM(t.value) AS total
FROM
dbo.[Transaction] AS t INNER JOIN
dbo.AccountWithBank AS a ON
t.accountID = a.accountID
WHERE
(NOT (a.bankAccountID IS NULL))
GROUP BY
a.bankAccountID,
t.creationDate,
t.transactionDate,
t.type,
SIGN(t.value)

David Portas wrote:
MIN(transactionID) AS transactionTotalID...

I'm still curious as if there's a way to generate an ID (something like
IDENTITY())...

Thanks for the fast response,
Pablo
- --
Pablo Montilla
www.odyssey.com.uy

:USG Unix: /U-S-G yoo'niks/ n.,obs. Refers to AT&T Unix
commercial versions after {Version 7}, especially System III and System V
releases 1, 2, and 3. So called because during most of the lifespan of
those versions AT&T's support crew was called the `Unix Support Group',
but it is applied to version that pre- and post-dated the USG group
but were of the same lineage. This term is now historical. See {BSD},
{{Unix}}.
-- from The on-line Hacker Jargon File V423


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (MingW32)

iD8DBQFEChTTvooSiBfQCSoRAl6WAKCIT4Pk/7umFVJMPjEI6LmX0IDtogCgh6Y7
YwFAh5jwnam2PcIIE24xGEE=
=zAjs
-----END PGP SIGNATURE-----

Sounds like a good idea. However your query already includes a key:
(bankaccountid, creationdate, transactiondate, type, sign) will be unique
because that is what your GROUP BY does.

In SQL Server 2005 you can use the ROW_NUMBER() function. In 2000 you'd have
to use some alternative like a self-join or a temp table with an IDENTITY
column. For example:

SELECT COUNT(*) AS row_num,
A1.au_id, A1.au_lname, A1.au_fname
FROM pubs.dbo.authors AS A1
JOIN pubs.dbo.authors AS A2
ON A1.au_id >= A2.au_id
GROUP BY A1.au_id, A1.au_lname, A1.au_fname
ORDER BY A1.au_id ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

David Portas wrote:

Great!

Thanks,
Pablo
- --
Pablo Montilla
www.odyssey.com.uy

Hope that helps.
-- Dave Hardcastle, 06 Oct 94


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (MingW32)

iD8DBQFECi49vooSiBfQCSoRAgegAKCcc326fIJ4ekz0+LP5dR 1t6NH+FwCdEUYV
55JD7da/EJ5t77deHAgiUYo=
=noMi
-----END PGP SIGNATURE-----

This code is completely wrong and violates the basics of both RDBMS and
data modeling. Did you know that VARCHAR is VARCHAR(1)?? Do you knwo
what 1NF means? Do you knwi why there is no such thing as a "total_id"
-- whichsis it, total or an identifier ?? Whyd does A.bankaccount_id
appear twice? Nmes like "type", "value" and "total" are too vague to
be used.

Then your question makes no sense -- where are the keys fromt he base
table? Or is the design soooooo screwed up that you do not have any?
Throw this mess out and start over.