Trouble Getting SQL Query To Work Using LIKE Clause With Astersik (*) As Wildcard In ADO.net

Apr 13, 2006

Please help.

Has anyone seen a problem querying Excel or Access database when the "LIKE" clause is used with the "*" wildcard? The problem I'm seeing is that the query fails to return a dataset whenever "*" wildcard is used.



For Example,

sQry="Select * From [EmployeeData$] WHERE Id LIKE 'AAA'" 'query WORKS

sQry="Select * From [EmployeeData$] WHERE Id LIKE 'AAA*'" 'query does NOT WORK

Assume that Table name is "EmployeeData", with "ID", "Name" and "Birthdate" as Fields.

Data:
ID Name Birthdate







AAA
Aaron
5/4/1975

CCC
Charlie
10/14/1948

DDD
Deloris
7/19/1998

The code I use is listed as follows:

Imports System.Data.OleDb


Imports System.Data
Public Class Form1


Private m_sConn1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:ExcelData1.xls;" & _

"Extended Properties=""Excel 8.0;HDR=YES"""

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click


'Use a DataSet to Query data from the EmployeeData table.

Dim QryConn As New OleDbConnection(m_sConn1)

'Dim strQry as string ="Select * From [EmployeeData$] WHERE Id LIKE 'AAA'" '- works

Dim strQry as string ="Select * From [EmployeeData$] WHERE Id LIKE 'AA*'" ' does not work

Dim da As New OleDbDataAdapter(strQry, QryConn)

Dim ds As DataSet = New DataSet

da.Fill(ds)

Dim dr As DataRow

For Each dr In ds.Tables(0).Rows 'Show results in output window

Debug.WriteLine(dr("Id") & ", " & dr("Name") & ", " & dr("Birthdate"))

'Expected output:






'AAA
Aaron
5/4/1975

Next

QryConn.Close()

End Sub

End Class

View 1 Replies


ADVERTISEMENT

Wildcard Used For SQL IN Clause

Dec 30, 2006

I am trying to control how users view records.  I want to create a solution that would, for instance permit:user A to view Store 1user B to view store 2 and store 3user C to view store 5 and store 6and User D to view all stores even if more stores were added in the future (in other words user D would have access to all records)
I want to create an 'authorization table' so that different users can see different records.  I think the easiest way to do this is to pass a parameter to the where clause, but the problem that I face is the how can I use a wildcard in a SQL 'IN' clause?  Does anyone have any suggestions.  Perhaps I am taking the wrong approach.  I would appreciate any guidance.

View 2 Replies View Related

Wildcard In The WHERE Clause

Jan 28, 2007

Hello,
I'm trying to pull some data from a table with the option to filter on 2 columns.
I've set up my sql statement to accept 2 parameters and I'd like to be able to send 1 or 2 wildcards if needed. My statement looks like this:SELECT *
FROM City WHERE CityName= @CityName AND State= @State
ORDER BY CityName
For example, if you wanted all of the cities in all of the states I would pass (*,*) as parameters.
Or if I wanted to see all of the states that have a city named Richmond, I'd pass (Richmond,*) as the parameters.
The wildcards are not returning anything and I don't know why. It works fine if I pass something like (Indianapolis,Indiana) as parameters so I think it's in my use of the wildcards that is wrong.
 Thanks.

View 4 Replies View Related

Trouble With A Where Clause

May 5, 2008

I've tried to post this somewhere else, but I haven't gotten it figured out yet:
I'm passing variables through a URL, and the page that accepts the variables has a where clause like this:"WHERE (name1 LIKE  @name1 + '%') and (property_add like @property_add + '%') and (ctlmap  = @ctlmap )"On the search page where the variables are entered, the user may not know all the information that goes into each input field.  (If the user just knew the name, they would search by name.  If the user just knew the address, they would search by address...etc.) Everything works great before I add the ctlmap variable.  When the ctlmap variable is added, no search results will turn up unless there is an entry made to the ctlmap field.  I know it has to have something to do with the ctlmap being set to equals rather than like, but I can't find a way to make the search work with a null value for ctlmap.  Thanks, everyone. Additional Info:        <SelectParameters>            <asp:QueryStringParameter ConvertEmptyStringToNull="False" Name="name1"                 QueryStringField="name" Type="String" />            <asp:QueryStringParameter ConvertEmptyStringToNull="False" Name="property_add"                 QueryStringField="address" />            <asp:QueryStringParameter ConvertEmptyStringToNull="False" Name="ctlmap"                 QueryStringField="ctlmap" />        </SelectParameters>
Here's another type of where clause that I tried but had no luck with:
WHERE (name1 LIKE  @name1 + '%') and (property_add like @property_add + '%') and (@ctlmap IS NULL or @ctlmap = ctlmap)
This is how I would try to do it in php/mysql, but I don't know how to make it work in asp.net/sql:
  if ($ctlmap != "")      {            $whereClause = $whereClause." and ctlmp = '".strtoupper($ctlmap)."' ";      }                     if ($whereClause != "")      {            $whereClause = " where ".$whereClause;      }Here's the MYSQL counterpart:$sqlString = "Select distinct district, map, bill_group, ctlmap, bill_parcel, propertyid, special, property_add, name1 from MUR_bill_master".$whereClause";
 
Can anyone help?
 

View 11 Replies View Related

Trouble With Having Clause

Jun 6, 2008

I am currently working with the following query:

SELECT w.styleId, w.modelyear, w.vehdesc,

ISNULL(CONVERT(varchar,wp.algfmv-isnull(ROUND(vp.value,0),0)),'') AS previousPeriodBeforeAlgAnalystFactor,

CONVERT(varchar,ISNULL(ROUND(vp.value,0),'')) as previousPeriodAlgAnalystFactor,

CONVERT(varchar,ISNULL(wp.algFmv,'')) AS previousPeriodAlgFmvAfterAnalystFactor,

CONVERT(varchar,ISNULL(vpaf.value,'')) As previousPeriodIntAnalystFactor,

CONVERT(varchar,ISNULL(wp.intFinalFmv,'')) AS previousPeriodFinalIntFmv,

CONVERT(varchar,ISNULL(w.algFmv,'')) AS currentPeriodAlgFmv,

'' AS currentPeriodAlgAnalystFactor

FROM RPM_CHR.dbo.Workspace w

LEFT OUTER JOIN RPM_CHR.dbo.Workspace wp ON wp.periodId = w.periodId - 1 AND wp.styleId = w.styleId

LEFT OUTER JOIN RPM_CHR.dbo.ValuesByStyle vp ON vp.periodId = wp.periodId AND vp.styleId = wp.styleId AND vp.valtype=45

LEFT OUTER JOIN RPM_CHR.dbo.ValuesByStyle vpaf ON vpaf.periodId = wp.periodId AND vpaf.styleId = wp.styleId AND vpaf.valtype = 20

WHERE w.periodId = 194 and (wp.algfmv > 0 or w.algfmv>0)

order by w.vehdesc, w.modelyear desc



This works well but instead of having to say "w.periodid=194" I would like w.periodid = max(w.perioidid). When I try to accomplish this using a having clause by replacing

"WHERE w.periodId = 194 and (wp.algfmv > 0 or w.algfmv>0)

order by w.vehdesc, w.modelyear desc
"

with

"WHERE (wp.algfmv > 0 or w.algfmv>0)

group by w.periodid, w.styleId, w.modelyear, w.vehdesc, w.algFmv,wp.intFinalFmv,vpaf.value,wp.algFmv,
vp.value

Having max(w.periodid)= w.periodid

order by w.vehdesc, w.modelyear desc"

it returns all the records in my table. Could anyone suggest a better way of doing this, or point out where I am erring?

Any help would be greatly appreciated.

I think I should add, the whole point of doing this is that there is a new perioidid each week and I want to only look at records with the current periodid.

Thanks,
Tasha

View 2 Replies View Related

Trouble Getting RS To Work On Vista Business

Nov 7, 2007

I've just upgraded SQL 2005 Standard to Developer on Vista Business (actually had to uninstall whole SQL 2005 product in order for the upgrade process to work)

But it seems that RS didn't completely uninstall (the new install said it was still there).

I've reapplied SP2 and The DB Engine and SSAS seem to be fine - but unfortunately RS is now crippled.

I've re-attached the two RS databases - ReportServer and ReportServerTempDB

Everything looks OK in the RS Configuration Manager - the DB version is reported as C.0.8.54 in the Database Setup page, and all the options above are Green Ticked.

But when I try to browse to the ReportServer from IE (Started as Administrator) it gives "Server Application Unavailable"


The Application Error log says "A request mapped to aspnet_isapi.dll was made within an application pool running in Integrated .NET mode. Aspnet_isapi.dll can only be used when running in Classic .NET mode. Please either specify preCondition="ISAPImode" on the handler mapping to make it run only in application pools running in Classic .NET mode, or move the application to another application pool running in Classic .NET mode in order to use this handler mapping."

An earlier error from IE suggested running C:Windows>system32inetsrvAPPCMD.EXE migrate config "Default Web Site/ReportServer"
which was successful: "Successfully migrated section "system.web/httpHandlers"

Other than that I have changed nothing in the IIS setup from when RS was working fine.

When I try and connect to RS from SSMS it gives the error "

The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable) Get Online Help

Login failed for user 'NT AUTHORITYNETWORK SERVICE'.
"
I'm sufferring all this nightmare because I need semi-additive measures in SSAS for a customer demo I'm trying to prepare - which wasn't available in the Standard Edition.

I've already had to wipe Vista off my laptop once before - in order to completely uninstall RS - I'd rather not have to do it again if possible. (I've also tried going back to XP, but my nice fast new Dell Inspiron 1721 doesn't have any XP display drivers and Dell say they only support Vista on this machine!)


Any help on getting RS going on Vista - much appreciated!

Clive (London UK)

View 3 Replies View Related

Trouble With Trigger: Doesn't Seem To Work At All And No Error Messages.

Oct 3, 2007

Hi Everyone,
I'm having some trouble with the below trigger. When I add a row of data either manually or using an INSERT query, it just doesn't do anything. It doesn't provide any error messages either. This makes me think that it's aborting the operation because rowsAffected are 0 or some other simple error. My row manipulation code could be suspect also. This is my first time writing a trigger or using T-SQL for that matter.
What I'm trying to do is to have the trigger add +1 to the Iter field of all rows where BouleID is equal to the BouleID of the row being inserted. So let's say I have the following table:


BouleID CurrentLocation Iter
A01 Inventory 1
A01 Cutting 0
A01 WIP 2
B01 WIP 0
B02 WIP 1
B02 Inventory 0

Now, if I insert a row with BouleID = A01 and Current Location = Polishing, I want the Iter field of all previous rows to iterate by +1 and this new row to have Iter = 0.

I am using SQL Management Studio Express, and SQL Server Express.

Any thoughts on anything wrong with my selection code and iteration code? Could I adapt this to handle more than one row by using a GROUP BY BouleID somehow?






Code Block

SET ANSI_NULLS ON


GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description: <this trigger will iterate the Iter field by one for each rows that has the BouleID matching the one of
-- the row being inserted>
-- =============================================
CREATE TRIGGER dbo.trgCG_DispoIterate$InsertTrigger
ON dbo.dbCG_Disposition
AFTER INSERT AS
BEGIN

DECLARE @rowsAffected int,
@msg varchar(2000), --for error message
@BouleID varchar(6) -- and do I need to enter more than one?

SET @rowsAffected = @@rowcount

IF (@rowsAffected = 0 or @rowsAffected > 1 ) RETURN --don't continue if no rows changed or doing more than one at a time.
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
SET ROWCOUNT 0

BEGIN TRY

--VALIDATION BLOCK Leave this alone for now

SELECT @BouleID = BouleID FROM Inserted --sets @bouleID equal to the BouleID of the row being inserted.

UPDATE dbo.dbCG_Disposition --This block sets the Iter field to it's previous value +1
SET Iter = ( Iter + 1 ) --
WHERE BouleID = @BouleID --


END TRY
BEGIN CATCH
IF @@TRANCOUNT >0
ROLLBACK TRANSACTION

--or this will get rolled back
EXECUTE dbo.ErrorLog$Insert -- this function creates an errorlog table which gets filled up if there is an error in the try block.

DECLARE @ERROR_MESSAGE nvarchar(4000)
SET @ERROR_MESSAGE = ERROR_MESSAGE()
RAISERROR (@ERROR_MESSAGE, 16, 1)
END CATCH

View 6 Replies View Related

Wildcard In Query?

Dec 6, 2007

I screwed up my database and double added data to a text field.

For example I need made this:

'http://192.168.200.1/images/' +PATH+ '.jpg'

Look like this:

'http://192.168.200.1/images/http://192.168.200.1/images/12345.jpg'

How can I query to reset this?

update perimage
set PERIMAGE_PATH = 'http://192.168.200.1/images/' +PATH+ '.jpg'
WHERE PERIMAGE_PATH <> 'http://192.168.200.1/images/' +PATH+ '.jpg'

That just makes the problem worse

View 3 Replies View Related

Wildcard In Query?

Dec 11, 2007

I want to update data only where the value of the 'image_path' column is NOT = 192.168.150.12/images/*

Im basically trying to exclude creating duplicates, where this path already exists.

Here is my code:


INSERT INTO IMAGE (FCN, IMAGE_NAME2)
SELECT FCN, Col066
FROM GRAB where Col066 <> ' '
update IMAGE
Set PERIMAGE_PATH = 'http://192.168.150.12/images/' +IMAGE_NAME2+ '.jpg'
FROM IMAGE WHERE image_name2 IS NOT NULL and perimage_path is NOT = 192.168.150.12/images/*

What is the proper code to do this. I know the last line does not work. Thanks

View 7 Replies View Related

Query On SQL CE 3.5 With Wildcard

Jan 9, 2008

Hi,

I have a problem with SQL CE 3.5 and VS 2005. When I execute query with parameters and wildcard, I have an error : FormatException.
Here is my query with parameters :
Query: SELECT "PARAGRAPHES"."PARA_ID" AS "Numero","PARAGRAPHES"."PARA_SECT_ID" AS "Section","PARAGRAPHES"."PARA_NUMERO" AS "Rang","PARAGRAPHES"."PARA_GROUPE" AS "Groupe","PARAGRAPHES"."PARA_ENTITES" AS "Entites","PARAGRAPHES"."PARA_LBL_COURT" AS "Court","PARAGRAPHES"."PARA_LBL_LONG" AS "Long","PARAGRAPHES"."PARA_ACTIVE" AS "Active","PARAGRAPHES"."PARA_CONDITIONS" AS "Conditions","PARAGRAPHES"."PARA_PARENTS" AS "Parents","PARAGRAPHES"."PARA_QUESTIONS" AS "QuestionsConditions" FROM "PARAGRAPHES" WHERE ( ( ( ( "PARAGRAPHES"."PARA_SECT_ID" = ? AND ( ( "PARAGRAPHES"."PARA_ENTITES" LIKE ?) OR "PARAGRAPHES"."PARA_ENTITES" IS NULL))) AND "PARAGRAPHES"."PARA_ID" IN (SELECT "DOSSIER_LINKS"."DOLI_PARA_ID" AS "Paragraphe" FROM "DOSSIER_LINKS" WHERE "DOSSIER_LINKS"."DOLI_DOSS_ID" = ?) AND "PARAGRAPHES"."PARA_NUMERO" = ?))
Parameter: @Section1 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 52.
Parameter: @Entites2 : String. Length: 3. Precision: 0. Scale: 0. Direction: Input. Value: "%D%".
Parameter: @Dossier3 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
Parameter: @Rang4 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2.
and my error :
[System.FormatException] = {"@Entites2 : %D% - FormatException"}

If I execute query in Query Analyzer (without parameter : values directly on query) there is no errors.

ThanksFabien

View 1 Replies View Related

SQL Parameter Query && Wildcard

Sep 22, 2006

I just migrated a stand alone MSAccess2003 .mdb to an .adp Access Data Project. In the former .mdb I had a parameter query to search for a portion of a 17 character part number, primarily the 2 grouping of 4 digits as in (00-0000-XXXX-0-00). In Access the query was written as

LIKE “*”&[Enter a portion of the part number]&”*”

Then we ported to a web based portal using MSAccess’s DAP (data access pages) and I was told due to ADO vs DAO the query had to be rewritten using the % symbol as in

LIKE “%”&[Enter a portion of the part number]&”%”

So far so good. However with the migration to a SQL Server and ANSI-92 compatible wildcard characters this has changed. The new query looks like this

LIKE @Enter_a_portion_of_the_part_number +n’%’

The problem I am having is the prompt for data entry works but the wildcard will only return matching values from the first digit place holder. Some of the books I have read said to use the Under Score character to move the search criteria to the section I want to search which would be starting with the 9th character (00-0000-XXXX-0-00) as in

LIKE @Enter_a_portion_of_the_part_number +n’________%

However the Under Score only works with Alpha Characters and not Numeric.

So how do I write a SQL Server Query to perform like it did in Access?

View 2 Replies View Related

Should This WHERE Clause Work?

Jan 16, 2008

Hi,I am having trouble with a WHERE clause:WHERE (([A] = @A) AND ([B] >= @B) AND (([C] < [D])) OR ([C] = 0 AND [D] = 0)) It's meant to only select data where A = @A, and B is more than or equal to @B, and one of the next two are true: C is less than D or C and D are both 0 Thanks,Jon 

View 3 Replies View Related

WHERE Clause Will Not Work

Feb 27, 2008

I am using the below WHERE clause and it will not filter out records where Companies.CompanyEmail = b@c.com
 WHERE (Companies.CompanyEmail <> 'a@a.com')  this does not work, is the @ causing a problem?  also tried 'a@a.com%' with no success.
WHERE (tblCompanies.C_CompanyEmail <> 'none')  this works fine.
Thank you
 
 

View 1 Replies View Related

WHERE Clause Does Not Work. Why?

Nov 2, 2006

I am asking for help to resolve this simple problem. It is a stored procedure. It is supposed to return a single row from a table. The table may have max 300 rows but now there is only one row in it. When I execute the query in SQLEXPRESS in the New Query Editor Window with the correct parameter it returns empty row.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CentrCtrl_SelectStats]
@symbol varchar (10) = 0
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM dbo.CenC_stocksStats WHERE symbol = '@symbol'
END

Now, I varied this simple statement this way and that way. I changed it into

SELECT * FROM dbo.CenC_stocksStats WHERE symbol = @symbol

with the same negative result: empty rowset.
If I remove the WHERE clause, the resulting set is OK--it is what I expect, but now the table has a single row and I do not want to have the entire table when it gets bigger in my C# code if I could retreive only the row corresponding to symbol I need. What is my problem in here? I am at a loss.

Thank you.

View 8 Replies View Related

LIKE Query With Table Of Wildcard Strings

Mar 7, 2008

The sql below is a simple example of LIKE query with a table of wildcard strings. I hope this helps others.

declare @LikeTable Table
(
LikeValue nvarchar(50)
)

insert into @LikeTable (LikeValue) values ('%blah')
insert into @LikeTable (LikeValue) values ('%abc%')
insert into @LikeTable (LikeValue) values ('%edf%')
insert into @LikeTable (LikeValue) values ('car%')
insert into @LikeTable (LikeValue) values ('%ome%')

declare @CompareValue nvarchar(50)
set @CompareValue = 'some value'

select * from @LikeTable where (@CompareValue like LikeValue)

View 4 Replies View Related

Question About Different Query Results With Wildcard

Dec 22, 2006

Hi, I'm working with a third party app on SQL Server 2000, and from what I can gather, programmed in C# & VisualFoxPro.

When we search with
Note contains 94949
we get 571 results, when we search with
Note contains 94949*
we get 575 results.

There should be at least a hundred different entries that start with "94949-1" so I expected the query with the wildcard to return something like 680 results, not an additional four rows.

Searching with
Note contains 94949-1*
got 483 results
Note contains 94949-10*
got 0 results


Could someone explain or point me to more documentation on the difference results we get?
Thanks

View 5 Replies View Related

HAVING Clause Does Not Work Like Sql92

Jul 19, 1999

CREATE TABLE [test] (
[label] [char] (10) ,
[order] [int]
)

INSERT INTO test ([label],[order]) VALUES ('a',1)
INSERT INTO test ([label],[order]) VALUES ('a',2)
INSERT INTO test ([label],[order]) VALUES ('b',1)
INSERT INTO test ([label],[order]) VALUES ('b',2)

SELECT
[label],[order]
FROM
test
GROUP BY
[label]
HAVING
[order] = max([order])

Server: Msg 8120, Level 16, State 1, Line 1
Column 'test.order' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8121, Level 16, State 1, Line 1
Column 'test.order' is invalid in the HAVING clause because it is not
contained in either an aggregate function or the GROUP BY clause.

View 2 Replies View Related

Handling Wildcard Characters In Query String

Nov 11, 2005

Hi

First interaction to the forum.
My Query is :

I had a User Management module in my application where I created a user with name

`~!@#$@%^&*()[_]+|}{":?><-=[[]];',./

Now I have a functionality to search for the user existing. For that give the search string or a single character and it finds out all the records containing the character.

How do I go about it as the SP i created for it gives correct results except the following

1. Search for % - Gives all record
2. Search for _ - Gives all records
3. Search for [ - Gives NO record
4. Search for the whole string - Gives NO Record

I handeled a few issues

1. replaced [ by [[]
2. replaced _ by [_]

So issues 2 & 3 are resolved.

Tried replacing % by [%] but did not work


Could someone plz help

Thanks in advance
Ashutosh

View 11 Replies View Related

ORDER BY Clause Does Not Work In SQL 2005?

Feb 6, 2006

Hi,
A quick background on the problem;
My company is in the process of a migration from Windows Advanced Server 2K, SQL 2K to Server 2003 and SQL 2005. I'm not certain of the exact process used by our DBA to convert the DB, but I can access it, and all my tables/views/sprocs appear to be in the right place.
I copied all my web files to our new server after the DBA was done with her job, made a new user on the new instance of SQL server, changed a few connection strings in my global.asa and global.asax, and ta-dah! Just like magic, the new site opened on our new servers without much resistance.
Except....
None of the content on our sites is sorted. I cannot seem to get ORDER BY statements to work at all. They appear to be disregarded by SQL server when not in MODIFY mode for a particular view (in SQL Server Management Studio).
So, when I MODIFY a view, add criteria (NOT SORT), save the changes, then OPEN, the criteria is respected. The filter is applied. BUT...
If I MODIFY a view and add a SORT using ORDER BY (by hand or with the Manager) the sort is NEVER respected when the view is Opened through the manager or in my code.
If I open any sorted view and then click MODIFY, and then RUN (without making ANY changes), the sort works with no problem whatsoever.
To summarize/restate my case, if I OPEN any view in the system that has an ORDER BY criteria, the sort is NOT APPLIED. If I instead right-click and MODIFY, then click RUN, the SORT is APPLIED.
I've tried sorting datetime and text fields, all with the same results - none. This single dumb issue has been delaying the migration of our servers for days!
Can anyone help?
 
Thanks always in advance,
Drew
 

View 10 Replies View Related

Left Join With Where Clause Does Not Work

May 25, 2012

I have tables A and B.

Table A has data, but B may have or not.

I have to get -- at least! -- the A table data.

It works when I dont have the where clause:

select * from A left outer join B on A.IDUser = B.IDUser

But it doesn't when I try to use the WHERE clause:

select * from A left outer join B on A.IDUser = B.IDUser
where A.IDUser = 2

View 3 Replies View Related

'Order By' Clause Work Incorrect

Apr 28, 2004

when i try the following SQL batch, I get a result-set which is not order by
datetime column 'out_date',but if I delete clause INTO #fifo_temp, I get a correct result with correct order.

who can help me?thanks in advance
...
select tag,stuff_id,stuff_name,cast(out_id as char(10)) as out_id,out_number,out_date,out_qty,remark
INTO #fifo_temp from ##stuff_fifo UNION
select tag,stuff_id,stuff_name,out_id,null,out_date,quant ity,remark
from acc_cost.dbo.stuff_out where tag='A' and left(out_id,3) in ('XSA','TAP')
ORDER BY out_date

DROP TABLE ##stuff_fifo
select * from #fifo_temp

the following can get a correct result:

select tag,stuff_id,stuff_name,cast(out_id as char(10)) as out_id,out_number,out_date,out_qty,remark
from ##stuff_fifo UNION
select tag,stuff_id,stuff_name,out_id,null,out_date,quant ity,remark
from acc_cost.dbo.stuff_out where tag='A' and left(out_id,3) in ('XSA','TAP')
ORDER BY out_date

View 4 Replies View Related

Parameterized Order By Clause: Doesn't Work

Jul 23, 2005

Can someone tell me why SQL seems to ignore my order by clause?I tried to run through the debugger, but the debugger stops at theselect statement line and then returns the result set; so, I have noidea how it is evaluating the order by clause.THANK YOU!CREATE proc sprAllBooks@SortAscend varchar(4),@SortColumn varchar(10)asIf @SortAscend = 'DESC'Select titles.title_id, title, au_lname, au_fname,Convert(varchar(12), pubdate, 101) as PubDatefrom authorsinner jointitleauthoronauthors.au_id = titleauthor.au_idinner jointitlesontitleauthor.title_id = Titles.title_idORDER BY au_lnameCASE @SortColumn WHEN 'title' THEN title END,CASE @SortColumn WHEN 'au_lname' THEN au_lname END,CASE @SortColumn WHEN 'PubDate' THEN PubDate ENDDESCELSESelect titles.title_id, title, au_lname, au_fname,Convert(varchar(12), pubdate, 101) as PubDatefrom authorsinner jointitleauthoronauthors.au_id = titleauthor.au_idinner jointitlesontitleauthor.title_id = Titles.title_idORDER BYCASE @SortColumn WHEN 'title' THEN title END,CASE @SortColumn WHEN 'au_lname' THEN au_lname END,CASE @SortColumn WHEN 'PubDate' THEN PubDate ENDGO

View 7 Replies View Related

Having Trouble Getting SP From Sql Server 2005 To Work In SQL Server 2000

Sep 18, 2006

I am getting an error saying incorrect syntax near fIt works in SQL Server 2005, but I cannot get it to work in SQL Server 2000  The error appears to be in the section that I marked in Bold. CREATE PROCEDURE [dbo].[pe_getReport]  -- Add the parameters for the stored procedure here    @BranchID INT,    @InvestorID INT,    @Status INT,    @QCAssigned INT,    @LoanOfficer nvarChar(40),    @FromCloseDate DateTime,    @ToCloseDate DateTime,    @OrderBy nvarChar(50)ASDECLARE         @l_Sql NVarChar(4000),        @l_OrderBy NVarChar(500),        @l_OrderCol NVarChar(150),        @l_CountSql NVarChar(4000),        @l_Where NVarChar(4000),        @l_SortDir nvarChar(4)BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.   SET NOCOUNT ON;    SET @l_Where = N' Where 1=1'    IF (@BranchID IS NOT NULL)       SET @l_Where = @l_Where + N' AND f.BranchID=' + CAST(@BranchID As NVarChar)    IF (@Status IS NOT NULL)       SET @l_Where = @l_Where + N' AND f.Status=' + CAST(@Status As NVarChar)    IF (@InvestorID IS NOT NULL)       SET @l_Where = @l_Where + N' AND f.InvestorID=' + CAST(@InvestorID As NVarChar)    IF (@QCAssigned IS NOT NULL)       SET @l_Where = @l_Where + N' AND f.QCAssigned=' + CAST(@QCAssigned As NVarChar)    IF (@LoanOfficer IS NOT NULL)       SET @l_Where = @l_Where + N' AND f.LoanOfficer LIKE ''' + @LoanOfficer + '%'''    IF (@FromCloseDate IS NOT NULL)       SET @l_Where = @l_Where + N' AND f.ClosingDate>=''' + CAST(@FromCloseDate AS NVarChar) + ''''    IF (@ToCloseDate IS NOT NULL)       SET @l_Where = @l_Where + N' AND f.ClosingDate<=''' + CAST(@ToCloseDate AS NVarChar) + ''''    IF @OrderBy IS NULL      SET @OrderBy = 'DateEntered DESC'   SET @l_SortDir = SUBSTRING(@OrderBy, CHARINDEX(' ', @OrderBy) + 1, LEN(@OrderBy))   SET @l_OrderCol = SUBSTRING(@OrderBy, 1, NULLIF(CHARINDEX(' ', @OrderBy) - 1, -1))  IF @l_OrderCol = 'InvestorName'     SET @l_OrderBy = 'i.InvestorName ' + @l_SortDir  ELSE IF  @l_OrderCol = 'BName'     SET @l_OrderBy = 'b.BName ' + @l_SortDir  ELSE IF  @l_OrderCol = 'StatusDesc'     SET @l_OrderBy = 's.StatusDesc ' + @l_SortDir  ELSE IF  @l_OrderCol = 'QCAssigned'     SET @l_OrderBy = 'q.LoginName ' + @l_SortDir  ELSE SET @l_OrderBy = 'f.' + @l_OrderCol + ' ' + @l_SortDir  SET @l_CountSql = 'SELECT f.FundedID As FundedID FROM FundedInfo AS f LEFT OUTER JOIN                     Investors AS i ON f.InvestorID = i.InvestorID LEFT OUTER JOIN                     Branches AS b ON f.BranchID = b.BranchID LEFT OUTER JOIN                     Status AS s ON f.Status = s.StatusID LEFT OUTER JOIN                     QCLogins AS q f.QCAssigned = q.LoginID '                     + @l_Where + ' ORDER BY ' + @l_OrderBy    CREATE TABLE #RsltTable (ID int IDENTITY PRIMARY KEY, FundedID int)  INSERT INTO #RsltTable(FundedID)  EXECUTE (@l_CountSql)SELECT f.DateEntered As DateEntered, f.LastName As LastName, f.LoanNumber As LoanNumber,       f.LoanOfficer As LoanOfficer, f.ClosingDate As ClosingDate,       i.InvestorName As InvestorName, b.BName As BName, s.StatusDesc As StatusDesc,       q.LoginName As LoginNameFROM       FundedInfo AS f LEFT OUTER JOIN       Investors AS i ON f.InvestorID = i.InvestorID LEFT OUTER JOIN       Branches AS b ON f.BranchID = b.BranchID LEFT OUTER JOIN       Status AS s ON f.Status = s.StatusID LEFT OUTER JOIN       QCLogins As q ON f.QCAssigned = q.LoginID WHERE FundedID IN(SELECT FundedID FROM #rsltTable) ORDER BY       CASE @OrderBy WHEN 'DateEntered ASC' THEN f.DateEntered END ASC,       CASE @OrderBy WHEN 'DateEntered DESC' THEN f.DateEntered END DESC,       CASE @OrderBy WHEN 'LastName ASC' THEN f.LastName END ASC,       CASE @OrderBy WHEN 'LastName DESC' THEN f.LastName END DESC,       CASE @OrderBy WHEN 'LoanNumber ASC' THEN f.LoanNumber END ASC,       CASE @OrderBy WHEN 'LoanNumber DESC' THEN f.LoanNumber END DESC,       CASE @OrderBy WHEN 'LoanOfficer ASC' THEN f.LoanOfficer END ASC,       CASE @OrderBy WHEN 'LoanOfficer DESC' THEN f.LoanOfficer END DESC,       CASE @OrderBy WHEN 'ClosingDate ASC' THEN f.ClosingDate END ASC,       CASE @OrderBy WHEN 'ClosingDate DESC' THEN f.ClosingDate END DESC,       CASE @OrderBy WHEN 'InvestorName ASC' THEN i.InvestorName END ASC,       CASE @OrderBy WHEN 'InvestorName DESC' THEN i.InvestorName END DESC,       CASE @OrderBy WHEN 'BName ASC' THEN b.BName END ASC,       CASE @OrderBy WHEN 'BName DESC' THEN b.BName END DESC,       CASE @OrderBy WHEN 'StatusDesc ASC' THEN s.StatusDesc END ASC,       CASE @OrderBy WHEN 'StatusDesc DESC' THEN s.StatusDesc END DESC,       CASE @OrderBy WHEN 'LoginName ASC' THEN q.LoginName END ASC,       CASE @OrderBy WHEN 'LoginName DESC' THEN q.LoginName END DESCENDGO

View 2 Replies View Related

Having Trouble With LIKE In Query

Mar 2, 2007

I have a table adapter query with a like clause that I can't get to work.  The field is "Type", so I have "LIKE '%@Type%'".  When I click the Execute Query button to test, not only does nothing get returned, I don't get the chance to enter the parameter.  If I change LIKE '%@Type%' to say, LIKE '%book%', the appropriate records are returned.  I actually need to check two parameters.  If I ad the second parameter, the where clause becomes(Type LIKE '%@Type%') AND (SendState = @SendState)When I test the query, a screen pops up to let me enter the state, but not the Type.  I can't see anything wrong with the query, but something must be. Diane 

View 4 Replies View Related

Trouble With Sub Query

Jun 10, 2008

I am having some trouble using a sub query. I want to use the red part as a sub query because I have to alter some values based on the NcodeM that gets assigned to each record. As a stand alone query the red part works well. When I run the whole thing I get error message:

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near ')'.



Select SaleYear, SaleMonth, vin10, NewUsed, VehicleYear,
VehicleMake, VehicleModel, VehicleTrim, AlgCode, CashDown, AppZip, ActualSalePrice,
TradeMake, TradeModel,TradeYear, TradeNcodeL, TradeNcodeM, OwingOnTrade, TradeAllowance, NetTradeIn

From D

(select left(consulting.dbo.vw_dds.date,4) AS SaleYear, left((right(consulting.dbo.vw_dds.date,4)),2)
AS SaleMonth,
vin10, NewUsed, VehicleYear, VehicleMake, VehicleModel, VehicleTrim, AlgCode, CashDown,

AppZip, (CashPrice-Rebate-TaxesIncludedInCashPrice) AS ActualSalePrice,

ltrim(consulting.dbo.vw_dds.trademake) AS TradeMake, ltrim(trademodel)TradeModel,
TradeYear, NcodeL AS TradeNcodeL, NcodeM AS TradeNcodeM, OwingOnTrade, TradeAllowance, NetTradeIn

from consulting.dbo.vw_dds

inner join (select distinct make, model, ncodel, ncodem, modelyear from us.dbo.algmaster) Code

on code.make = ltrim(consulting.dbo.vw_dds.trademake) and code.model= ltrim(trademodel) and
code.modelyear = tradeyear) D

Any suggestions would be greatly appreciated.

Thanks,
Tasha

View 2 Replies View Related

Query Trouble

Nov 8, 2007



I am having trouble figuring this one out.

I have a table named studentgrades

In it, it contains studentid, teacherid, classgrade


If I do this code it gives me the count of student that made an "A" grouped by teacherid

select
teacherid,
count(grade) as GradeACount
where
classgrade between 90 and 100
from
studentgrades
group by
teacherid

ok this seems simple enough...if I manilpulate the where clause I can get "B","C" and "D" student counts.

My question is how can i get a dataset to return

teacherid,GradeACount,GradeBCount,GradeCCount,GradeDCount

all in one query...I am trying to automate a report so that school districts can pull their own queries via Reporting Services.


I appreciate your time.

Alex Flores

View 5 Replies View Related

Convoluted Query Trouble

Nov 16, 2006

Our Client/Contact database works like this:

CLIENTDB - This table holds the records of all Clients, Contacts, Suppliers, etc. They are defined as one type of record or another by the 'Contact_Type' field - Clients are type 'B', Contacts are 'D', etc.

Contacts are often linked to a Client, but don't have to be. These links are stored in the LINKS table.

So, what I'm trying to do, is generate a report of all Contacts that aren't linked to Clients but possibly should be because they share a company name. The SQL I've used to get this info is as follows:

SELECT *, CLIENTDB.Formal_Name as FN
FROM CLIENTDB
WHERE (CLIENTDB.Contact_Type = 'D')
AND (NOT EXISTS (SELECT * FROM CLIENTDB, LINKS JOIN CLIENTDB as CB ON CLIENTDB.Contact_ID = LINKS.Link_Record_ID))
AND (EXISTS (SELECT * FROM CLIENTDB WHERE (CLIENTDB.Contact_Type = 'B') AND (lower(CLIENTDB.Formal_Name) LIKE '$FN')))


The problem is the final AND condition - I want this to filter the results down to only those Contacts that have a Formal_Name like an existing Client, but when I add or remove this one line, it makes no difference to the output.

Any suggestions greatly appreciated

Thanks, Nick

View 1 Replies View Related

Having Trouble With A Difference Query

May 28, 2008

Hi, I have created a query (using SQL 2005) that will pull the people who have spent the most on tickets purchased:

Select P.Passenger_ID, Passenger_Name, Ticket_Price
From Passenger P, Ticket_Purchase T
Where P.Passenger_ID = T.Passenger_ID
Group By P.Passenger_ID, Passenger_Name, Ticket_Price
Having Ticket_Price >= All (Select Max(Ticket_Price)
From Ticket_Purchase
Group By Ticket_Price);


Passenger_ID Passenger_Name Ticket_Price
---------------------------------------
132812298 Nice,Richard 1750.00
234890032 Franco,Sylvia 1750.00
339209841 Kim,Jongouk 1750.00
385894857 Uribe,Gloria 1750.00

(4 row(s) affected)


I now want to be able to only choose the Passenger_ID's from above who are not listed in another table called Frequent_Flier, which should leave me with only 2 records not 4.


I am wondering if I add the below to the first query to eliminate those passengers in the Frequent_Flier table:
NOT IN (Select Passenger_ID
From Frequent_Flier);

When I add it to the Where clause I get an error. Should I be sub-querying that differently or is there a better way to do this.

Thanks for any help you can offer.

View 4 Replies View Related

Trouble With SQL Query Problem

Nov 13, 2006

Employee
EMPLOYEE_IDLAST_NAMEFIRST_NAMEMIJOB_ID MANAGER_IDHIRE_DATESALARYCOMMISIONDEPARTMENT_ID PHONE_NUMBER

Job
JOB_IDFUNCTION JOB_TYPE
Department
DEPARTMENT_IDNAMELOCATION_ID
Location
LOCATION_IDREGIONAL_GROUP
Here are the different tables in my database and Im trying to get a list of all the managers last names with the last names of the people that the manager manages. FUNCTION describes who is a manager and who has other positions. You can link the two tables together with Job_ID.

Im stuck on this, Im a noob at SQL. Please help

View 2 Replies View Related

Trouble With Update Query

Feb 3, 2008



I want to fill in a field whose name is stored in a variable. This code runs, but the field is not filled in afterward. I think I'm doing something wrong:


SET @field = N'bindery'

SET @ordernum = N'SM38948M08'


UPDATE Orders

SET @field = GETDATE() + 5

WHERE ordernum = @ordernum


My problem is related to using the @field variable in the UPDATE query.

How do I fix this?

Thanks!
Brian

View 8 Replies View Related

Trouble Using Group By Statement With Query

Mar 20, 2007

Hi,
I am having trouble getting my query right. i am using a stored procedure to retrieve this data and bind it to my grid view.
Problem: I can't associate a field with a column that i am returning with my record set.
Details: I have query that i want to return 9 columns (UserName, Pre-Approved, Processing, Underwriting, Conditioned, Approved, Docs Out, Docs Back, Conditions Met). The username column lists all loan agents. I want the other 8 columns to list the name of the borrower (crestline..borrower.lastname)  that is associate with that loan agent and that loan state.  Each time a record is found where there is a valid loan agent (UserName) that meets the 'where' conditions, there will be a borrower. the 'LoanKey' field is a primary key that lets me get the borrower name from the borrower table. I dont know how to construct my query such that those borrower names get associated with their respective column header.
if the query works, my Gridview should look like this ('Name' refers to the borrower name)
UserName | Pre-Approved | Processing | UnderWriting | Conditioned | Approved | Docs Out | Docs Back | Conditions Met 
Bob           |                     |                  |                     |    Name      |                |               |                   |
Bob           |                     |      Name   |                     |                   |                |               |                   |      
Bob           |                     |                  |                     |                   |                |               |      Name    |        
Steve         |                     |                  |      Name      |                   |                |               |                   | 
Steve         |                     |                  |                     |                   |                |    Name  |                   |
 
Here is my sql call: 
SELECT cfcdb..users.username, crestline..borrower.lastname,CASE WHEN crestline..loansp.LoanStatus='Pre-Approved' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Pre-Approved',CASE WHEN crestline..loansp.LoanStatus='Processing' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Processing',CASE WHEN crestline..loansp.LoanStatus='Underwriting' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Underwriting',CASE WHEN crestline..loansp.LoanStatus='Conditioned' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Conditioned',CASE WHEN crestline..loansp.LoanStatus='Approved' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Approved',CASE WHEN crestline..loansp.LoanStatus='Docs Out' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Docs Out',CASE WHEN crestline..loansp.LoanStatus='Docs Back' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Docs Back',CASE WHEN crestline..loansp.LoanStatus='Conditions Met' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Conditions Met'FROM cfcdb..usersinner join (crestline..loansp inner join crestline..borrower on crestline..loansp.loankey = crestline..borrower.loankey)on crestline..loansp.fstnamelo=cfcdb..users.firstname AND crestline..loansp.lstnamelo=cfcdb..users.lastnameinner join cfcdb..users_roleson cfcdb..users.username = cfcdb..users_roles.usernamewhere cfcdb..users.active = 1 AND cfcdb..users_roles.groupid = 'agent'AND crestline..loansp.enloanstat <> 'Closed' AND crestline..loansp.enloanstat <> 'Cancelled' AND crestline..loansp.enloanstat <> 'Declined' AND crestline..loansp.enloanstat <> 'On Hold'order by cfcdb..users.username asc
 

View 2 Replies View Related

Query Trouble Regarding Bitwise Exclusive

Feb 25, 2001

HI, i am trying to make query that has computations with it. but when there's a point computing between int and float i had to use cast() function to convert certain data types. somehow it only works on converting float to integer because when i'm converting an integer into float inorder to be computed with a float it bombs. my query is like this ....


SELECT cast(((cast(((lat - (SELECT LAT FROM TPS_ZIPUSA WHERE ZIP_CODE = 00210)) * 69.1) AS int) ^ 2) + (cast((69.1 * (lng - (SELECT Lng FROM TPS_ZIPUSA WHERE ZIP_CODE = 00210)) * (COS((SELECT LAT FROM TPS_ZIPUSA WHERE ZIP_CODE = 00210) / 57.3))) AS int) ^ 2)) AS float) ^ .5
FROM TPS_ZIPUSA


.5 is where the query bombs. any idea why is this happenning?

by the way, i'm using sql server 7.0.

francis,

View 2 Replies View Related

Select Query Union Trouble

Nov 26, 2007

Given the following tables:

[Members]
-memberID (PK)
-memberName

[Questions]
-questionID (PK)
-questionText

[Surveys]
-surveyID (PK)
-surveyName
-surveyDescription
-surveyType (FK)

[SurveyQuestions]
-surveyID (PK/FK)
-questionID (PK/FK)

[SurveyQuestionMemberResponse]
-surveyID (PK/FK)
-memberID (PK/FK)
-questionID (PK/FK)
-yesResponse(bit)
-noResponse(bit)
-undecidedResponse(bit)

How can I write a query to return the results for a given survey for all members (including members who have not given responses) given the surveyID.

In the [SurveyQuestionMemberReponse] table I record survey results for any members who have answered the survey. However, if a member has not responsed to the survey they will not have a record in this table.

I want to return a list of members with their response to each question in the survey. If a member has not given a response I would like to indicate they have not responded to the survey and they should still appear in the list.

When I attempt to write a query to UNION the results of a query aimed at gathering all of the results in the [SurveyQuestionMemberReponse] to all of the people in the [Members] table I recieve an error when I include the questionText field in my result set.

The error indicates:

The text data type cannot be selected as DISTINCT because it is not comparable.

Can someone please point me in the right direction. I suspect I am going about this all wrong.

[NOTE] The 'surveyType' in the [Surveys] table indicates which subset of members a given Survey should be available to. For this example let's just assume that every survey should belong to all members.

Thanks,

Zoop

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved