I need some help on my Sql string

Hi,

i have a sqlString to select records from database as below,

StrSql="Select Distinct ItemCode From StockOnHand Where " & _
 "Description like @.Des"

this sql string works correctly, but i need to display those distinct ItemCode with their Description and Price, but i failed using the sql String at below...

StrSql="Select Distinct ItemCode, Description, Price From StockOnHand Where " & _
 "Description like @.Des"

I know there is something wrong with my sql string.. but my SQL knowledge is onli at the basic level, can any1 pls help me on this...?

Thanks in advances
life's Ng::I know there is something wrong with my sql string..

Good, let's start VERY VERY VERY at the basics.

This:

::but i failed using the sql String at below...

will NOT get you help.

It did not "just fail", it gave you an error string DESCRIBING the error. Now, even while you may not understand this error string when you read it (and normally it is pretty self-explaining), WE may want to have a look at the EXACT error coming there.

So please, post the error you got, too.

Btw, using:

"Select DISTINCT" is about the best way to kill your peroformance anyhow. And - sorry - not really smart in the queries you give there. Unless, naturally, you want your database to be terribly slow.|||Without knowing the exact error, I can tell you that this is not correct:
"Description like @.Des"

try this instead:
"Description like '%@.Des%'"|||Hi, Sorry for my mistake to not descript my problem well and thanks for ur info bout "DISTINCT"

i dint receive any error with both of the SQL statements.

In my table, there is 10 columns ("Description", "ItemCode", "Prices".......)

the 2nd one return records which is not expected... few records selected with the same ItemCode, i onli want the distinct ItemCode displayed according to its description.

This page is created just for user to search&check on the ItemCode using their Description... I think i need to reconstruct my database design.

Best Regards
life's Ng

I need Optimised duplicate finding query

I have a query which is like this
Select field a, field b,field c, field d from table1 where field d not in
(select distinct field d from table 2)
Table 1 has 35000 records
Table 2 has 12391876 records
Field d is of type varchar.
If there is any duplicate to be found in table 2 then the insertion from
table 1 to table 2 will not happen.
I need this query to be get optimised.Hi
DECLARE @.rowcount INT
SELECT Field1,COUNT(*) FROM Table2
GROUP BY Field1
HAVING COUNT(*)>1
SET @.rowcount =@.@.ROWCOUNT
IF @.rowcount >0 --Do exist duplicate rows
.........
"Cynthia" <Cynthia@.discussions.microsoft.com> wrote in message
news:CDCF5DF6-52FA-4D80-AAED-1F3803720972@.microsoft.com...
>I have a query which is like this
> Select field a, field b,field c, field d from table1 where field d not in
> (select distinct field d from table 2)
>
> Table 1 has 35000 records
> Table 2 has 12391876 records
> Field d is of type varchar.
>
> If there is any duplicate to be found in table 2 then the insertion from
> table 1 to table 2 will not happen.
>
> I need this query to be get optimised.
>|||I understand that the below query is used for finding out a duplicate value
in table2.
Note : field d contains <FILE NAME> . if there are 20 records in a file then
20 records will have the same filename and so on. A group of files will be
imported to the temporary table (table 1). Before inserting it to the main
table (table 2) a check is done whether that file is existing in the main
table. If it is existing then the insertion process will not done in order t
o
avoid duplicate file being entered into the main table.
We use the below said query given by me for this task.
So I need a optimised query.
"Uri Dimant" wrote:

> Hi
> DECLARE @.rowcount INT
> SELECT Field1,COUNT(*) FROM Table2
> GROUP BY Field1
> HAVING COUNT(*)>1
> SET @.rowcount =@.@.ROWCOUNT
> IF @.rowcount >0 --Do exist duplicate rows
> ..........
> "Cynthia" <Cynthia@.discussions.microsoft.com> wrote in message
> news:CDCF5DF6-52FA-4D80-AAED-1F3803720972@.microsoft.com...
>
>|||> So I need a optimised query.
CREATE indexes to optimize your query
http://www.sql-server-performance.c...red_indexes.asp
http://www.sql-server-performance.c...red_indexes.asp
http://www.sql-server-performance.c...ing_indexes.asp
"Cynthia" <Cynthia@.discussions.microsoft.com> wrote in message
news:2B4DD3B0-5BFE-4984-9726-9036457E8EA1@.microsoft.com...
>I understand that the below query is used for finding out a duplicate value
> in table2.
> Note : field d contains <FILE NAME> . if there are 20 records in a file
> then
> 20 records will have the same filename and so on. A group of files will be
> imported to the temporary table (table 1). Before inserting it to the main
> table (table 2) a check is done whether that file is existing in the main
> table. If it is existing then the insertion process will not done in order
> to
> avoid duplicate file being entered into the main table.
> We use the below said query given by me for this task.
> So I need a optimised query.
> "Uri Dimant" wrote:
>|||You might try using a not exists instead of not in. Not in will select all
12,391,876 rows from table2 then order them and summarize them, which
requires a fiar amount of in memory processing. The Not exists will check
table2 for each row in table1, meaning a maximum of 35,000 lookups. Note,
this will be faster if you have an index on table2.fieldd, but may be much
slower is this index does not exist.
Select fielda, fieldb,fieldc, fieldd from table1 t1 where not exists
(select 1 from table2 t2 where t2.fieldd = t1.fieldd)
"Cynthia" <Cynthia@.discussions.microsoft.com> wrote in message
news:CDCF5DF6-52FA-4D80-AAED-1F3803720972@.microsoft.com...
> I have a query which is like this
> Select field a, field b,field c, field d from table1 where field d not in
> (select distinct field d from table 2)
>
> Table 1 has 35000 records
> Table 2 has 12391876 records
> Field d is of type varchar.
>
> If there is any duplicate to be found in table 2 then the insertion from
> table 1 to table 2 will not happen.
>
> I need this query to be get optimised.
>

I need help with query string Parser error

I can not get this query to work can some on help me?

SELECT count(JOBID) AS 'transcount', count(distinct patientid) AS 'patient count', sum(TRANSPORTATION_TCOST)
AS 'tcost,
sum(TRANSPORTATION_DISC_COST) as ' dtcost ',
avg(TRANSPORTATION_DISC) as "avgTDisc",
(sum(transportation_tcost) + sum(TRANSPORTATION_DISC_COST)) as "TGrossAMTBilled',
(sum(transportation_tcost) / count(DISTINCT patientid)) AS 'PatAvgT', sum(TRANSPORTATION_DISC) AS 'avgPercentDiscT' job.JURSDICTION
FROM job LEFT JOIN
payer ON payer.payerid = job.payerid LEFT JOIN
states ON job.jurisdiction = states.initials
WHERE (job.transportation = '1') AND ((job.datedcreated = @.startdate) AND (job.datecreated = @.enddate)) AND states.region = 'GA'
GROUP BY job.JURISDICTION
ORDER BY PatAvgT DESC

Please include the specific error message you are getting. Just browsing at the query I see a missing quote from:

AS 'tcost,

|||

In addition to Cloris' post above, you have used a mix of double quotes/single quotes for alias column names. You dont need to use any quotes for alias.