Where Clause Problem

[dand11]

I have the following select query but its returning everything and not filtering on the where clause:

declare @Article char(30);
set @Article := 'CLOTHING/FUR';

SELECT
						  'OFFENSE' AS TblSource,
						P_date as entered_date
						, CONVERT(CN, CHAR(10))  AS REFNUM
						, ''  AS UNIQUEKEY
            , IF (ISNULL(Pro_Code),'',Pro_Code) AS ARTICLE
            , IF (ISNULL(Rec_Loc), '', Rec_Loc) AS DESCRIPT
            , IF(ISNULL(serial), '', serial)  AS SERNO
            , '' AS OAN
            , IF(isnull(Model), '', Model) AS MODELNO
            , IF(isnull(Brand), '', Brand) AS BRAND
            , '' AS ORIGINATINGORG
            , now() AS DATECREATED,  CONCAT(@Article, '%','')
FROM property
            WHERE @Article is null OR UPPER(Pro_Code) LIKE CONCAT(@Article, '%')

 

[r937]

instead of

set @Article := 'CLOTHING/FUR';

try

set @Article = 'CLOTHING/FUR';



r937.com | rudy.ca

 

[dand11]

Thnks, but still its still not working correctly. I have the following now but its not filtering, still getting all records.

declare @Article char(30);
Set @Article = 'CLOTHING/FUR%';

SELECT
						  'OFFENSE' AS TblSource,
						P_date as entered_date
						, CONVERT(CN, CHAR(10))  AS REFNUM
						, ''  AS UNIQUEKEY
            , IF (ISNULL(Pro_Code),'',Pro_Code) AS ARTICLE
            , IF (ISNULL(Rec_Loc), '', Rec_Loc) AS DESCRIPT
            , IF(ISNULL(serial), '', serial)  AS SERNO
            , '' AS OAN
            , IF(isnull(Model), '', Model) AS MODELNO
            , IF(isnull(Brand), '', Brand) AS BRAND
            , '' AS ORIGINATINGORG
            , now() AS DATECREATED, @Article as article
FROM property
            WHERE @Article is null or UPPER(Pro_Code) LIKE @Article

 

[dand11]

It appears that the @Article value is null inside the select clause. I'm getting a null value returned in the Article column. I'm using MySQL Query Browser.

 

[towerbase]

dand11, I have excuted similar code to yours and it seems to work okay. Can you show us some output that is unexpected?
What is the purpose of

@Article is null

- surely @Article is never null if you assign a string to it?

Rudy, The MySQL website indicates that := should work for assignments (and is arguably better than using =).

http://dev.mysql.com/doc/refman/5.1/en/user-variables.html

Andrew
Hampshire, UK

 

[dand11]

The app gets the select clause from an XML statement. Sometimes the search criteria won't have values in the variable and I need the "@Article is null" so that it won't filter out everything else. I will be adding more in the Where clause but right now I'd like to get this smaller portion going.

When I run the query I get a null value in the column for "Article" which holds the @Article value.

 

[towerbase]

So can you provide an example of an actual SQL statement sent to MySQL and a few erroneous outputs from that SQL?

Andrew
Hampshire, UK

 

[ingresman]

what happes if you create the query with real values rather than concat etc?

 

[dand11]

I believe the actual statement sent to the MYSQL database is

declare @Article char(30);
		set @Article = 'CLOTHING/FUR%';

		SELECT  'OFFENSE' AS TblSource,
			P_date as entered_date
			, CONVERT(CN, CHAR(10))  AS REFNUM
			, ''  AS UNIQUEKEY
            		, COALESCE(Pro_Code,'') AS ARTICLE
            		, COALESCE(Rec_Loc,'') AS DESCRIPT
            		, COALESCE(serial,'') AS SERNO
           		, '' AS OAN
            		, COALESCE(Model,'')  AS MODELNO
            		, COALESCE(Brand,'') AS BRAND
            		, '' AS ORIGINATINGORG
            		, now() AS DATECREATED
			, 10 as TotalCount
                        , @Article as article
		FROM property		
			 WHERE @Article is null OR UPPER(Pro_Code) LIKE @Article;

 

[dand11]

Sorry, I forgot to add the error statement:

Event Type:	Error
Event Source:	WSInterop
Event Category:	None
Event ID:	0
Date:		9/19/2008
Time:		12:09:53 PM
User:		N/A
Computer:	MARIANNAPD
Description:
The description for Event ID ( 0 ) in Source ( WSInterop ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Reference Num: 12953-6439
Application Name: NA

Message:
OdbcException - SearchForProperty
ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.51b-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare @Art char(30);
		set @Art = 'CLOTHING/FUR%';

		SELECT  'OFFENSE' AS ' at line 1
Stack Trace:    at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
   at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(Int32 startRecord, Int32 maxRecords, DataTable[] dataTables)
   at CTS.BASE.DBObject.RunDBCommandFillDataTables(String commandText, CommandType commandType, DbParameter[] parameters, DataTable[] dataTables)
   at CTS.DBOBJECT.MARIANNA.BaseQuery`1.FillDataTable(String key, DataTable dt, T desc, String UserID, String UserName)
   at CTS.DBOBJECT.MARIANNA.PropertySearch.SearchForProperty(PropertyDescription oProperty, Int32 startRow, Int32 endRow, String UserName, String UserID)
   at CTS.DBOBJECT.MARIANNA.DBLogic.SearchForProperty(PropertyDescription oProperty, String UserID, String UserName, Int32 startRow, Int32 endRow, String pageID, String pageDir)
   at CTS.WS.DATASEARCH.BIZLogic.SearchForProperty(PropertyDescription oProp, RequestorSecurityLevel RequestorInfo, UsernameToken uToken, Int32 pageNum, String pageID, String pageDir)
   at CTS.WS.DATASEARCH.SEARCH.SearchForProperty(String Article, String Description, String SerialNO, String OwnerNO, String Model, String Brand, RequestorSecurityLevel RequestorInfo, Int32 pageNum, String pageID, String pageDir)
.

 

[DonQuichote]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare @Art char(30);

That says it all.

You probably use that statement outside of a procedure. Remove the "declare" line and it should work.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)