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 Complete Forum Thread with Replies
Related Forum Messages:
Issue With Missing Field Contents
Hi there, I have a problem in SQL 2005, can someone help? I happened to create a new field, lets say: sSubject with type: nvarchar(50), null. then manually I go in and add this field with data and SAVE it. For some reason, few days later the apps cannot work. And I went into DB and found out that the mentioned field contents has become "NULL". What is going there in DB? Thanks....cheers
View Replies !
Missing Field Generated By Identity Seed
Remedy an application that uses a sql database assigns a field number to any new fields that are created however we are getting an error when trying to create a new field telling us the number already exists. The sql database shows the field as being existing when executing a sp_help on that table but it is not showing up when executing a List * from the table. I am confused to why I am getting different results?
View Replies !
Field Names Missing In MDX Data Set When Using NON EMPTY Clause
So I have an MDX query in an SSRS data set. Here is my MDX query: Code SnippetSELECT { [Measures].[Gross Sales Amount USD], [Measures].[Net Sales Amount USD] } ON COLUMNS, { ([Promotion].[Media Property].[Promo Code Description].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@BeginDateConvert, CONSTRAINED) : STRTOMEMBER(@EndDateConvert, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@PromotionMediaProperty, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Promotion].[Campaigns].[Campaign].&[Paid Partner] } ) ON COLUMNS FROM ( SELECT ( { [Products].[Product Line].[Line].&[Merchandise] } ) ON COLUMNS FROM ( SELECT ( { [BusinessUnit].[Business Unit].[Product Business Unit].&[40] } ) ON COLUMNS FROM [Net Sales]))))) WHERE ( [BusinessUnit].[Business Unit].[Product Business Unit].&[40], [Products].[Product Line].[Line].&[Merchandise], [Promotion].[Campaigns].[Campaign].&[Paid Partner] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS This query returns 4 fields. Media Property, Promo Code Description, Gross Sales, and Net Sales. For the given query the measures are empty or null. I do not want any data to show up when the measures are null so I put in NON EMPTY clauses before the COLUMNS and before the ROWS. So now my query looks like this: (I only added the NON EMPTY clauses) Code Snippet SELECT NON EMPTY { [Measures].[Gross Sales Amount USD], [Measures].[Net Sales Amount USD] } ON COLUMNS, NON EMPTY{ ([Promotion].[Media Property].[Promo Code Description].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@BeginDateConvert, CONSTRAINED) : STRTOMEMBER(@EndDateConvert, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@PromotionMediaProperty, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Promotion].[Campaigns].[Campaign].&[Paid Partner] } ) ON COLUMNS FROM ( SELECT ( { [Products].[Product Line].[Line].&[Merchandise] } ) ON COLUMNS FROM ( SELECT ( { [BusinessUnit].[Business Unit].[Product Business Unit].&[40] } ) ON COLUMNS FROM [Net Sales]))))) WHERE ( [BusinessUnit].[Business Unit].[Product Business Unit].&[40], [Products].[Product Line].[Line].&[Merchandise], [Promotion].[Campaigns].[Campaign].&[Paid Partner] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS Adding the NON EMPTY returns nothing... not even field names. This is a problem because, I have a table in the report that looks at this data set and when there are no fields, the report can't run. How can I still have NON EMPTY functionality and still show the fields? Is this a problem in SSRS?
View Replies !
Inserted Records Missing In Sql Table Yet Tables' Primary Key Field Has Been Incremented.
I have a sql sever 2005 express table with an automatically incremented primary key field. I use a Detailsview to insert new records and on the Detailsview itemInserted event, i send out automated notification emails. I then received two automated emails(indicating two records have been inserted) but looking at the database, the records are not there. Whats confusing me is that even the tables primary key field had been incremented by two, an indication that indeed the two records should actually be in table. Recovering these records is not abig deal because i can re-enter them but iam wondering what the possible cause is. How come the id field was even incremented and the records are not there yet iam 100% sure no one deleted them. Its only me who can delete a record. And then how come i insert new records now and they are all there in the database but now with two id numbers for those missing records skipped. Its not crucial data but for my learning, i feel i deserve understanding why it happened because next time, it might be costly.
View Replies !
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 !
Query To Find A Missing Number
Hello,I need to write a query to find out a set of missing number in a givensequence.Eg : a Column in some table has the following dataCol11234568910Here I need to write a query to find out that number 7 is missing in thegiven sequence.One possible solution is by using any loop. But I am looking out if the samecan be achieved using any query.Thanks in advance.Regards,Mahesh
View Replies !
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 !
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 !
In Query Analyzer Some Table Are Missing
Dear All i have a problem In Query Analyzer .When I create two diffrent table with two diffrent schemas dbo and guest but when i log on query analyzer and run query from guest schema's tahle it gives error object not found i think its some rights problem ..
View Replies !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
Max Of Date Field In Query
I have a table with date fielde.g. SrNo Date 1 1-MAR-2008 2 3-Mar-2008 3 7-Mar-2008 4 10-Mar-2008 5 15-Mar-2008 I need a query to find out Max date which is less than '8-Mar-2008' . i.e i need single output '7-Mar-2008'anybody helpThanks
View Replies !
How To Split The Field In The Sql Query?
I have the field LocationID (string)which has values like "AZ001","AZ002","IN002","IN004" first 2 will be always alphabets and remaining 3 will be numbers, I want to split it like "AZ" ," 001" "AZ","002" "IN" "002" "IN" "004" now i will populate dropdownlist with unique values of "AZ" "IN" according to first dropdownlist how i will populate second dropdownlist? So how to write sql query for splitting? and then populating the dropdownlist ?
View Replies !
SQL DateTime Field Query
I was wondering if someone could help me here with some reporting I'm trying to do. For website visits, I currently use getdate() to have SQL insert the date and time of the visit in the table. I was wondering, is there a way to grab just the month from the field? I would like to chart this and I need to group the visits by month. I'm not sure how I would go about filtering just the month out of the entire date and time fields.
View Replies !
Empty Field Query
Hi Friends, I have one query that i have to Replace the Empty Value of a filed with some other value how can i do in SQL?? ID Phone Name 1 122 abc 2 xyz 3 444 mmm 4 525 ccc 5 nvb Now i want replace the blank (Empty) filed with some charaters Numbers how can i do that?? any Help Ashu
View Replies !
How Do I Query For Particular Criteria In Every Field In The Db?
I need to query a table for a record only if there is a certain text string in ANY OF THE TABLE'S fields. I know of using the WHERE clause with logical OR (e.g. - WHERE field1 = 'xyz' or field2 = 'xyz' or ...) but there is a limit of 40 expressions with an Access database, and I have 102 fields to look through! That's my dillema. I know I may need to go to MSSQL or Oracle, but I'd rather stay with Access. Is there a syntactical way to express the query I need? Or do I have to look somewhere other than Access for my database needs? Thanks for the advice!
View Replies !
Use Calculated Field In Same Query
Right now I have one view that grabs records and sums up related records etc.... and returns a result. So basically it has the ID number and the number I calculated. THen I have another view that takes that number and performs calculations on it into three different columns. Is there any way to make these two view into one without a lot of repetative statements? Here is an example: SELECT (tblTest.Quantity * tblTest.Price) as SubTotal, SubTotal * 1.06 as Total Obviously that doesn't work, but what could I do to get that basic thing to work? Thanks!
View Replies !
Query Detail As A Field.
I have two tables(Order and OrderDetail) of master-detail relationship. I have a nchar field in the detail table called itemno. I want to query like: Select Order.OrderNo, Order.Date, SUM(OrderDetail.ItemNo) as ItemNos,.... From Order Inner Join OrderDetail on Order.OrderID=OrderDetail.OrderID Where Order.OrderID=10 so that the resulting field ItemNos will become a string in format "ItemNo01, ItemNo02, ItemNo03,...." How can I write this query in T-SQL? Thanks
View Replies !
Really Simple Query Needs Another Field
I have written this query in SQL select * from wce_contact where (Mail3Date <= '2007-09-25') AND NOT (Mail1Date is NULL ) AND NOT (Mail2Date is NULL ) AND (Mail4Date is NULL ) AND (Mail5Date is NULL ) AND (Mail6Date is NULL ) AND (Mail7Date is NULL ) AND (Mail8Date is NULL ) AND (Mail9Date is NULL ) AND (Mail10Date is NULL ) AND (Mail11Date is NULL ) AND (Mail12Date is NULL ) AND (Mail13Date is NULL ) AND (Mail14Date is NULL ) AND (Mail15Date is NULL ) AND (Mail16Date is NULL) Little bit of background info - We have a database of over 500k websites with email addresses. We use email marketing to contact these sites and everytime they get mailed i need to fill in the MailDate field. Obv Mail5Date means they have been emailed 5 times etc The above qry is trying to find all contacts that have been mailed 3 times and havent been mailed in the last 42 days. I also have an IDStatus field that allows me not to use the contacts that have opted out our mailing or website shutdown etc I try adding this to the bottom of the query AND (IDstatus is Null) But the query runs and finds 0 contacts which i know isnt the case, as when i use our front end database application it produces results. I was wondering if anybody could possibly help me? Thanks!
View Replies !
Sort On Field Constructed In Query
I want to sort on a field that I construct during a query....how can I solve this?Basically what i am doing is selecting mediafiles with comments in descending order. (so mediafile with most comments on top)Here's my (not working) query, because it throws the error that the mediaComments column is unknown....SELECT *FROM(select ROW_NUMBER() OVER (ORDER BY mediaComments DESC) as RowNum,m.title,m.usercode,mediaComments=(select count(*) from MediaComments where mediaid=m.mediaid)FROM Media m WHERE m.usercode>0group by m.title,m.usercode) as InfoWHERE RowNum between @startRowIndex AND (@startRowIndex + @maximumRows-1)
View Replies !
Query To Sum The Same Field Twice In The Select Statement
Hello friends , I have table (MoneyTrans) with following structure [Id] [bigint] NOT NULL, [TransDate] [smalldatetime] NOT NULL, [TransName] [varchar](30) NOT NULL, -- CAN have values 'Deposit' / 'WithDraw' [Amount] [money] NOT NULL I need to write a query to generate following output Trans Date, total deposits, total withdrawls, closing balance i.e. Trans Date, sum(amount) for TransName='Deposit' and Date=TransDate , sum(amount) for TransName=Withdraw and Date=TransDate , Closing balance (Sum of deposit - sum of withdraw for date < = TransDate ) I am working on this for past two days with out getting a right solution. Any help is appreciated Sara
View Replies !
DISTINCT SELECT Query With More Than One Field
Hello, I want to do a DISTINCT SELECT query with more than one field, for example a ID field with a Type field, as if both fields make the primary key, like (ID 1 ,Type 1) ,( ID 1, Type 2) and (ID 2, Type 1) is ok but not (ID 1, Type1) and (ID 1,Type 1) if its not possible to do a distinct with more than one then what other techniques are possible to get the duplicate data out. the reason why I want to use distinct is that I can use that query to export that data to where both of these fields make the primary key. Thanks in advance
View Replies !
SQL Query - Search Field For String
Hi, I have a SQL server 2005 database with a series of multiple fields. One of the fields has a array of strings seperated by semi-colons like so: Red;Green;Blue My question is, how can i run a query on all of the fields that have the value of say Green in it. Note that these values vary in different order and numbers. Thanks
View Replies !
Help! - Query On Time Field In SQL Server - How?
Hi,I have an orders database and I need to be able to write a querythat groups sales by:1. Date2. Time of day (both am and pm)I currently have a date/time field named "Submitted" that storesjust the date.However, how do I store the time?I know I can store the date/time in the "Submitted" field Icreated. However, because of the time being stored in that field, Ican't execute an easy query such as:select * from database where submitted = '2/10/05'(reason being, the time portion makes each "Submitted" fieldrecord unique, so just the date won't be recognized)I realize there probably is a much better way to do this in theSQL query language that I am not aware of.Two questions:1. Based on what I'm trying to do -- query based on date and groupby number of sales for each hour of the day (am and pm) -- what is thebest approach to store the time?2. What does the query look like that querys by date and groupsorders by time of day (am and pm)?Thanks for your help.J
View Replies !
How To Query By Keyword For Image Field.
If the data type of field is "varchar",we can use "like" to query if it hassome substring.Such as "where custom.valuevariant like '%Verizon%' ", it will query out allrecords that contains string "Verizon".But how to do when data type of field custom.valuevariant is "image"?Thanks
View Replies !
Referencing A Calculated Field In A Query
I would have a question, if it is possible to reference a calculated field in T-SQL query. The following expression does not work in SQLExpress 2005 SELECT Qty, UnitPrice, Tax, Qty*UnitPrice as Expr1, Expr1*(1.0 + Tax) AS Expr2 This problem has occurred after upsizing to SQLExpress from Access XP/Jet Engine. Since Access does not have any issue with the expression above, the SQLExpress does not even accept it. The only way how to avoid the issue in the SQLExpress seems probably to be - Duplicate some calculations (i.e. expand every expression with duplicating some mathematic operations with some performance loss) - Utilize computed columns if possible (no idea on performance impact here) Am I right or is there any other way how to reference a calculated field? Any suggestion is greatly welcomed! Thanks in advance.
View Replies !
Help - Updating A Field In Query Analyzer
Hi All, I'm trying to create a script that updates a field in a table, based on data in another table. It should be simple, but I'm doing something wrong. Here's the code: USE DBMyDatabase UPDATE TblToBeUpdated SET IDField=TblOther.IDNew WHERE IDField=TblOther.IDOld SELECT Pk, IDField FROM TblToBeUpdated What am I doing wrong? The error code I get is: Server: Msg 107, Level 16, State 3, Line 1 The column prefix 'TblOther' does not match with a table name or alias name used in the query. Server: Msg 107, Level 16, State 1, Line 1 The column prefix 'TblOther' does not match with a table name or alias name used in the query. Thanks. Henry
View Replies !
Query/select The First 10 Characters In A Field
I'm comparing two tables and need to compare the first 8 characters of one field in table A to the first 8 characters in another field in table B. So instead of where 'John Smith' = 'John Smith' it would compare where 'John Smi' = 'John Smi' I know I've done this before but can't find a good reference. It's something like this when calling a query within a WHILE loop: SELECT * FROM blah WHERE FID = '".$row['FID']."' AND LEFT('TRACK', 8) = LEFT('".$row['TRACK']."',8) Am I close? Any help would be appreciated.
View Replies !
Query To See If An Int Field Starts With A Certain Number
How would I write a query on a table containing a column of ints, where I want to retrieve the rows where that int value starts with a number? I know that you can do this with strings by using "....WHERE thisfield LIKE ('123%')", but if 'thisfield' is an int, how would I do this? Thanks!
View Replies !
Need Help With Update Query To Populate New Field
Hi folks: I have added a new field to an established table, and am having trouble figuring out how to populate its values: Two tables are involved: Jobs and Parts There is a one-to-one relationship between each JobID and its PartID Each Part has a PartPrice. Now I have added to the Jobs table a JobPrice field. Whenever a new Job is created, JobPrice takes the current value of its Part's PartPrice. Each Job's JobPrice remains constant for historical purposes, while the PartPrice may fluctuate at my client's whim. The trouble is that the Jobs table is 10k+ records large, and I need to fill the JobPrice values. I am at a loss. I know how to commit the update one record at a time: UPDATE Jobs SET JobPrice = (SELECT PartPrice FROM Parts WHERE (PartID = [the part in question])) WHERE (JobID = [the job in question]) My SQL knowledge is limited to basic statements that I use in my .NET work, and I rarely create anything in Management Studio more elaborate than what you see above. Many thanks for your time, Matt
View Replies !
How Do I Use A Parameter In A Dataset Field Value Using An MDX Query?
Let me simplify. The value of my dataset field Dimension_1 is [SOB Year].[Yr Id].[Yr Id].[MEMBER_CAPTION] I want to use parameters for SOB Year and Yr Id like I do on the query. But if I use ="["& Parameters!dimension_1_table.Value &"].["& Parameters!dimension_1_column.Value &"].[" & ["& Parameters!dimension_1_column.Value &"]" it returns null for the values. I don't know how to make these parameters I am passing in parameters for the table and column to build an MDX query here is my query. ="SELECT NON EMPTY { [Measures].[Liability Amt] } ON COLUMNS, NON EMPTY { (["& Parameters!dimension_1_table.Value &"].["& Parameters!dimension_1_column.Value &"].["& Parameters!dimension_1_column.Value &"].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [SOB Clean] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS" Anyone know what value I need to use on the dataset field to access the column? Thanks, Roger
View Replies !
SQL Query Not Returning Identity Field - Sometimes
Hello, I have a C# application that adds records to a SQL Server database using a query something like this one: INSERT INTO table_name (first_name, last_name, date_added) ('john', 'smith', '1/1/2005 12:00:00pm') ; SELECT SCOPE_IDENTITY() AS [Scope_Identity] This works fine unless there's already a John Smith in the database. When that happens, Scope_Identity is null even though the date_added is different. About half the time the record is added even though Scope_Identity is null. I've added code to notify me when this happens, but it's a pain in the neck to re-run my import utility for individual records. (The table I'm adding to does have a autonumbered key field) Thanks in advance!
View Replies !
UPDATE Query On Partial Field
How would I accomplish update just a part of a field? I have 500+ records in a table that got written as \share 01234.jpg, \share 01235.jpg...\share 01734.jpg but the records should have ended with .tif instead. Now the application cannot find the correct image because the extension is incorrect. How can I use the Update statement to just change the extension on these records? Thanks.
View Replies !
Condition On Query Created Field
Hi all, i have a query as below that creates a count in the field Total. I wanted to then be able to say only show me where there are more than 2 incidents. But i can't get it to recognise my created field. Any advice? Thanks. select a.vchcompanyname, count(*) as total from company a inner join incident b on b.iownerid = a.icompanyid where b.iincidentcategory = 1 and a.icompanytypecode = 102165 --and total > 2 (Wont recognise Total) group by b.iownerid, a.icompanyid, a.vchcompanyname order by 2 desc
View Replies !
Increment An ID Field In Sql Query String
Hello.. can anyone help me with this query string? String SQL = "INSERT Employee(Employee ID, UserName, JobRole, Department, Level, Email)(SELECT max(EmployeeID) + 1 FROM Employee) AS Employee ID, VALUES(EmployeeID, '" + newUserName + "', '" + newJobRole + "', '" + newDept + "', '" + newLevel + "', '" + newEmail + "')"; I am trying to insert values into a table, but i have an Employee ID field, which needs incrementing. How can i do this through my SQL query string? Is this possible? As it can't accept a NULL value. Thanks, Sandy
View Replies !
|
TRACKER
