|
one way doing this is using union
Eg.
SELECT prodid, no1, no2, 0 as overall from ......
union
SELECT prodid, 0 as no1, 0 as no2, (no1 + no2) / x as overall from ......
other way of doing is use procedure
|
|
|
|
|
I am trying to call a Oracle stored procedure using ADO in C++.
The store procedure has 12 input parameters, and 2 output
parameters with one of them being a Ref Cursor. I followed the
example in a post by Koushik Biswas on the subject
(http://www.codeproject.com/script/Articles/list_articles.asp?userid=811562).
But when I called
pRecordset = pCommand->Execute(NULL, NULL,
adCmdStoredProc | adCmdUnspecified );
I got an error message of "Unspecified error".
I don't have visibility to the stored procedure, but using describe
from sqlplus, I got the following:
SQL> describe myproc
Parameter Type Mode Default?
----------------------- ---------- ---- --------
P_1 VARCHAR2 IN
P_2 NUMBER IN
P_3 NUMBER IN
P_4 NUMBER IN
P_5 NUMBER IN
P_6 NUMBER IN
P_7 NUMBER IN
P_8 NUMBER IN
P_9 NUMBER IN
P_10 NUMBER IN
P_11 DATE IN
P_12 VARCHAR2 IN
P_OUT_ERROR_TXT VARCHAR2 OUT
P_OUT_CURSOR REF CURSOR OUT
Most of the parameters can be set to NULL.
Here is part of my code:
spCmdOracle->CommandType = adCmdStoredProc;
spCmdOracle->CommandText = "{CALL myproc(?,?,?,?,?,?,?,?,?,?,?,?,?)}";
_bstr_t strdata = "MYTEST";
_ParameterPtr p_1 = spCmdOracle->CreateParameter("@p_1",
adVarChar, adParamInput,
strdata.length(), strdata);
spCmdOracle->Parameters->Append(p_1);
_variant_t d((float)25);
_ParameterPtr p_2 = spCmdOracle->CreateParameter("@p_2",
adSingle, adParamInput,
sizeof(float), d);
spCmdOracle->Parameters->Append(p_2);
_variant_t a((float)40);
_ParameterPtr p_3 = spCmdOracle->CreateParameter("@p_3",
adSingle, adParamInput,
sizeof(float), a);
spCmdOracle->Parameters->Append(p_3);
_variant_t null_id;
_ParameterPtr p_4 = spCmdOracle->CreateParameter("@p_4",
adSingle, adParamInput,
sizeof(float), null_id);
spCmdOracle->Parameters->Append(p_4);
_ParameterPtr p_5 = spCmdOracle->CreateParameter("@p_5",
adSingle, adParamInput,
sizeof(float), null_id);
spCmdOracle->Parameters->Append(p_5);
_variant_t o((float)10000);
_ParameterPtr p_6 = spCmdOracle->CreateParameter("@p_6",
adSingle, adParamInput,
sizeof(float), o);
spCmdOracle->Parameters->Append(p_6);
_variant_t p((float)20000);
_ParameterPtr p_7 = spCmdOracle->CreateParameter("@p_7",
adSingle, adParamInput,
sizeof(float), p);
spCmdOracle->Parameters->Append(p_7);
_ParameterPtr p_8 = spCmdOracle->CreateParameter("@p_8",
adSingle, adParamInput,
sizeof(float), null_id);
spCmdOracle->Parameters->Append(p_8);
_ParameterPtr p_9 = spCmdOracle->CreateParameter("@p_9",
adSingle, adParamInput,
sizeof(float), null_id);
spCmdOracle->Parameters->Append(p_9);
_ParameterPtr p_10 = spCmdOracle->CreateParameter("@p_10",
adSingle, adParamInput,
sizeof(float), null_id);
spCmdOracle->Parameters->Append(p_10);
_ParameterPtr p_11 = spCmdOracle->CreateParameter("@p_11",
adSingle, adParamInput,
sizeof(float), null_id);
spCmdOracle->Parameters->Append(p_11);
_ParameterPtr p_12 = spCmdOracle->CreateParameter("@p_12",
adBSTR, adParamInput,
sizeof(float), null_id);
spCmdOracle->Parameters->Append(p_12);
char out_error[1000];
_ParameterPtr p_13 = spCmdOracle->CreateParameter("@P_OUT_ERROR_TXT",
adBSTR, adParamOutput,
sizeof(out_error), out_error);
spCmdOracle->Parameters->Append(p_out_error_txt);
spRsOracle = spCmdOracle->Execute(NULL, NULL, adCmdStoredProc | adCmdUnspecified );
The last line caused an exception with "Unspecified error".
If I change the setting of spCmdOracle->CommandText to use the stored procedure name
instead of the excape sequence, I got an error message PLS-00306:
Code meaning = IDispatch error #3092,
Description = ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'MYPROC'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
I am new on this subject and cannot find any help nearby. I would greatly appreciate any
here can give me some hint on what could be wrong.
Thanks.
|
|
|
|
|
hi there
i have 2 table with no relationship between them
and i need 2 extract from table1 the col1
and the phase of col1 in table2 col2
for example:
Table1
Col1
1000
1001
1002
=============================================
Table2
Col2
A1000A
bbb1000bcd
cdf1000frg
A1001a
bvc1001dcfe
=============================================
Result:
colRes count_in_table_2
1000 3
1001 2
1002 0
thanks in advance
roni vars
|
|
|
|
|
The following will result in a table scan, but it will do the job:
SELECT T1.Col1, COUNT(*) FROM Table1 T1
INNER JOIN Table2 T2 ON T1.Col1 LIKE '%' + T2.Col2 + '%'
Another alternative would be to create a full-text index on Table2.Col2. It all depends on how much data we're talking about and how often and how busy your server is when you run the query.
|
|
|
|
|
hi there thaks for your quick answer
it's not working performance it's not issue
i work with small table
thanks again
any suggestion
roni vars
|
|
|
|
|
hi again
i think u forget the group by T1.Col1
SELECT T1.Col1, COUNT(*) FROM Table1 T1
INNER JOIN Table2 T2 ON T1.Col1 LIKE '%' + T2.Col2 + '%'
group by T1.Col1
it's still not working
roni vars
|
|
|
|
|
Sorry,
I wrote that a little quickly. Yes, I forgot the GROUP BY, but the main problem was I reversed the references on the join . Try this (I tested it this time ):
SELECT T1.Col1, COUNT(*) FROM Table1 T1
INNER JOIN Table2 T2 ON T2.Col2 LIKE '%' + T1.Col1 + '%'
group by T1.Col1
|
|
|
|
|
hi Mark
i'm sorry but is still doesn't work i try both sql
and return me 0 rows
SELECT T1.Col, COUNT(*) FROM tbl1 T1
INNER JOIN tbl2 T2 ON T2.col LIKE '%' + T1.col + '%'
group by T1.Col
SELECT T1.Col, COUNT(*) FROM tbl1 T1
INNER JOIN tbl2 T2 ON T1.Col LIKE '%' + T2.Col + '%'
group by T1.Col
i also try this 2 sql sentences and is stil doesn't work
its return me
1000 0
1001 0
etc....
1)
select t1.col ,(SELECT count(*) FROM tbl2 t2
WHERE t1.col in (select t2.col from tbl2 t2))
From tbl1 t1
2)
select tbl1.col ,count(case when CHARINDEX(tbl1.col ,tbl2.col) > 0 then 1 else null end)
from tbl1,tbl2
group by tbl1.col
by the way i work with sql server 2005
thanks again
waiting for rescue
|
|
|
|
|
I'm running 2005 also. This script:
IF OBJECT_ID('Table1') IS NOT NULL
DROP TABLE Table1
IF OBJECT_ID('Table2') IS NOT NULL
DROP TABLE Table2
go
CREATE TABLE Table1(Col1 Varchar(15))
CREATE TABLE Table2(Col2 VARCHAR(100))
INSERT INTO Table1 VALUES('1000')
INSERT INTO Table1 VALUES('1001')
INSERT INTO Table1 VALUES('1002')
INSERT INTO Table2 VALUES('A1000A')
INSERT INTO Table2 VALUES('bbb1000bcd')
INSERT INTO Table2 VALUES('cdf1000frg')
INSERT INTO Table2 VALUES('A1001a')
INSERT INTO Table2 VALUES('bvc1001dcfe')
SELECT T1.Col1, COUNT(*) FROM Table1 T1
INNER JOIN Table2 T2 ON T2.Col2 LIKE '%' + T1.Col1 + '%'
GROUP BY T1.Col1
Gives me these results:
1000 3
1001 2
If this isn't working for you, you'll need to give me your schema and what the results are (or the error messages).
|
|
|
|
|
thanks mark is work fine when the column type is varchar
but when the type is nchar it's doesnt work becuse it's fix length
roni vars
|
|
|
|
|
hi mark
You realy help in this Query
You are like Books Online
thanks again for your important help
and if it is nchar column type need to use ltrim rtrim function
SELECT T1.Col1, COUNT(*) FROM Table1 T1
INNER JOIN Table2 T2 ON ltrim(rtrim(T2.Col2)) LIKE '%' + ltrim(rtrim(T1.Col1)) + '%'
GROUP BY T1.Col1
regards
roni vars
|
|
|
|
|
sory my mistake i try this Sentence instead of first sentence i wrote before
select t1.col ,(SELECT count(*) FROM tbl2 t2 WHERE t1.col like '%'+ t2.col +'%') as count1
From tbl1 t1
roni vars
|
|
|
|
|
Hi,
I am having some trouble with an sql statement and im just wondering if anyone could help me I have a ScoutingReport Table that stores player scouting info such as the matched, coached and playerId and I need a count of the amount of times a player has been scouted but if the same scout reports on the same match twice then I must only be counted once hers what I have started with any help would be appreciated.
Select Count(Distinct(MatchId))
From ScoutingReport
where ScoutedPersonId = 10002
group by MatchId
order by MatchId
Thanks in advance
Tim
|
|
|
|
|
Try:
select ScotedPersonId, count(distinct MatchId)
from ScotingReport
group by ScoutedPersonId
order by ScoutedPersonId
|
|
|
|
|
thats what i need so far but i should also have said that if a different scout reports on the same match then it should be counted as 2 reports any ideas
thanks for your reply
|
|
|
|
|
Which database are you using? You may be able to get away with:
select ScoutedPersonId,
count(distinct convert(varchar, MatchId) + '-' + convert(varchar, ScoutId))
from ScotingReport
group by ScoutedPersonId
order by ScoutedPersonId (which cheats a bit and turns the MatchId and ScoutId into a single string value that can be counted) or
select ScoutedPersonId, Sum(ScoutCount)
from (
select ScoutedPersonId, MatchId, count(distinct ScoutId) AS ScoutCount
from ScoutingReport
group by ScoutedPersonId, MatchId
) A
group by ScoutedPersonId
order by ScoutedPersonId (which uses an inline view).
Regards
Andy
|
|
|
|
|
both worked perfectly
ur an absolute gentleman thanks alot man much appreciated
|
|
|
|
|
hi i am using this query to count the amount of team scout reports but when a player has no reports null is returned but i need 0 to be returned this is the sql any help would be appreciated
select sr.ScoutedPersonId, count(distinct convert(varchar, sr.MatchId) + '-' + convert(varchar, sr.ScoutId)) as 'IndCount',
(
-------------------
this is the team count that is not working
select
CASE
When count(distinct convert(varchar, MatchId) + '-' + convert(varchar, ScoutId)) > 0
Then count(distinct convert(varchar, MatchId) + '-' + convert(varchar, ScoutId))
Else 0
END
from ScoutingReport
Where Type = 1 -- team scout reports
And ScoutedPersonId = sr.ScoutedPersonId
group by ScoutedPersonId
---------------
) as 'TeamCount'
from ScoutingReport sr
Join Person p on p.ID = sr.ScoutedPersonId
Where sr.Type = 0
Group By sr.ScoutedPersonId
Having count(distinct convert(varchar, sr.MatchId) + '-' + convert(varchar, sr.ScoutId)) = 1
Order By sr.ScoutedPersonId
|
|
|
|
|
Hi all, i have a few quetion to discover, bee so pleasant to explain me how i can do:
1)
I have combobox and data source for one
this.comboBox1.DataSource = this.authorsBindingSource;
Now I need to add to combobox an row, what not exist in bindingSource (supoose I want do display in combobox an additional row "not defined")
How I can do this????
---------------------------------------------------------------
2)
How I can to mark a rows in dataTable, so that ones not will be stored in database then this dataTable updated.
I mean:
//myDataTable - DataTable object
// I want that this row shouldn’t bee stored in database, but exist in //dataTable (what I should do)
DataRow dr1 = myDataTable.NewRow();
///initilize row columns with data
..
myDataTable.Rows.Add(dr1);
// I want that this row should bee stored in database and exist in //dataTable (rest all as is)
DataRow dr2 = myDataTable.NewRow();
///initilize row columns with data
..
myDataTable.Rows.Add(dr2);
//Want to bee stored dr2 not dr1
this.myDataTableAdapter.Update(myDataTable);
How I can do this.
-----------------------------------------------------------------------------------
3) How I can explore what column (DataColumn type) have default value???
|
|
|
|
|
Hi ..
I a table ,, which has integer field "ID" ,,
I'd like order ID on Sql statment as a string ..
for eg ..
fields is
1 , 2 , 101 , 103 , 201 , 202
the order of fields will be :
1 , 2 , 101 , 103 , 201 , 202
but if that fields is String the order will be :
1 , 101 , 103 , 2 , 201 , 202
it's that I want ....
then how can I on the sql statment order an Integer field as String field
thanks for my favorite forum ...
jooooo
|
|
|
|
|
select cast(orderid as varchar(10)) from table1 order by cast(orderid as varchar(10))
use the above query u can get what ever output ur saying.
|
|
|
|
|
so thanks my friend
it's ok
jooooo
|
|
|
|
|
Unusual requirement! Order by Convert(varchar(10), ID)
|
|
|
|
|
thanks my friend ,,
your methos also is good
jooooo
|
|
|
|
|
We have sql server with the name production1 (IPID:100.102.33.33) ,from asp.net iam able to connect properly ,yesterday we changed the ip Id of this system(production1) from that time if iam trying to connect from asp.net code it is giving error sqlserver does not exists access denied.
Iam able to connect in query analyzer for the same sql server by using same userid,password.
Iam able to connect in vb.net for the same sql server with same connection string which iam using in asp.net
Iam unable to connect in asp.net error sqlserver does not exists access denied.
Please help me is there any problem with asp user or what?
Thanks
|
|
|
|
|