|
Hi,
I have two table like this:
quote_tb:
ID item vendor quote
11 a v1 100
11 a v2 200
11 b v1 400
11 b v2 300
11 c v1 555
11 c v2 777
22 a v1 122
22 a v2 222
22 b v1 322
22 b v2 422
22 c v1 555
22 c v2 777
vendor_tb
name phone# fax# address#
v1 1111 2222 3, x street
v2 1212 2323 4, m street
I am trying to write an sql that given me the following result:
lowestQ_tb
ID item vendor quote phone# fax#
11 a v1 100 1111 2222
11 b v2 300 1212 2323
11 c v1 555 1111 2222
i.e for each of the items a,b,c having the id(11) select the vendors that have the lease quote and then get the phone and fax numbers of the vendors form the table vendor_tb.
To get the first part i.e. lowest quote for each item, this is the sql I use:
SELECT f.item, f.vendor, f.quote
FROM (SELECT item, min(quote) AS minprice FROM quote_tb WHERE ID='11' GROUP BY item)
AS x INNER JOIN quote_tb AS f ON f.item = x.item AND f.quote = x.minprice
which gives me the result:
ID item vendor quote
11 a v1 100
11 b v2 300
11 c v1 555
I don't know how to take it further form here to get lowestQ_tb. Please can you tell me how to get the above desired result i.e tabele lowestQ_tb. Is it possible to do such a thing?
Thanks,
Tara
|
|
|
|
|
How about:
select Q.ID, Q.item, Q.vendor, Q.Quote, V.phone, V.fax
from (
select ID, item, min(quote) AS MinQuote
from quote_tb
where ID = 11
group by ID, item
) AS MinQ
inner join quote_tb Q
on Q.ID = MinQ.ID
and Q.item = MinQ.item
and Q.quote = MinQ.quote
inner join vendor_tb AS V
on V.name = Q.vendor
order by Q.ID, Q.item Note that if two different suppliers give the same quote for the same item then they will both be displayed. You could correct that using:
...
from (
select Q.ID, Q.item, min(vendor) AS MinVendor
from (
select ID, item, min(quote) AS MinQuote
from quote_tb
where ID = 11
group by ID, item
) AS MinQ
inner join quote_tb AS Q
on Q.ID = MinQ.ID
and Q.item = MinQ.item
and Q.quote = MinQ.quote
group by Q.ID, Q.item
) AS MinS
inner join quote_tb Q
on Q.ID = MinS.ID
and Q.item = MinS.item
and Q.vendor = MinS.MinVendor
...It may help you to run each sub-query in turn to see how it is built-up. If you are using SQL-Server 2005 then you may be able to rewrite this more concisley using the windowing-functions.
Regards
Andy
|
|
|
|
|
Thank you for the reply.
I am basically connecting to MS Access through my VC++ code. I tried the code that you have given and it works with a few parenthesis added. Thank you.
Thanks,
Tara
|
|
|
|
|
See my addition in bold, I hope this will help you.
SELECT f.item, f.vendor, f.quote, v.phone, v.fax
FROM (SELECT item, min(quote) AS minprice FROM quote_tb WHERE ID='11' GROUP BY item)
AS x INNER JOIN quote_tb AS f ON f.item = x.item AND f.quote = x.minprice
inner join vendor_tb as v on f.vendor = v.name
Regards,
Mehroz
|
|
|
|
|
Thank you for the reply.
But for some reason, whatever way I try it doesn't seem to be working.
I am connecting to MS Access database through VC++. In whatever way I try to add an extra 'inner join' it gives me the error : "Syntax error(missing operator) in query expression". If I remove the extra 'inner join' its all fine.
Can't understand why. Is it something to do with the formatting?
Thanks,
Tara
Thanks,
Tara
|
|
|
|
|
I got it. Just needed to place a parenthesis.
SELECT f.item, f.vendor, f.quote, v.phone, v.fax
FROM ((SELECT item, min(quote) AS minprice FROM quote_tb WHERE ID='11' GROUP BY item)
AS x INNER JOIN quote_tb AS f ON f.item = x.item AND f.quote = x.minprice)
inner join vendor_tb as v on f.vendor = v.name
Thank you for the help.
Thanks,
Tara
|
|
|
|
|
Hi Everyone,
Is it possible to detect the expiry of password in oracle without being an DBA?. Can anyone help me in this regard.
Regards,
Manowj
|
|
|
|
|
Thanks for looking
I have this query:
select * from Table1 where field1 in (SELECT Value FROM fnSplitString ('a,b,c', ','))
Ive also tryed casting Value:
select * from Table1 where field1 in (SELECT cast(Value as varchar(50)) FROM fnSplitString ('a,b,c', ','))
Field1 has <database default> as collation and is of type varchar(50)
fnSplitString returns a table, in this case, it would return:
Value
a
b
c
of type varchar
Im getting error: Cannot resolve collation conflict for equal to operation.
And i dont know why
I am able to use this function but using it with integer values:
select * from table where ID in (SELECT Value FROM fnSplitString ('1,2,3', ','))
Ill appeciate any help
Code of the function
<br />
CREATE function dbo.fnSplitString(<br />
@String varchar (4000),<br />
@Delimiter varchar (10)<br />
)<br />
returns @ValueTable table ([Value] varchar(4000))<br />
begin<br />
declare @NextString varchar(100)<br />
declare @Pos int<br />
declare @NextPos int<br />
declare @CommaCheck varchar(1)<br />
<br />
--Initialize<br />
set @NextString = ''<br />
set @CommaCheck = right(@String,1) <br />
<br />
--Check for trailing Comma, if not exists, INSERT<br />
--if (@CommaCheck <> @Delimiter )<br />
set @String = @String + @Delimiter<br />
<br />
--Get position of first Comma<br />
set @Pos = charindex(@Delimiter,@String)<br />
set @NextPos = 1<br />
<br />
--Loop while there is still a comma in the String of levels<br />
while (@pos <> 0) <br />
begin<br />
set @NextString = substring(@String,1,@Pos - 1)<br />
<br />
insert into @ValueTable ( [Value]) Values (@NextString)<br />
<br />
set @String = substring(@String,@pos +1,len(@String))<br />
<br />
set @NextPos = @Pos<br />
set @pos = charindex(@Delimiter,@String)<br />
end<br />
<br />
return<br />
end<br />
<br />
Alexei Rodriguez
|
|
|
|
|
OK i "fixed it"
This works:
select * from Table1 where (Field1 COLLATE Traditional_Spanish_CI_AI) in (SELECT Value FROM fnSplitString ('a,b,c', ','))
This also woks if i use Field1 COLLATE Traditional_Spanish_CI_AI
But whats wrong with my original query??
Is there any other way to make this work?
Thanks
Alexei Rodriguez
|
|
|
|
|
Hi!!!
I'm creating a program to build a DataTable and I want to export it to a .dbf file.
One of the result is this line:
Create Table Results.dbf ( NumerodeIndividuosouNinhos number(4,0), Quadricula Char(5))
Unfortunely, it gave me a OleBdException and the error was "Field name is a duplicate or invalid.".
But where is the error here? It isn't a duplicate field...if it's invalid, why is invalid?
Thank You
|
|
|
|
|
I believe creating a DBF this way requires that Column names be limited to 10 chars.
|
|
|
|
|
Hi There!!!
Thank You, perryf_00. That worked well.  Now I've to modify that Column names to 10 chars max.
|
|
|
|
|
Anybody know how to define a NOCHECK add constraint (or equivalent) in a DataSet?
I see the DataRelation and ForeignKeyConstraint classes, but I don't see anything that I can use to set a constraint in the DataSet as a nocheck constraint.
Any help is greatly appreciated.
Thanks!
Charles.
|
|
|
|
|
It's been a while since I worked with this, but I remember having the same issue. I believe adding a relation adds both a check constraint and a foreign key constraint. You should be able to loop through the constraints collection and remove the check constraints and leave the foreign key constraints in place.
|
|
|
|
|
Thanks for the reply.
Any idea how I distinguish them? I expected a Type property or something like that on the Constraint object. Or, checking the constraint type (i.e., ForeignKeyConstraint)...
Charles.
|
|
|
|
|
You can use GetType() and check the type of the object. If you're using C# you could use :
if(constraint is ForeignKeyConstraint)
|
|
|
|
|
Hi
am quite new to SQL/ADO.
how can i improve the speed of retrieving data of this query?
I tried very long already but still..
thats is the best i can get.
it took 10s to complete the whole query.
Its querying oracle database.
SELECT DISTINCT ppar_engparameters.parmval, ppar_engparameters.parmname, prcd.prcdname||' '||prcd.prcdversion, prcd.activeflag, prcd.prodstatis
FROM prcd, ppar_engparameters
WHERE ppar_engparameters.parmval='" & name & "' AND prcd.activeflag='A'
AND ppar_engparameters.prcdname = prcd.prcdname
and also how can i combine this query to the first one without compromising on the speed of retrieving data?
when i tried, it took me hundreds of seconds.
SELECT catnumber, category
FROM catg_pll
where catg_pll.partprcdname||' '||catg_pll.partprcdversion = prcd.prcdname||' '||prcd.prcdversion
|
|
|
|
|
Some things to look at are:
- Make sure you have proper indexes on your tables.
- Look at using some inner (or outer) joins, depending on what data you are trying to return.
Scott.
—In just two days, tomorrow will be yesterday.
—Hey, hey, hey. Don't be mean. We don't have to be mean because, remember, no matter where you go, there you are. - Buckaroo Banzai
[ Forum Guidelines] [ Articles] [ Blog]
|
|
|
|
|
hi,
I am not sure of the indexes on the tables as this is a company database and I am actually a student on attachment and the company ask me to try this.
I had tried using inner joins but the speed got even slower result return.
Thus I am now lost of what to do already. Haix. 
|
|
|
|
|
tonyong wrote: I am not sure of the indexes on the tables as this is a company database and I am actually a student on attachment and the company ask me to try this
Honestly that shouldn't matter. You are working for them for a reason. It should be expected that you ask some level of questions in order to get your job done. However, given the situation you may need to ask those questions a bit more carefully than normal. If you ask something along the lines of "You want to verify the indexes on the tables" I don't see any reason this should cause problems.
You may want to review the joins again. My guess is that you used the wrong type of join. It's been a long time since I've worked with Oracle, but if there is any type of tool that lets you look at the execution plan for the query (the steps that the database engine will perform to actually run the query) that is the best place to start. That should tell you if indexes are being used, how many rows are being returned for each part of the query, etc.
Scott.
—In just two days, tomorrow will be yesterday.
—Hey, hey, hey. Don't be mean. We don't have to be mean because, remember, no matter where you go, there you are. - Buckaroo Banzai
[ Forum Guidelines] [ Articles] [ Blog]
|
|
|
|
|
I see. I will go and check again with them.
I am not sure which type of joins to use as my knowledge with regards to sql is very little. In school we are given the codes to see the results and this is actually the first time i am trying to come up with my own codes to query the database and for days i am still not able to come up with any better than then first one.
There is 3 tables i need to get data from. All the 3 table has 2 common keys.
As for the execution plan, I guess i had to ask them to see if i am able to get them.
|
|
|
|
|
|
Hi,
after reading the two, i came out with a new query that combines the two query together. However the speed is still not good. Still thinking of ways to make it faster.
In the query what i want is to get data out from 3 tables that is corresponded to B value in one of the table.
|
|
|
|
|
The following SQL would return all of the data from all 3 tables:
SELECT ppar_engparameters.parmval, ppar_engparameters.parmname,
prcd.prcdname||' '||prcd.prcdversion, prcd.activeflag,
prcd.prodstatis, catg_pll.catnumber, catg_pll.category
FROM ppar_engparameters
INNER JOIN prcd
ON prcd.prcdname = ppar_engparameters.prcdname
AND prcd.activeflag = 'A'
LEFT OUTER JOIN catg_pll
ON catg_pll.partprcdname = prcd.prcdname
AND catg_pll.partprcdversion = prcd.prcdversion
WHERE ppar_engparameters.parmval= 'MyValue'I have removed the "distinct" clause that you had in the original. This is often used to hide poorly-designed tables - you understand where-and-why you are getting duplicates before blindly using this.
Proper indexes are required to make this run fast. I would expect the following indexes (or primary keys) from your original description:
--For the link from product-parameter to product.
create unique index prcd_idx1 on prcd (prcdname, prcdversion)
--For the link from product.
create index catg_pll_idx1 on catg_pll (partprcdname, partprcdversion)
--For the search on parameter values.
create index ppar_engparameters_idx1 on ppar_engparameters
(parmval, activeflag)Other indexes may also be required for other activities. There should ideally be a primary key or unique-index on every table.
Oracle's query optimiser uses statistical data from indexes to determine the best way of answering your queries. There is a command (that I cannot remember) for recalculating these statistics.
Oracle is also able to display the query-plan that it uses - so you would be able to tell exactly what Oracle is doing under the covers. Search Google[^] for more information.
Hope that helps.
Regards
Andy
|
|
|
|
|
The distinct clause does make a difference of ard 20s. But how come it give repeated values?
My current code is
select distinct ppar_engparameters.parmval, ppar_engparameters.parmname,<br />
prcd.prcdname||' '||prcd.prcdversion, prcd.activeflag, prcd.prodstatus, <br />
catg_pll.catgnumber, catg_pll.category<br />
from ppar_engparameters<br />
inner join prcd<br />
on prcd.prcdname = ppar_engparameters.prcdname and prcd.activeflag='A'<br />
inner join catg_pll<br />
on catg_pll.partprcdname = ppar_engparameters.prcdname and catg_pll.partprcdversion = <br />
ppar_engparameters.prcdversion<br />
where ppar_engparameters.parmval='MyValue'
As for the query plan and stuffs i still cant access to it.
Thanks.
Tony
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
