|
Have a read of this
Migrating database to SQL Azure[^]
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
I wrote a query that gets the counts of which has different statuses.
The query goes like
SELECT COUNT(rec.rmaNum) as Created
FROM RMARecords rec
LEFT JOIN RMAUsers ucr on ucr.id = rec.userCreated
WHERE rec.rmaStatus = 0
UNION
SELECT COUNT(rec.rmaNum) as Received
FROM RMARecords rec
LEFT JOIN RMAUsers ure on ure.id = rec.userCreated
WHERE (rec.rmaStatus = 1 OR rec.rmaStatus = 2)
UNION
SELECT COUNT(rec.rmaNum) as Closed
FROM RMARecords rec
LEFT JOIN RMAUsers ucl on ucl.id = rec.userCreated
WHERE rec.rmaStatus = 3
UNION
SELECT COUNT(rec.rmaNum) as Voided
FROM RMARecords rec
LEFT JOIN RMAUsers ucl on ucl.id = rec.userCreated
WHERE rec.rmaStatus = 4
The output of this query is;
Created
-------
0
1
3
6
I think because of the union the result set comes like this. What I want as output is different
Created Received Closed Voided
--------/----------/-------/--------
--0-----/-----1----/--3--/----6---
How can I get as a result set like the upper result? Thanks in advance.
|
|
|
|
|
I'd do it this way.
select sum(created_count), sum(received_count), sum(closed_count), sum(voided_count) from (
SELECT COUNT(rec.rmaNum) created_count
,0 received_count
,0 closed_count
,0 voided_count
FROM RMARecords rec
LEFT JOIN RMAUsers ucr on ucr.id = rec.userCreated
WHERE rec.rmaStatus = 0
UNION
SELECT 0 created_count
,COUNT(rec.rmaNum) received_count
,0 closed_count
,0 voided_count
FROM RMARecords rec
LEFT JOIN RMAUsers ure on ure.id = rec.userCreated
WHERE (rec.rmaStatus = 1 OR rec.rmaStatus = 2)
UNION
SELECT 0 created_count
,0 received_count
,COUNT(rec.rmaNum) closed_count
,0 voided_count
FROM RMARecords rec
LEFT JOIN RMAUsers ucl on ucl.id = rec.userCreated
WHERE rec.rmaStatus = 3
UNION
SELECT 0 created_count
,0 received_count
,0 closed_count
,COUNT(rec.rmaNum) voided_count
FROM RMARecords rec
LEFT JOIN RMAUsers ucl on ucl.id = rec.userCreated
WHERE rec.rmaStatus = 4
);
You could also look into creating a view for those joins. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Hello Chris,
In your first line
select sum(created_count), sum(received_count), sum(closed_count), sum(voided_count) from (
I am having errors in this line for the column names which you placed inside the brackets. For; created_count, received_count, closed_count, voided_count.
The error is; invalid column name.
In my sql statement I am only looking to the column name rmaNum.
|
|
|
|
|
Your original post included the 'as' keyword for aliasing. Perhaps your DB requires that. Try the following
...
SELECT COUNT(rec.rmaNum) as created_count
,0 as received_count
,0 as closed_count
,0 as voided_count
FROM RMARecords rec
...
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
You might try:
SELECT
SUM(CASE WHEN rec.rmaStatus = 0 THEN 1 ELSE 0 END) as Created
SUM(CASE WHEN (rec.rmaStatus = 1 OR rec.rmaStatus = 2) THEN 1 ELSE 0 END) AS Received
SUM(CASE WHEN rec.rmaStatus = 3 THEN 1 ELSE 0 END) AS Closed
SUM(CASE WHEN rec.rmaStatus = 4 THEN 1 ELSE 0 END) AS Voided
FROM RMARecords rec
LEFT JOIN RMAUsers ucr on ucr.id = rec.userCreated
|
|
|
|
|
TRY THIS
djj55 wrote: SELECT
SUM(CASE WHEN rec.rmaStatus = 0 THEN 1 ELSE 0 END) as Created
SUM(CASE WHEN (rec.rmaStatus = 1 OR rec.rmaStatus = 2) THEN 1 ELSE 0 END) AS Received
SUM(CASE WHEN rec.rmaStatus = 3 THEN 1 ELSE 0 END) AS Closed
SUM(CASE WHEN rec.rmaStatus = 4 THEN 1 ELSE 0 END) AS Voided
FROM RMARecords rec
LEFT JOIN RMAUsers ucr on ucr.id = rec.userCreated
comma missing..
SELECT
SUM(CASE WHEN rec.rmaStatus = 0 THEN 1 ELSE 0 END) as Created,
SUM(CASE WHEN (rec.rmaStatus = 1 OR rec.rmaStatus = 2) THEN 1 ELSE 0 END) AS Received ,
SUM(CASE WHEN rec.rmaStatus = 3 THEN 1 ELSE 0 END) AS Closed,
SUM(CASE WHEN rec.rmaStatus = 4 THEN 1 ELSE 0 END) AS Voided
FROM RMARecords rec
LEFT JOIN RMAUsers ucr on ucr.id = rec.userCreated
Maulik Dusara
Sr. Sofware Engineer
I love it when a plan comes together
|
|
|
|
|
Thanks! I just did a quick non-tested coding. 
|
|
|
|
|
Hi,
It's clear that you have met some trouble in this problem, why not try esProc to solve it?
esProc is a tool for mass data computation, especially fit for the complex data computation like yours.
Check below for more details:
A Query Language Over-perform SQL[^]
|
|
|
|
|
Gentleman:
I was using a MySql connection in VS 2008 in a database that I made with .Net 3.5 and it worked perfectly (a year ago). I updated to .Net 4.0 about that time but I have not had to use MySql since. Yesterday, I tried to get a MySql connector for .Net 4.0 project and I have not been able to do it. I have tried an older version connector but it does not work at all (no intellisence). In fact, the VS 2008 app no longer works, and the only way I can justify this is to feel that the some update along the way has nullified the original reference. So....is there a dot net connector for MySql for .Net 4.0?...which one works?...and where do I get it from? The dev.mysql.com site seems to be down right now.
Thank You for your help...Pat
Well...OK...since I could not get an answer here, I tried the following and it seems to have resolved the issue; I downloaded the provider marked 6.4.4 from the site dev.mysql.com, but I UNINSTALLED all previous MySql providers first from my (.Net 4.0) application and it is now working perfectly. I hope this will help someone else.. .
modified 28-Feb-12 16:06pm.
|
|
|
|
|
MySQL resources are a little thin on the ground here, it is after all a MS centric site and your problem is a little esoteric.
Glad you posted your resolution as it may well help the next poor sod struggling with this.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello Mr Holmes,
Understood. You have personally helped me in the past and I am gratefull. It is my pleasure to be able to occasionally add a small contribution. Thank you for your reply..Pat
|
|
|
|
|
Hi, I'm trying to write a dynamic query in a procedure called by a service. From examples i've found in the oracle docs, I think this is the right method, but I keep getting an exception. It seems to be a problem with an operator, but I don't see anything wrong. Anyone care to take a look? Thanks,
Exception:
ORA-00920: invalid relational operator
ORA-06512: at "SINC.EQUIPMENT_MGMT_PKG", line 89
ORA-06512: at line 1
The second error is pointing at the whitespace after the line "v_where:= ltrim(v_where, ' and');"
Procedure:
procedure FetchItemTechData_PRC
( c_items out T_CURSOR,
p_Niin IN CHAR,
p_Fsc IN CHAR,
p_Description in VARCHAR2,
p_Idn IN CHAR,
p_Tam IN CHAR,
p_Cos IN CHAR,
p_Scos IN CHAR,
p_Ec IN VARCHAR2,
p_LocalTam in VARCHAR2,
p_Status in Number,
p_PageNum in Number
) AS
v_where varchar2(2000);
BEGIN
if p_Niin is not null then
v_where:= concat(' p_Niin like ''%', concat(p_Niin, '%'''));
end if;
if p_Fsc is not null then
v_where:= concat(concat(v_where, ' and itd.fsc like ''%'), concat(p_Fsc, '%'''));
end if;
if p_Description is not null then
v_where:= concat(concat(v_where, ' and lower(itd.description) like lower(''%'), concat(p_Description, '%'')'));
end if;
if p_Idn is not null then
v_where:= concat(concat(v_where, ' and lower(itd.idn) like lower(''%'), concat(p_Idn, '%'')'));
end if;
if p_Tam is not null then
v_where:= concat(concat(v_where, ' and lower(itd.tam) like lower(''%'), concat(p_Tam, '%'')'));
end if;
if p_Cos is not null then
v_where:= concat(concat(v_where, ' and lower(itd.cos) like lower('), concat(p_Cos, ')'));
end if;
if p_Scos is not null then
v_where:= concat(concat(v_where, ' and lower(itd.scos) like lower('), concat(p_Scos, ')'));
end if;
if p_Ec is not null then
v_where:= concat(concat(v_where, ' and lower(itd.ec) like lower(''%'), concat(p_Ec, '%'')'));
end if;
if p_LocalTam is not null then
v_where:= concat(concat(v_where, ' and lower(itd.local_tam) like lower(''%'), concat(p_LocalTam, '%'')'));
end if;
if p_PageNum > -1 then
v_where:= concat(concat(v_where, ' and rownum > '), p_PageNum * 100);
v_where:= concat(concat(v_where, ' and rownum < '), (p_PageNum+1) * 100);
end if;
v_where:= ltrim(v_where, ' and');
open c_items for
'select
itd.niin,
itd.fsc,
itd.description,
itd.idn,
itd.tam,
itd.cos,
itd.scos,
itd.ec,
itd.local_tam,
itd.local_ind,
itd.serialized_ind,
itd.ui,
itd.unit_price,
itd.status,
itd.created_id,
itd.created_dt,
itd.modified_id,
itd.modified_dt
from item_tech_data itd
where :whereClause' using v_where;
END FetchItemTechData_PRC;
|
|
|
|
|
I could be wrong, but I don't think you can use bind variables in that manner. Typically they should contain a value that can be operated on. In your case the bind variable contains all the operators and values.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
I'm relatively new to oracle... so I'll assume you're right. I modified the proc a bit so it doesn't use the bind variable and instead just concats the string together. Now I get a different exception...
ORA-00909: invalid number of arguments
ORA-06512: at SINC.EQUIPMENT_MGMT_PKG, line 116
ORA-06512: at line 1
where line 116 is again the whitespace line just before opening the cursor. I'm basing this off of this - see section "Sample Multiple-Row Query Using Native Dynamic SQL". Maybe i'm just going about this wrong.. ?
procedure FetchItemTechData_PRC
( c_items out T_CURSOR,
p_Niin IN CHAR,
p_Fsc IN CHAR,
p_Description in VARCHAR2,
p_Idn IN CHAR,
p_Tam IN CHAR,
p_Cos IN CHAR,
p_Scos IN CHAR,
p_Ec IN VARCHAR2,
p_LocalTam in VARCHAR2,
p_Status in Number,
p_PageNum in Number
) AS
v_where varchar2(2000);
BEGIN
if p_Niin is not null then
v_where:= ' itd.niin like ''%' || p_Niin || '%''';
end if;
if p_Fsc is not null then
v_where:= v_where || ' and itd.fsc like ''%' || p_Fsc || '%''';
end if;
if p_Description is not null then
v_where:= v_where || ' and lower(itd.description) like lower(''%' || p_Description || '%'')';
end if;
if p_Idn is not null then
v_where:= v_where || ' and lower(itd.idn) like lower(''%' || p_Idn || '%'')';
end if;
if p_Tam is not null then
v_where:= v_where || ' and lower(itd.tam) like lower(''%' || p_Tam || '%'')';
end if;
if p_Cos is not null then
v_where:= v_where || ' and lower(itd.cos) like lower(' || p_Cos || ')';
end if;
if p_Scos is not null then
v_where:= v_where || ' and lower(itd.scos) like lower(' || p_Scos || ')';
end if;
if p_Ec is not null then
v_where:= v_where || ' and lower(itd.ec) like lower(''%' || p_Ec || '%'')';
end if;
if p_LocalTam is not null then
v_where:= v_where || ' and lower(itd.local_tam) like lower(''%' || p_LocalTam || '%'')';
end if;
if p_PageNum > -1 then
v_where:= v_where || ' and rownum > ' || (p_PageNum * 100);
v_where:= v_where || ' and rownum < ' || ((p_PageNum+1) * 100);
end if;
v_where:= ltrim(v_where, ' and');
v_where:= 'where ' || v_where;
open c_items for
('select
itd.niin,
itd.fsc,
itd.description,
itd.idn,
itd.tam,
itd.cos,
itd.scos,
itd.ec,
itd.local_tam,
itd.local_ind,
itd.serialized_ind,
itd.ui,
itd.unit_price,
itd.status,
itd.created_id,
itd.created_dt,
itd.modified_id,
itd.modified_dt
from item_tech_data itd '
|| v_where);
END FetchItemTechData_PRC;
|
|
|
|
|
The only thing I can think of is making sure that the 'out' parameter you supply when you call the procedure has the correct number and type of fields to match the select list.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Yeah... it's just a reference cursor, so it works like it is. It was working before I added all of the if's. lol. But i need to be able to filter this stuff depending on which fields are populated.
|
|
|
|
|
Post the code using code blocks.
You can debug by removing the dynamic blocks until only the first one is left.
If that works then add one dynamic block at a time.
Also rather than trying to remove the last term add a first fixed term. Thus your constructed where would look something like...
where 1=1 and p_Niin like '%x%' ...
|
|
|
|
|
Thanks... I didn't even think about fixing the first base where clause string like that. Much easier. and yeah, I've just been moving through the if statements. I'm sure there's just a quote or something missing.
|
|
|
|
|
Hi there! is there a way to disallow multiple connections for a login? During login, I would like the server to check whether the login is already connected or not. If it is, I would also want to send a message to the user's client that the login used is already connected. I just want to avoid the case where a user may run a client more than once with each instance connected to the server. Thanks in advance.
|
|
|
|
|
Assuming you are using SQL SErver (we need to know this as servers are different).
I would expect this to be a client requirement, most apps use a SQL Server set of credentials to connect. However sql server can identify the workstation connection if it is in the connection string and if you are using Windows authentication yeck the database knows who you are.
You can always rat through the sysobjects and find this information, it will be a challenge getting the connection event and informing the client. As said this should be done at the client.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks Holmes. I was wondering whether there is a command to set this check on the server. With the presence of the 'InfoMessage' event in the 'ADO.Net' provider, I thought of sending the message from the server so that it will be trapped in the 'InfoMessage' event for the client.
My first try was checking the connection and login from 'master..sysprocesses' using the client. I think I will have to stick to that if it will be difficult doing it on the server and also send the message.
|
|
|
|
|
I don't think what you describe would work very well -- what if the client crashes and tries to get reconnected?
What I do is have the login process assign a GUID to the "session" and the client must provide that GUID with each following access. But I have a second login effectively log the other session out.
|
|
|
|
|
Dan_K wrote: I just want to avoid the case where a user may run a client more than once with
each instance connected to the server.
Why? That is really a problem for the client app, not the database.
|
|
|
|
|
I do understand your point jschell. But I want to know how the database server can provide support. In Win32 API programming, FindWindow() can be used to detect whether the client application is already running on the computer so that I disallow multiple instances. I wrote the client application in .Net but I do not know the way around it in this Framework.
But even if that is solved, a user can run the client application from different computers using the same login name. That's what I want to avoid and the reason why I am thinking of a support from the database server.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
