UNION - Errror "Unable to parse query text."

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

SQL Server 2005 Forums

Transact-SQL (2005)

UNION - Errror "Unable to parse query text."

Author

Topic

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-02-26 : 09:14:08

SELECT SID, SAmAccountName, DOMAIN, EmployeeID, CustAtr, DisplayName, UPN, Date, flag, Date_Mod, Date_Exp, IsActive, OU, Description, IDType
FROM dbo.CORP_EMP_IDS as corp_emp_ids
WHERE (EmployeeID BETWEEN 'A' AND 'Z') AND (IsActive = 1) AND (EmployeeID NOT IN ('gen', 'G', 'M', 'S', 'T', 'TT', 'AdminAcct', 'TestAcct', 'ConfRoom', 'AcctAdmin')) AND (Date_Exp > GETDATE() OR
Date_Exp = '') AND (SAmAccountName NOT LIKE '%$%')

UNION

SELECT SID, SAmAccountName, DOMAIN, EmployeeID, CustAtr, DisplayName, UPN, Date, flag, Date_Mod, Date_Exp, IsActive, OU, Description, IDType
FROM dbo.CORP_EMP_IDS AS CORP_EMP_IDS_1
WHERE (CustAtr BETWEEN 'A' AND 'Z') AND (IsActive = 1) AND (CustAtr NOT IN ('gen', 'G', 'M', 'S', 'T', 'TT', 'AdminAcct', 'TestAcct', 'ConfRoom', 'AcctAdmin','AdminAccts')) AND (Date_Exp > GETDATE() OR
Date_Exp = '') AND (SAmAccountName NOT LIKE '%$%')

ORDER BY EmployeeID

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-02-26 : 11:49:54

What application are you using that is unable to parse this?

e4 d5 xd5 Nf6

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-26 : 12:01:33

Try this equivalent

SELECT DISTINCT	[SID],
		SAmAccountName,
		DOMAIN,
		EmployeeID,
		CustAtr,
		DisplayName,
		UPN,
		Date,
		flag,
		Date_Mod,
		Date_Exp,
		IsActive,
		OU,
		[Description],
		IDType
FROM		dbo.CORP_EMP_IDS
WHERE		(
			CustAtr BETWEEN 'A' AND 'Z'
			AND CustAtr NOT IN ('gen', 'G', 'M', 'S', 'T', 'TT', 'AdminAcct', 'TestAcct', 'ConfRoom', 'AcctAdmin','AdminAccts')
		OR
			EmployeeID BETWEEN 'A' AND 'Z'
			AND EmployeeID NOT IN ('gen', 'G', 'M', 'S', 'T', 'TT', 'AdminAcct', 'TestAcct', 'ConfRoom', 'AcctAdmin')
		)
		AND IsActive = 1
		AND (Date_Exp > GETDATE() OR Date_Exp = '')
		AND SAmAccountName NOT LIKE '%$%'
ORDER BY	EmployeeID

E 12°55'05.25"
N 56°04'39.16"

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-02-26 : 12:57:56

Awesom! It worked. You are a good help. Thank you.

Swordfish83
Starting Member

5 Posts

Posted - 2009-02-10 : 15:18:44

Hi Peso great job!!!, maybe you help me for this. I have the following SQL:

SELECT CENTRO_COSTO, CUENTA_CONTABLE,
(SELECT MAX(FECHA) AS FECHA
FROM AIRPAKNI.MAYOR AS MAYOR_2) AS FECHA,
(SELECT TOP (1) { fn IFNULL(SUM(DEBITO_LOCAL) * - 1, SUM(CREDITO_LOCAL)) } AS SALDO_FISC_LOCAL
FROM AIRPAKNI.MAYOR
WHERE (TIPO_ASIENTO = 'CA') AND (CUENTA_CONTABLE = MAYOR_1.CUENTA_CONTABLE) AND
(CENTRO_COSTO = MAYOR_1.CENTRO_COSTO)) AS SALDO_FISC_LOCAL, { fn IFNULL(SUM(DEBITO_LOCAL), 0)
} AS DEBITO_FISC_LOCAL, { fn IFNULL(SUM(CREDITO_LOCAL), 0) } AS CREDITO_FISC_LOCAL
FROM AIRPAKNI.MAYOR AS MAYOR_1
WHERE (CUENTA_CONTABLE > '4.0.0.00.00.00.00') AND (FECHA BETWEEN '12-01-2008' AND '12-31-2008') AND (NOT (TIPO_ASIENTO = 'CA'))
GROUP BY CENTRO_COSTO, CUENTA_CONTABLE
UNION ALL
SELECT TOP (100) PERCENT CENTRO_COSTO, CUENTA_CONTABLE, FECHA, SALDO_FISC_LOCAL, DEBITO_FISC_LOCAL, CREDITO_FISC_LOCAL
FROM AIRPAKNI.SALDO
WHERE (NOT (FECHA = '12-31-2008')) AND (CUENTA_CONTABLE > '4.0.00.00.00.00.00')
ORDER BY FECHA, CUENTA_CONTABLE, CENTRO_COSTO

But when i try open the query in the SQL 2005 display one massege, "UNABLE TO PARSE QUERY TEXT" maybe the "UNION ALL" is the problem.

Suggestion for one equivalent my friend. Thank you.....

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-10 : 16:00:45

Start a new thread on your issue instead of hijacking this one. Your main issue is the curly braces and the stuff inside them.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-10 : 16:13:57

[code]SELECT CENTRO_COSTO,
CUENTA_CONTABLE,
MAX(FECHA) AS FECHA,
SUM(CASE WHEN TIPO_ASIENTO = 'CA' THEN COALESCE(-DEBITO_LOCAL, CREDITO_LOCAL) ELSE 0 END) AS SALDO_FISC_LOCAL
SUM(CASE WHEN TIPO_ASIENTO = 'CA' THEN 0 ELSE COALESCE(DEBITO_LOCAL, 0) END) AS DEBITO_FISC_LOCAL,
SUM(CASE WHEN TIPO_ASIENTO = 'CA' THEN 0 ELSE COALESCE(CREDITO_LOCAL, 0) END) AS CREDITO_FISC_LOCAL
FROM AIRPAKNI.MAYOR
WHERE CUENTA_CONTABLE > '4.0.0.00.00.00.00'
AND FECHA >= '20081201'
AND FECHA < '20090101'
GROUP BY CENTRO_COSTO,
CUENTA_CONTABLE

UNION ALL

SELECT CENTRO_COSTO,
CUENTA_CONTABLE,
FECHA,
SALDO_FISC_LOCAL,
DEBITO_FISC_LOCAL,
CREDITO_FISC_LOCAL
FROM AIRPAKNI.SALDO
WHERE (FECHA < '20081231' OR FECHA >= '20090101')
AND CUENTA_CONTABLE > '4.0.00.00.00.00.00'

ORDER BY FECHA,
CUENTA_CONTABLE,
CENTRO_COSTO[/code]

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

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-02-10 : 18:58:47

quote:
WHERE (FECHA < '20081231' OR FECHA >= '20090101')

Doesn't that return every single date?

Some days you're the dog, and some days you're the fire hydrant.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-11 : 01:23:39

All dates, except December 31, 2008.

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

Swordfish83
Starting Member

5 Posts

Posted - 2009-02-12 : 14:40:55

mmm.... but the UNION ALL is the problem??? or not??? when open the query on my SQL Server 2005

Swordfish83
Starting Member

5 Posts

Posted - 2009-02-12 : 14:42:27

mmm.... but the UNION ALL is the problem??? or not??? when open the query on my SQL Server 2005

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-12 : 15:19:33

quote:
Originally posted by Swordfish83

mmm.... but the UNION ALL is the problem??? or not??? when open the query on my SQL Server 2005

No, its just a missing comma.

SELECT		CENTRO_COSTO,
		CUENTA_CONTABLE,
		MAX(FECHA) AS FECHA,
		SUM(CASE WHEN TIPO_ASIENTO = 'CA' THEN COALESCE(-DEBITO_LOCAL, CREDITO_LOCAL) ELSE 0 END) AS SALDO_FISC_LOCAL,
		SUM(CASE WHEN TIPO_ASIENTO = 'CA' THEN 0 ELSE COALESCE(DEBITO_LOCAL, 0) END) AS DEBITO_FISC_LOCAL,
		SUM(CASE WHEN TIPO_ASIENTO = 'CA' THEN 0 ELSE COALESCE(CREDITO_LOCAL, 0) END) AS CREDITO_FISC_LOCAL
FROM		AIRPAKNI.MAYOR
WHERE		CUENTA_CONTABLE > '4.0.0.00.00.00.00'
		AND FECHA >= '20081201'
		AND FECHA < '20090101'
GROUP BY	CENTRO_COSTO,
		CUENTA_CONTABLE

UNION ALL

SELECT		CENTRO_COSTO,
		CUENTA_CONTABLE,
		FECHA,
		SALDO_FISC_LOCAL,
		DEBITO_FISC_LOCAL,
		CREDITO_FISC_LOCAL
FROM		AIRPAKNI.SALDO
WHERE		(FECHA < '20081231' OR FECHA >= '20090101')
		AND CUENTA_CONTABLE > '4.0.00.00.00.00.00'

ORDER BY	FECHA,
		CUENTA_CONTABLE,
		CENTRO_COSTO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources