I have a really large table with many Proposal fields and corresponding approval fields.
Propose1
Approve1
Propose2
Approve2
Would it be good practice or even possible for me to select all proposal fields using a wildcard somehow within the select statement. If it is ok, how would I go about doing it?
I want to do a query where I dont want to put 100 fields in the query out of 105...I want to do a
select * , without a, without b from /where
sort of thing....
SOS help
Hi,
I have a fairly basic query I want to make but I'm struggling on figuring out how to do it. Let's say I have some fields (e.g. Value1, Value2, Value3, etc). I simply want to do a Select statement that returns the highest value among those fields for every row in my db.
At first I thought of the built in Max function until I realized that is for within a column only.
Any help would be appreciated, thank you.
Here is the problem that I am struggling with. The structure is:
States: StateID, StateName
Counties: CountyID, CountyName
Cities: CityID, CityName
Zips: Zip, StateID, CountyID, CityID
Regions: RegionID, RegionName
Region_Data: RegionID, StateID, CountyID, CityID
The thing about the region is that it can be defined by states only, in which case CountyID and CityId are NULL, can be defined by Counties too, in which case only CityID is NULL or it can be defined up to City level, in which case all 3 are set to something. Example, Northeast would be all cities from northeast, but Pocono would be just some counties in PA, and so on...
The issue is now selecting all cities that belong into a region... Normally I would join the Zips table with the Region_Data table and retrieve all CityIDs... The issue is that, as I said, the CountyID and CityID might be null for some records, so I am not sure how to retrieve them?
I came up with one idea in which I create 3 temporary tables and I select in them all the records that have 1) only state, 2) only state and county, 3) all 3, then I join each and I union the results...
But I am wondering if there is a way to do the select in one shot?
Any idea? I appreciate your help,
Thank you,
Iulian
Regards,
Iulian
I have:
Dim queryString As String = "SELECT distinct [CSULOG5].[status] + [CSULOG5].[lmca_nbr] FROM [CSULOG5]" I even tried & instead of +.
but it doesn't like it. Is it legit syntax for mssqlserver?
This is a vb project aspnet
Hi,
Could anyone tell me what governs whether a column is set as nullable or not nullable when creating a table using SELECT...INTO. It just seems to pick at random for me! I'm quite sure this is not the case. Is there a way to force a column to be non- nullable? I seem to be wasting a lot of time going through and altering the schema so I can use the columns in keys and indexes.
Dave
An ADODB error spawn everytime I query more than 15 fields in a specific SQL Table. My sql statement is something like this:
set rs=server.createobject("ADODB.Recordset")
rs.open "select * from mytbl",application("mycon"),1,3
And the error is something like this:
"you can't query more than the maximum field "
The data I have has a 1 in for each Yes answer and a 2 for each no answer. I just want the select statement to show the word yes when there's a 1 and the word no when there's a 2. I don't need to update or change the database. Could anyone lead me in the right direction here? Thanks
View 4 Replies View RelatedIn order to ask my question on a SQL query I will use a simplified version of a table I was struggling with...
¦¦¦¦SHOP¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦CARS
KILBURN MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes
KILBURN MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦BMW
KILBURN MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIAT
BRIXTON AUTOMOBILE¦¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes
BRIXTON AUTOMOBILE¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦BMW
WEST HAMPSTEAD CARS¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes
CAMDEM MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIAT
NORTHERN LONDON CAR-STORE¦¦¦¦¦¦¦¦¦¦¦BMW
NORTHERN LONDON CAR-STORE¦¦¦¦¦¦¦¦¦¦¦FIAT
So my question is: how can I select only SHOPs which have no CARS variety (e.g. a variety =1)?
In other words I am looking for a Query that would give me this outcome:
¦¦¦¦SHOP¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦CARS
WEST HAMPSTEAD CARS¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes
CAMDEM MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIAT
...because WEST HAMPSTEAD CARS, selling Mercedes only, has a CARS variety equal to 1 as well as Camdem Motors which sells only FIAT.
I tried with this query:
SELECT DISTINCT SHOP, CARS
FROM CAR_SELLERS
GROUP BY SHOP, CARS
HAVING COUNT(CARS) = 1
But it doesn't work.
In addition I also would like to know for example how to create a similar query for a different desired CARS variety (e.g. 2, 3...)
How to do the tabs or attach images in the Post New Topic Message box... I wanted to attach a screen shot of the table but I was not able to and so, in order to post my question, I prepared a simplified version of the table plus I had to do the tabs/spaces manually with the symbol (¦)
Hi,Is there a way to exclude fields in a query other than just includingthe ones you want. If there are 20 fields and you want to see all but3, it would be a lot easier to exclude the 3.Thanks
View 8 Replies View RelatedI have a code that gives me the sample I am looking for, but only displays one field.
NuminHH is my field
7320EN_Var is my Table
Proof is the new table I want to create.
SELECT DISTINCT [NuminHH] INTO Proof
from 7320EN_Var
I thought this was working, but apparently it was not. I was wondering how I would create a SELECT Statement for values that are blank (Equal to ""). I really could have swore that this was working, but I guess it wasn't.
Here is my code:
Code Block
try
{
string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Environment.CurrentDirectory + @"MyDB.accdb;Jet OLEDB:Database Password=MyPassword;";
// create an open the connection
OleDbConnection conn = new OleDbConnection(conString);
OleDbCommand command = new OleDbCommand();
command = conn.CreateCommand();
// create the DataSet
DataSet ds = new DataSet();
// run the query
command.CommandText = "SELECT ID AS [#], Company AS [Company], FName AS [First Name], LName AS [Last Name], Type AS [Type] FROM Table1 WHERE Tags = @P0;";
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter = new OleDbDataAdapter(command);
command.Parameters.Add("@P0", OleDbType.VarChar).Value = "";
adapter.Fill(ds);
// close the connection
conn.Close();
bindingSource1.DataSource = ds.Tables[0];
DataGridView.DataSource = bindingSource1;
// set the size of the dataGridView Columns
this.DataGridView.Columns[0].Visible = false;
this.DataGridView.Columns[1].Width = 234;
this.DataGridView.Columns[2].Width = 50;
this.DataGridView.Columns[3].Width = 50;
this.DataGridView.Columns[4].Width = 75;
//Sort on the Title Column
DataGridViewColumn sortColumn = DataGridView.Columns[1];
ListSortDirection direction;
direction = ListSortDirection.Ascending;
DataGridView.Sort(sortColumn, direction);
//Set the Selected Property of the First Row to False
DataGridView.Rows[0].Selected = false;
}
catch
{
}
Thank you again for your help.
I have a table with two sets of fields, one for PRIMARY Mail Address and the other for ALTERNATE Mail Address. Both sets are five fields long. I will call them PRIMARY & ALTERNATE for this discussion. I want to select the PRIMARY fields if ALTERNATE is either null or zero, and the ALTERNATE fields if data is present.
I have used this syntax but it gives a table with both the PRIMARY & SECONDARY records.
SELECT TXPRCL, TXALTR, TXANAM
FROM dbo.PCWEBF21
WHERE (TXALTR > 0)
UNION
SELECT TXPRCL, TXTAXP, TXTNAM
FROM dbo.PCWEBF21
WHERE TXALTR = 0
Would a Select Case work? something like
Select PRIMARY when ALTERNATE = 0, and ALTERNATE when ALTERNATE > 0 or is not null.
Hi, how do i do a select statement with asp.net to return a record if a field is null. I have the following code:
SqlCommand cmd = new SqlCommand("SELECT * FROM Categories WHERE ParentId = @ParentId", cn);.Parameters.Add("@ParentId", SqlDbType.Int).Value = parentId != null ? (object) parentId : DBNull.Value;cn.Open();
The variable parentId is a nullable int. However when i pass in a null value it doesn't return anything even though there are records in the database that have a null value for the ParentId field.
Appreciate if someone could tell me what i am doing wrong. Thanks
Is it possible to combine fields and text in a select statement?
In a dropDownList I want to show a combination of two different fields, and have the value of the selected item come from a third field. So, I thought I could maybe do something like this:
SELECT DISTINCT GRP AS GroupName, "Year: " + YEAR + "Grade: " + GRD AS ShowMe
FROM GE_Data
WHERE (DIST = @DIST)
I hoped that would take the values in YEAR and GRD and concatenate them with the other text. Then my dropDownList could show the ShowMe value and have the GroupName as the value it passes on. However, when I test this in the VS Query Builder, it says that Year and Grade are unknown column names and changes the double-quotes to square brackets.
If this is possible, or there's a better way to do it, I'd love some more info.
Thanks!
-Mathminded
Hello,
I m writing a stored procedure to query a table Population that has the following fields - CityId, CityName, Plus0, Plus10, Plus20, Plus30, Plus40, Plus50, Plus60, Plus70, Plus80. The field Plus0 contains the number of people of age > 0 living in the city, Plus10 contains the number of people of age > 10 living in the city and so on. Given the city id and age groups like 20To40, 50To60, 40Plus, etc., I should be able to query the number of people in the city corresponding to the requested age group. Note that if the requested age group was 20To60, I need to make use of only 2 fields Plus20 and Plus60 in the table to compute this value. And if the requested age group was 40Plus, then I need only the value in the field Plus40. The problem is that a wide variety of age groups can be requested like 0Plus, 10Plus, ... , 80Plus, 0To10, 0To20, 0To30, .... 70To80.
Which is the most effecient way to handle this ?
1. Have a stored procedure that returns all the fields even though only 1 or 2 of them would be actually used ?
In this case, if I returned data for a large number of cities then there would be a lot of unnecessary information that was returned by the query. Lots of data would be passed through the network though most of it would not be used.
2. Have a stored procedure that takes in parameters @Plus0, @Plus10, @Plus20, .. @Plus80 that are bits indicating whether the field was required or not and then using a CASE statement to return values for a field only if the corresponding bit parameter was set, and returning NULL if the corresponding bit paramter was not set ?
In this case, I would be returning NULL for all those fields that were not required for a particular age group. This would save some network bandwidth, wouldn't it ?
3. Pass in the age group itself (ex: 0To20) as a parameter to the stored procedure and have lots of IF statements, one for each age group, that return only the fields that are needed for that age group.
This leads to a lot of code repitition.
4. Use a similar approach as above but use dynamic SQL queries to avoid code repitition.
But using dynamic SQL queries can affect the performance of the stored procedure as they need to be compiled each time.
5. Any other possible approaches ??
Looking forward to your responses,
Thanks much,
bmgun.
Hi:
I used this statement, select * from table1 where date1 = null, in SQL Query window and got a few records back. Now, I used the same statement in my VB 5 code and no record is found. How do I select all the records in table1 which do not have values in field date1? Thanks for the help.
-Nicole-
I have two tables - products and productpropertyvalue
I need to select multiple fields from the productpropertyvalue as it corresponds to the product id. The script I am using is
select a.id, a.productname, a.siteprice,
b.propertyvalue
from product a, productpropertyvalue b
where a.id = b.productid and propertyid=590
This allows me to extract only 1 propertyid. I need to make it add 3 other columns for propertyid=589, 617, 615
Any help solving this problem would be appreciated - thanx!
Ron
Hello,
I've been busy all night searching and reading trying to figure out how I can do the following.
I have a table that tracks user IDs in multiple fields. When I select records from this table I need a way to resolve those ID fields back into user names by referencing the users table. SQL statement thus far...
SELECT A.Username as NameA, B.Username As NameB, FROM theTable, Users As A, Users As B WHERE theTable.UserIDA ???
How do I resolve theTable.UserIDA and theTable.UserIDB back to Users.Username so that the records returned fill the fields NameA and NameB?
Your help is greatly appreciated.
Thanks,
-Mike
Ontario, Canada
Dear All
I am trying to populate an OledbDatareader for binding to a ASP datagrid.
For this I use select statement to display combined fields in a datagrid cell.
Eg. Select (Field1+ '<br/>' + Field2 + '<br/>' + Field 3) As Address .. and so on.
But the problem is if any of the three field is null the combined field 'Address' returns as Null.
Please help me to overcome this problem.
Regards
kalanad ( beginner)
hey all,
say i have the following function
SELECT GLF_CHART_ACCT.DESCR1, F1ADR_ADDRESS.ADDR1, F1ADR_ADDRESS.ADDR2,
F1ADR_ADDRESS.ADDR3, F1ADR_ADDRESS.ADDR_CITY, F1ADR_ADDRESS.ADDR_STATE,
F1ADR_ADDRESS.POST_CODE, F1ADR_ADDRESS.PHONE_NBR, F1ADR_ADDRESS.FAX_NBR,
F1ADR_ADDRESS.EMAIL_ADDR_NAME, F1ADR_ADDRESS.CONTACT_NAME,
F1ADR_ADDRESS.CONTACT_TITLE, GLF_CHART_ACCT.ACCNBRI, F1ADR_ADDRESS.ENTITY_UNIQUE_NBR
FROM GLF_CHART_ACCT
INNER JOIN F1ADR_ADDRESS ON (GLF_CHART_ACCT.CHART_NAME = F1ADR_ADDRESS.ENTITY_KEY1)
AND (GLF_CHART_ACCT.ACCNBRI = F1ADR_ADDRESS.ENTITY_KEY2)
GROUP BY GLF_CHART_ACCT.DESCR1, F1ADR_ADDRESS.ADDR1, F1ADR_ADDRESS.ADDR2,
F1ADR_ADDRESS.ADDR3, F1ADR_ADDRESS.ADDR_CITY, F1ADR_ADDRESS.ADDR_STATE,
F1ADR_ADDRESS.POST_CODE, F1ADR_ADDRESS.PHONE_NBR, F1ADR_ADDRESS.FAX_NBR,
F1ADR_ADDRESS.EMAIL_ADDR_NAME, F1ADR_ADDRESS.CONTACT_NAME, GLF_CHART_ACCT.ACCNBRI,
F1ADR_ADDRESS.CONTACT_TITLE, GLF_CHART_ACCT.CHART_NAME, F1ADR_ADDRESS.ENTITY_UNIQUE_NBR,
GLF_CHART_ACCT.SELN_TYPE1_CODE
HAVING CHART_NAME='ARCHART' AND GLF_CHART_ACCT.DESCR1 <> '' AND GLF_CHART_ACCT.SELN_TYPE1_CODE = 'Trade'
AND GLF_CHART_ACCT.DESCR1 LIKE '%" + Search + "%' ORDER BY GLF_CHART_ACCT.DESCR1;
I get errors if not all the fields are included in the group by clause.
what i dont get is why i have to create seperate groups for this query...or am i reading it wrong??
Cheers,
Justin
Let's say I have a table of users.
Let's imagine there's two fields:
username (PK),
password
Now I need to authenticate a user against this table. What is the recommended approach? Is it better / faster to
(1) SELECT * FROM [User]
WHERE username = 'whatever' AND password='whatever'
or
(2) SELECT * FROM [User]
WHERE username = 'whatever'
and then in my code check that the record returned matched the password?
I was curious...Is there a way to select distinct on a combination of some fields andthe for each record returned also get the other fields of anarbitrarily chosen record matching the fields in the distinct record.For example, if I have a select distinct on say three fields:SELECT DISTINCT Code1, Code2, Code3but the table also has other fields, maybe Foo1 and Foo2, and I wantFoo1 and Foo2 to also be displayed. Since there may be multiplerecords that match a particular Code1, Code2, Code3, then I just wantone of those to be arbitrarily chosen.
View 3 Replies View RelatedHi to allI wish to be able to have a standard select statement which hasadditional fields added to it at run-time based on suppliedparameter(s).iedeclare @theTest1 nvarchar(10)set @theTest1='TRUE'declare @theTest2 nvarchar(10)set @theTest2='TRUE'selectp_full_nameif @theTest1='TRUE'BEGINother field1,ENDif @theTest2='TRUE'BEGINother field2ENDfrom dbo.tbl_GIS_personwhere record_id < 20I do not wish to use an IF statement to test the parameter for acondition and then repeat the entire select statement particularly asit is a UNIONed query for three different statementiedeclare @theTest1 nvarchar(10)set @theTest1='TRUE'declare @theTest2 nvarchar(10)set @theTest2='TRUE'if @theTest1='TRUE' AND @theTest2='TRUE'BEGINselectp_full_name,other field1,other field2from dbo.tbl_GIS_personwhere record_id < 20ENDif @theTest1='TRUE' AND @theTest2='FALSE'BEGINselectp_full_name,other field1from dbo.tbl_GIS_personwhere record_id < 20END......if @theTest<>'TRUE'BEGINselectp_full_namefrom dbo.tbl_GIS_personwhere record_id < 20ENDMake sense? So the select is standard in the most part but with smallvariations depending on the user's choice. I want to avoid risk ofbreakage by having only one spot that the FROM, JOIN and WHEREstatements need to be defined.The query will end up being used in an XML template query.Any help would be much appreciatedRegardsGIS Analyst
View 2 Replies View RelatedCan someone show me what I need to do to have a result set only displaying records from the specified date range. All the tables can relate on Date, shift, and ProductType (Besides UnprocessedProductTracking which can only relate on Date and Shift) I have a lot of trouble with joins...well I'm just not to experienced. Help!
CREATE Procedure dbo.spButterMarTotals
(
@fromdate smalldatetime,
@todate smalldatetime
)
AS
--
--Exec spButterMarTotals '11/15/06','11/15/06'
--
--
Select Distinct ButterballsThrownPounds, ReworkUsedPounds, ReworkMadePounds, CoolersUsedPounds, CoolersMadePounds, TrucksMadePounds,
ResidualFilmPounds, ResidualSqueezePounds, ProducedPounds, Blocks As ProcessingThrown, Weight AS NFCPounds, Pounds AS BirdsEyePounds,
T1AL50Type, T2Type, T3Type, T4Type, AL51Type, FillMach1Type
from tblproductionMaterialmetrics A
Inner Join tblNFCTracking B on
A.ProductType = B.ProductType
Inner Join tblProcessingDtl C on
C.ProductType = B.ProductType
Inner Join tblIngredientProduction D on
D.shift = C.shift
Inner Join tblUnprocessedProductTracking E on
D.shift = C.Shift
Where A.Date between @Fromdate and @todate
float
View 1 Replies View RelatedI have Database Library, which has a lot of tables and we need 3 tables for quary:
Table Librarians: field ID, field Surname;
Table StudentCard: field ID, foreign key on table Librarians and other fields, which we don't use
Table TeacherCard: field ID, foreign key on table Librarians and other fields, which we don't use
Query: Select the librarian's surname, which gave the most count of books.
I know, how to resolve, when i took datas only from one table, e. g. TeacherCard
SELECT TOP 1 WITH TIES Librarians.LastName, MAX(Librarians.CountOfBooks) AS Books FROM
(SELECT L.LastName, COUNT(*) AS CountOfBooks FROM Libs L, T_Cards T
WHERE T.Id_Lib IN (SELECT L.Id)
GROUP BY L.LastName) AS Librarians
GROUP BY Librarians.LastName
ORDER BY MAX(Librarians.CountOfBooks) DESC
GO
I dont know, how to use datas from TeacherCard and from StudetnCard at the same time.
I am trying to figure out a way to toggle the visibility of attribute data based on a parameter. Specifically, I have a report that has many columns that an end-user may not want to see, depending on what they are using the report for. I know you can toggle visibilities on individual columns easily enough, however I want the user to be able to select which fields (at the attribute level) they want visible on the report up in the parameters, via a multi-value prompt.
Is this possible with reporting services 2005?
Thanks.
HelloI am trying to search 2 columns on a databsae table using a string put into a box, the code i have at the moment is SqlConnection conn = new SqlConnection(SqlDSFindPost.ConnectionString); SqlCommand cmd = new SqlCommand ("SELECT * FROM tblBlog WHERE UserName LIKE @UserName OR Title LIKE @Title; ", conn); cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 50).Value = '%' + TextBox1.Text + '%'; cmd.Parameters.Add("@Title",SqlDbType.NVarChar, 50).Value = '%' + TextBox1.Text + '%'; conn.Open(); cmd.ExecuteNonQuery(); GridView1.DataBind(); I have tried all sorts of strings and even typeed the string directly into the parameter but never get any results, yet when i type the wildcards directly into the textbox i get the correct rows returned. Can anybody see anything wrong with my code and tell me where i am going wrong, or alternativly point me in the direction of some c# code for searching a database similar to the search box abovei dont do a lot in asp or c# so this is driving me crazy Thanks for looking
View 2 Replies View RelatedDoes anyone know how I could show all the records of tools with the word released after them? For example, 'Volume Monitor 4.4 Released'
I tried this statement with no luck:
Select * from Issues where Tools LIKE 'RELEASED %'
Thanks,
Russ
I have a need to use wildcards in a sql statement. e.g. select * from tbl where field='%computer%'.
How can I substitute the string "computer" for a variable declared in the stored procedure.
Procedure Sample
@Str varchar(50)
AS
select * from tbl where field = '%' & @Str & '%'
(How do incorporate the wildcard variable @Str?
I am running the data import below in a stored procedure:
EXEC xp_cmdshell "bcp TCCSTGB..TGB_Fimport in d:MSSQLTGB_ImportsNNNYYYYMMDDHHMM.SDF /f d:mssqlFormatsTGB_Fimport.fmt /Usa /P ", no_output
I want to replace the NNNYYYYMMDDHHMM with a wildcard (for example *), so that import will pull ANY .SDF files in, but it will not run. i get the following:
output
------------------------------------------------------------------------------
DB-LIBRARY error:
Bcp: Unable to open host data-file.
Hi
I need to replace the use of wild cards in my query with something else which achieves the same thing. The problem is the web application which uses the query does throws an error when using '%' characters. Any ideas?
The following statement appears in the where clause:
AccType.Value like '@Opened_By[%DIST%APP% as Distance and Business Provider, DIST% as Distance, APP% as Business Provider]'
Thanks