|
you can issue a second statement
SELECT DISTINCT SectionName FROM myView
|
|
|
|
|
Right O, but that causes another trip to the database, and I already have it in memory, is there a better way?
|
|
|
|
|
Use a Hashtable, make the SectionName the key.
|
|
|
|
|
I am not a DBA and only know enough SQL to get the job done. Well, most of the time. The rest of the time I wing it and it normally works fine. This time though I am stuck and need your wisdom 
Ok I have three tables, Companies, Mailshots and Emails.
In Emails is the Status column which can contain three possible values; Sent, Failed, In Queue.
I need a view which will show me the number of Sent, Failed and In Queue rows for all Mailshots for all Companies.
So the result should be something like this:
Company | Mailshot | Sent | Failed | In Queue
---------------------------------------------
ACME | ACME101 | 100 | 10 | 150
ACME | ACME102 | 300 | 10 | 250
ACME | ACME103 | 0 | 0 | 350
I have been trying to wrap my head around CUBE and PIVOT in SQL but alas God never intended for my brain to grow so big, so I can't (wrap around that is.)
Any help, however obvious, is greatly appreciated. Thank you. *slinks off back to HTML and CSS*
Paul Watson
Bluegrass
Cape Town, South Africa
Robert Edward Caldecott wrote:
My father-in-law calls yer man bits "weasels"
|
|
|
|
|
select
C.CompanyName,
S.MailShotDesc,
SUM(case E.Status when 'S' then 1 else 0 end) 'Sent',
SUM(case E.Status when 'F' then 1 else 0 end) 'Failed',
SUM(case E.Status when 'Q' then 1 else 0 end) 'In Queue'
from
Company C,
MailShot S,
EMails E
where
S.CompanyKey = S.CompanyKey and
E.MailShotKey = S.MailShotKey
group by
C.CompanyName,
S.MailShotDesc
order by 1, 2
onwards and upwards...
|
|
|
|
|
This format is the simple way to cross-tab detail records with different statuses.
onwards and upwards...
|
|
|
|
|
Thank you very much basementman, much appreciated and it works like a bomb
Paul Watson
Bluegrass
Cape Town, South Africa
Robert Edward Caldecott wrote:
My father-in-law calls yer man bits "weasels"
|
|
|
|
|
Friends i made a trigger in SQL server. The purpose of this trigger is very simple i.e to find out the number of records updated in the table and then print it. For this purpose i am using:
select @count = count(*) from updated
This is working fine in my home computer, but i am getting error in my office computer that:
Invalid object 'updated'
i.e SQL server is unable to find the table 'updated' which is its own internal table.
How can i get rid of this error.
Thanks
Imtiaz
|
|
|
|
|
the tables exposed to triggers are 'inserted' and 'deleted'. I expect that at home, you actually have a user table named 'updated' in your db, which is why it is working.
onwards and upwards...
|
|
|
|
|
zbasementman wrote:
the tables exposed to triggers are 'inserted' and 'deleted'.
Both of these are also not working for me 
|
|
|
|
|
I think if you change your select to this format, it may work:
select @count = count(COLUMN_NAME) from INSERTED
|
|
|
|
|
hello,
i have long string to store in my database.
i am using varchar, but its too small for storing it.
i used text before, but i can't use where clause with it without using LIKE nad IS NULL.
so which datatype should i use for storing storing complete information?
LOOKING FOR GUIDENCE
ASIM NAZIR
Asim
|
|
|
|
|
i think varchar is best option.
sorry for my bad English.
|
|
|
|
|
If you can get away with it, use a varchar(7500). If your data is larger that 7500, you will need to use text.
You can also use PatIndex and CharIndex with text fields.
So, if you want to do an equality join, you can use something like:
where
CharIndex('StringToSeek',TextField) = 1
onwards and upwards...
|
|
|
|
|
Hi All!!
I have binded my text fields to DataColumns of table at runtime.In data navigation mode, all text fields of my application are disabled. When I press the 'Add New' button, all fields are enabled and cleared.
Problem is that when I focus to next control, all the data of current record is again loaded in all textboxes . While I want to remain them clear.
plzzzzzzzzzzzzzzzzz help me.
Thanx in Advance
sorry for my bad English.
|
|
|
|
|
I have a website that needs to access a SQL Server 2000 database hosted on the local machine. I've created an ODBC connection to the database and created users with applicable permissions in the database itself, but when I try and login with the query analyzer or from the website I get the error "SGL doesn't associate this user with a trusted connection." This is the DSN I'm using.
DATABASE_DSN = "Provider=SQLOLEDB.1; Password=abcd; Persist Security Info=True;User ID=abcd;Initial Catalog=extrosite;Data Source=LocalServer"
Please help 
Paul 
I have been afraid always. When you see something insurmountable ahead of you, say to yourself: "All right! I am afraid. Now that I've been properly afraid, let's go forward." That is the whole secret. - Jeanne d'Arc
modified 18-Jul-18 11:59am.
|
|
|
|
|
Don't worry peeps I've solved it 
Paul
I have been afraid always. When you see something insurmountable ahead of you, say to yourself: "All right! I am afraid. Now that I've been properly afraid, let's go forward." That is the whole secret. - Jeanne d'Arc
modified 18-Jul-18 11:59am.
|
|
|
|
|
|
a) I had to configure my SQL server to accept SQL authentication by opening Enterprise Manager, opening properties for my server, and on the Security tab setting authentication to SQL/Windows NT as opposed to NT, and...
b) fixing my connection string. The new one became:
DATABASE_DSN = "Provider=sqloledb;Data Source=(local);Initial Catalog=extrosite;User ID=abcd; Password=abcd"
Paul
I have been afraid always. When you see something insurmountable ahead of you, say to yourself: "All right! I am afraid. Now that I've been properly afraid, let's go forward." That is the whole secret. - Jeanne d'Arc
modified 18-Jul-18 11:59am.
|
|
|
|
|
hello,
i am reading values from an html page, now i want to put them into database.
now, should i put them directly i.e. get one by one from the page and continue putting them to database accordingly. or
i should first write the values to a .txt file (say a.txt) and then enter them to databse using this text file.
?
am using SQL SERVER2000 AND C#
ASIM
Asim
|
|
|
|
|
I'm presuming you are looking for tags or something?
If one insert fails should they all fail?
Probably get all values from page, then insert one by one.
Cheers,
Simon
"Don't try to be like Jackie. There is only one Jackie.... Study computers instead.", Jackie Chan on career choices.
animation mechanics in SVG (my first abstract photo)
|
|
|
|
|
Hi,
i insert a new recordset in ADO
_RecordsetPtr rs;
...
rs.AddNew()
After this i set the fields in rs with my application data and do a
rs.Update()
My Problem is that if copy back the data from rs to my application
i get an error on fields which have a standard value like datetime (getdate()).
One method to avoid this problem i've found was to close and open the recordset again,
but in this case i have to move the rs to the right position, using GetAbsolutPosition won't work because in some tables my primary key consists of more than 1 column so there is no guaranty new recordset goes to the end of the table.
Any help /ideas to solve this problem are welcome
thx in advance !
|
|
|
|
|
hello,
am working in c# an dtrying to connect to a sql server database.
my where clause is not working. without whre it's fine.
exception is :
the text,ntext and image data types can't be compared or sorted , except when using IS NULL OR LIKE operators.
now how can i use where clause.
e.g. plz
ASIM
Asim
|
|
|
|
|
I think the error message is self-explanitory.
onwards and upwards...
|
|
|
|
|
I get this problem using an OleDBConnection and executing a source code that inserts and deletes on multiple tables under same transaction (connection.BeginTransaction()). The problem decreases if I remove transactions management but the only crucial work around, for the moment, is closing and opening the connection before any insert, which is not acceptable solution.
Thanks,
Luigi
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
