Can't seem to get this to work

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

Can't seem to get this to work

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2013-08-08 : 10:47:04

This SQL won't pass in Crystal Reports, any reason why? "KEYWORD FROM NOT EXPECTED"

SELECT LMLTPC, COALESCE(IRLOC1,'') as IRLOC1, COALESCE(IRLOC2,'')

as IRLOC2, COALESCE(IRLOC3,'') as IRLOC3, IRPRT#, IRQOH#, IRWHS#,

'' as IEPRT#, '.00' as IEQOH#, '' as IELOC1, '' as IELOC2, '' as

IELOC3, '' as IEWHS#

FROM

SELECT LMLTPC, LMLOC1, LMLOC2, LMLOC3 FROM ASTDTA.ICLOCMLM

WHERE LMLTPC in ('PAL', 'RAK')

left outer join

(SELECT IRLOC1, IRLOC2, IRLOC3, IRPRT#, IRQOH#, IRWHS# FROM

ASTDTA.ICBLDTIR )

On LMLOC1=IRLOC1 AND LMLOC2=IRLOC2 AND LMLOC3=IRLOC3

UNION ALL

(SELECT ' ' as LMLTPC, ' ' as IRLOC1, ' ' as IRLOC2, ' ' as IRLOC3,

'' as IRPRT#, '.00' as IRQOH#, '' as IRWHS#, IEPRT#, IEQOH#, IELOC1,

IELOC2, IELOC3,

IEWHS# FROM ASTDTA.ICBALMIE)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-08 : 10:52:32

quote:
Originally posted by AdamWest

This SQL won't pass in Crystal Reports, any reason why? "KEYWORD FROM NOT EXPECTED"

SELECT LMLTPC, COALESCE(IRLOC1,'') as IRLOC1, COALESCE(IRLOC2,'')

as IRLOC2, COALESCE(IRLOC3,'') as IRLOC3, IRPRT#, IRQOH#, IRWHS#,

'' as IEPRT#, '.00' as IEQOH#, '' as IELOC1, '' as IELOC2, '' as

IELOC3, '' as IEWHS#

FROM ( -- need a bracket here

SELECT LMLTPC, LMLOC1, LMLOC2, LMLOC3 FROM ASTDTA.ICLOCMLM
-- this is not valid syntax
WHERE LMLTPC in ('PAL', 'RAK')

left outer join

(SELECT IRLOC1, IRLOC2, IRLOC3, IRPRT#, IRQOH#, IRWHS# FROM

ASTDTA.ICBLDTIR )

On LMLOC1=IRLOC1 AND LMLOC2=IRLOC2 AND LMLOC3=IRLOC3

UNION ALL

(SELECT ' ' as LMLTPC, ' ' as IRLOC1, ' ' as IRLOC2, ' ' as IRLOC3,

'' as IRPRT#, '.00' as IRQOH#, '' as IRWHS#, IEPRT#, IEQOH#, IELOC1,

IELOC2, IELOC3,

IEWHS# FROM ASTDTA.ICBALMIE)

There seems to be serious problems with the syntax. For example, the LEFT OUTER JOIN after a WHERE clause is not valid syntax.

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2013-08-08 : 10:59:08

ok but where would the where part go? We need that.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-08 : 11:13:21

Since I don't know the logic you are trying to implment, the following is only a guess. This should overcome any parsing errors.

SELECT 
	LMLTPC, 
	COALESCE(IRLOC1,'') as IRLOC1, 
	COALESCE(IRLOC2,'') as IRLOC2, 
	COALESCE(IRLOC3,'') as IRLOC3, 
	IRPRT#, 
	IRQOH#, 
	IRWHS#,
	'' as IEPRT#, 
	'.00' as IEQOH#, 
	'' as IELOC1, 
	'' as IELOC2, 
	'' as IELOC3, 
	'' as IEWHS#
FROM
(
	SELECT 
		LMLTPC, 
		LMLOC1, 
		LMLOC2, 
		LMLOC3 
	FROM 
		ASTDTA.ICLOCMLM
	left outer join
	(
		SELECT 
			IRLOC1, 
			IRLOC2, 
			IRLOC3, 
			IRPRT#, 
			IRQOH#, 
			IRWHS# 
		FROM
			ASTDTA.ICBLDTIR 
	) s2 On 
		LMLOC1=IRLOC1 AND LMLOC2=IRLOC2 AND LMLOC3=IRLOC3 
WHERE 
	LMLTPC in ('PAL', 'RAK')
) s2
UNION ALL
SELECT 
	' ' as LMLTPC, 
	' ' as IRLOC1, 
	' ' as IRLOC2, 
	' ' as IRLOC3,
	'' as IRPRT#, 
	'.00' as IRQOH#, 
	'' as IRWHS#, 
	IEPRT#, 
	IEQOH#, 
	IELOC1,
	IELOC2, 
	IELOC3,
	IEWHS# 
FROM 
	ASTDTA.ICBALMIE

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2013-08-08 : 15:18:14

James, thanks for this,
i run and it gets error

IRWHS# not in specified tables/

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-08 : 15:24:15

quote:

Originally posted by James K

Since I don't know the logic you are trying to implment, the following is only a guess. This should overcome any parsing errors.

SELECT 
	LMLTPC, 
	COALESCE(IRLOC1,'') as IRLOC1, 
	COALESCE(IRLOC2,'') as IRLOC2, 
	COALESCE(IRLOC3,'') as IRLOC3, 
	IRPRT#, 
	IRQOH#, 
	IRWHS#,
	'' as IEPRT#, 
	'.00' as IEQOH#, 
	'' as IELOC1, 
	'' as IELOC2, 
	'' as IELOC3, 
	'' as IEWHS#
FROM
(
	SELECT 
		LMLTPC, 
		LMLOC1, 
		LMLOC2, 
		LMLOC3,
		IRPRT#, 
		IRQOH#, 
		IRWHS# 
 	FROM 
		ASTDTA.ICLOCMLM
	left outer join
	(
		SELECT 
			IRLOC1, 
			IRLOC2, 
			IRLOC3, 
			IRPRT#, 
			IRQOH#, 
			IRWHS# 
		FROM
			ASTDTA.ICBLDTIR 
	) s2 On 
		LMLOC1=IRLOC1 AND LMLOC2=IRLOC2 AND LMLOC3=IRLOC3 
WHERE 
	LMLTPC in ('PAL', 'RAK')
) s2
UNION ALL
SELECT 
	' ' as LMLTPC, 
	' ' as IRLOC1, 
	' ' as IRLOC2, 
	' ' as IRLOC3,
	'' as IRPRT#, 
	'.00' as IRQOH#, 
	'' as IRWHS#, 
	IEPRT#, 
	IEQOH#, 
	IELOC1,
	IELOC2, 
	IELOC3,
	IEWHS# 
FROM 
	ASTDTA.ICBALMIE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources