How To Match Usernames From A SP?

Mar 25, 2008

Hello,

I am writing a SP, inside the SP i have a select statement.

I also have another SP, when i run it, it sends a list back with all the users that are currently online.

Now in my first SP, i want to construct a select statement that returns a users data, but only for the users that are online.

 

This is the first SP (GetAllOnlineUsers), that produces a list with users online (just abit rewritten from the membership function total users online):

SELECT u.Username FROM dbo.aspnet_Users u(NOLOCK), dbo.aspnet_Applications a(NOLOCK), dbo.aspnet_Membership m(NOLOCK) WHERE u.ApplicationId = a.ApplicationId AND LastActivityDate > @DateActive AND a.LoweredApplicationName = LOWER(@ApplicationName) AND u.UserId = m.UserId

 

This is my second SP, in witch i want to get the online users data:

SELECT m.username, d.data1, d.data2 FROM m.userstable INNER JOIN data ON (data.username = m.username) WHERE (m.username IN (EXEC GetAllOnlineUsers))

Is it possible to exec a SP into a select statement?

View 3 Replies


ADVERTISEMENT

Passwords And Usernames

Mar 15, 2006

i have been reading many asp.net books

where is a good place to hold user name and password while the user is on your side in a cookie or applicaion user variable?

and if the user closes the navigator..what command removes the log in information ?

View 1 Replies View Related

Sp_helpuser , Active Usernames For Database Question

Jan 31, 2000

If sp_helpuser showed some Username with (null) LoginName, I believe these must be some obsolete username defined in the database before ,can I have them cleaned up , anyone has this experience ?

Also, I was able to find out from sp_who, or select * from sysprocesses the active login id to the system, is there quick way ,stored procedure etc to find out the usernames for the current active session for a particular database ?? I don't think these usernames are shown up in the current activity under EM..

Anthony

View 1 Replies View Related

Sum Of Any Two Value Match

Feb 8, 2015

I have a table having single column id and n number of rows and 1 want to know all combination of id's where sum or subtraction of any two id's is equal to 7.

id
1
2
3
4
5
6
9
12
18
..
..

so output should be like

2 5
3 4
17 10
.......

View 4 Replies View Related

Sysindexes Don't Match

Sep 7, 2004

It seems my sysindexes table is inaccurate on a nonclustered index. In my case the rowcount (rows and rowcnt) do not match the actual rowcount of the table. The command UPDATE STATISTICS doesn't change the rows or rowcount, adding 'FULSLCAN' won't budge rowcount either.

After I did a dbcc reindex, the number of rows matched, however, upon adding rows in the table both rows and rowcount are out of sync again.

It's a fairly straightforward table, no triggers, no computed fields, only integer, datetime, varchar and bigint columns. There's a clustered index on a bigint column and a nonclustered index on a integer column.

dbcc show_statistics show that the nonclustered index is updated and it's rows and rows sampled match the number of rows in the table (not in the sysindexes-table).

I'd like to know if I'm chasing ghosts here or if there's something very wrong here. What could be causing the counts being inaccurate? Anyone who could shed some light?

View 2 Replies View Related

Match The Correct Value

Apr 25, 2008

I have this sql statement:

SELECT Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastName, PersonSkills.Skills
FROM PersonSkills INNER JOIN....

How can I choose a certain value from PersonSkills.Skills?

for example, I would like to choose a value from PersonSkills.Skills that matches a certain product. I can do the Max/Min value but the selected value needs to match the product.

The tables that I have are:
PersonSkills:
id, ProductID, Skills

Product:
id, ProductName

Ive been reading and playing around with this without success.

View 6 Replies View Related

Select Where No Match

Mar 14, 2014

I want to select Property names or owner id from table A where there is no match between owner ID in table A and B , for example Property 3 in red. In table A only one person can be listed as owner of property. In table B many people can be listed as owner of the same property.

The way i script will return property 1 twice (as no match) because the owner ID from table A '123456' mismatch twice with owner ID from table B

'111111' and '222222'. I dont want Property 1 to be selected at all because the owner '123456' is listed as one of the owners in Table B

View 2 Replies View Related

Match Value From Column In T1 To T2?

Nov 11, 2014

I have two tables in the same DB, one named “Client” one named “Case”.

-One column inside Client is “ClientID”, another is “ClientCode”.

-One column inside Case is “CaseID” and another is “ClientCode”, which corresponds to the ClientCode column in the previous table.

I need a file with output that looks like this CaseID+ClientID.

So, an example row from the “Client” table” could be ClientID = 123, ClientCode=999. An example row from the “Case table” could be CaseID=555, ClientCode=999. So, I need output of 555123 .

View 5 Replies View Related

Extact Match

May 4, 2007

Scenario: 2 sets of data
Set 1 (200 records): firstname, lastname, address, city, phone
Set 2 (100 records): firstname, lastname, address, city, phone

I run a query using phone as criteria/condition (where xx.phone=yy.phone). I got 75 matches based on the phone numbers

However, if I deleted the phone numbers from set 2 data, used below query, the return result is only 45 matches. My question is what technique that I can use to optimize the result just based on criteria like first, last, and address.

select xx.firstname, xx.lastname, yy.Firstname, yy.Lastname, xx.address, yy.address, xx.city, yy.city, yy.phone
from set2 xx, set1 yy where
substring(soundex(xx.Firstname),0,3)=substring(soundex(yy.Firstname),0,3)
and substring(soundex(xx.lastname),0,4)=substring(soundex(yy.Lastname),0,4)
and substring(xx.address,1,7)= substring(yy.address,1,7)
and xx.city=yy.city;


Thank you

id....

View 20 Replies View Related

Match In Two Tables

Dec 28, 2007

Hi, hope someone can help.

I have a table of employees and two sites. Most employees only do shifts at the one site but may occasionally work at the other site. What i want is the details of those who have worked at both sites.

for e.g.

Employees
1 Dave
2 Peter
3 John

Site 1
Mon John
Tue Peter
Wed John
Thu John
Fri Peter

Site 2
Mon Dave
Tue Dave
Wed Dave
Thu Dave
Fri Peter

So the answer should be 2 Peter. Hope this explains the problem. Probably very obvious but it has me stumped.

Thanks

View 4 Replies View Related

Mix And Match Rows

Jul 14, 2006

In the trading (stock market) industry there is a practice of rolling up (merging) multiple trades into a single trade in an effort to save on ticket charges. The way this is done is performing a SUM() on the quantities and calculating an average price. (Average price is the SUM(Qty * Price) / SUM(Qty).

So, given :

Qty     Price
20      $5
20      $10


You get:

40      $7.5           -- 20 + 20 and SUM(20 * $5, 20 * $10) / SUM(20 + 20)

Here is my dilema: If given a set of trades, I need to loop through them and check every combination to determine which one matches the expected rolled-up final trade. In other words,

If I know that the final trade is:

15     $10

And I have the following trades in my set:

TradeId     Qty     Price
1                10       $10
2                 7         $20
3                5          $10

I need to check the roll-up of trades (1, 2), (1, 3), and (2, 3) and determine that it final trade was made by rolling up trades 1 and 3.

In the real situation, the number of trades that I need to check is not set to a specific number.

Any help would be appreciated. Cursors, temp tables, functions, recursive calls, .NET (I am running SQL 2005 so have access to CLR) are ALL acceptable solutions...







Here is a sample SQL code (table and data) to work with.

USE [tempdb]

DROP TABLE [Trades]
GO

CREATE TABLE [Trades] (
[TradeId] INT,
[Quantity] INT,
[Price] DECIMAL(6,2)
)
GO

-- need to find trades that rollup to quantity 30 and average price 7.5

INSERT INTO [Trades] VALUES (1, 10, 10)
INSERT INTO [Trades] VALUES (2, 10, 5.0)
INSERT INTO [Trades] VALUES (3, 25, 7.5)
INSERT INTO [Trades] VALUES (4, 10, 10.0)
INSERT INTO [Trades] VALUES (5, 2, 2.0)
INSERT INTO [Trades] VALUES (6, 10, 7.5)

SELECT
[TradeId],
[Quantity],
[Price],
[Quantity] * [Price] AS [MarketValue]
FROM
[Trades]

-- need to find the trades that roll up to quantity 30 with an average price of $7.5
-- Trades 2, 4, and 6 are the solution

SELECT
SUM([Quantity]) AS [TotalQuantity],
SUM([Quantity] * [Price]) / SUM([Quantity]) AS [AveragePrice]
FROM
[Trades]
WHERE
[TradeId] IN (2, 4, 6)

-- what I need to get back is the SET of rows that make up the rolled trade, so I want to see

SELECT
*
FROM
[Trades]
WHERE
[TradeId] IN (2, 4, 6)


Thank you for any and all help in this.

- Jason

View 15 Replies View Related

I Have Two Tables One With Id And One With Storyid Which Match...

Aug 29, 2007

i have two tables one with id and one with storyid which match. i need to check which record is at top of table two. and then pull top four from table one where the top story from table two (storyid) is not one of the top four in table one (id). is this possible? can anyone help?

View 8 Replies View Related

Closest Match SQL Query

Oct 5, 2007

I have a need to execute a query in T-SQL on a numeric field in a SQL table.
However if there is no exact match I'd likea query that will return the row that is just below my value.
 As an example if the table has values:   1,2,4,5,7,9, 15 and 20 for instance and I am matching with a varibale containing the value 9 then I'd like it to return that row. however if my variable has the value 19 I want the row containign 15 returned.
Is this possible? Can anyone help me with such a query?
Regards
Clive

View 6 Replies View Related

Find Value Match In SQL Table

Oct 28, 2007

Find value match in SQL table
Is there any application that can compare two tables and find the similarities (there is no high rate of exact match on the field values but there are similarities)?
 

View 2 Replies View Related

Ambiguous Match Found.

Nov 3, 2003

Does anyone know what this error means?

Ambiguous match found.

i'm using sql server 2000


here is my code

Dim con As SqlConnection = New SqlConnection

con.ConnectionString = _
"Data Source=localhost;" + _
"Initial Catalog=registeruser;" + _
"User ID=int422;" + _
"Password=int422"


Dim cmd As SqlCommand = New SqlCommand

cmd.Connection = con
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT salt,hash FROM users WHERE login_id = '" + user.Text + "'"


Dim rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)


con.Open()

Dim salt, hash As String



While rdr.Read()


If user.Text = rdr.Item("login_id").ToString() Then

salt = rdr.Item("salt").ToString()
hash = rdr.Item("hash").ToString()


End If


End While

con.Close()


Label1.Text = salt



End Sub

View 1 Replies View Related

Odbc Dll Versions Don't Match??

May 15, 2001

I'm trying to correct an error that a user is getting when running SQL Server 7.0 SP3, Desktop edition/ Windows NT 4.0 SP6. The error says that C:WINNTSYSTEM32odbcint.dll and C:WINNTSYSTEM32odbccp32.dll are different versions. I tried to install MDAC 2.5 sp1 to correct this problem but this message continues throughout the install. This is causing sqlagent service to hang. Any ideas of how to reinstall ODBC components without reinstalling the OS?

View 1 Replies View Related

Any Part Of Field Match

Jun 10, 2004

How can I make my search button have the "Any part of field" match as a default? with a simple query...


I have a field in MS access with hundreds of words (cv)... I want to be able to find a word in "Any part of field"

my try:

WHERE ((([cv].[detail cv])=[detail]));

detail is nowhere to find... i am prompt to give a value. fine.but it
equals whole field; detail must be the sole value of the field detail cv...


help!

mchel

View 7 Replies View Related

[Match Any Portion] - Possible In MSSQL?

Oct 10, 2005

I asked this question in the mysql forums, but I am also interested in any info regarding MSSQL. I've seen databases with search systems with the functionality I seek below. I just have no idea how they are doing it. Thank you.

We have an mysql inventory database. We want to be able to put in 4984.600 and choose "match any portion" and it finds 4984600 which is in our database.

Does Mysql have a "match any portion" search function? In this case LIKE didn't work which we tried already.

Any ideas? or are we stuck with using MSSQL. We know this will work with MSSQL.

Thank you very much. This is a huge problem for us.

Jim

View 3 Replies View Related

[Match Any Portion] - Possible In MSSQL?

Oct 11, 2005

Hello,

I asked this question about mysql on another forum, but I would also like to know about MSSQL. I may need to switch to this platform if Mysql doesn't work. Here's my problem:

Instead of "match", I am looking for a "match any portion" way to do searches with mysql.

We have an mysql inventory database. We want to be able to put in 4984.600 and choose "match any portion" and it finds 4984600 which is in our database.

Does Mysql have a "match any portion" search function? In this case LIKE didn't work which we tried already.

Any ideas?

Thank you very much. This is a huge problem for us.

Jim

View 3 Replies View Related

Need To Create Table(s) To Match This Xml

Dec 6, 2005

Hi,

I am not an expert with SQL2000 and need to create table(s) to match this xml format(if possible):

<?xml version="1.0" encoding="UTF-8" ?>
<MenuData xmlns="http://tempuri.org/MenuDataDataSet.xsd">
<Menu Id="RootMenu">
<Item1 Caption="About TTF FIS" Url="" Target="" Id="AboutItem">
<ChildMenu1 Id="AboutMenu">
<ChildItem Caption="FAQs" Url="Faqs.aspx" Target="_self" Id="FaqsItem" />
</ChildMenu1>
</Item1>
<Item2 Caption="Resource Solutions" Url="" Target="" Id="BizSolItem">
<ChildMenu2 Id="ResourceMenu">
<ChildItemMenu2 Caption="Resource Tracking" Url="" Target="" Id="AccItem">
<ChildMenuChild2 Id="AccMenu">
<ChildItemChild2 Caption="Resource_A" Url="Resource_A.aspx" Target="_self" Id="AItem" />
<ChildItemChild2 Caption="Resource_B" Url="Resource_B.aspx" Target="_self" Id="BItem" />
<ChildItemChild2 Caption="Resource_C" Url="Resource_C.aspx" Target="_self" Id="CItem" />
<ChildItemChild2 Caption="Resource_D" Url="Resource_D.aspx" Target="_self" Id="DItem" />
</ChildMenuChild2>
</ChildItemMenu2>
<ChildItemMenu2 Caption="Engineering" Url="Engineering.aspx" Target="_self" Id="EngItem" />
<ChildItemMenu2 Caption="Design" Url="Design.aspx" Target="_self" Id="MarItem" />
<ChildItemMenu2 Caption="Deployment" Url="Fire.aspx" Target="_self" Id="FireItem" />
</ChildMenu2>
</Item2>
<Item3 Caption="MES Solutions" Url="" Target="" Id="PerSolItem">
<ChildMenu3 Id="PerSolMenu">
<ChildItemMenu3 Caption="MES First Solution" Url="Education.aspx" Target="_self" Id="EduItem" />
<ChildItemMenu3 Caption="MES Second Solution" Url="Household.aspx" Target="_self" Id="HousItem" />
<ChildItemMenu3 Caption="MES Third Solution" Url="PersonalAccidents.aspx" Target="_self" Id="PerAcItem" />
<ChildItemMenu3 Caption="MES Fourth Solution" Url="Protection.aspx" Target="_self" Id="ProtItem" />
<ChildItemMenu3 Caption="MES Fifth Solution" Url="Retirement.aspx" Target="_self" Id="RetItem" />
<ChildItemMenu3 Caption="MES Sixth Solution" Url="Small Commercial Business.aspx" Target="_self"
Id="SmallBizItem" />
<ChildItemMenu3 Caption="MES Seventh Solution" Url="Term Life.aspx" Target="_self" Id="TermItem" />
<ChildItemMenu3 Caption="Others" Url="Yachts.aspx" Target="_self" Id="YacItem" />
</ChildMenu3>
</Item3>
<Item4 Caption="Our Company" Target="" Url="" Id="ComItem">
<ChildMenu4 Id="ComMenu">
<ChildItemMenu4 Caption="Identity" Url="Identity.aspx" Target="_self" Id="IdItem" />
<ChildItemMenu4 Caption="Promise" Url="Promise.aspx" Target="_self" Id="PromItem" />
</ChildMenu4>
</Item4>
</Menu>
</MenuData>

any help appriciated

View 2 Replies View Related

Column Prefix Does Not Match

Oct 2, 2006

SQL 2000
I am testing a query for use in Crystal Reports. It was copied from an existing query with the necessary adjustments. The first part of it works correctly;

SELECT NA.*
into #cl_temp
FROM OLT.dbo.NACBTR NA
WHERE NA.CourseCode in ('RGF00001','RGF00002','RGF00005','RGF00006', 'RGF00038','RGF00039','RGF00040','RGF00041','RGF00042','RGF00043') And
NA.completedDate >= '01/01/2006' and
NOT EXISTS (SELECT * FROM hrdw.dbo.E_View EV WHERE NA.ssn = EV.ssn)

but when I add the second line;

select #cl_temp.*,
ISNULL((select 1 from #cl_temp where #cl_temp.coursecode = 'RGF00001'),0) as fire_yes into #oshasafety_temp

I receive the error message:
The column prefix '#cl_temp' does not match with a table name or alias name used in the query.

Any ideas?
Thanks,
Tom
btw, sql newbie.

View 5 Replies View Related

The Column Prefix 'MS1' Does Not Match With A ...

Aug 29, 2007

I have an Access database, that is in connection with sql server.
On my computer with access2007 i have no problems with viewing tables and stuff.

But on other computers with access2003 it gives an error when i use this query:

INSERT INTO [tbl_sap-staffel] ( ItemCode, CardCode, [Amount-0], [Price-0], [Amount-1], [Price-1], [Amount-2], [Price-2] )
SELECT [qry_sap-spp-0].ItemCode, [qry_sap-spp-0].CardCode, [qry_sap-spp-0].Amount, [qry_sap-spp-0].Price, [qry_sap-spp-1].Amount, [qry_sap-spp-1].Price, [qry_sap-spp-2].Amount, [qry_sap-spp-2].Price
FROM ([qry_sap-spp-0] LEFT JOIN [qry_sap-spp-1] ON ([qry_sap-spp-0].ItemCode = [qry_sap-spp-1].ItemCode) AND ([qry_sap-spp-0].CardCode = [qry_sap-spp-1].CardCode)) LEFT JOIN [qry_sap-spp-2] ON ([qry_sap-spp-1].ItemCode = [qry_sap-spp-2].ItemCode) AND ([qry_sap-spp-1].CardCode = [qry_sap-spp-2].CardCode);


It says:quote:
ODBC call failed
[Microsoft][ODBC SQL Server Driver][SQL Server]
The column prefix 'MS1' does not match with a table name or alias name used in the query.
The column prefix 'MS2' does not match with a table name or alias name used in the query.

And it will give this error 6 times.

I dont know what to do.
Can anybody help me?

View 2 Replies View Related

Match Field And Return To 1

May 14, 2007

hi..i'm new in sql progaming,i try to make make a query that in table field "match" return to "1"if no member record in another table and return to "0" if there is anyrecord member :extable member:member idA 12B 14Table Incoming.member note matchC bla..bla 1A bla..bla 0D bla..bla 1...... ....... .....can anyone help me please?D

View 3 Replies View Related

How To Match Grouped Rows In MS Sql

Apr 8, 2008

Hi,

Data in my table is loking like this.













InvID
ItemInputDtTime
SrNo
ItemId
Rate

Qty
GroupID

8252
07-04-2008 15:51
1
001138
9.99
1
1

8252
07-04-2008 15:51
2
000009
0.5
1
1

8252
07-04-2008 15:51
3
000016
1
1
1

8252
07-04-2008 15:52
4
000207
NULL
1
1

8252
07-04-2008 15:52
5
000203
NULL
1


1



8252


07-04-2008 15:52
6
001138
11.9
1
2

8252
07-04-2008 15:52
7
000016
1
1
2

8252
07-04-2008 15:52
8
000009
0.5
1
2

8252
07-04-2008 15:52
9
000207
NULL
1
2

8252
07-04-2008 15:52
10
000203
NULL
1
2

8252
07-04-2008 15:52
11
001138
11.9
1
3

8252
07-04-2008 15:52
12
000009
0.5
1
3

8252
07-04-2008 15:52
13
000008
0.5
1
3

8252
07-04-2008 15:53
14
001106
5
1
4

8252
07-04-2008 15:53
15
001000
10
1
5

8252
07-04-2008 15:54
16
001202
10
1
6

8252
07-04-2008 15:54
17
001117
13.9
1
7

8252
07-04-2008 15:54
18
001113
NULL
1
7

8252
07-04-2008 15:54
19
001117
13.9
1
8

8252
07-04-2008 15:54
20
001113
NULL
1
8

8252
07-04-2008 15:54
21
001117
13.9
1
9

8252
07-04-2008 15:54
22
001115
2
1
9




same colored items are grouped by GroupID. Each group contains ItemID, Qty and rate.
How can i compare IteamID, Qty and Rate of each group with other group's ItemID, Qty and rate?
OR
How can i get number of groups with same ItemID, Qty and rate?


All I need to do by T-SQL


Thanx

View 14 Replies View Related

Row Yielded No Match During Lookup

Sep 8, 2006

In SSIS. I am having trouble exporting records
that don't match from a lookup transformation. I get the following
error:

Row yielded no match during lookup.


I would really like to have a list of all records that did not match so
that I could send an email of those missing rows

Please give me solution with example

Thanks

View 9 Replies View Related

Row Yielded No Match During Lookup

Sep 14, 2006

I have configured a lookup transformation to 'redirect error' all no-matched rows to a text file using the flat file destination.

Now I want to send the same text file as an email.I Know email can be send using the send email task but i need to know where to place send email task and how to check whether flat file contains the error data.

Can we use the send email task on eventhandler and invoke the same in case of such error "row yielded no match during lookup" so that we can send the such non matching rows as an email.

Or else any other way to send an email after generating the text file ocntaining the non matching rows.



Please suggest using steps or example

View 4 Replies View Related

Match And Delete From Max(date)

Oct 8, 2007

I want to match dates from two tables, and I want to match the six latest dates. If I have a match a will delete it from one of the tables.
I will do a while loop and do a delete for every date that already exist. If ,for example, the third date not have a match the loop must do a break.
but if all six is a match they all will be deleted from one table.

how to check it?
max(date)
max(date) - 1
max(date) - 2
...
Some one ho know and understand the question?



View 4 Replies View Related

Query To Match Some Fields Out Of Many - HELP...

May 12, 2008

I have a table with 6 fields, and I will have all 6 parameters passed in - is there any way to write a query to give me rows based on matching ANY combination of 4 fields out of the 6 parameters passed in ? This is driving me crazy... short of doing an OR statement for all the different combinations - I have no idea how to do this....




This is what I have so far -

SELECT @nodeMachineType = nodeMachineType,
@nodePKID = NodePKID,
@biosVar = Bios,
@computerNameVar = ComputerName,
@diskVolumeVar = DiskVolume,
@guidVar = Guid,
@macAddressVar = MacAddress,
@motherboardVar = motherboard
FROM Nodes_Active
WHERE
case when Bios = @bios then 1 else 0 end +
case when ComputerName = @computerName then 1 else 0 end +
case when DiskVolume = @diskVolume then 1 else 0 end +
case when guid = @guid then 1 else 0 end +
case when macAddress = @macAddress then 1 else 0 end +
case when MotherBoard = @motherboard then 1 else 0 end >= 4

View 10 Replies View Related

DB Design :: Inner Join With No Match

Jun 16, 2015

I have a view where I am joining two very large tables.

I am joining where Brand and then Product match.

My problem is that there are quite a few rows where there is a Brand and Product in Table A but it is not in Table B and the other way around, Products in B that aren't in A and I need ALL of the products to show, not just the ones that are in both tables.

The field names are the same in each table, one is North America and the other is Europe. HOW do I write that?

SELECT
dbo.SummaryNA.Brand,
dbo.SummaryNA.Product,
dbo.SummaryEU.Brand AS Expr1,
dbo.SummaryEU.Product AS Expr2,
dbo.SummaryNA.Supplier,

[Code] ....

View 4 Replies View Related

The Column Prefix 'h' Does Not Match... Parses Ok

Aug 18, 2006

I get the error below from the following SQL. This SQL worked until I tried adding a third table "ship_to_salesrep". If I comment-out the third join and the last condition it works.

I have tried putting the "r" table in the FROM statement and still it does not work. Everything looks right to me -- what am I doing wrong?

Server: Msg 107, Level 16, State 2, Line 2
The column prefix 'h' does not match with a table name or alias name used in the query.


SELECT distinct
m.inv_mast_uid,
CONVERT(DECIMAL(10,4),0.00) as 'inv_cost',
CONVERT(DECIMAL(10,4),0.00) as 'oe_cost',
l.invoice_line_type, s.cost,
h.invoice_no, h.order_no, h.order_date, h.invoice_date, h.customer_id,
h.ship_to_id, h.ship2_name, h.ship2_address2, h.ship2_city, h.ship2_state, h.ship2_postal_code,
h.terms_desc, h.po_no, h.salesrep_id, h.salesrep_name, h.period, h.year_for_period,
h.ship_date, h.total_amount, h.amount_paid, h.terms_taken, h.allowed, h.paid_in_full_flag,
h.last_maintained_by, h.printed, h.printed_date, h.shipping_cost, h.invoice_reference_no,
h.invoice_adjustment_type, h.memo_amount, h.bad_debt_amount, h.invoice_class, h.period_fully_paid,
h.year_fully_paid, h.approved, h.other_charge_amount, h.tax_amount, h.original_document_type,
h.date_paid, h.print_flag, h.print_date, h.customer_id_number, h.date_created, h.date_last_modified,
h.consolidated, h.sold_to_ah_uid, h.sold_to_customer_id, h.invoice_batch_uid, h.sales_location_id,
h.source_type_cd,
l.qty_requested, l.qty_shipped, l.unit_of_measure, l.item_id, l.item_desc, l.unit_price,
l.extended_price, l.gl_revenue_account_no, l.gl_salse_tax_account_no, l.pricing_quantity, l.line_no,
l.sales_cost, l.commission_cost, l.other_cost, l.other_charge_item, l.exceptional_sales, l.pricing_unit,
l.invoice_line_uid, l.invoice_line_uid_parent
into jch1.dbo.sales_history_invoices
FROM
invoice_hdr h, invoice_line l
left join inv_mast m on l.item_id = m.item_id
left join inventory_supplier s on m.inv_mast_uid = s.inv_mast_uid
left join ship_to_salesrep r on h.ship_to_id = r.ship_to_id
WHERE
l.invoice_no = h.invoice_no and
h.invoice_date >= '2006-07-01' and
h.invoice_date < '2006-08-01' and
l.invoice_line_type = 0 and
m.inv_mast_uid is not NULL and
r.primary_salesrep_flag = 'Y';


Thanks,
Charles

View 3 Replies View Related

Function To Match Similar Words

Mar 30, 2007

Is there a function to match simalar words
say for example 'tom' will match 'tomy'

Thanks

View 4 Replies View Related

How To Select The Record That Doesn't Match Another One?

Aug 15, 2004

Hello, every one:

I ahve two tables A and B that have AddreesID each other. How to select AddressID from TableB that doesn't match AddressID in TableA? Thanks.

ZYT

View 5 Replies View Related

T-SQL (SS2K8) :: Add String WHERE Filter That Will Match Everything

Nov 19, 2014

I need to add a filter clause like
WHERE username = '%%%'

I know you will say 'why add a filter if you're not going to use it?' but I need to for a certain application which will use the parent query for child queries in which I select the specificity required for the child query's data set.I've tried '%*%' and '%_%' but always it returns nothing. I need the filter to exist yet not really filter.

View 6 Replies View Related







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