New to SQL Server and Hit my first problem (1 Viewer)

[geoffcodd]

Hi there,

I have the following SQL which I have shortened

SELECT DISTINCT 
TMA.tblB0100_Accounts.Suspected_Non_Biller

,'BusOps_Region' = 
	CASE 
		WHEN TMA.tblA1000_Country_OpCo_Codes.Region Is Null THEN TMA.tblA1000_Country_OpCo_Codes.region
		ELSE TMA.tblA1000_Country_OpCo_Codes.region
	END

WHERE (((TMA.tblB0200_Account_Details.Inactive)=0) 
AND ((TMA.tblB0200_Account_Details.Account_Type)='Corporate') 
AND (('BusOps_Region') <> '/'))

Bit I am having an issue with is AND (('BusOps_Region') <> '/')) as BusOps_Region is set in the Select statement.

The SQL runs fins as it is but is ingoring this Where Clause.

Any help much appreciated

Thanks
Geoff

 

[SQL_Hell]

Hi there

There is no FROM clause in this sql? It wont work at all in its current format

 

[geoffcodd]

As I said I have shorted the SQL to just show the bits that are causing the issue, full SQL is

SELECT DISTINCT 
TMA.tblB0100_Accounts.Suspected_Non_Biller
, TMA.tblB0100_Accounts.On_Initial_Scrub
, TMA.tblB0200_Account_Details.Inactive
,'BusOps_Region' = 
	CASE 
		WHEN TMA.tblA1000_Country_OpCo_Codes.Region Is Null THEN TMA.tblA1000_Country_OpCo_Codes.region
		ELSE TMA.tblA1000_Country_OpCo_Codes.region
	END
, TMA.tblB0200_Account_Details.Account_Type
, TMA.tblA1000_Country_OpCo_Codes.Region_1
, TMA.tblB0200_Account_Details.NASP_ID, '/' AS NASP_Name
, TMA.tblB0200_Account_Details.Country
,'ISO_Country' = 
	CASE 
		WHEN TMA.tblA1000_Country_OpCo_Codes.C_Code Is Null THEN '/'
		ELSE TMA.tblA1000_Country_OpCo_Codes.C_Code
	END
, TMA.tblB0200_Account_Details.SAP_NASP_ID
, TMA.tblB0100_Accounts.Name AS Account_Name
, TMA.tblB0200_Account_Details.Segment
, TMA.tblB0200_Account_Details.A_End
, TMA.tblB0200_Account_Details.EMEA_HQ
, TMA.tblB0200_Account_Details.B_End
FROM (TMA.tblB0100_Accounts 
LEFT JOIN TMA.tblA1000_Country_OpCo_Codes 
ON TMA.tblB0100_Accounts.OpCo = TMA.tblA1000_Country_OpCo_Codes.OpCo_N) 
LEFT JOIN TMA.tblB0200_Account_Details 
ON TMA.tblB0100_Accounts.SAP_NASP_ID = TMA.tblB0200_Account_Details.SAP_NASP_ID
WHERE (((TMA.tblB0200_Account_Details.Inactive)=0) 
AND ((TMA.tblB0200_Account_Details.Account_Type)='Corporate') 
AND (('BusOps_Region') <> '/'))

 

[SQL_Hell]

Well you didn't actually say that, you just said you'd 'shortened' it which is ambiguous:

Anyway..

Where are you running this select?

There is nothing wrong the sql you have posted, the brakets in the where clause aren't really needed but apart from that it looks fine... are you sure the where clause is evaluating properly?

If you run the following:

SELECT *
FROM (TMA.tblB0100_Accounts 
LEFT JOIN TMA.tblA1000_Country_OpCo_Codes 
ON TMA.tblB0100_Accounts.OpCo = TMA.tblA1000_Country_OpCo_Codes.OpCo_N) 
LEFT JOIN TMA.tblB0200_Account_Details 
ON TMA.tblB0100_Accounts.SAP_NASP_ID = TMA.tblB0200_Account_Details.SAP_NASP_ID
WHERE 'BusOps_Region' = '/'

*code edited due to typo

Do you get the rows returned that you are trying to ommit in your previous select?

 

[SQL_Hell]

Ok I see what the problem is now,

You cannot reference 'BusOps_Region' in your where clause like that, you have to do it like this:

[SIZE=2][COLOR=#0000ff]SELECT [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]DISTINCT[/COLOR][/SIZE]
[SIZE=2]TMA[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]tblB0100_Accounts[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]Suspected_Non_Biller[/SIZE]
[SIZE=2][COLOR=#808080],[/COLOR][/SIZE][SIZE=2][COLOR=#ff0000]'BusOps_Region'[/COLOR][/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]CASE[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]WHEN[/COLOR][/SIZE][SIZE=2] TMA[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]tblA1000_Country_OpCo_Codes[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]Region [/SIZE][SIZE=2][COLOR=#808080]Is[/COLOR][/SIZE][SIZE=2][COLOR=#808080]Null [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]THEN[/COLOR][/SIZE][SIZE=2] TMA[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]tblA1000_Country_OpCo_Codes[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]region[/SIZE]
[SIZE=2][COLOR=#0000ff]ELSE[/COLOR][/SIZE][SIZE=2] TMA[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]tblA1000_Country_OpCo_Codes[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]region[/SIZE]
[SIZE=2][COLOR=#0000ff]END[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]WHERE[/COLOR][/SIZE][SIZE=2][COLOR=#808080]((([/COLOR][/SIZE][SIZE=2]TMA[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]tblB0200_Account_Details[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]Inactive[/SIZE][SIZE=2][COLOR=#808080])=[/COLOR][/SIZE][SIZE=2]0[/SIZE][SIZE=2][COLOR=#808080])[/COLOR][/SIZE]
[SIZE=2][COLOR=#808080]AND[/COLOR][/SIZE][SIZE=2][COLOR=#808080](([/COLOR][/SIZE][SIZE=2]TMA[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]tblB0200_Account_Details[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]Account_Type[/SIZE][SIZE=2][COLOR=#808080])=[/COLOR][/SIZE][SIZE=2][COLOR=#ff0000]'Corporate'[/COLOR][/SIZE][SIZE=2][COLOR=#808080])[/COLOR][/SIZE]
[SIZE=2][COLOR=#808080]AND[/COLOR][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]CASE[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]WHEN[/COLOR][/SIZE][SIZE=2] TMA[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]tblA1000_Country_OpCo_Codes[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]Region [/SIZE][SIZE=2][COLOR=#808080]Is[/COLOR][/SIZE][SIZE=2][COLOR=#808080]Null [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]THEN[/COLOR][/SIZE][SIZE=2] TMA[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]tblA1000_Country_OpCo_Codes[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]region[/SIZE]
[SIZE=2][COLOR=#0000ff]ELSE[/COLOR][/SIZE][SIZE=2] TMA[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]tblA1000_Country_OpCo_Codes[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]region[/SIZE]
[SIZE=2][COLOR=#0000ff]END[/COLOR][/SIZE][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][SIZE=2][COLOR=#808080]<>[/COLOR][/SIZE][SIZE=2][COLOR=#ff0000]'/'[/COLOR][/SIZE]

 

[geoffcodd]

Brilliant

Thanks very much, big change from doing stuff in Access to SQL Server but is paying dividends

Thanks

 

[SQL_Hell]

No worries

The reason you cannot use 'BusOps_Region' in your where clause like you did is because 'BusOps_Region' doesn't actually exist yet, it only gets created at run time, but your where clause is trying to reference it at compile time so therefore it's trying to reference something that doesn't exist yet.

Clear as mud?

This is a good place to come if you're having issues converting over to SQL server, there are plenty of people who are very skilled on this subject