SQL query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL query

Author

Topic

SebastienLeto
Starting Member

3 Posts

Posted - 2010-06-28 : 04:42:42

Hello,

I use the following SQL query in access without any problem.
However, when I want to use this query from an excel macro, it doesn't work anymore. I just copy/paste the SQL statement to the macro but I get various errors.
I have tried inserting quotes and double quotes in the SQL statement but no success.
This is the statement :

SELECT DISTINCT com_el.sat_name, com_el.long_nom, freq.ntc_id, com_el.adm, com_el.prov, com_el.act_code, pub_ssn.ssn_ref
FROM (freq INNER JOIN com_el ON freq.ntc_id = com_el.ntc_id) INNER JOIN pub_ssn ON com_el.ntc_id = pub_ssn.ntc_id
WHERE (((com_el.adm)<>"F"))
GROUP BY com_el.sat_name, com_el.long_nom, freq.ntc_id, com_el.adm, com_el.prov, com_el.act_code, pub_ssn.ssn_ref, freq.freq_mhz, pub_ssn.seq_no
HAVING (((com_el.prov)="9.6") AND ((com_el.act_code)="A" Or (com_el.act_code)="M") AND ((pub_ssn.ssn_ref)="CR/C") AND ((pub_ssn.seq_no)=1) AND (([freq]![freq_mhz]) Between 17300 And 20200 Or ([freq]![freq_mhz]) Between 18100 And 18400 Or ([freq]![freq_mhz]) Between 24750 And 25250 Or ([freq]![freq_mhz]) Between 27000 And 31000))
ORDER BY com_el.long_nom;

Do any of you have an idea of a way through ?

Thanks,

Sachin.Nand

2937 Posts

Posted - 2010-06-28 : 06:29:46

Can you please tell us what those errors are?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-06-28 : 06:47:08

[freq]![freq_mhz] should be freq.freq_mhz etc.
Try:


SELECT DISTINCT com_el.sat_name, com_el.long_nom, freq.ntc_id, com_el.adm, com_el.prov, com_el.act_code, pub_ssn.ssn_ref
FROM freq
	JOIN com_el ON freq.ntc_id = com_el.ntc_id
	JOIN pub_ssn ON com_el.ntc_id = pub_ssn.ntc_id
	JOIN
	(
		SELECT 17300, 20200
		UNION ALL SELECT 18100, 18400
		UNION ALL SELECT 24750, 25250
		UNION ALL SELECT 27000, 31000
	) D (Lo, Hi)
		ON freq.freq_mhz BETWEEN D.Lo AND D.Hi
WHERE com_el.adm <> 'F'
	AND com_el.prov = '9.6'
	AND com_el.act_code IN ('A', 'M')
	AND pub_ssn.ssn_ref = 'CR/C'
	AND pub_ssn.seq_no = 1
GROUP BY com_el.sat_name, com_el.long_nom, freq.ntc_id, com_el.adm, com_el.prov, com_el.act_code, pub_ssn.ssn_ref --, freq.freq_mhz, pub_ssn.seq_no
ORDER BY com_el.long_nom;

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-28 : 06:50:11

We're not in Access anymore, Sebastian.

1) Double quotes acts like a column name delimiter in SQL Server
2) Exaclamation mark is not a valid column delimiter in SQL Server
3) You have a number of redundant checks

SELECT		com_el.sat_name,
		com_el.long_nom,
		freq.ntc_id,
		com_el.adm,
		com_el.prov,
		com_el.act_code,
		pub_ssn.ssn_ref
FROM		freq
INNER JOIN	com_el ON freq.ntc_id = com_el.ntc_id
INNER JOIN	pub_ssn ON com_el.ntc_id = pub_ssn.ntc_id
WHERE		com_el.adm <> 'F'
		AND com_el.prov= '9.6'
		AND com_el.act_code IN ('A', 'M')
		AND pub_ssn.ssn_ref= 'CR/C'
		AND pub_ssn.seq_no = 1
		AND	(
				freq.freq_mhz Between 17300 And 20200
				Or freq.freq_mhz Between 24750 And 25250
				Or freq.freq_mhz Between 27000 And 31000
			)
GROUP BY	com_el.sat_name,
		com_el.long_nom,
		freq.ntc_id,
		com_el.adm,
		com_el.prov,
		com_el.act_code,
		pub_ssn.ssn_ref
ORDER BY	com_el.long_nom;

N 56°04'39.26"
E 12°55'05.63"

SebastienLeto
Starting Member

3 Posts

Posted - 2010-06-28 : 10:02:49

Hello,

First of all thank you for the answers !

I have tried correcting the statement as you proposed, however I get errors in both proposed corrections:

For the proposition of Peso I get the error :
Syntax error (operator missing) in expression 'freq.ntc_id = com_el.ntc_id INNER JOIN pub_ssn ON com_el.ntc_id = pub_ssn.ntc_id'.

For the proposition of Ifor I get the error:
"Error in the FROM statement"

What do you think ? Is it a syntax problem or did I miss something else ?

I have tried with my own statements while correcting the ! issue, and replacing " " by ' ' and it seems to work (but no other modifications like correcting the redundant checks).

I am going to see how can I modifiy my statement to get less redundant checks as you have proposed.

SebastienLeto
Starting Member

3 Posts

Posted - 2010-06-28 : 10:07:46

Hello again, sorry for the double posting,

After adding some parenthesis the statement from Peso also works. I must say it looks more efficient but would there be a gain in the request speed if I use your statement Peso ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-28 : 10:24:34

Yes, there is no need to group by more columns than for the DISTINCT clause.
GROUP BY does the same thing.

N 56°04'39.26"
E 12°55'05.63"

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-28 : 10:57:31

ummmm....GROUP BY may do the Samething, but I suspect not the same way

SELECT DISTINCT (Col List)

HAS to be more effecient than

SELECT Collist
GROUP BY Collist

If you have no scaler functions, don't use GROUP BY

(You will look like a scrub)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-28 : 12:57:48

quote:
Originally posted by X002548

ummmm....GROUP BY may do the Samething, but I suspect not the same way

SELECT DISTINCT (Col List)

HAS to be more effecient than

SELECT Collist
GROUP BY Collist

Why would you think DISTINCT is more effecient that a GROUP BY?

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-28 : 13:11:35

Doesn't SQL have collect ALL of the rows as compared to just the first distinct values of a col list?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-28 : 13:35:39

So the answer is...without a sql server development team "hack" to figure out what the developer scrub is doing first, SQl Server would have done more work in the past

http://www.sql-server-performance.com/faq/select_distinct_group_by_p1.aspx

quote:

SELECT DISTINCT productcode
FROM sales

SELECT productcode
FROM sales
GROUP BY productcode

Answer

The goal of both of the above queries is to produce a list of distinct product codes from the sales table. The first query uses SELECT DISTINCT to accomplish this task, and the second query uses GROUP BY.

If you were to run these two identical queries in Query Analyzer, with the Execution Plan option turned on, you would find that not only are the results identical, but the Execution Plans are identical. The SQL Server Query Optimizer has the ability to "decipher" each query, determining what the ultimate results are, and to produce the most efficient Execution Plan possible.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources