query question

I have a table where one of the fields is named SHIPPER
in the field, it will have the names of various shippers. ShipperA,
ShipperB ShipperC and ShipperD as an example
I need to make a query where it will return all the records where the
SHIPPER is ShipperA, ShipperB, ShipperD
All help would be great!
ThanksSELECT *
FROM YourTable
WHERE Shipper IN ('ShipperA', 'ShipperB', ShipperD')
?
If this doesn't work, please post DDL and sample data (
http://www.aspfaq.com/etiquette.asp?id=5006 )
"johnfli" <john@.here.com> wrote in message
news:OTiQIf8rEHA.3588@.tk2msftngp13.phx.gbl...
> I have a table where one of the fields is named SHIPPER
> in the field, it will have the names of various shippers. ShipperA,
> ShipperB ShipperC and ShipperD as an example
> I need to make a query where it will return all the records where the
> SHIPPER is ShipperA, ShipperB, ShipperD
> All help would be great!
> Thanks
>|||Thank you for the quick reply.
I wasn't able to get it to work, I am sure it has something to do with the
wildcards.
I modified your line to read:
WHERE Shipper IN ('%ShipperA%', '%ShipperB%', '%ShipperD%')
as for some reason, the names were inputted with partial address
thanks again if you're able to fix this.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23i6goi8rEHA.3848@.TK2MSFTNGP14.phx.gbl...
> SELECT *
> FROM YourTable
> WHERE Shipper IN ('ShipperA', 'ShipperB', ShipperD')
> ?
> If this doesn't work, please post DDL and sample data (
> http://www.aspfaq.com/etiquette.asp?id=5006 )
>
> "johnfli" <john@.here.com> wrote in message
> news:OTiQIf8rEHA.3588@.tk2msftngp13.phx.gbl...
>|||Sorry, IN does not support wild cards. You'll have to use LIKE with OR:
WHERE Shipper LIKE '%ShipperA%'
OR Shipper LIKE '%ShipperB%'
OR Shipper LIKE '%ShipperD%'
"johnfli" <john@.here.com> wrote in message
news:uz2VMo8rEHA.1644@.tk2msftngp13.phx.gbl...
> Thank you for the quick reply.
> I wasn't able to get it to work, I am sure it has something to do with the
> wildcards.
> I modified your line to read:
> WHERE Shipper IN ('%ShipperA%', '%ShipperB%', '%ShipperD%')
> as for some reason, the names were inputted with partial address
> thanks again if you're able to fix this.
>|||perfect!
thank you
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OWbQUr8rEHA.556@.tk2msftngp13.phx.gbl...
> Sorry, IN does not support wild cards. You'll have to use LIKE with OR:
> WHERE Shipper LIKE '%ShipperA%'
> OR Shipper LIKE '%ShipperB%'
> OR Shipper LIKE '%ShipperD%'
>
> "johnfli" <john@.here.com> wrote in message
> news:uz2VMo8rEHA.1644@.tk2msftngp13.phx.gbl...
the[vbcol=seagreen]
>

Query Problems!

I'm having a problem write a query that pulls the new updated data from tbl1. The fields are state_no and date_insp. the criteria for the search is 00054000 & 1/1/2003 until present time.
I want to compare that data to tbl2 to see if it has any pre-existing data such as tbl1.Owner name, tbl1.Owner_addy, tbl2Owner_name, tbl2.Owner_addy so that I'm not duplicating the date in tbl2 when i do the import.

If this isn't clear to your please write back and I will explain more.HERE'S A QUERY THAT i CAME UP WITH TO TRY AND SEE IF IT WORKS BUT I DON'T THINK THAT ITS CHECKING EARLIER STATE_NO:

SELECT DISTINCT STATE_NO, OWNER_NAME, BUSINESS_NAME
FROM TESTNEWIMPORTBOILER T, HBC_CONTACT C
where state_no >= '00054000' and date_inspection >= '1/1/2003'
AND
T.OWNER_NAME =
(SELECT BUSINESS_NAME
FROM HBC_CONTACT
WHERE BUSINESS_NAME = T.OWNER_NAME)

Originally posted by Bigced_21
I'm having a problem write a query that pulls the new updated data from tbl1. The fields are state_no and date_insp. the criteria for the search is 00054000 & 1/1/2003 until present time.
I want to compare that data to tbl2 to see if it has any pre-existing data such as tbl1.Owner name, tbl1.Owner_addy, tbl2Owner_name, tbl2.Owner_addy so that I'm not duplicating the date in tbl2 when i do the import.

If this isn't clear to your please write back and I will explain more.

Query performance

have a database table. The table has number of fields. Out of those fields one is Company and
another is DateTime. The table has thousands of records. I want to get the most recent
record for each company. In order to do that I am using the following query

SELECT * from CompanyDetail AS X
WHERE [DateTime]=(SELECT max([DateTime]) FROM CompanyDetail WHERE Company=X.Company)
ORDER BY Company

Note: There is only one record exists for the given company on a given date

The problem is that this query is very slow. Am I doing something wrong or there could be another
alternative way to improve it ?

Just to clarify that I am using Access database

Thanks in advance
KDV

This forum is for SQL Server so you should post this in a MS Access forum. You should check if you have the required indexes on the table. For this example, it would help to have index on Company and DateTime columns.

Query Optimize.

Hi all,

Table 'A' is having 105 fields & 233000 records.
One Clusterd Primary Key & 10 nonclusterd key.

If I joined with table 'A' or 'Select * from 'A' , Query takes more time so please let me know best way to structure query or table, Indexes etc.

Reply to me asap positivaly ...

Regards,
M. G.Without having the DDL (the CREATE TABLE and CREATE INDEX statements in particular), and the query(s) that are running slow, I'm pretty severly handicapped in giving you much advice.

The only real insight that I can give without more informations is to be sure that your statistics are current using the UPDATE STATISTICS (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_4vxn.asp) statement.

-PatP

Query optimization

I have a staging table with the following fields:
LineNumber,StudentId,EnteranceYear,Statu
s and some other fields.
There are some duplicates based on two fields (studentID and EnteranceYear)
for which I'm trying to update the older record to be active (status=1) and
all the other duplicates to be inactive by setting status=0 .I wrote this
query first
UPDATE dbo.IF_C4TRANSFORM SET
Status = 0
WHERE FileID=2565 AND
LINE_LineNumber not in
(
select max(t.LineNumber)
from MyTable t
group by t.StudentId_ID , t.EnteranceYear
having count(*) > 1
)
but it dosen't work properly.if subquery return nothing in set the status of
all records to 0 ,no matter they are duplicated or not .then I created the
following query which I don;t like at all.
I'm just wondering if there is a better way to do so:
update MyTable t set
Status = 0
WHERE FILEID=2565 and
LINE_NUMBER IN
(
SELECT t.LineNumber FROM MyTable t
WHERE FileID=2565 and
Exists
(
SELECT 1
FROM MyTable t2
WHERE t.StudentId_ID = t2.StudentId_ID AND
t.EnteranceYear = t2.EnteranceYear AND
t.LineNumber< t2.LineNumber
)
)
Thanks a lotUPDATE MyTable
SET Status = 0
WHERE FileID=2565
AND NOT EXISTS
(select * from MyTable as t
where t.StudentID = MyTable.StudentID
and t.EntranceYear = MyTable.EntranceYear
and t.LineNumber > MyTable.LineNumber)
Roy

query on primary key field

Hello, I have a query on primary key fields, the performance very bad. it
takes about 111 seconds to get no record found, out of 5 million records in
one table.
It is totally unacceptable, what I can do:
1. create non-cluster index? from what I know since the query fields are
primary key, this should be not necessary.
2. remove all those convert function in the query? but from the query
anlyzer result, seems there are not so much computation time
consumed.(I/O=60, CPU=2.65)
Here are the query:
select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_tid
=? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
cc_trandate_time
Any idea? Thanks in advance.
Andrew
Yes, I am assuming that cc_tid is your Primary Key field? Make sure that it
is indexed, regardless. If it is, it is already indexed but make sure that
it is not the Clustered Index. A better choice of a Clustered Index would be
your cc_trandate_time field as it would be a good candidate for a range
query, which you are doing, it is not "too" wide, and is not likely to be
updated frequently.
What is doe the Optimizer estimated execution plan look like? Could you
publish the text results with the sub-tree estimated costs?
Also, what is the expected number of returned rows? How many cpus and
memory on this server? How many different independent disks is this database
distributed accross?
Thanks.
Sincerely,
Anthony Thomas
"Andrew" wrote:

> Hello, I have a query on primary key fields, the performance very bad. it
> takes about 111 seconds to get no record found, out of 5 million records in
> one table.
> It is totally unacceptable, what I can do:
> 1. create non-cluster index? from what I know since the query fields are
> primary key, this should be not necessary.
> 2. remove all those convert function in the query? but from the query
> anlyzer result, seems there are not so much computation time
> consumed.(I/O=60, CPU=2.65)
> Here are the query:
> select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
> cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
> tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_tid
> =? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
> cc_trandate_time
> Any idea? Thanks in advance.
> Andrew

query on primary key field

Hello, I have a query on primary key fields, the performance very bad. it
takes about 111 seconds to get no record found, out of 5 million records in
one table.
It is totally unacceptable, what I can do:
1. create non-cluster index? from what I know since the query fields are
primary key, this should be not necessary.
2. remove all those convert function in the query? but from the query
anlyzer result, seems there are not so much computation time
consumed.(I/O=60, CPU=2.65)
Here are the query:
select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_tid
=? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
cc_trandate_time
Any idea? Thanks in advance.
AndrewYes, I am assuming that cc_tid is your Primary Key field? Make sure that it
is indexed, regardless. If it is, it is already indexed but make sure that
it is not the Clustered Index. A better choice of a Clustered Index would be
your cc_trandate_time field as it would be a good candidate for a range
query, which you are doing, it is not "too" wide, and is not likely to be
updated frequently.
What is doe the Optimizer estimated execution plan look like? Could you
publish the text results with the sub-tree estimated costs?
Also, what is the expected number of returned rows? How many cpus and
memory on this server? How many different independent disks is this database
distributed accross?
Thanks.
Sincerely,
Anthony Thomas
"Andrew" wrote:
> Hello, I have a query on primary key fields, the performance very bad. it
> takes about 111 seconds to get no record found, out of 5 million records in
> one table.
> It is totally unacceptable, what I can do:
> 1. create non-cluster index? from what I know since the query fields are
> primary key, this should be not necessary.
> 2. remove all those convert function in the query? but from the query
> anlyzer result, seems there are not so much computation time
> consumed.(I/O=60, CPU=2.65)
> Here are the query:
> select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
> cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
> tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_tid
> =? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
> cc_trandate_time
> Any idea? Thanks in advance.
> Andrew

query on primary key field

Hello, I have a query on primary key fields, the performance very bad. it
takes about 111 seconds to get no record found, out of 5 million records in
one table.
It is totally unacceptable, what I can do:
1. create non-cluster index? from what I know since the query fields are
primary key, this should be not necessary.
2. remove all those convert function in the query? but from the query
anlyzer result, seems there are not so much computation time
consumed.(I/O=60, CPU=2.65)
Here are the query:
select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_tid
=? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
cc_trandate_time
Any idea? Thanks in advance.
AndrewYes, I am assuming that cc_tid is your Primary Key field? Make sure that it
is indexed, regardless. If it is, it is already indexed but make sure that
it is not the Clustered Index. A better choice of a Clustered Index would b
e
your cc_trandate_time field as it would be a good candidate for a range
query, which you are doing, it is not "too" wide, and is not likely to be
updated frequently.
What is doe the Optimizer estimated execution plan look like? Could you
publish the text results with the sub-tree estimated costs?
Also, what is the expected number of returned rows? How many cpus and
memory on this server? How many different independent disks is this databas
e
distributed accross?
Thanks.
Sincerely,
Anthony Thomas
"Andrew" wrote:

> Hello, I have a query on primary key fields, the performance very bad. it
> takes about 111 seconds to get no record found, out of 5 million records i
n
> one table.
> It is totally unacceptable, what I can do:
> 1. create non-cluster index? from what I know since the query fields are
> primary key, this should be not necessary.
> 2. remove all those convert function in the query? but from the query
> anlyzer result, seems there are not so much computation time
> consumed.(I/O=60, CPU=2.65)
> Here are the query:
> select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
> cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
> tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_t
id
> =? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
> cc_trandate_time
> Any idea? Thanks in advance.
> Andrew

Query on 2 bases

How to make a query which contains fields from two different bases ?

Thanks in advance.

ReNThis request was posted earlier today under the title "Inter-Database
References"

John
"TNR" <stage7@.tgs.fr> wrote in message
news:bg38re$u6c$1@.news-reader5.wanadoo.fr...
> How to make a query which contains fields from two different bases ?
> Thanks in advance.
> ReN|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<3f252ad9$0$18494$ed9e5944@.reading.news.pipex.net>...
> This request was posted earlier today under the title "Inter-Database
> References"
> John
> "TNR" <stage7@.tgs.fr> wrote in message
> news:bg38re$u6c$1@.news-reader5.wanadoo.fr...
> > How to make a query which contains fields from two different bases ?
> > Thanks in advance.
> > ReN

If your databases are on the same server then it's as simple as:

select *
from database1..table1 inner join database2..table2

if they are on different servers, then you'll need to add a linked
server to the database that you are running your query on. This can
be done through the GUI or by running sp_addlinkedserver system stored
procedure. Once you've done it then, the command is:

select *
from database1..table1 inner join linkedservername.database2..table2

Hope this helps
Hodge

Query of counts

I have a table with three fields: AcctNo INT, Code CHAR(1), Amount MONEY

The Code has three values: 'A', 'B', or 'C'.

Each AcctNo has 1, 2, or all 3 of the Codes assigned to it (ie. AcctNo is not unique)

I need to know how many AcctNos have one value assigned to the Code field, how many have two, and how many have three and I need to know the sum of the Amount for each group.

Can this be done in one statement or do I need three statements?

Fred

The query below returns the results as requested. Is this what you're looking for?

Chris

DECLARE @.Values TABLE (AcctNo INT, Code CHAR(1), Amount MONEY)

INSERT INTO @.Values(AcctNo, Code, Amount)

SELECT 1, 'A', 1.00 UNION

SELECT 1, 'B', 2.50 UNION

SELECT 2, 'C', 1.25 UNION

SELECT 3, 'C', 1.43 UNION

SELECT 3, 'A', 1.96 UNION

SELECT 3, 'B', 2.00 UNION

SELECT 4, 'C', 1.43 UNION

SELECT 4, 'A', 1.96 UNION

SELECT 4, 'B', 2.10 UNION

SELECT 5, 'B', 0.92 UNION

SELECT 5, 'A', 1.24 UNION

SELECT 6, 'C', 0.02 UNION

SELECT 7, 'B', 0.11

SELECT SUM([AccountNoCount]), SUM(TotalAmount), [CodeCount]

FROM (SELECT COUNT(DISTINCT AcctNo) AS [AccountNoCount], SUM(Amount) AS TotalAmount, COUNT(Code) AS [CodeCount]

FROM @.Values

GROUP BY AcctNo) t

GROUP BY [CodeCount]

|||

Yes, that is what I wanted first.

But I need to add another level of complexity. There are duplicates of the AccountNo and Code, only the Amount is different. I need to count duplicates as one. (or anything more than one; there were some with three times and four)

Thanks,

Fred

|||

All you should need is an extra DISTINCT, see below.

Chris

SELECT SUM([AccountNoCount]), SUM(TotalAmount), [CodeCount]

FROM (SELECT COUNT(DISTINCT AcctNo) AS [AccountNoCount], SUM(Amount) AS TotalAmount, COUNT(DISTINCT Code) AS [CodeCount]

FROM @.Values

GROUP BY AcctNo) t

GROUP BY [CodeCount]

Query no returning desired resluts, HELP

I have 3tables/views I need to pull into one query for a results set for a Gridview table.

2 views, 1 table.

1st view, vw_ORG has fields, ORGID, ORGNAME

2nd view, vw_Staff, has fields, ORGID, STAFFID, FIRSTNAME, LASTNAME, MIDDLENAME, PREFIX, DEGREE

1st table : tbl_Data, has fields, ORGID, LASTMODIFIEDBY (which is a fk, pk in vw_Staff), LASTMODIFIED (date time ).

Basically my results need to have the ORGID, ORGNAME, LASTMODIFIED DATE and LASTMODIFIEDBY.

Would look like this...

ORGID ORGNAME LASTMODIFIED DATE LASTMODIFIEDBY
1 Science 10/10/2006 9:42 a.m. John P. Smith
22 Mathmatic 10/01/2006 9:15 p.m. Leslie Stahl
95 Football 5/01/2006 12:15 a.m. Terrell Owens

I have gotten results that displays the lastmodified date use MAX but can't figure out how to pull in the names from vw_Staff

Here is that part if you want to use as a starting base.

Select DISTINCT b.orgid, b.OrgName,
(SELECT Max(d.lastmodified)FROM tbl_Data d WHERE d.orgid = b.orgid) AS modifiedDate
From vw_ORGS b
ORDER BY ORGID

I've been banging my head against a wall for 2 days now and am desperatly needing some resolution before the voices in my head start telling me to harm myself j/k.No one can help with this... ?|||select o.ORGID
, o.ORGNAME
, d.LASTMODIFIED
, s.FIRSTNAME
from vw_ORG as o
inner
join tbl_Data as d
on d.ORGID = o.ORGID
inner
join vw_Staff as s
on s.STAFFID = d.LASTMODIFIEDBY|||To many results from your code. but the attmept is most appreciated.

Basically it has to be a MAX date from when the ORG was last modified. Only one row of data per ORG.|||so the tbl_Data table has multiple "last modified" rows per ORGID?select o.ORGID
, o.ORGNAME
, d.LASTMODIFIED
, s.FIRSTNAME
from vw_ORG as o
inner
join tbl_Data as d
on d.ORGID = o.ORGID
and d.LASTMODIFIED =
( select max(LASTMODIFIED)
from tbl_Data
where ORGID = o.ORGID )
inner
join vw_Staff as s
on s.STAFFID = d.LASTMODIFIEDBY|||Yes, that is corrrect.. I was able to get the quasi results needed by using SELECT DISTINCT?

But another issue is that I need to retireve all ORGS weather or not the have a last modified date or not. Is there a IS NULL function or something that can be used to with the select max(LASTMODIFIED) to do this?

Thanks.|||change INNER to LEFT OUTER in two places

Query needed

Hi, I've a prob in my office.i have a table with the following fields,

table : acc_file
acc_cod nvarchar(6) P.key
acc_name nvarchar(30),
level int,
acc_mas nvarchar(6) which should any one of the previously entered
values of the field acc_cod(reference).

the rows will be (example)

ASSET ASSETS 0
LIAB LIABILITIES 0
curass current assets 1 Asset
Fixass fixed assets 1 Asset
curlia current liability 1 LIAB
BANKBAL BANLBALANCES 2 curass.

I want the rows to be displayed or ordered by the acc_mas and level.As
per the accounting standards the level field is given . for instance
it needed to be

ASSET ASSETS 0 ' base level
curass current assets 1 Asset
BANKBAL BANLBALANCES 2 curass.
Fixass fixed assets 1 Asset
LIAB LIABILITIES 0 ' base level
curlia current liability 1 LIAB

HOW IT WILL BE PERFORMED

LEVEL 0 IS THE BASE LEVEL OF THE ASSET, LIAB, ETC
LEVEL 1 IS THE NEXT LEVEL OF THE ASSET IE, CURRENT ASSET, FIXED ASSET

I want that the query should return the values as per the acc_mas and
level as the example given above.Is it posiable thru the query .

With Thanks .
RaghuOn 8 Nov 2003 06:02:39 -0800, raghuraman_ace@.rediffmail.com
(Raghuraman) wrote:

>Hi, I've a prob in my office.i have a table with the following fields,
>table : acc_file
>acc_cod nvarchar(6) P.key
>acc_name nvarchar(30),
>level int,
>acc_mas nvarchar(6) which should any one of the previously entered
>values of the field acc_cod(reference).
>the rows will be (example)
>ASSET ASSETS 0
>LIAB LIABILITIES 0
>curass current assets 1 Asset
>Fixass fixed assets 1 Asset
>curlia current liability 1 LIAB
>BANKBAL BANLBALANCES 2 curass.
>I want the rows to be displayed or ordered by the acc_mas and level.As
>per the accounting standards the level field is given . for instance
>it needed to be
>
>ASSET ASSETS 0 ' base level
>curass current assets 1 Asset
>BANKBAL BANLBALANCES 2 curass.
>Fixass fixed assets 1 Asset
>LIAB LIABILITIES 0 ' base level
>curlia current liability 1 LIAB
>HOW IT WILL BE PERFORMED
>
>LEVEL 0 IS THE BASE LEVEL OF THE ASSET, LIAB, ETC
>LEVEL 1 IS THE NEXT LEVEL OF THE ASSET IE, CURRENT ASSET, FIXED ASSET
>I want that the query should return the values as per the acc_mas and
>level as the example given above.Is it posiable thru the query .
>
>With Thanks .
>Raghu
Is that level a fixed quantity or could there also be level 3, level 4
etc?|||Yes friend , every acc_cod (pk field )may have multiple levels 3,4,5,etc
and each level may also have inner levels starting from (1,2,3, etc)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||>Hi, I've a prob in my office.i have a table with the following fields,
>table : acc_file
>acc_cod nvarchar(6) P.key
>acc_name nvarchar(30),
>level int,
>acc_mas nvarchar(6) which should any one of the previously entered
>values of the field acc_cod(reference).
>the rows will be (example)
>ASSET ASSETS 0
>LIAB LIABILITIES 0
>curass current assets 1 Asset
>Fixass fixed assets 1 Asset
>curlia current liability 1 LIAB
>BANKBAL BANLBALANCES 2 curass.
>I want the rows to be displayed or ordered by the acc_mas and level.As
>per the accounting standards the level field is given . for instance
>it needed to be
>
>ASSET ASSETS 0 ' base level
>curass current assets 1 Asset
>BANKBAL BANLBALANCES 2 curass.
>Fixass fixed assets 1 Asset
>LIAB LIABILITIES 0 ' base level
>curlia current liability 1 LIAB
>HOW IT WILL BE PERFORMED
>
>LEVEL 0 IS THE BASE LEVEL OF THE ASSET, LIAB, ETC
>LEVEL 1 IS THE NEXT LEVEL OF THE ASSET IE, CURRENT ASSET, FIXED ASSET
>I want that the query should return the values as per the acc_mas and
>level as the example given above.Is it posiable thru the query .
>
>With Thanks .
>Raghu
>Is that level a fixed quantity or could there also be level 3, level 4
>etc?
>On 09 Nov 2003 13:20:51 GMT, Raghu Raman <raghuraman_ace@.rediffmail.com> wrote:

>Yes friend , every acc_cod (pk field )may have multiple levels 3,4,5,etc
>and each level may also have inner levels starting from (1,2,3, etc)
>*** Sent via Developersdex http://www.developersdex.com ***
>Don't just participate in USENET...get rewarded for it!

In that case I'd be interested to see some ideas, as well. It's doable
if you know how many levels there are, but if you don't I don't think
sql server can do this with a straight query.

The following link has a discussion of this problem.

http://www.sqlteam.com/item.asp?ItemID=8866|||Hi - this sounds like something that I did recently, which required a
self cross join unioned with a self cross join and recursed. Um...
yeah anyway it was a bit wierd.

If you fancy posting some sample create and inserts we could maybe
have a bash at making something work for you.

Lyndon Hills <lyndon@.nospam.tenegi.com> wrote in message news:<squvqvc41es2fi1673mhinkcfjqbttnihq@.4ax.com>...
> >Hi, I've a prob in my office.i have a table with the following fields,
> >table : acc_file
> >acc_cod nvarchar(6) P.key
> >acc_name nvarchar(30),
> >level int,
> >acc_mas nvarchar(6) which should any one of the previously entered
> >values of the field acc_cod(reference).
> >the rows will be (example)
> >ASSET ASSETS 0
> >LIAB LIABILITIES 0
> >curass current assets 1 Asset
> >Fixass fixed assets 1 Asset
> >curlia current liability 1 LIAB
> >BANKBAL BANLBALANCES 2 curass.
> >I want the rows to be displayed or ordered by the acc_mas and level.As
> >per the accounting standards the level field is given . for instance
> >it needed to be
> >ASSET ASSETS 0 ' base level
> >curass current assets 1 Asset
> >BANKBAL BANLBALANCES 2 curass.
> >Fixass fixed assets 1 Asset
> >LIAB LIABILITIES 0 ' base level
> >curlia current liability 1 LIAB
> >HOW IT WILL BE PERFORMED
> >LEVEL 0 IS THE BASE LEVEL OF THE ASSET, LIAB, ETC
> >LEVEL 1 IS THE NEXT LEVEL OF THE ASSET IE, CURRENT ASSET, FIXED ASSET
> >I want that the query should return the values as per the acc_mas and
> >level as the example given above.Is it posiable thru the query .
> >With Thanks .
> >Raghu
> >Is that level a fixed quantity or could there also be level 3, level 4
> >etc?
> >On 09 Nov 2003 13:20:51 GMT, Raghu Raman <raghuraman_ace@.rediffmail.com> wrote:
> >Yes friend , every acc_cod (pk field )may have multiple levels 3,4,5,etc
> >and each level may also have inner levels starting from (1,2,3, etc)
> >*** Sent via Developersdex http://www.developersdex.com ***
> >Don't just participate in USENET...get rewarded for it!
> In that case I'd be interested to see some ideas, as well. It's doable
> if you know how many levels there are, but if you don't I don't think
> sql server can do this with a straight query.
> The following link has a discussion of this problem.
> http://www.sqlteam.com/item.asp?ItemID=8866|||>> As per the accounting standards the level field [sic]is given. <<

In the accounting systems I have seen, the account codes are fixed
length strrings of digits, arranged in a hierarchy, like the the Dewey
Decimal system, so you just sort them numerically. Do a GOOGLE on
"Uniform Chart of Accounts" and you will get all of the various state
requirements in the US.

Query multiple fields at once??

I have a table with 10 fields. I want to select any row that contains
the word "bob" in any one of the 10 fields. I've been writing this
query by checking each field individually connected with an OR
statement. Such as...
Select * from tablename where (column1 like '%bob%') or (column2 like
'%bob%') or ... and so on.
Is there an easier way to search all fields/columns in a more simple
SQL query?
The concept of ... Select * from tablename where (* like '%bob%')
?
Thanks,
- Steve
Nope. Your method of using the [OR] is the only way.
That you have to search multiple columns hints of denormalized data. Perhaps
this type of operation would be simpler if the table schema was
reconsidered.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"kennedystephen" <skennedy@.oaconsulting.com> wrote in message
news:1162572395.398641.35870@.h48g2000cwc.googlegro ups.com...
>I have a table with 10 fields. I want to select any row that contains
> the word "bob" in any one of the 10 fields. I've been writing this
> query by checking each field individually connected with an OR
> statement. Such as...
> Select * from tablename where (column1 like '%bob%') or (column2 like
> '%bob%') or ... and so on.
> Is there an easier way to search all fields/columns in a more simple
> SQL query?
> The concept of ... Select * from tablename where (* like '%bob%')
> ?
> Thanks,
> - Steve
>
|||Hi Stephen
This sounds like your design may want further normalisation?
I guess you could do something like
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where ISNULL(column1,'') + '|' + ISNULL(column2,'') + '|' +
ISNULL(column3,'') + '|' + ISNULL(column4,'') + '|' + ISNULL(column5,'') +
'|' + ISNULL(column6,'') + '|' + ISNULL(column7,'') + '|' +
ISNULL(column8,'') + '|'+ ISNULL(column9,'') + '|' + ISNULL(column10,'')
like '%bob%'
so long as you are not looking for something containing the delimiter! I am
not sure how that would perform. Other possible options might be:
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column1 like '%bob%'
UNION
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column2 like '%bob%'
UNION
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column3 like '%bob%'
UNION
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column4 like '%bob%'
etc ...
If you know that only one column contained the search string then you could
use UNION ALL in the above method.
or
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
FROM ( Select column1 AS Searchcolumn, column1, column2, column3, column4,
column5, column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column2 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column3 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column4 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column5 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename ) A
where Searchcolumn like '%bob%'
HTH
John
"kennedystephen" wrote:

> I have a table with 10 fields. I want to select any row that contains
> the word "bob" in any one of the 10 fields. I've been writing this
> query by checking each field individually connected with an OR
> statement. Such as...
> Select * from tablename where (column1 like '%bob%') or (column2 like
> '%bob%') or ... and so on.
> Is there an easier way to search all fields/columns in a more simple
> SQL query?
> The concept of ... Select * from tablename where (* like '%bob%')
> ?
> Thanks,
> - Steve
>
|||And if it were equality instead of like, you could reverse the clause, e.g.
WHERE 'bob' IN (column1, column2, column3, ...)
But I agree that the design does not seem fundamentally sound to me.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0256D861-C01E-4766-BE20-09ED5790337B@.microsoft.com...
> Hi Stephen
> This sounds like your design may want further normalisation?
> I guess you could do something like
> Select column1, column2, column3, column4, column5, column6, column7,
> column8, column9, column10
> from tablename
> where ISNULL(column1,'') + '|' + ISNULL(column2,'') + '|' +
> ISNULL(column3,'') + '|' + ISNULL(column4,'') + '|' + ISNULL(column5,'') +
> '|' + ISNULL(column6,'') + '|' + ISNULL(column7,'') + '|' +
> ISNULL(column8,'') + '|'+ ISNULL(column9,'') + '|' + ISNULL(column10,'')
> like '%bob%'
> so long as you are not looking for something containing the delimiter! I
> am
> not sure how that would perform.
|||stephen,
If this is a common requirement for your applications, then you might want
to consider full text indexing and searches. BOL can get you started.
-- Bill
"kennedystephen" <skennedy@.oaconsulting.com> wrote in message
news:1162572395.398641.35870@.h48g2000cwc.googlegro ups.com...
>I have a table with 10 fields. I want to select any row that contains
> the word "bob" in any one of the 10 fields. I've been writing this
> query by checking each field individually connected with an OR
> statement. Such as...
> Select * from tablename where (column1 like '%bob%') or (column2 like
> '%bob%') or ... and so on.
> Is there an easier way to search all fields/columns in a more simple
> SQL query?
> The concept of ... Select * from tablename where (* like '%bob%')
> ?
> Thanks,
> - Steve
>

Query multiple fields at once??

I have a table with 10 fields. I want to select any row that contains
the word "bob" in any one of the 10 fields. I've been writing this
query by checking each field individually connected with an OR
statement. Such as...
Select * from tablename where (column1 like '%bob%') or (column2 like
'%bob%') or ... and so on.
Is there an easier way to search all fields/columns in a more simple
SQL query?
The concept of ... Select * from tablename where (* like '%bob%')
?
Thanks,
- SteveNope. Your method of using the [OR] is the only way.
That you have to search multiple columns hints of denormalized data. Perhaps
this type of operation would be simpler if the table schema was
reconsidered.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"kennedystephen" <skennedy@.oaconsulting.com> wrote in message
news:1162572395.398641.35870@.h48g2000cwc.googlegroups.com...
>I have a table with 10 fields. I want to select any row that contains
> the word "bob" in any one of the 10 fields. I've been writing this
> query by checking each field individually connected with an OR
> statement. Such as...
> Select * from tablename where (column1 like '%bob%') or (column2 like
> '%bob%') or ... and so on.
> Is there an easier way to search all fields/columns in a more simple
> SQL query?
> The concept of ... Select * from tablename where (* like '%bob%')
> ?
> Thanks,
> - Steve
>|||Hi Stephen
This sounds like your design may want further normalisation?
I guess you could do something like
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where ISNULL(column1,'') + '|' + ISNULL(column2,'') + '|' +
ISNULL(column3,'') + '|' + ISNULL(column4,'') + '|' + ISNULL(column5,'') +
'|' + ISNULL(column6,'') + '|' + ISNULL(column7,'') + '|' +
ISNULL(column8,'') + '|'+ ISNULL(column9,'') + '|' + ISNULL(column10,'')
like '%bob%'
so long as you are not looking for something containing the delimiter! I am
not sure how that would perform. Other possible options might be:
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column1 like '%bob%'
UNION
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column2 like '%bob%'
UNION
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column3 like '%bob%'
UNION
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column4 like '%bob%'
etc ...
If you know that only one column contained the search string then you could
use UNION ALL in the above method.
or
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
FROM ( Select column1 AS Searchcolumn, column1, column2, column3, column4,
column5, column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column2 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column3 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column4 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column5 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename ) A
where Searchcolumn like '%bob%'
HTH
John
"kennedystephen" wrote:
> I have a table with 10 fields. I want to select any row that contains
> the word "bob" in any one of the 10 fields. I've been writing this
> query by checking each field individually connected with an OR
> statement. Such as...
> Select * from tablename where (column1 like '%bob%') or (column2 like
> '%bob%') or ... and so on.
> Is there an easier way to search all fields/columns in a more simple
> SQL query?
> The concept of ... Select * from tablename where (* like '%bob%')
> ?
> Thanks,
> - Steve
>|||And if it were equality instead of like, you could reverse the clause, e.g.
WHERE 'bob' IN (column1, column2, column3, ...)
But I agree that the design does not seem fundamentally sound to me.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0256D861-C01E-4766-BE20-09ED5790337B@.microsoft.com...
> Hi Stephen
> This sounds like your design may want further normalisation?
> I guess you could do something like
> Select column1, column2, column3, column4, column5, column6, column7,
> column8, column9, column10
> from tablename
> where ISNULL(column1,'') + '|' + ISNULL(column2,'') + '|' +
> ISNULL(column3,'') + '|' + ISNULL(column4,'') + '|' + ISNULL(column5,'') +
> '|' + ISNULL(column6,'') + '|' + ISNULL(column7,'') + '|' +
> ISNULL(column8,'') + '|'+ ISNULL(column9,'') + '|' + ISNULL(column10,'')
> like '%bob%'
> so long as you are not looking for something containing the delimiter! I
> am
> not sure how that would perform.|||stephen,
If this is a common requirement for your applications, then you might want
to consider full text indexing and searches. BOL can get you started.
-- Bill
"kennedystephen" <skennedy@.oaconsulting.com> wrote in message
news:1162572395.398641.35870@.h48g2000cwc.googlegroups.com...
>I have a table with 10 fields. I want to select any row that contains
> the word "bob" in any one of the 10 fields. I've been writing this
> query by checking each field individually connected with an OR
> statement. Such as...
> Select * from tablename where (column1 like '%bob%') or (column2 like
> '%bob%') or ... and so on.
> Is there an easier way to search all fields/columns in a more simple
> SQL query?
> The concept of ... Select * from tablename where (* like '%bob%')
> ?
> Thanks,
> - Steve
>

Query multiple fields at once??

I have a table with 10 fields. I want to select any row that contains
the word "bob" in any one of the 10 fields. I've been writing this
query by checking each field individually connected with an OR
statement. Such as...
Select * from tablename where (column1 like '%bob%') or (column2 like
'%bob%') or ... and so on.
Is there an easier way to search all fields/columns in a more simple
SQL query?
The concept of ... Select * from tablename where (* like '%bob%')
?
Thanks,
- SteveNope. Your method of using the [OR] is the only way.
That you have to search multiple columns hints of denormalized data. Perhaps
this type of operation would be simpler if the table schema was
reconsidered.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"kennedystephen" <skennedy@.oaconsulting.com> wrote in message
news:1162572395.398641.35870@.h48g2000cwc.googlegroups.com...
>I have a table with 10 fields. I want to select any row that contains
> the word "bob" in any one of the 10 fields. I've been writing this
> query by checking each field individually connected with an OR
> statement. Such as...
> Select * from tablename where (column1 like '%bob%') or (column2 like
> '%bob%') or ... and so on.
> Is there an easier way to search all fields/columns in a more simple
> SQL query?
> The concept of ... Select * from tablename where (* like '%bob%')
> ?
> Thanks,
> - Steve
>|||Hi Stephen
This sounds like your design may want further normalisation?
I guess you could do something like
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where ISNULL(column1,'') + '|' + ISNULL(column2,'') + '|' +
ISNULL(column3,'') + '|' + ISNULL(column4,'') + '|' + ISNULL(column5,'') +
'|' + ISNULL(column6,'') + '|' + ISNULL(column7,'') + '|' +
ISNULL(column8,'') + '|'+ ISNULL(column9,'') + '|' + ISNULL(column10,'')
like '%bob%'
so long as you are not looking for something containing the delimiter! I am
not sure how that would perform. Other possible options might be:
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column1 like '%bob%'
UNION
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column2 like '%bob%'
UNION
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column3 like '%bob%'
UNION
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column4 like '%bob%'
etc ...
If you know that only one column contained the search string then you could
use UNION ALL in the above method.
or
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
FROM ( Select column1 AS Searchcolumn, column1, column2, column3, column4,
column5, column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column2 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column3 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column4 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column5 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename ) A
where Searchcolumn like '%bob%'
HTH
John
"kennedystephen" wrote:

> I have a table with 10 fields. I want to select any row that contains
> the word "bob" in any one of the 10 fields. I've been writing this
> query by checking each field individually connected with an OR
> statement. Such as...
> Select * from tablename where (column1 like '%bob%') or (column2 like
> '%bob%') or ... and so on.
> Is there an easier way to search all fields/columns in a more simple
> SQL query?
> The concept of ... Select * from tablename where (* like '%bob%')
> ?
> Thanks,
> - Steve
>|||And if it were equality instead of like, you could reverse the clause, e.g.
WHERE 'bob' IN (column1, column2, column3, ...)
But I agree that the design does not seem fundamentally sound to me.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0256D861-C01E-4766-BE20-09ED5790337B@.microsoft.com...
> Hi Stephen
> This sounds like your design may want further normalisation?
> I guess you could do something like
> Select column1, column2, column3, column4, column5, column6, column7,
> column8, column9, column10
> from tablename
> where ISNULL(column1,'') + '|' + ISNULL(column2,'') + '|' +
> ISNULL(column3,'') + '|' + ISNULL(column4,'') + '|' + ISNULL(column5,'') +
> '|' + ISNULL(column6,'') + '|' + ISNULL(column7,'') + '|' +
> ISNULL(column8,'') + '|'+ ISNULL(column9,'') + '|' + ISNULL(column10,'')
> like '%bob%'
> so long as you are not looking for something containing the delimiter! I
> am
> not sure how that would perform.|||stephen,
If this is a common requirement for your applications, then you might want
to consider full text indexing and searches. BOL can get you started.
-- Bill
"kennedystephen" <skennedy@.oaconsulting.com> wrote in message
news:1162572395.398641.35870@.h48g2000cwc.googlegroups.com...
>I have a table with 10 fields. I want to select any row that contains
> the word "bob" in any one of the 10 fields. I've been writing this
> query by checking each field individually connected with an OR
> statement. Such as...
> Select * from tablename where (column1 like '%bob%') or (column2 like
> '%bob%') or ... and so on.
> Is there an easier way to search all fields/columns in a more simple
> SQL query?
> The concept of ... Select * from tablename where (* like '%bob%')
> ?
> Thanks,
> - Steve
>

Query Memory?

I had posted a msg in another thread asking about forcing MSSQL to use
query memory vs disk I/O due to long fields.

Is there any way for me to benchmark what SQL is using in Memory vs.
allocated memory.

We are running SQL 2000 Standard, not Enterprise, so it can only
address the 2GB.Here is a link to the other posting...

http://groups.google.com/group/comp...659ba61722dc3ea

Query logic not working...

I have a little system of 3 tables Job, employees and times. This times table has the fields times_id, employee_id and job_id

I'm trying to have a query that pull of employees that don't have a certain job_id yet. I'm going to put this data in a table so the user knows they are available for that job. The code i have isn't working, and i'm not sure why.

SELECT
DISTINCT times.employee_id, employee.employee_name
FROM employee
INNER JOIN times ON employee.employee_id = times.employee_id
WHERE (times.job_id <> @.job_id)

Thanks in advance for any help. I'm sure I missing someting silly, or maybe i need to have a stored procedure involved?... Thanks!

Try a subquery:

SELECT
DISTINCT employee_id, employee_name
FROM employee
WHERE employee_id not in
(SELECT employee_id FROM times
WHERE (job_id= @.job_id) )

|||That worked great, I've totally forgot about sub-queries. Thanks a lot Iori Jay.|||

OR

SELECT
DISTINCT employee_id, employee_name
FROM employee
WHERE not exists (SELECT employee_id FROM times
WHERE (job_id= @.job_id) )