|
Try something like:
--Create temp table containing all possible descriptions.<br />
select distinct Desc<br />
into #temp01<br />
from YourTable<br />
<br />
--Use outer join to pull list of all descriptions, then cross-match<br />
--with person attributes. If the person does not have a matching<br />
--attribute then the YT.Desc value will be null. The "case" <br />
--statement outputs true/false appropriately.<br />
select T1.Desc,<br />
case when YT.Desc is null then 'True' else false end There<br />
from #temp01 T1<br />
left outer join YourTable YT<br />
on YT.PersonId = 856<br />
and YT.Desc = T1.Desc<br />
order by 1
You can do this without using the temporary table if you wanted (just replace #temp01 in the second query with "(select distinct Desc from YourTable)" to create an in-line view.
Andy
|
|
|
|
|
Can anyone explain to me, What I need to do to create a data dictionary? The following is from the help section of Advantage. I don't know how to implement this into VB.Net code in my ASP.Net project.
Creates a data dictionary.<br />
<br />
Syntax<br />
UNSIGNED32 AdsDDCreate( UNSIGNED8 *pucDictionaryPath,<br />
UNSIGNED16 usEncrypt,<br />
UNSIGNED8 *pucDescription,<br />
ADSHANDLE *phAdminConn );<br />
<br />
Parameters<br />
pucDictonaryPath (I) Full file path of the data dictionary to create. <br />
<br />
usEncrypt (I) A non-zero value will cause the data dictionary data<br />
file to be encrypted.<br />
<br />
pucDescription (I) An optional description of the database in the data <br />
dictionary. If NULL, no database description is <br />
stored in the data dictionary. The database<br />
description can be added or changed later with <br />
AdsDDSetDatabaseProperty.<br />
<br />
phAdminConn (O) Returns a database administrative connection handle <br />
if the data dictionary is created successfully.<br />
<br />
Example:<br />
Create a data dictionary without encrypting the data dictionary data files, and then add a table to the database.<br />
<br />
AdsDDCreate( "n:\\MyData\\myData.ADD", 0, "This is the database of my tables and indexes.", &hDD );<br />
AdsDDAddTable( hDD, "Customer Information", "n:\\MyData\\customer.ADT", ADS_ADT, "customer.adi;customer2.adi",<br />
"This table contains information on all customers." )
Thanks in advance.
Beginner in VB and ASP.Net
|
|
|
|
|
i have 3 records in a datatable that i loop through to update the database the problem is that the the update strored procedure updates all records where recordNo = @RecNo to the last row of my datatable so all threee records in my table at the database have the same values.
|
|
|
|
|
Does your table have a unique primary key?
my blog
|
|
|
|
|
Yes you are on to it I was updating a table not using a key so i had many lineitems with the same itemid, silly me. So i had too change my update statement in my sproc to say WHERE ID = ID AND Name = Name. All is working well now, so thanks for the reply anyway.
|
|
|
|
|
Is there a way to change the Collation name of a database on SQL Server 2000?
Thank you!
theJazzyBrain
Excellence is not an act, but a habit! Aristotle
|
|
|
|
|
ALTER DATABASE DBNAME COLLATE Latin1_General_CI_AS<br />
GO
HTH
|
|
|
|
|
Hi there,
I have a situation where a user may want to search against stored date ranges and single dates... while using either a single date or a date range. A document has content values associated with it.
The data is stored within a Postgresql database (although this is more a strategic thing so for the purpose of this discussion that doesn't really matter) and used by a c# application. In
the content table amongst other things it includes 2 date fields - a start date field (that doubles as the single date storage) and an end date field.
ORs are very hard for databases to do on large tables, so I am trying my best to avoid using an OR - instead joining the same table multiple times and using AND. Of course multiple joins are pretty hard for it too - just doesn't seem to be as hard.
I'm also trying to avoid using temporary tables where possible.
Searching for a single date within a range is pretty easy without resorting to either OR or temporary tables.
Note below isn't the actual code, just a simplified mockup.
Searching a single date against a stored range goes something like: (1990-01-01 being the search term)
select docid from document d, contentvalue v1 where
docid.cvid = v1.cvid AND
v1.type = 'daterange' AND
v1.startdate >= '1990-01-01' AND
v1.enddate <= '1990-01-01';
... a range against a single date goes ...
(1990-01-01 - 2000-01-01 being search term)
select docid from document d, contentvalue v1 , contentvalue v2
where
docid.cvid = v1.cvid AND
v1.type = 'date' AND
v1.startdate >= '1990-01-01'
v1.cvid = v2.cvid AND
v2.type = 'date' AND
v2.startdate <= '2000-01-01';
Right now though I'm stuck trying to figure out a decent way of comparing 2 overlapping ranges without using temporary tables or an OR.
If anyone has any alternate strategies for the whole thing, or for decent date-range against date-range storage within a database, and / or any links to any resources on the web I'd be very thankful.
/**********************************
Paul Evans, Dorset, UK.
**********************************/
|
|
|
|
|
How do we bind a ado edit control to a combobox to get the
second recordset field data
|
|
|
|
|
Anyone know of a way to connect to a btrieve 6.15 database using VB?
|
|
|
|
|
|
Hi.
I need to synchronize the local tables of my Access application with the tables that are deployed on the Web Server. My question is: Is this possible in MS Access or Do I need to use a different language to accomplish this synchronization module? Thank you very much for your insights...
|
|
|
|
|
i want to write a win32 DLL which can access SQL2000 database.
is there any win32 function existing?
Or i have to use MFC/.NET functions ?
|
|
|
|
|
Both ADO and ODBC can be used from a Win32 dll. Plus there is the SqlLib that comes with the SQL Server developer tools.
Have a read on MSDN for full details.
Michael
CP Blog [^]
|
|
|
|
|
I want another efficient way to re-write this query.
Note : @empid, @batchid and @name are not always passed to the SP
CREATE PROCEDURE FindEmployee
@empid INTEGER = NULL,
@batchid INTEGER = NULL,
@name VARCHAR(30) = '%'
AS
SELECT * FROM Employees
WHERE @empid LIKE COALESCE(CONVERT(varchar(3), @empid), '%')
AND @batchid LIKE COALESCE(CONVERT(varchar(3), @batchid), '%')
AND name LIKE ('%' + @name + '%'
1. If @empid and @batchid were not provided, is there a way to remove thoese columns from the where clause conditionally without writing a seperate query
2. What are the otherways to improve the performance of this kind of a query
Thanks
UB
You may stop this individual, but you can't stop us all... after all, we're all alike. +++Mentor+++
|
|
|
|
|
As a general rule, don't use functions or the LIKE operator on your table columns or your query won't use any indexes.
The easiest way of speeding this query is by replacing this (I assume there's a typo and there is no '@' outside the COALESCE):
WHERE empid LIKE COALESCE(CONVERT(varchar(3), @empid), '%')
AND batchid LIKE COALESCE(CONVERT(varchar(3), @batchid), '%')
for something like this:
WHERE empid BETWEEN @firstEmpid AND @lastEmpid
AND batchid BETWEEN @firstBatchid AND @lastBatchid
And do a bit of math to calculate the first and last ids.
If this filter returns a small result set, then the slowest part (show below) of the query won't need to run against a lot of records and the query will be way faster.
AND name LIKE ('%' + @name + '%'
Yes, even I am blogging now!
|
|
|
|
|
I create a new table. The table consists of only one column of type varchar(7).
Now i want to fill 50 random values in this table. Each value should be a seven digit number. e.g the final shape of table should be something like:
Number
======
2216789
2134567
1287905
..
..
Can anyone tell me the SQL query to generate random numbers of seven digits and enter in a table ?? It is better if numbers entered are unique.
Imtiaz
|
|
|
|
|
Try:
select cast(rand() * 10000000 as integer)
my blog
|
|
|
|
|
Assuming we're talking about SQL Server, this will work for a single random number, but not for generating 50 random numbers simultaneously.
From BOL:
"Repetitive invocations of RAND() in a single query will produce the same value."
If you tried a single insert statement to insert 50 numbers simultaneously, they'd all be the same number.
Try this:
DECLARE @Rand TABLE (Number int PRIMARY KEY)
WHILE (SELECT COUNT(*) FROM @Rand) < 50
BEGIN
INSERT INTO
@Rand
(
Number
)
SELECT
FLOOR(RAND() * 8999999) + 1000000
WHERE
NOT EXISTS (SELECT * FROM @Rand WHERE Number = FLOOR(RAND() * 8999999) + 1000000)
END
SELECT * FROM @Rand
You'll notice, however, that when you do the select at the end, even though the 50 numbers are indeed random, they'll still most likely be in numerical order. If you want them randomly ordered, you should add an identity column to the table variable, and make it the primary key.
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
Hi. I try to connect to a Microsoft SQL database using Ole Db - IRowSet interface. This works, but I can't bind it to a grid control (I use Microsoft Datagrid control).
I have:
IRowset* pRowset;
DDX_Control(pDX, IDC_DATAGRID1, m_grid);
........................................
I try to bind by
m_grid.SetRefDataSource((LPUNKNOWN)pRowset);
but it raises an exception : Type mismatch.
Well, I tried - I suppose . Could someone help me?
Gabi
|
|
|
|
|
Hello everyone,
I want to know how can I refresh a detail table that connects to a master table.
I have a relation between the tables, but I don't use datagrid for the master table (I have controls on the form (VB.NET) that represent the master table columns and datagrid that suppose to get the child rows of the detail table) but when I move the cursor's position, the detail datagrid won't seem to refresh itself.
How can I use the relation when I dont use datagrids..?
thank, Roy
|
|
|
|
|
Hi
You please make clear that you are usring Database Table or DataTable.
SenDIL
|
|
|
|
|
Hey,
I use database tables.
The database is MSAccess.
|
|
|
|
|
Hie...
Please recommend the site which related to Ms Access database.
I had enough of connection to Ms SQL Database.
I visit tons and tons of connection of SQL.
Is there any Access connection out there?
Hope to hear from you soon.
Thanks.
Regards,
Aaron
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
