|
Hello,
I have the following stored procedure:
DECLARE @@Cuenta int;
SELECT @@Cuenta = COUNT(*) FROM Recibos_Reimpresiones GROUP BY ID_recibo HAVING (ID_recibo = @ID_recibo);
SET @@Cuenta = @@Cuenta + 1;
INSERT INTO dbo.Recibos_Reimpresiones (ID_recibo, Reimpresion, ID_sesion, FechaHora)
VALUES (@ID_recibo, @@Cuenta, @ID_sesion, GETDATE())
UPDATE Recibos
SET Reimpresiones = (@@Cuenta)
WHERE (ID_recibo = @ID_recibo)Basically what it's trying to do is to get the number of reprintings from the Recibos_Reimpresiones table. If should contain no rows (for a receipt) if it has only been printed once.
When I try to reprint a receipt, a record should be added to this table, stating the number of receipt, and the reprinting number (the first reprinting being one.) Also, the reprint count in the receipt record in Recibos should be updated.
But when I try to run this stored procedure, I get an error saying that the Reimpresion field of the Recibos_Reimpresiones table does not allow NULLs and that the record could not be inserted. That means that @@Cuenta is NULL, and it should be an integer.
So, anyone knows what's happening here? Does COUNT(*) return NULL instead of 0 if no records are present? Ideas on how to fix it?
Thanks!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
You're assuming too much here. I'd be running the first two lines, and then see what your count value is. No, COUNT does not return NULL for 0. Figure out what the count value is, then figure out what the value of your variable is ( I don't see how they could differ ), then keep adding a line at a time, and check to see what each line is doing.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Actually it was returning NULL. So, I fixed it adding this:
IF @@Cuenta IS NULL
SET @@Cuenta = 0;
SET @@Cuenta = @@Cuenta + 1;and now everything works as expected.
At the time I had not a single record in the table, but now that I have some records, I try with a receipt number that has not been reprinted, and it still returns NULL.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Luis Alonso Ramos wrote:
GROUP BY ID_recibo HAVING (ID_recibo = @ID_recibo);
This looks a bit odd to me. When looking for a single return value with an aggregate I've never used a GROUP BY because that is likely to return multiple values. Of course, the HAVING clause will reduce that back to one or zero results.
It would be better to say:
SELECT @@Cuenta = COUNT(*)
FROM Recibos_Reimpresiones
WHERE ID_recibo = @ID_recibo because this is guaranteed to return exactly one result every time, even if there are no records. This would mean that you could get rid of your IF statement that checks if @Cuenta is NULL
Does this help?
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Colin Angus Mackay wrote:
This looks a bit odd to me.
I'm using Access to design most of my stored procedures, and using GROUP BY is the only way I know of using aggregate functions with Access UI (of course, you can always write the SQL code yourself.)
But as this project I'm working in right now grows, I'm writing more and more complex stored procedures, mainly thanks to your teachings here!! thanks!
I just tried your code and it seems to work fine. Thanks again!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
|
i fail to install mysql coz of this
how to solve this??
make all-recursive
make[1]: Entering directory `/root/mysql-3.23.54a'
Making all in include
make[2]: Entering directory `/root/mysql-3.23.54a/include'
make all-am
make[3]: Entering directory `/root/mysql-3.23.54a/include'
make[3]: Nothing to be done for `all-am'.
make[3]: Leaving directory `/root/mysql-3.23.54a/include'
make[2]: Leaving directory `/root/mysql-3.23.54a/include'
Making all in Docs
make[2]: Entering directory `/root/mysql-3.23.54a/Docs'
make all-am
make[3]: Entering directory `/root/mysql-3.23.54a/Docs'
make[3]: Nothing to be done for `all-am'.
make[3]: Leaving directory `/root/mysql-3.23.54a/Docs'
make[2]: Leaving directory `/root/mysql-3.23.54a/Docs'
Making all in readline
make[2]: Entering directory `/root/mysql-3.23.54a/readline'
make[2]: Nothing to be done for `all'.
make[2]: Leaving directory `/root/mysql-3.23.54a/readline'
Making all in libmysql
make[2]: Entering directory `/root/mysql-3.23.54a/libmysql'
source='libmysql.c' object='libmysql.lo' libtool=yes \
depfile='.deps/libmysql.Plo' tmpdepfile='.deps/libmysql.TPlo' \
depmode=gcc3 /bin/sh ../depcomp \
/bin/sh ../libtool --mode=compile gcc
-DDEFAULT_CHARSET_HOME="\"/usr/local/mysql\""
-DDATADIR="\"/usr/local/mysql/var\""
-DSHAREDIR="\"/usr/local/mysql/share/mysql\"" -DUNDEF_THREADS_HACK
-DDONT_USE_RAID -I. -I. -I.. -I./../include -I../include -I./.. -I..
-I.. -O3 -DDBUG_OFF -c -o libmysql.lo `test -f libmysql.c || echo
'./'`libmysql.c
rm -f .libs/libmysql.lo
gcc -DDEFAULT_CHARSET_HOME=\"/usr/local/mysql\"
-DDATADIR=\"/usr/local/mysql/var\"
-DSHAREDIR=\"/usr/local/mysql/share/mysql\" -DUNDEF_THREADS_HACK
-DDONT_USE_RAID -I. -I. -I.. -I./../include -I../include -I./.. -I..
-I.. -O3 -DDBUG_OFF -c libmysql.c -MT libmysql.lo -MD -MP -MF
.deps/libmysql.TPlo -fPIC -DPIC -o .libs/libmysql.lo
gcc -DDEFAULT_CHARSET_HOME=\"/usr/local/mysql\"
-DDATADIR=\"/usr/local/mysql/var\"
-DSHAREDIR=\"/usr/local/mysql/share/mysql\" -DUNDEF_THREADS_HACK
-DDONT_USE_RAID -I. -I. -I.. -I./../include -I../include -I./.. -I..
-I.. -O3 -DDBUG_OFF -c libmysql.c -MT libmysql.lo -MD -MP -MF
.deps/libmysql.TPlo -o libmysql.o >/dev/null 2>&1
mv -f .libs/libmysql.lo libmysql.lo
source='net.c' object='net.lo' libtool=yes \
depfile='.deps/net.Plo' tmpdepfile='.deps/net.TPlo' \
depmode=gcc3 /bin/sh ../depcomp \
/bin/sh ../libtool --mode=compile gcc
-DDEFAULT_CHARSET_HOME="\"/usr/local/mysql\""
-DDATADIR="\"/usr/local/mysql/var\""
-DSHAREDIR="\"/usr/local/mysql/share/mysql\"" -DUNDEF_THREADS_HACK
-DDONT_USE_RAID -I. -I. -I.. -I./../include -I../include -I./.. -I..
-I.. -O3 -DDBUG_OFF -c -o net.lo `test -f net.c || echo './'`net.c
rm -f .libs/net.lo
gcc -DDEFAULT_CHARSET_HOME=\"/usr/local/mysql\"
-DDATADIR=\"/usr/local/mysql/var\"
-DSHAREDIR=\"/usr/local/mysql/share/mysql\" -DUNDEF_THREADS_HACK
-DDONT_USE_RAID -I. -I. -I.. -I./../include -I../include -I./.. -I..
-I.. -O3 -DDBUG_OFF -c net.c -MT net.lo -MD -MP -MF .deps/net.TPlo
-fPIC -DPIC -o .libs/net.lo
gcc -DDEFAULT_CHARSET_HOME=\"/usr/local/mysql\"
-DDATADIR=\"/usr/local/mysql/var\"
-DSHAREDIR=\"/usr/local/mysql/share/mysql\" -DUNDEF_THREADS_HACK
-DDONT_USE_RAID -I. -I. -I.. -I./../include -I../include -I./.. -I..
-I.. -O3 -DDBUG_OFF -c net.c -MT net.lo -MD -MP -MF .deps/net.TPlo -o
net.o >/dev/null 2>&1
mv -f .libs/net.lo net.lo
source='password.c' object='password.lo' libtool=yes \
depfile='.deps/password.Plo' tmpdepfile='.deps/password.TPlo' \
depmode=gcc3 /bin/sh ../depcomp \
/bin/sh ../libtool --mode=compile gcc
-DDEFAULT_CHARSET_HOME="\"/usr/local/mysql\""
-DDATADIR="\"/usr/local/mysql/var\""
-DSHAREDIR="\"/usr/local/mysql/share/mysql\"" -DUNDEF_THREADS_HACK
-DDONT_USE_RAID -I. -I. -I.. -I./../include -I../include -I./.. -I..
-I.. -O3 -DDBUG_OFF -c -o password.lo `test -f password.c || echo
'./'`password.c
rm -f .libs/password.lo
gcc -DDEFAULT_CHARSET_HOME=\"/usr/local/mysql\"
-DDATADIR=\"/usr/local/mysql/var\"
-DSHAREDIR=\"/usr/local/mysql/share/mysql\" -DUNDEF_THREADS_HACK
-DDONT_USE_RAID -I. -I. -I.. -I./../include -I../include -I./.. -I..
-I.. -O3 -DDBUG_OFF -c password.c -MT password.lo -MD -MP -MF
.deps/password.TPlo -fPIC -DPIC -o .libs/password.lo
gcc -DDEFAULT_CHARSET_HOME=\"/usr/local/mysql\"
-DDATADIR=\"/usr/local/mysql/var\"
-DSHAREDIR=\"/usr/local/mysql/share/mysql\" -DUNDEF_THREADS_HACK
-DDONT_USE_RAID -I. -I. -I.. -I./../include -I../include -I./.. -I..
-I.. -O3 -DDBUG_OFF -c password.c -MT password.lo -MD -MP -MF
.deps/password.TPlo -o password.o >/dev/null 2>&1
mv -f .libs/password.lo password.lo
source='get_password.c' object='get_password.lo' libtool=yes \
depfile='.deps/get_password.Plo' tmpdepfile='.deps/get_password.TPlo' \
depmode=gcc3 /bin/sh ../depcomp \
/bin/sh ../libtool --mode=compile gcc
-DDEFAULT_CHARSET_HOME="\"/usr/local/mysql\""
-DDATADIR="\"/usr/local/mysql/var\""
-DSHAREDIR="\"/usr/local/mysql/share/mysql\"" -DUNDEF_THREADS_HACK
-DDONT_USE_RAID -I. -I. -I.. -I./../include -I../include -I./.. -I..
-I.. -O3 -DDBUG_OFF -c -o get_password.lo `test -f get_password.c
|| echo './'`get_password.c
rm -f .libs/get_password.lo
gcc -DDEFAULT_CHARSET_HOME=\"/usr/local/mysql\"
-DDATADIR=\"/usr/local/mysql/var\"
-DSHAREDIR=\"/usr/local/mysql/share/mysql\" -DUNDEF_THREADS_HACK
-DDONT_USE_RAID -I. -I. -I.. -I./../include -I../include -I./.. -I..
-I.. -O3 -DDBUG_OFF -c get_password.c -MT get_password.lo -MD -MP -MF
.deps/get_password.TPlo -fPIC -DPIC -o .libs/get_password.lo
gcc -DDEFAULT_CHARSET_HOME=\"/usr/local/mysql\"
-DDATADIR=\"/usr/local/mysql/var\"
-DSHAREDIR=\"/usr/local/mysql/share/mysql\" -DUNDEF_THREADS_HACK
-DDONT_USE_RAID -I. -I. -I.. -I./../include -I../include -I./.. -I..
-I.. -O3 -DDBUG_OFF -c get_password.c -MT get_password.lo -MD -MP -MF
.deps/get_password.TPlo -o get_password.o >/dev/null 2>&1
mv -f .libs/get_password.lo get_password.lo
/bin/sh ../libtool --mode=link gcc -O3 -DDBUG_OFF -rdynamic -o
libmysqlclient.la -rpath /usr/local/mysql/lib/mysql -version-info
10 libmysql.lo net.lo violite.lo password.lo get_password.lo
errmsg.lo my_init.lo my_static.lo my_malloc.lo my_realloc.lo
my_create.lo my_delete.lo mf_tempfile.lo my_open.lo mf_casecnv.lo
my_read.lo my_write.lo errors.lo my_error.lo my_getwd.lo my_div.lo
mf_pack.lo my_messnc.lo mf_dirname.lo mf_fn_ext.lo mf_wcomp.lo
typelib.lo safemalloc.lo my_alloc.lo mf_format.lo mf_path.lo
mf_unixpath.lo my_fopen.lo my_symlink.lo my_fstream.lo mf_loadpath.lo
my_pthread.lo my_thr_init.lo thr_mutex.lo mulalloc.lo string.lo
default.lo my_compress.lo array.lo my_once.lo list.lo my_net.lo
charset.lo hash.lo mf_iocache.lo my_seek.lo my_pread.lo mf_cache.lo
my_gethostbyname.lo my_port.lo getopt.lo getopt1.lo getvar.lo my_lib.lo
strmov.lo strxmov.lo strnmov.lo strmake.lo strend.lo strnlen.lo
strfill.lo is_prefix.lo int2str.lo str2int.lo strinstr.lo strcont.lo
strcend.lo bchange.lo bmove.lo bmove_upp.lo longlong2str.lo strtoull.lo
strtoll.lo llstr.lo ctype.lo dbug.lo -lz -lcrypt -lnsl -lm
rm -fr .libs/libmysqlclient.la .libs/libmysqlclient.*
.libs/libmysqlclient.*
gcc -shared libmysql.lo net.lo violite.lo password.lo get_password.lo
errmsg.lo my_init.lo my_static.lo my_malloc.lo my_realloc.lo
my_create.lo my_delete.lo mf_tempfile.lo my_open.lo mf_casecnv.lo
my_read.lo my_write.lo errors.lo my_error.lo my_getwd.lo my_div.lo
mf_pack.lo my_messnc.lo mf_dirname.lo mf_fn_ext.lo mf_wcomp.lo
typelib.lo safemalloc.lo my_alloc.lo mf_format.lo mf_path.lo
mf_unixpath.lo my_fopen.lo my_symlink.lo my_fstream.lo mf_loadpath.lo
my_pthread.lo my_thr_init.lo thr_mutex.lo mulalloc.lo string.lo
default.lo my_compress.lo array.lo my_once.lo list.lo my_net.lo
charset.lo hash.lo mf_iocache.lo my_seek.lo my_pread.lo mf_cache.lo
my_gethostbyname.lo my_port.lo getopt.lo getopt1.lo getvar.lo my_lib.lo
strmov.lo strxmov.lo strnmov.lo strmake.lo strend.lo strnlen.lo
strfill.lo is_prefix.lo int2str.lo str2int.lo strinstr.lo strcont.lo
strcend.lo bchange.lo bmove.lo bmove_upp.lo longlong2str.lo strtoull.lo
strtoll.lo llstr.lo ctype.lo dbug.lo -lz -lcrypt -lnsl -lm
-Wl,-soname -Wl,libmysqlclient.so.10 -o .libs/libmysqlclient.so.10.0.0
(cd .libs && rm -f libmysqlclient.so.10 && ln -s
libmysqlclient.so.10.0.0 libmysqlclient.so.10)
(cd .libs && rm -f libmysqlclient.so && ln -s libmysqlclient.so.10.0.0
libmysqlclient.so)
ar cru .libs/libmysqlclient.a libmysql.o net.o violite.o password.o
get_password.o errmsg.o my_init.o my_static.o my_malloc.o my_realloc.o
my_create.o my_delete.o mf_tempfile.o my_open.o mf_casecnv.o my_read.o
my_write.o errors.o my_error.o my_getwd.o my_div.o mf_pack.o
my_messnc.o mf_dirname.o mf_fn_ext.o mf_wcomp.o typelib.o safemalloc.o
my_alloc.o mf_format.o mf_path.o mf_unixpath.o my_fopen.o my_symlink.o
my_fstream.o mf_loadpath.o my_pthread.o my_thr_init.o thr_mutex.o
mulalloc.o string.o default.o my_compress.o array.o my_once.o list.o
my_net.o charset.o hash.o mf_iocache.o my_seek.o my_pread.o mf_cache.o
my_gethostbyname.o my_port.o getopt.o getopt1.o getvar.o my_lib.o
strmov.o strxmov.o strnmov.o strmake.o strend.o strnlen.o strfill.o
is_prefix.o int2str.o str2int.o strinstr.o strcont.o strcend.o
bchange.o bmove.o bmove_upp.o longlong2str.o strtoull.o strtoll.o
llstr.o ctype.o dbug.o
ranlib .libs/libmysqlclient.a
creating libmysqlclient.la
(cd .libs && rm -f libmysqlclient.la && ln -s ../libmysqlclient.la
libmysqlclient.la)
make[2]: Leaving directory `/root/mysql-3.23.54a/libmysql'
Making all in client
make[2]: Entering directory `/root/mysql-3.23.54a/client'
source='mysql.cc' object='mysql.o' libtool=no \
depfile='.deps/mysql.Po' tmpdepfile='.deps/mysql.TPo' \
depmode=gcc3 /bin/sh ../depcomp \
g++ -DUNDEF_THREADS_HACK -I. -I. -I.. -I./../include -I../include
-I./.. -I.. -I.. -O3 -DDBUG_OFF -fno-implicit-templates
-fno-exceptions -fno-rtti -c -o mysql.o `test -f mysql.cc || echo
'./'`mysql.cc
/bin/sh ../libtool --mode=link g++ -O3 -DDBUG_OFF
-fno-implicit-templates -fno-exceptions -fno-rtti -rdynamic -o mysql
mysql.o readline.o sql_string.o completion_hash.o
../readline/libreadline.a -lncurses ../libmysql/libmysqlclient.la -lz
-lcrypt -lnsl -lm
g++ -O3 -DDBUG_OFF -fno-implicit-templates -fno-exceptions -fno-rtti
-rdynamic -o .libs/mysql mysql.o readline.o sql_string.o
completion_hash.o ../readline/libreadline.a -lncurses
../libmysql/.libs/libmysqlclient.so -lz -lcrypt -lnsl -lm -Wl,--rpath
-Wl,/usr/local/mysql/lib/mysql
mysql.o(.text+0x189c): In function `com_source(String*, char*)':
: undefined reference to `errno'
collect2: ld returned 1 exit status
make[2]: *** [mysql] Error 1
make[2]: Leaving directory `/root/mysql-3.23.54a/client'
make[1]: *** [all-recursive] Error 1
make[1]: Leaving directory `/root/mysql-3.23.54a'
make: *** [all] Error 2
|
|
|
|
|
I have three tables:
- Article
- Album
- linkArticleAlbum
I want get a list of all the albums and also have some way of determining whether it is linked to the currently selected article.
What I WANT to do is something like
SELECT
linkArticleAlbum.Article,
Album.ID,
Album.Title
FROM Album LEFT JOIN linkArticleAlbum ON linkArticleAlbum.Album = Album.ID
WHERE linkArticleAlbum.Article = 7 OR linkArticleAlbum.Article IS NULL
Here's what my sample data looks like
Album
------
| ID |
1
2
3
Article
-------
| ID |
7
8
9
linkArticleAlbum
------------------
| Article | Album
7 1
8 1
9 2
As you can see, I won't get a record with Album 2 since my WHERE clause eliminates it.
I'm stuck. Advise anything, including a redesign of my DB. I've apparently done something stupid and my murky brain can't figure it out right now.
········ to die is gain ········
|
|
|
|
|
Well, you haven't done anything clearly wrong in your database design, but your query needs some help. If you include a WHERE clause, only the rows that satisfy the condition will be listed, so LEFT JOINing won't help.
If it's really important to have the results indicate whether the album is linked to the selected article, this would probably do the trick:
SELECT <br />
al.ID, <br />
al.Title, <br />
l.Article, <br />
IIf([l].[Article] Is Not Null AND l.Article = [article ID],"Linked","Unlinked") AS IsLinked<br />
FROM <br />
Album AS al LEFT JOIN linkArticleAlbum AS l <br />
ON al.ID = l.Album;
Given your setup, where a single Album may be associated with many Articles, you should be aware that you may get multiple results for a single Album (from your example, Album 1 would appear twice in the results).
If you don't care about indicating in the results whether the Album is linked (i.e. you want to handle that in code) then the query is even shorter:
SELECT <br />
al.ID, <br />
al.Title, <br />
l.Article<br />
FROM <br />
Album AS al LEFT JOIN linkArticleAlbum AS l <br />
ON al.ID = l.Album;
Hope this helps.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
|
|
|
|
|
Thanks for the help.
The example does help to some degree, but in one iteration of my attempts I achieved essentially the same thing.
To clarify, I would prefer to have each Album appear only once. With the current queries, each album shows up at least once, but will appear as many times as it is referenced in the linkArticleAlbum table.
Can I eliminate the duplicates while ensuring that I can test for l.Article = [article ID]???
|
|
|
|
|
Hi
I'm using access db and i want to get records that falls between spesific time period through VB. My sql does not work with time comparition.
..... WHERE sTime >= #8:30 AM# AND eTime < #10:30 AM#...
I also found that when i check the fields from VB, my time fields in DB do not show time but dates...
Please help me to solve the problem..
Thanks
Leelanga
|
|
|
|
|
You can transfer time into minutes using DATEPART function and use this value
As example:
DATEPART(month, GETDATE())
syntax: DATEPART ( datepart , date )
datepart name or abbreviation:
hour - hh
minute - mi, n
second - ss, s
millisecond - ms
You can use it that way:
WHERE DATEPART ( mi, sTime ) +DATEPART ( hh, sTime ) *60
>= DATEPART ( mi, #8:30 AM#) +DATEPART ( hh, #8:30 AM#) *60
AND
DATEPART ( mi, eTime ) +DATEPART ( hh, eTime) *60
<
DATEPART ( mi, #10:30 AM#) +DATEPART ( hh, #10:30 AM#) *60
Not a beatiful code, but it's works.
Also, You can use user-defined function to increase readability.
Hope, this helps.
Happy coding, Alexey.
|
|
|
|
|
Thanx Alexey...it's grate, i didn't know that before
Leelanga
|
|
|
|
|
Is it ossible to pass table as parametr to stored procedure???
sql server 2000
------------------------------------
To study, study and only to study
|
|
|
|
|
I guess yes if will user only one text parameter that contais your table in an xml format then you can user sp_xml_preparedocument and openxml to read rows from your table being passes through SP parameters
|
|
|
|
|
Thanks, however i already solve this problem i use a global table.
------------------------------------
To study, study and only to study
|
|
|
|
|
hi all,
I am on my way building my 1st windows app...
and recently got stuck with dataset efficiency problem (fill() function)..
FYI my app will implement search feature with many different criteria, for example user will be able to search orders table by using combination of criterias (eg: orderID, order date etc).
the way I designed my data access atm is, everytime the search form loaded, I will fill the ds with all order data. and then using datagrid + dataview I ll filtered out the data depend on user criteria. However I am concerned with the fact that this will slowdown the loading time when working with huge order database.
If anyone know the efficient way to do this. I ve thought of possibility of requerying the database, so initially dataset is left empty and filled+cleared every time user do the search. But I dont think this is a good idea as well. :S
anyone with ideas? TIA
|
|
|
|
|
You should generally retrieve as little information from the server as possible. This will reduce the network bandwidth required between the database server and your application. It also means that SQL Server doesn't have to process as much data in order to satisfy your query. Also, remember that SQL Server is much more efficient at filtering a result set than the filtering in .NET - after all, that is a big part of SQL Server's purpose in life.
However, this is general advice and may not be applicable for your application. You should do some performance testing to see which works better. If your sample set of data while you are working on the development is too small you may get skewed results.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Hello all
I am making a client server application in which client will call system calls and send the information to server in VC++.
I want to store that information into database e.g Access using ADO( in VC++).
Plz guide me what should i do?
Do i have to do serialization of data?
e.g From client i have to send the the computer name and user name to server. how will i do this ?
how will i serialize data?
how will i use ADO? I am newbie plz be aptient with me and also tell me examples as well
Thanks a lot
|
|
|
|
|
Hello,
I have a problem building a query.
There is a Master table with fields.
ItemID[int][PK] Description1[text] Description2 [text]
1 "Any desc" "Any Desc"
2 " " " "
and then there is a child table.
ID[int] ItemID[int] CustomerID[int]
1 1 546
2 1 546
3 1 534
4 2 545
the two table are inner-joined with ItemID.
I want to getthe data from two, but The Items selected should't repeat i.e. should be grouped by ItemID.
I have problems in this bec Description fields are text in this case.
So can anyone help me in this ?
Looking forward to hearing from you.
The problem is that i want to get just the
Regards,
Asim
|
|
|
|
|
I don't know that I understand you correctly. You want to INNER JOIN the master and child table, but you don't want to repeat the ItemID (the join column) because it will return duplicate text columns? I am somewhat curious as to what format you want the data in. Performing joins tends to produce denormalised output and you cannot really get away from that.
The best way that I can think of is to do two queries, one to join the tables but not return the text columns, and another to return just the text columns.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Hi, I have create one database in sql server. In the database I create one table that have the field and appropriate datatype. But when I detach the database from the sql server and change the location of the data file, and reattach it agian to the database. All the field datatype become to char datatype with lenght of 10. I don't understand why it become like that? Dose anyone have any idea about that? And how to recover it to the orriginal table that make all field have the previous datatype???
A thousand mile of journey, begin with the first step.
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
What's the field type in the first place? Are you using SQL 2000?
|
|
|
|
|
Albert Pascual wrote:
Are you using SQL 2000?
Yes
Albert Pascual wrote:
What's the field type in the first place?
There are around 200 fields in my table. And during the time that I create the all field set to char with 10 lengh. And after that I try to change each data type of the field to match my requirement for my application. Some field contain bit, smallint, smalldatetime, nvachar... But the first field is nvachar with 15 lengh. I'm sure that before I close the table i also save to the change that i have made to my table. Then I start to detach the database from SQL server then move the source file to other destination of my computer but still in the same drive C:. Then I start to Attach the database to SQL server, but when I open the table I found that all the field data type in the table become to char with 10 lengh. I don't understand about this change, Do you have any idea about that???
A thousand mile of journey, begin with the first step.
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
Hi,
I'm using an inner datagrid(Master\details), with item template columns.Its getting the data from the database. In that i've a column enterddate in which i'm getting the date in the format 12/26/2005 12:00:00 a.m by default. want to remove this 12:00:00 thing but not able to get it. have tried using the DataFormatString={0 } in the html part but not wrking out. kindly let me know if ny answers to this.
Thanks a lot,
Manasi
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
