-----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.

-----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:
That got me thinking...I can probably go with returning
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

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 ;

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

David Portas wrote:
Yup, but I need a single column, as I'm using DLinq...=)

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+LP5dR1t6NH+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.

Data Warehouse Load Hints?
Product qty in a given branch
Is this statement vulnerable to code injection?
Query master file and two entries of related file into a single re
ISOLATION!!!