Query To Find A Missing Number
Hello,
I need to write a query to find out a set of missing number in a given
sequence.
Eg : a Column in some table has the following data
Col1
1
2
3
4
5
6
8
9
10
Here I need to write a query to find out that number 7 is missing in the
given sequence.
One possible solution is by using any loop. But I am looking out if the same
can be achieved using any query.
Thanks in advance.
Regards,
Mahesh
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
How To Find The Missing Records ?
Hi All I know this a simple question, need help from you. I have 2 tables ItemMaster with primary key clustered (vehmake, itemno), OrderItems has foreign key (vehmake, itemno) refers to ItemMaster. My question is how to findout the combination of missing records in the master. I tried this and it does not bring any values. select * from OrderItems where vehmake not in (select vehmake from ItemMaster) and itemno not in (select itemno from ItemMaster) Thank in Advance Jaya
View Replies !
View Related
Find Missing Data From Table
Here is an issue that has me stumped for the past few days. I have atable called MerchTran. Among various columns, the relevant columns forthis issue are:FileDate datetime, SourceTable varchar(25)SQL statement:SELECT DISTINCTFileDate, SourceTableFROMMerchTranORDER BYFileDate, SourceTableData looks like this:FileDate DataSource-----------------------------------2005-02-13 00:00:00.000S12005-02-13 00:00:00.000S22005-02-13 00:00:00.000S32005-02-14 00:00:00.000S12005-02-14 00:00:00.000S22005-02-14 00:00:00.000S32005-02-15 00:00:00.000S22005-02-15 00:00:00.000S32005-02-16 00:00:00.000S12005-02-16 00:00:00.000S22005-02-16 00:00:00.000S32005-02-17 00:00:00.000S12005-02-17 00:00:00.000S22005-02-18 00:00:00.000S12005-02-18 00:00:00.000S22005-02-18 00:00:00.000S32005-02-19 00:00:00.000S12005-02-19 00:00:00.000S3We run a daily process that inserts data in to this table everyday forall 3 sources S1, S2, S3Notice how some data is missing indicating the import process for thatparticular source failed.Example: Missing record2005-02-15 00:00:00.000S12005-02-17 00:00:00.000S32005-02-19 00:00:00.000S2Can someone please help me with a SQL Statement that will return me the3 missing records as above.Thanks in advance for all your help!DBA in distress!Vishal
View Replies !
View Related
Find Missing/deleted Records?
I have 2 tables say table1 and table2 with the same structure. Each recordis identified by a field 'SerialNo'. Now there should be a total of 500000records in both tables with serialno from 1 to 500000. Either a record is intable1 or table2. I want to find records (or SerialNo's) that are inneither table (if deleted by accident etc). What would be the sql query?I'm using SQL 6.5thx
View Replies !
View Related
Find Missing Sequences By Category
I have to identify missing records from the example below. Category BatchNo TransactionNo CAT1 1 1 CAT1 1 2 CAT1 2 3 CAT1 2 4 CAT1 2 5 CAT1 3 6 CAT1 3 7 CAT1 3 8 CAT1 5 12 CAT1 5 13 CAT1 5 14 CAT1 5 15 CAT1 7 18 CAT2 1 1 CAT2 1 2 CAT2 3 6 CAT2 3 7 CAT2 3 8 CAT2 3 9 CAT2 4 10 CAT2 4 11 CAT2 4 12 CAT2 6 14 I need a script that will identify missing records as below Category BatchNo CAT1 4 CAT1 6 CAT2 2 CAT2 5 I do not need to know that CAT1 8 and CAT2 7 are not there as they potentially have not been inserted yet. I idealy want a nice clean SQL statement and do not particually want to insert new table's or triggers although views i Can deal with to an extent. Considerations up to 50,000 records added per day!!! Only need script to run once a day and I have insert dates to help me. Only 12 Categorys Batch numbers always start at 1 for different categorys
View Replies !
View Related
RAID Set For Database XXX Is Missing Member Number 1.
I saw a previous request for help, but I did not see a solution (other than deleting and re-initializing the dump. I'm copying a database from one server to another and I repetedly get this error when I try to restore from the .bak file I create on backup. The host machine for the database is a workstation (no RAID). The Destination does have RAID, but is funtioning properly (according to the SysOps and in testing file copies). I've tried copying the .bak file to several different drives several different times and I get the same error. I also tried to back-up a database on the Destination server and restore the .bak file to a newly created database and I get the same error. Anyone solve this problem?
View Replies !
View Related
Comparing Data In Two Tables To Find Missing Records
I have two tables of book information. One that has descriptions of thebook in it, and the isbn, and the other that has the book title,inventory data, prices, the isbn.Because of some techncal constraints I won't get into now, I can'tcombine them both into one table. No problem. Things are going fine aslong as there is a description in the one table to corrispond to theisbn and other data in the other table.However, about half of the products are not yet entered into thedescrition table. I'd like to run a sql query that pulls up all theisbns that don't exist in the other. In other words, I'd like to get aquery that tells me exactly which isbns do not yet have descrition datain them. I know there is some sql that says to search from one filewhere the number does not exist in the other, but it slips my mind. Cansomeone help me on this please?Thank you!Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View Replies !
View Related
How To Find Missing Records From Tables Involving Composite Primary Keys
Table 1 Code Quarter 500002 26 500002 27 500002 28 500002 28.5 500002 29 Table 2 Code Qtr 500002 26 500002 27 I have these two identical tables with the columns CODE & Qtr being COMPOSITE PRIMARY KEYS Can anybody help me with how to compare the two tables to find the records not present in Table 2 That is i need this result Code Quarter 500002 28 500002 28.5 500002 29 I have come up with this solution select scrip_cd,Qtr,scrip_cd+Qtr from Table1 where scrip_cd+Qtr not in (select scrip_cd+qtr as 'con' from Table2) i need to know if there is some other way of doing the same Thanks in Advance Jacx
View Replies !
View Related
Find Highest Number
hello all, i'm not new to SQL but i cant seem to get this right: hope some one will: how to find highest unique number of a certain column(val)for a specific name(name is in INPUT parameter) i.e : id | name | val --------------- 1 | name1 | 2.7 2 | name1 | 3.5 3 | name1 | 3.5 4 | name1 | 3.5 5 | name1 | 1.3 6 | name2 | 3.1 7 | name2 | 3.1 8 | name2 | 2.9 requested result: if input param(name)=name1 result: 1 | name1 | 2.7 if input param(name)=name2 result: 8 | name2 | 2.9 hope some one can write the sql cmd for this i'll be grateful ! thank you!
View Replies !
View Related
Find Number Of Decimals
Can someone suggest a FAST way to select Currency values where the number has more than X decimal places? There are zillions of rows so looping in code is not the preferred solution. (a bug forgot to round) Thanks for your help
View Replies !
View Related
Find First Free Number
I have a table Col1 Col2 1 1000 2 1001 4 1003 5 1004 7 1006 I want to find the first free number from first column. Now It should return 3. After inserting a row with col1 = 3 it should return 6 and after inserting a row with col1 = 6 it should return 8. Is it posible ?
View Replies !
View Related
SP To Find A Gap In A Number Field
I have a part number field which contains whole numbers. (1 - 5000+) however there are gaps in the numbers e.g. Part Number 3876 maybe missing. The table has new parts inserted from a VB Client. Im trying to write a stored procedure where when a user trys to run an update without entering a part number the first missing part number is found. If they do provide a part number then their number is committed as long as it is unique. I've tried using cursors but am not sure on the best approach. Any suggestions? Thanks in advance Alex
View Replies !
View Related
Find Max Serial Number From 2 Tables
I need a query to find max serial number by comparing two different tables. Here is my requirementI am having two tables named Table1 and Table2. Each tables having more than 30,000,000 records.I want a simple query to find Max srno from two tables.For exampleIf Table1 max is 245 where partno=2 and ano=2and Table2 max is 343 where partno=2 and ano=2Then 343 is max serial noIf Table1 max is 435 where partno=2 and ano=2and Table2 max is 34 where partno=2 and ano=2Then 435 is max serial noI used this query but its taking more time select max(v.MaxSrNo) from ((select max(MaxSrNo) as MaxSrNo from Table1 where partno=@partno and ano=@ano)union all (select max(MaxSrNo) from Table2 where partno=@partno and ano=@ano)) as v Pls give me a simple query to find max srno.
View Replies !
View Related
How To Find The Number Of Rows In A Table
I try to find the number of rows in a table with this commands: CountRec = New SqlParameterCountRec.ParameterName = "@countrec"CountRec.SqlDbType = SqlDbType.IntCountRec.Value = 0MyCommand = New Data.SqlClient.SqlCommand()MyCommand.CommandText = "select count(*) as @countrec from Customer;"MyCommand.CommandType = Data.CommandType.TextMyCommand.Connection = MyConnectionMyCommand.Parameters.Add(CountRec)MyCommand.Connection.Open()MyReader = MyCommand.ExecuteReaderiRecordCount = CountRec.Value This is the result: Incorrect syntax near '@countrec'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '@countrec'.Source Error: Line 39: Line 40: MyCommand.Connection.Open() Line 41: MyReader = MyCommand.ExecuteReader Line 42: iRecordCount = CountRec.Value Line 43: Source File: E:DevelopWebASPwebAccessTimberSalesUserEntry.aspx.vb Line: 41 What to do? I need a complete example to see how it works. Thanks...
View Replies !
View Related
To Find The Number Of Occurences Of A Particular Character In A String
Hi all, @str varchar(500) select @str = 'abcd,efgh,i,jklmn,opsqrtuv' For the above string, which string function can be used to find the number of occurences of a particular character, for example, ','? For this example, the answer should be 4. Is there any built-in function in SQL to do this? Thanks in advance, -Praveena
View Replies !
View Related
How To Find Number Of Data Packets Sent In A Transaction
I thought statistics io contained that info... Code: set statistics io on --do transaction here ...but it doesn't. I'm running some comparison tests in Query analyzer and thought there was a way to output the number of packets transmitted during a particular transaction. Selecting @@Pack_Sent isn't any help since it returns the total number of packets sent since the server was last started, I need the total number of packets transmitted local to a single transaction. Any help is appreciated, thx.
View Replies !
View Related
How To Find The SQL Server License Number On Registry
I'm trying to find the SQL Server License Number used to install the software on the server. Does the installation program stores the information on the Registry or not? Looking through registry I found the ProductCode but I'm not sure if that is the valid License Number ... It does looks more like the unistall code ... Thanks for your help wamregua
View Replies !
View Related
Find Logic Flaw, Order Number Generator
This procedure has been returning duplicate numbers. (Tested with scripts that called this proc and put value in a table.) How can it return duplicates? Does the transaction Begin/Commit not guarantee transactional consistency? CREATE PROCEDURE sp_UpdateOrderNumber @customer int AS DECLARE @NewOrderId int, @nSQLError int, @nRowCount int BEGIN TRAN UPDATE CUSTOMERS SET ORDER_NUMBER=ORDER_NUMBER + 1 WHERE COMPANY_ID=@customer SELECT@nSQLError = @@error, @nRowCount = @@rowcount If @nSQLError != 0 OR @nRowCount != 1 /* Check for Errors */ Begin Rollback Tran Return -999 End SELECTORDER_NUMBER FROMCUSTOMERS WHERECOMPANY_ID=@customer SELECT@nSQLError = @@error, @nRowCount = @@rowcount If @nSQLError != 0 OR @nRowCount != 1 /* Check for Errors */ Begin Rollback Tran Return -998 End COMMIT TRAN
View Replies !
View Related
SQL Query: Missing Field Value
Suppose in a table, there is some data in a field: 1, 2, 3, 5, 6, 8, 9. I need a sql query which will list the missing numbers: 4,7 (Missing digits) Query: Select * From table1 field ------ 1 2 3 5 6 8 9 Expected query:?? filed1 ------ 4 7 Please help me to get a query which will provide my expected data. Thanks. /Fakhrul(mfhossain@gmail.com)
View Replies !
View Related
Query For Getting Missing Dates In Months
I have a table like FK_ID, Value, Date (here FK_ID is foreign key)this table getting updated frequently by daily bases that means one record per one day(For example in January month it has maximum 31 records or minimum 0 records, in February it has maximum 28 or 29 or minimum 0 records, based on calender year)I need to query this table to get missing dates in between particular monthsfor example for one FK_ID has only 25 records in Jan 2008 month and in Feb 2008 it has 10 records , so i need to get those missing 6 dates from JAN month and 18 dates from FEB monthhow can i query this
View Replies !
View Related
Query To Get Range Of Values Missing
I have two columns, where I have the start and stop numbers (and each of them ordered asc). I would like to get a query that will tell me the missing range. For example, after the first row, the second row is now 2617 and 3775. However, I would like to know the missing values, i.e. 2297 for start and 2616 for stop and so on as we go down the series. Thanks in advance to any help provided! StartStop --------- 20452296 26173775 568936948 3727084237 84409178779 179013179995 180278259121 259292306409 307617366511
View Replies !
View Related
SSIS Missing Records From Query
We have an SSIS package which runs regularly throughout the day, on 15 minute intervals. This package is moving data between two SQL Server instances, performing some simple identity mapping along the way. The primary source table is large, and we want to move only those records which have changed since the last time the package ran, so we use VersionDatestamps in the table, and pick up the dataset to be transferred by using the pacakge execution time, and the (previously recorded by the package) last-run time. The problem we are having, is that the initial dataset picked up by SSIS is often missing records. The missing records are clearly within the time window that the package queried for, though they are near the boundary (within a minute, of the boundary, but as much as 30 seconds away) and typically all have an identical version datestamp to each other, within a single execution of the package. At first, we thought this was an issue with date precision, but we've both truncated the dates, and even expanded the time window, and we still experience the same phenomenon. The stored procedure which is responsible for updating the records in question, runs as a previous step to the SSIS package, within a single SQL Server Agent Job. Has anyone experienced anything similar, or have some recommendation on how to track the source of this down?
View Replies !
View Related
Syntax Error (missing Operator) In Query Expression '?
I'm getting the error listed above. To create this I used the wizard in VS2005 for the datagrid. The delete works but not the updating. I can't seem to find what's wrong. In the update parameters I've removed eventID and added it, neither works. Delete does work. Here's the code: <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="ds1" DataKeyNames="eventID"><asp:CommandField ButtonType="Button" ShowEditButton="True" ShowDeleteButton="True" /><asp:BoundField DataField="eventID" HeaderText="ID" SortExpression="eventID" ReadOnly="True" /><asp:BoundField DataField="trainer" HeaderText="trainer" SortExpression="trainer" /><asp:BoundField DataField="employeeNumber" HeaderText="Emp#" SortExpression="employeeNumber" /><asp:BoundField DataField="area" HeaderText="Area" SortExpression="area" /><asp:TemplateField HeaderText="Training Date" SortExpression="trainingDate"><EditItemTemplate><asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("trainingDate", "{0:M/dd/yy}") %>'></asp:TextBox></EditItemTemplate><ItemTemplate><asp:Label ID="Label2" runat="server" Text='<%# Bind("trainingDate", "{0:M/dd/yy}") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:CheckBoxField DataField="additionalTrainerExpected" HeaderText="Addl Trainer Expected" SortExpression="additionalTrainerExpected" ><asp:TemplateField HeaderText="Ext Trainer" SortExpression="extendedTrainer"><EditItemTemplate><asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Bind("extendedTrainer") %>' /></EditItemTemplate><ItemTemplate><asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Bind("extendedTrainer") %>' Enabled="false" /></ItemTemplate><HeaderStyle Font-Names="Verdana" Font-Size="Small" /><ItemStyle Font-Bold="False" HorizontalAlign="Center" /></asp:TemplateField><asp:TemplateField HeaderText="NonExt Trainer" SortExpression="nonextendedTrainer"><EditItemTemplate><asp:CheckBox ID="CheckBox2" runat="server" Checked='<%# Bind("nonextendedTrainer") %>' /></EditItemTemplate><ItemTemplate><asp:CheckBox ID="CheckBox2" runat="server" Checked='<%# Bind("nonextendedTrainer") %>' Enabled="false" /></ItemTemplate><HeaderStyle Font-Names="Verdana" Font-Size="Small" /><ItemStyle HorizontalAlign="Center" /></asp:TemplateField><asp:SqlDataSource ID="ds1" runat="server" ConnectionString="<%$ ConnectionStrings:TrainingClassTrackingConnectionString %>"ProviderName="<%$ ConnectionStrings:TrainingClassTrackingConnectionString.ProviderName %>" UpdateCommand="UPDATE [trainingLog] SET [trainer] = ?, [employeeNumber] = ?, [additionalTrainerExpected] = ?, [extendedTrainer] = ?, [nonextendedTrainer] = ?, [area] = ?, [trainingDate] = ? FROM [trainingLog] WHERE [eventID] = ?"> <UpdateParameters><asp:Parameter Name="eventID" Type="Int32" /><asp:Parameter Name="trainer" Type="String" /><asp:Parameter Name="employeeNumber" Type="String" /><asp:Parameter Name="additionalTrainerExpected" Type="Boolean" /><asp:Parameter Name="extendedTrainer" Type="Boolean" /><asp:Parameter Name="nonextendedTrainer" Type="Boolean" /><asp:Parameter Name="area" Type="String" /><asp:Parameter Name="trainingDate" Type="DateTime" /></UpdateParameters> <DeleteParameters><asp:Parameter Name="eventID" Type="Int32" /></DeleteParameters>
View Replies !
View Related
Syntax Error (missing Operator) In Query Expression
Hi, Please could someone assist - the above error occurs. This is my code: Protected Sub btnReport_Click(ByVal sender As Object, ByVal e As System.EventArgs) ' Response.Redirect("CrystalreportTEST_Print.aspx?C_id=" & ddlCompany.SelectedValue) Dim myConnection As New OleDbConnection(connString) Dim Str As String = "SELECT clientid,company FROM Client WHERE company =" & ddlCompany.SelectedItem.Text Dim cmd As New OleDbCommand(Str, myConnection) Dim ds As New DataSet Dim da As New OleDbDataAdapter(cmd) da.Fill(ds) Label7.Text = ds.Tables(0).Rows(0).Item("clientid") ' Response.Redirect("CrystalreportTEST_Print.aspx?C_id=" & ds.Tables(0).Rows(0).Item("clientid")) End Sub Thank you in advance
View Replies !
View Related
Filters && Parameters Pane Missing In MDX Query Designer
I'm developing my first Analysis Server report in SRS. Everything was going well when I discovered the filters & parameters pane was missing in the MDX Query Designer in data mode. I have my columns and rows but need to drag a dimension into the filters & parameters pane in MDX Query Designer and set a filter and parameter on it. Any idea how to make it viewable?
View Replies !
View Related
ERROR: The Nested Query May Be Missing An ORDER BY Clause.
Hi, on executing the below query i am getting the following error ERROR: Errors in the back-end database access module. Nested table keys in a SHAPE query must be sorted in the same order as the parent table. The nested query may be missing an ORDER BY clause. even though the order by clause is presenet in the nested query SELECT t.[ProductId], Predict ([Association].[Product Basket],3) From [Association] PREDICTION JOIN SHAPE { OPENQUERY([Adventure Works Cycle MSCRM], 'SELECT DISTINCT [ProductId] FROM (SELECT ProductId FROM ProductBase) as [Product] ORDER BY [ProductId]')} APPEND ({OPENQUERY([Adventure Works Cycle MSCRM], 'SELECT [ProductId] FROM (SELECT ProductId FROM ProductBase) as [Product] ORDER BY [ProductId]')} RELATE [ProductId] To [ProductId] ) AS [Product] AS t ON [Association].[Product Id] = t.[ProductId] AND [Association].[Product Basket].[Product Id] = t.[Product].[ProductId]
View Replies !
View Related
MDX Query Builder Missing Rows And Columns Areas
I was using the MDX Query Builder to create MDX queries for a SSRS report. I'm not sure what happened, but when I tried to create another dataset against the cube, the "Drop Column Fields Here" and "Drop Row Fields Here" areas were no longer available for me to drop attributes onto. I have restarted VS, rebooted, you name it, I've tried it (short of re-installing). Has anyone encountered this and how did you "fix" it. BTW: In order to continue working, I decided to use ProClarity to build the MDX for me and when I tried to paste it into the MDX editor, I get the following error: "The query cannot be prepared: The query must have at least one axis. ..". So, as I've seen from other posts, you can't use "any" MDX in the MDX Query Builder. Malinda
View Replies !
View Related
Show Zero Values For Missing Data In Query Resultset
Hello, I have the following query which grabs monthly usage data which is logged to a database table from a web page: SELECT Button = CASE ButtonClicked WHEN 1 THEN '1st Button' WHEN 2 THEN '2nd Button' WHEN 3 THEN '3rd Button' WHEN 4 THEN '4th Button' WHEN 5 THEN '5th Button' WHEN 6 THEN '6th Button' WHEN 7 THEN '7th Button' WHEN 8 THEN '8th Button' WHEN 9 THEN '9th Button' ELSE 'TOTAL' END, COUNT(*) AS [Times Clicked] FROM WebPageUsageLog (NOLOCK) WHERE DateClicked BETWEEN @firstOfMonth AND @lastOfMonth GROUP BY ButtonClicked WITH ROLLUP ORDER BY ButtonClicked The results look like this: TOTAL 303 1st Button 53 2nd Button 177 3rd Button 10 4th Button 4 6th Button 18 7th Button 19 8th Button 21 9th Button 1 If a button is never clicked in a given month, it never gets logged to the table. In this example, the 5th button was not clicked during the month of December, so it does not appear in the results. I want to modify my query so it displays the name of the button and a zero (in this case "5th Button 0") in the results for any buttons that were not clicked. For some reason I am drawing a blank on how to do this. Thanks in advance. -Dave
View Replies !
View Related
JDBC Query Running Indefinitely (Connection Missing)
We see an unusual case where a query seems to be taking a long time (more than 30 minutes) as shown by the Java thread dump (below) - however the SQL server DB does'nt show any corresponding Connection for the query at the lower layer. The JDBC layer seems to be "in progress" as far as processing the results of the query are concerned. If the Connection was dropped or had a failure we should have seen a corresponding SQLException in the JDBC layer - which is also not the case. Any tips on how to debug this? Is there a timeout set on the JDBC Connection which causes it to wait before it detects any failures? thanks "JMS Session Delivery Thread" daemon prio=6 tid=0x0000000006434780 nid=0x868 run nable [0x0000000020d6e000..0x0000000020d6f860] at java.net.SocketInputStream.socketRead0(Native Method) at java.net.SocketInputStream.read(SocketInputStream.java:129) at com.microsoft.sqlserver.jdbc.DBComms.receive(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePrep aredStatement(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedState mentExecutionRequest.executeStatement(Unknown Source) at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source ) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unkno wn Source) - locked <0x00000000955574e0> (a com.microsoft.sqlserver.jdbc.TDSWriter) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate (Unknown Source) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(Del egatingPreparedStatement.java:101) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(Del egatingPreparedStatement.java:101) at org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeInsert(JdbcAc cessImpl.java:213) at org.apache.ojb.broker.core.PersistenceBrokerImpl.storeToDb(Persistenc eBrokerImpl.java:2021) at org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBro kerImpl.java:977) at org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBro kerImpl.java:1014) at org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBro kerImpl.java:884) at org.apache.ojb.broker.core.PersistenceBrokerImpl.storeAndLinkOneToOne (PersistenceBrokerImpl.java:1074) at org.apache.ojb.broker.core.PersistenceBrokerImpl.storeReferences(Pers istenceBrokerImpl.java:1050) at org.apache.ojb.broker.core.PersistenceBrokerImpl.storeToDb(Persistenc eBrokerImpl.java:1981) at org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBro kerImpl.java:977) at org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBro kerImpl.java:1014) at org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBro kerImpl.java:884) at org.apache.ojb.broker.core.DelegatingPersistenceBroker.store(Delegati ngPersistenceBroker.java:220) at org.apache.ojb.broker.core.DelegatingPersistenceBroker.store(Delegati ngPersistenceBroker.java:220) at org.apache.ojb.broker.core.DelegatingPersistenceBroker.store(Delegati ngPersistenceBroker.java:220) at com.serus.db.ojbutils.SerusPersistenceBrokerHandle.store(SerusPersist enceBrokerHandle.java:178) at com.serus.common.OJBDAOUtil.store(OJBDAOUtil.java:636) at com.serus.dao.wip.AbsWipDAO.saveDTO(AbsWipDAO.java:251) at com.serus.dao.wip.tx.LotTXDAO.saveLotTx(LotTXDAO.java:56) at com.serus.manager.wip.txprocessing.AbstractTxProcessorImp.storeLotTx( AbstractTxProcessorImp.java:1380) at com.serus.manager.wip.txprocessing.LotTxProcessingMoveStage.storeLotT x(LotTxProcessingMoveStage.java:802) at com.serus.manager.wip.tx.LotTxManager.saveLotTx(LotTxManager.java:248 ) at com.serus.manager.wip.tx.LotTxXMLProcessor.parseMessageOriginal(LotTx XMLProcessor.java:137) at com.serus.manager.wip.tx.LotTxXMLProcessor.parseMessage(LotTxXMLProce ssor.java:78) at com.serus.events.listener.SerusJMSListener.onSerusMessage(SerusJMSLis tener.java:197) at com.serus.events.listener.AbstractEventListener.onMessage(AbstractEve ntListener.java:53) at progress.message.jimpl.Session.deliver(Unknown Source) at progress.message.jimpl.Session.run(Unknown Source) at progress.message.jimpl.Session$SessionThread.run(Unknown Source)
View Replies !
View Related
Return Missing Records Over Multiple Tables. Query Challenge!
I have received some data out of a relational database that is incomplete and I need to find where the holes are. Essentially, I have three tables. One table has a primary key of PID. The other two tables have PID as a foreign key. Each table should have at least one instance of every available PID. I need to find out which ones are in the second and third table that do not show up in the first one, which ones are in the first and third but not in the second, and which ones are in the first and second but not in the third. I've come up with quite a few ways of working it but they all involve multiple union statements (or dumping to temp tables) that are joining back to the original tables and then unioning and sorting the results. It just seems like there should be a clean elegant way to do this. Here is an example: create table TBL1(PID int, info1 varchar(10) ) Create table TBL2(TID int,PID int) Create table TBL3(XID int,PID int) insert into TBL1 select '1','Someone' union all select '2','Will ' union all select '4','Have' union all select '7','An' union all select '8','Answer' union all select '9','ForMe' insert into TBL2 select '1','1' union all select '2','1' union all select '3','8' union all select '4','2' union all select '5','3' union all select '6','3' union all select '7','5' union all select '8','9' insert into TBL3 select '1','10' union all select '2','10' union all select '3','8' union all select '4','6' union all select '5','7' union all select '6','3' union all select '7','5' union all select '8','9' I need to find the PID and the table it is missing from. So the results should look like: PID MISSING FROM 1 TBL3 2 TBL3 3 TBL1 4 TBL2 4 TBL3 5 TBL1 6 TBL1 6 TBL2 7 TBL2 10 TBL1 10 TBL2 Thanks all.
View Replies !
View Related
Linked Server Query To Oracle ORA-00936: Missing Expression
I finally have my server linked, figured out my date issed with the decode statement and now I get a missing Expression error from Oracle. Here is the statement: SELECT * FROM OPENQUERY(PROD_ORACLE,' SELECT LEFT(CUST_ORDER_STUS_NME, 25) as CUST_ORDER_STUS_NME, LEFT(SRVC_ORDER_STUS_NME, 25) as SRVC_ORDER_STUS_NME, LEFT(Ser_ORD, 20) AS Ser_ORD, LEFT(CUST_ORDER_NME, 25) as CUST_ORDER_NME, LEFT(SRVC_ORDER_NME, 25) as SRVC_ORDER_NME, LEFT(CLS_ALLOWED_NBR, 2) as CLS_ALLOWED_NBR, LEFT(NOC_TO_NOC_NME, 3) as NOC_TO_NOC_NME, LEFT(CHARS_ID, 20) as CHARS_ID, LEFT(PRI_DNS_QTY, 5) as PRI_DNS_QTY, LEFT(SCNDY_DNS_QTY, 5) as SCNDY_DNS_QTY, LEFT(ORDER_TYPE_CD, 10) as ORDER_TYPE_CD, LEFT(ACTY_NME, 25) as ACTY_NME, replace(LEFT(CUST_A_NME, 50), ''|'', ''-'') as CUST_A_NME, LEFT(RLAT_CKT_ID, 8) as RLAT_CKT_ID, DECODE (BILL_CLR_DT, GREATEST(BILL_CLR_DT, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), BILL_CLR_DT, NULL), DECODE (CMPLT_DT, GREATEST(CMPLT_DT, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), CMPLT_DT, NULL), REPLACE(replace(LEFT(CMNT_TXT, 1000), char(10), '' ''), ''|'', ''-'') as CMNT_TXT, LEFT(CUST_H1_ID, 9) as CUST_H1_ID, LEFT(CUST_CMS_ID, 8) as CUST_CMS_ID, LEFT(Circuit_ID, 30) as Circuit_ID, DECODE (SO_CMPLT_DT, GREATEST(SO_CMPLT_DT, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), SO_CMPLT_DT, NULL), LEFT(DNS_ADMIN_NME, 30) AS DNS_ADMIN_NME, LEFT(DNS_ADMIN_PHN_NBR, 15) AS DNS_ADMIN_PHN_NBR, LEFT(DNS_ADMIN_EMAIL_ADDR, 128) AS DNS_ADMIN_EMAIL_ADDR, LEFT(CONTACT, 30) AS CONTACT, LEFT(GOVT_CD, 10) AS GOVT_CD, LEFT(SOTS_ID, 30) AS SOTS_ID, LEFT(RAS_USER_BLK_NBR, 20) AS RAS_USER_BLK_NBR, LEFT(VSYS_QTY, 10) AS VSYS_QTY, LEFT(ZONE_QTY, 10) AS ZONE_QTY, LEFT(PLCY_QTY, 10) AS PLCY_QTY, LEFT(PIC_CODE, 6) AS PIC_CODE, LEFT(SO_Order_Entry_Nbr, 15) as SO_Order_Entry_Nbr, LEFT(SO_Rlat_Order_Entry_Nbr, 15) as SO_Rlat_Order_Entry_Nbr, '' '' as filler From PROD_ORACLE..RDBADM.CUST_SRVC_ORDER_V') Here is Query Analyzers response: Server: Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing a query for execution against OLE DB provider 'OraOLEDB.Oracle'. [OLE/DB provider returned message: ORA-00936: missing expression] OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandPrepare:repare returned 0x80040e14]. ANY help is greatly appreciated!!
View Replies !
View Related
|
TRACKER
