Query Dependent On Three Fields?
Oct 13, 2014
I am trying to do a select query like below...
SELECT INVENTORY_ITEM_TAB.ITEM_NO, INVENTORY_ITEM_TAB.DESCR, INVENTORY_ITEM_TAB.STATUS_FLG, INVENTORY_ITEM_TAB.PRICINGUOM,
INVENTORY_ITEM_TAB.PURCHUOM, INVENTORY_ITEM_TAB.ITEM_CATEGORY, INVENTORY_ITEM_TAB.ICINTERNALNOTES, INVENTORY_WHS.QTY_ON_HAND,
INVENTORY_WHS.QTY_ON_ORDER, INVENTORY_WHS.QTY_ALLOCATED
FROM INVENTORY_ITEM_TAB INNER JOIN
INVENTORY_WHS ON INVENTORY_ITEM_TAB.ITEM_NO = INVENTORY_WHS.ICWHSPCODE
WHERE (INVENTORY_ITEM_TAB.ITEM_CATEGORY = 'SS') AND QTY_ALLOCATED, QTY_ON_HAND, Qty_On_ORDER <> 0
ORDER BY INVENTORY_ITEM_TAB.DESCR
But I don't want to select records if all of these fields have a 0 in them - QTY_ALLOCATED and QTY_ON_HAND and QTY_ON_ORDER.
how to do this type of query. If any of those fields doesn't have a 0 then I would want to return it. I just don't want them if all three of those fields have 0.
View 3 Replies
ADVERTISEMENT
Jan 26, 2007
We have an interesting performance issue with a distributed query. When run by a system-administrator account, the remote computer returns the requested row set. It does the same thing for small row counts (<=7) for user accounts. On larger row counts, however, the user account returns the ENTIRE rowset from the remote table, and performs the restrict operation locally. Performance-wise, this results in a difference between 11 seconds and 12 MINUTES. I'm assuming it's some sort of security issue in DTC, but the remote server is on Windows Server 2000, and there's no security button for DTC under Component Services.
Suggestions?
The query in question is:
EXEC ("INSERT INTO #XML (vin, ws_xml, lang_id)
SELECT slo.vin,
br.xml,
br.lang_id
FROM #SALE_LINEUP_ORDER slo
INNER JOIN RemoteServer.mydatabase.dbo.build_record br ON slo.vin = br.vin
INNER JOIN #LANGUAGE_IDS li ON br.lang_id = li.lang_id")
View 4 Replies
View Related
Dec 4, 2004
I have three columns (CWOC, C, and PWC). both CWOC and PWC are dependent on the value in C, yet C is dependent on the value in PWC.
Basically:
C = PWC * BR
PWC = MR - CWOC
CWOC = MC - C
In setting up these columns in the database how do I set up columns like these that are so dependent on each other?
This was set up previously in Excel using iteration, but now I'm recreating the application in VB.net and SQL Server.
Thanks for your suggestions.
View 4 Replies
View Related
Jun 9, 2008
Hi,
I remember seeing a fancy query that checked for multiple fields in a table (I think using a select statement in the where clause but not sure), but can't remember how to do it... here is what I want to do (and maybe there is a much easier way). Thanks!
Table1
id item color
1 shoe red
2 shoe blue
3 coat green
4 coat black
Table2
item color
shoe red
coat green
I want everything in Table1 where item and color are not a match.
So my results should be:
2 shoe blue
4 coat black
I'm sorry if this is a dumb question... it's been that kind of a day!
Thanks!
View 1 Replies
View Related
Jun 15, 2004
I have a ABC service on a server1 that is dependent on MSSQLSERVER service running on server2. Does anyone know how to setup the service ABC on server1 to have a dependancy on MSSQLSERVER running on server2?
Thanks,
jgs
View 3 Replies
View Related
Aug 20, 2013
I am trying to query only the Max date dependant on quantity
Create Table dbo.TestParts
(Part char(30), Desc1 char(50), Desc2 char(50));
Create Table dbo.TestStructure
(Model char(30), Part char(30), EDATE smalldatetime, QtyPer float);
GO
Insert INTO dbo.TestParts Values('101111','Widget A', 'Batteries Not Included'),
[Code] ....
Looking for a return of:
PART Description EDATE QtyPer
101112 Widget B ..... 2012-12-03 3
101113 Widget C ..... 2012-12-03 5
101114 Widget D ..... 2012-12-01 1
Widget A should not show because the last date the qty was changed to Zero
Closest that I have come..........(which is pulling the part 101111 which should be incorrect)
Select ts.Part, RTRIM(tp.Desc1) + ' ' + RTRIM(tp.Desc2) as Description,
ts.EDATE, ts.QtyPer
FROM testing.dbo.TestStructure ts
Inner Join (Select Part,MAX(EDATE) as Date
FROM testing.dbo.TestStructure
WHERE QTYPER <> '0'
[Code] .....
View 3 Replies
View Related
Jul 20, 2005
Suppose I have the following table:col1 col2hammet jonesjlo afflectafflect armandwills snoptarmand hammetjones smithIf someone choses armand, then I'd like to returnamand hammet jones smithThe first selection goes over to the second column, gets that valueand locates it back in column one and returns column 2 and so on.One way of doing it is to set up a separate query for each one andthen construct a new query to get them all.I'm thinking there's a more elegant way to do this. Any suggestionswould be appreciated.-David
View 2 Replies
View Related
Nov 15, 2007
Hi,
I have a problem with a report.
in this report 1 use 2 combo boxes. The first with my organisation on different levels.
And in the second a value for every level and all.
Now i want to filter the organisation box with the second combo box.
For the first time it works but if i change after that the box it doesn't work.
I use SQL Reporting Services 2005
View 4 Replies
View Related
May 10, 2007
I was just analysing the security which can be given to different users to access respective databases. So i tried, on my local server, to deny permission to myself to access Model database. After this i am not able to connect to my local server at all. Error: Permission denied. I am using windows authentication mode.
I have also deleted the local server registration, and re-registered it, but still the condition is same. Do i need to uninstall SQL Server completly to get rid of this prob.? I also registered a new data server, and there every thing is going fine. So now what do i do to get connected to my local server.
View 5 Replies
View Related
Jul 23, 2007
Hi,
I have a report which uses a parameter called "Interval" with possible string values of "Daily", "Weekly", and "Monthly" and I want these values to set the default values for my StartDate and EndDate parameters. However, the default value remains static. And I have tried all of the solutions found in this forum. Any suggestions?
I am using SSRS 2005. And I have tried using an iif statement for StartDate, also tried Property->Custom Code, and also tried using stored procedure to populate Start and End Dates, but none of these provide dynamic parameter setting like I was hoping for.
And I think this should work because I know that if it is not a date that it works correctly. For example if I have one parameter called country and a subsequent one named state, every time I change the country it will let me re-pick the state. But this is not working correctly here.
Any ideas on what I am doing wrong or how to fix this?
View 1 Replies
View Related
Jan 9, 2007
I am not able to delete a row due to the presence of its parent table. There is no circular relationship and the child has no dependencies when I verify the Delete trigger. Any advice? See error msg below:
Msg 30010, Level 16, State 1, Procedure tD_My_child_table_name, Line 43
Cannot DELETE last My_child_table_name CI because My_parent_table_name exists.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
View 4 Replies
View Related
Feb 18, 2003
Hi there,
I'd need to generate multiple recordsets within a MS SQL stored procedure. The thing is these recordsets depend on each previous one (the first one obviously has parameters). How can I make this work? I can't create views with SPs. Thank you very, very much.
Martin
View 4 Replies
View Related
Sep 22, 2005
I want to execute multiple SQL Server scheduled jobs using OSQL, but I only want to execute a job if the previous job completed. Is this possible? So far I am unable to prevent all jobs from starting before any have completed.
OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber1'" -o usp_start_job.out
IF ERRORLEVEL 1 GOTO ERROR
OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber2'" -o usp_start_job.out
IF ERRORLEVEL 1 GOTO ERROR
OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber3'" -o usp_start_job.out
IF ERRORLEVEL 1 GOTO ERROR
GOTO EXIT
:ERROR
ECHO *** ERROR *** Check Log File
:EXIT
*** JOB COMPLETED ***
Dave
View 5 Replies
View Related
Oct 31, 2007
Excuse my illiteracy in this subject, I just learned about this yesterday, I managed to create a report by using business intelligence report wizard, and when I preview it seems that I can view the fields that I wanted to view.
But in addition to that I also want to add hyperlinks to some of the fields, because I am planning to embed the whole report in a web page. For example if I go on the field that I want to add hyperlink and right click->properties->navigation tab->Jump to url, I thought that I can handle it. Actuall it works fine if I link it to http://www.microsoft.com but let's assum e the name of the field is ID, and you want to link to http://somewebsite/somepage.aspx?id= (id in that cell), how can I achieve this parametric behavior, actually I tried right click cell->properties->navigation tab->Jump to url, and filled url text box http://somewebsite/somepage.aspx?id=&(concatenation operator)=Fields!ID.Value but what happens is instead of evaluating the value of ID field, it takes expression as text as text and that's why it links to totally wrong url. Is there a way to get around this problem.
And can somebody suggest me a detailed tutorial on programmatically using reports in .net web applications using c#.
If somebody can help me I'd be really glad.
Erinc
View 3 Replies
View Related
May 4, 2008
Hello
I have following data and environement is SQL Server 2000.
Hello
I have following data and environement is SQL Server 2000.
MemberID
Dependent_Empid
DependentName
Benefit
1
100
Kalim
100
2
100
Brian
100
3
100
Swadia
200
4
101
Allan
200
5
101
Cynthia
200
6
103
Noreen
200
7
103
Larry
300
8
103
Mike
300
and I am desired for the following output but I dont want to use function. Can anybody help me for script for the desired output. I will be much thankful.
Dependent_Empid
Members
100
Kalim 100, Brian 100, Swadia 200
101
Allan 200, Cynthia 200
103
Noreen 200, Larry 300, Mike 300
View 9 Replies
View Related
Jan 4, 2001
Greetings all:
I am looking for a way to get detailed information about each created table (regardless of permanancy) in a stored procedure, similar to the information one received from using sp_help on an individual table.
Does anyone know of such an animal?
Thank you, and Happiest of New Years,
Jack Cole
Magellan Healthcare, Inc.
View 1 Replies
View Related
Mar 10, 2003
I have a table with one row for each test a user has taken, with columns for userid, score, and test date. I have a query that gets the highest score and the date of the latest test for each user. Easy. But how can I also get the score achieved on the latest test for each agent?
Thanks,
John
(By the way, I've been looking for a good SQL mailing list to ask this question and have been unsuccessful. If there's a better forum than this for this type of question, please let me know).
View 6 Replies
View Related
Sep 14, 2004
I wonder if anyone can help me -
I am trying to write a little application which recompiles any views dependent on a given view.
I have a script which will identify any views containing a given text string, but I now need a way to force SQL Server to refresh its cache info (or recompile) the views.
I have to admit that I am unclear on exactly how the caching works; i have tried using sp_recompile, but this does not seem to do the job.
Does anyone have any ideas?
View 1 Replies
View Related
Jul 20, 2005
Hi,DDL:-- create table #task (taskID int identity(1,1) primary key, taskNamevarchar(25) unique, taskCompleteDate dateTime, taskComplete bitdefault(0));/*Business Rules:a) if taskCompleteDate is NULL (like default) then, taskComplete maynot be set to 1 (attempt to update it to 1 would fail);b) else automatically set taskComplete = 1*/I was thinking using CHECK constraint (mutual constraint if possible),along the following line:CHECK (if taskCompleteDate is null set taskComplete=0 else settaskComplete=1)Hmm, your thought?Thanks.
View 5 Replies
View Related
May 23, 2007
Hi all,
I have a data mining model that has a binary dependent variable (let's say 'Yes' and 'No'). I noticed that the model is very sensitive to the distribution of the binary dependent variable. I could not come up with an explaination. I know the model is not over/under fits and multicollinearity is not an issue. And I have enough data size to train the model.
I would expect some sensitivity but not to this extend:
Yes % in training data
Yes % in the prediction results
with DT
with LR
with NN
30.0%
6.0%
11.0%
14.0%
35.0%
12.0%
15.0%
24.0%
37.5%
20.0%
23.0%
27.0%
40.0%
28.0%
33.0%
36.0%
50.0%
43.0%
52.0%
61.0%
DT - Decision Trees
LR - Logistic Regression
NN - Neural Networks
And to make things more interesting, although the precision (% of correct 'Yes's ), true positive ratio and false positive ratio is changing; the overall accuracy (and therefore the score in the mining accuracy chart) remains almost same.
If anyone has an idea it would be highly appreciated.
Thanks in advance, Afsin
View 3 Replies
View Related
Dec 30, 1999
Hi There,
Here I have small problem with default and rule.
After create rule or default then we will bind that to any table.
I bounded that rule to some of tables.If i want see the list of objects dependent on this rule or default
how to see.I know sp_depends stored procedure will show the all dependent
objetcs but i could not get through that.I found in help it says sp_depends works
for all objects in the database like table,view and so on.But default and rule also
objects i could not get it.Please let me know on this if you can give this answer as early as possible.
I am very thanks to you.Please don't specify SQL-DMO Listboundedcolumns function.....
Thanks
Ramki
View 1 Replies
View Related
Feb 27, 2015
I have two procedures Sp1 and Sp2. The Sp1 is calling from Sp2. If there come new parameters or removed from the first procedure Sp1 then it will effect the Sp2 and its execution will break if required changes not done. So I want that when any changes making in SP1 then i need alert about the Sp2 changes.
View 3 Replies
View Related
Jan 31, 2007
Hi Frens,
Could anybody tell me how can I find all the database objects that are used in /referenced by/called by/dependent on a given stored procedure? In other words, I am looking for something like a stored procedure or a function that takes as input the name of a stored procedure and outputs all the names of the tables, functions, procedures, cursors and etc. database objects that are used in that procedure. Could you please give me suggestions or possible answers for this?
Thanks a lot for your time.
Regards,
-Ram.
View 11 Replies
View Related
Oct 25, 2006
I am having trouble figuring out how to complete this any help is appreiciated.
I have Mon , Tues , Weds , Thurs , Fri as bit fields I need to write a query for a report to see if the value is true, and display M, T, W, Th, F in a column Days if they are scheduled that day
example:
Mon and Wed I got on the bus the checkboxes are set to True, I want one column in the report Days: to display M, W
View 11 Replies
View Related
Aug 6, 2004
Hi i am trying to get the following result:
CALCULATE THE CURRENT NUMBER OF SHARES on base of number of shares bought and number of shares sold
so the logic is
int total
if transactiontype is "buy", total = total + amount bought
if transactiontype is sell, total = total-amount sold
plus must be grouped by share
base data are stored in 1 table as
share name transaction type share amount
tesco buy 200
tesco sell 50
sounds rather easy does it not?
well sql syntax must be very foreign to me as i do not manage it
I tried a few options:
1 to write a database function as above and then call it from sproc
but i need to use group by share name or id so it calculates for each share and it will not let me to use function in select with group by
2 i tried to use if within sproc but i get complain incorrect syntax (I use MSDE database accessed via Visual) - I think it suppose to be possible but it throws me out with the most simple if or = is there some special syntax?
3 I created 2 views: 1 view: all sold shares, 2nd view: all bought shares and their bought/sold quantities
as in
select share name, sum(sharequantity)
where transaction = sell
from table
group by share name
and the same for purchases
then I did union of these 2 views with boughtquantity - soldquantity what worked but it only showed me shares which were sold, if share was never sold it was not shown I guess due to union
so I am still stuck!
View 7 Replies
View Related
Apr 18, 2008
I am attempting to use a CLR Function to perform an update operation, since a SQL UDF will not allow this within the function. For the POC, I hard-coded the db connection string and setup the assembly to use EXTERNAL_ACCESS. This worked fine and the update operation was running properly via the CLR Function. However, I now want to make the code run in the current db context without plugging in a connection string. So, I've applied "context connection=true" instead of the explicit db string. When I do this, I am receiving the following error:
A .NET Framework error occurred during execution of user defined routine or aggregate
'MyTestCLRUDF':
System.Data.SqlClient.SqlException: Invalid use of side-effecting or time-dependent operator in 'UPDATE' within a function.
System.Data.SqlClient.SqlException:
etc.
I don't understand why simply swapping the connection string would cause this issue. I assume this is somehow related to permissions, and I am missing something. I've tried using each of the three permission levels (external, safe, and unsafe), and no luck.
Any help is appreciated! Here's the code:
public static bool MyTestCLRUDF(Guid myID)
{
string connectionString = "context connection=true";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand("MySprocName", connection);
command.CommandType = CommandType.StoredProcedure;
SqlParameter parameter = new SqlParameter("@ID", myID);
command.Parameters.Add(parameter);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
return true;
}
View 6 Replies
View Related
Oct 12, 2015
I am trying to move data from 8 different tables that are dependent on each other through foreign key relationship.
Basically they have millions of rows in each table and they have data for the past 5 years. I want to move data for the past 120 days and move it to 8 new tables in the same database. So I created the new tables along with their relationships. Now I need to move in the order (parent table first).
The child table has 50million rows data to move
The intermediate tables have 10 mil 10mil 10mil and 40 mil 50mil and 20 mil rows to move
The parent table has 10 mil rows to move
if I choose to move this data through an SSIS package what is the best way? Or is there a better way to move this data faster?
I will be doing this move only once. After that I have maintenance purge jobs that will cleanup data on a daily basis.
View 7 Replies
View Related
Aug 9, 2004
Hello all,
How can i write a sql statement that can give me the name of the fileds in a specified table.
Eg: if i have a table name "table1" , which has the fields "field1", "field2",........ How can i get these fields names using sql query.
any help.
View 25 Replies
View Related
Jun 6, 2001
how can i write a SQL query that will pull all records that are equal to NULL??
View 1 Replies
View Related
Sep 19, 2007
Can anyone please help me to find the diffence between two fields.
Field1 - 16:12:27:123
Field2 - 16:12:36:750
I need to find the difference between field2 and field1??
View 5 Replies
View Related
Dec 12, 2014
I have 2 tables.
Clients
Contacts (multiple contacts for one client)
I'm trying to do a search that pulls a contacts where the search matches either the First Name, last Name or the clients name. If it does return clients, I'd like it to also return all the contacts associated with it.
I have two problems:
1. The query is not bringing up a lot of clients. In many cases a letter brings nothing back. Like G and H even though A and B return results.
2. If it finds a client it only returns one contact. I'd like it to return all contacts for the client.
Here's my query:
SELECT addressbook.clientid, clients.clientname, addressbook.addressid, addressbook.fname, addressbook.lname FROM clients, addressbook where clients.clientid = addressbook.addressid
AND (addressbook.fname LIKE
'".strtoupper($_GET['txtsearch'])."%' OR addressbook.lname LIKE
'".strtoupper($_GET['txtsearch'])."%' OR clients.clientname LIKE
'" . strtoupper($_GET['txtsearch'])."%')
View 1 Replies
View Related
Apr 26, 2006
I am trying to join two fields in a query in SQL 2000. For example.
Update myTable SET field_1 = @field_1_value , field_2 = @field_2_value, field_3 = @field_1_value + ' x ' + field_2_value
Is this even possible.
I want the user to input values for fields 1 and 2, then in the background combine the two and insert that value in field 3.
Thanks in advance,
Scotty_C
View 7 Replies
View Related
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
