Databases Forum

I've created an append query to move records from one table to another. Am i right in thinking it should move the records then delete them from the previous table? If so then for some reason its not working for me. Using MS Access 2003. [ICODE]INSERT INTO Tbl_Job_Temp SELECT Tbl_Job_List.* …

The below query is updating all the rows in the table1 eventhough the inner query only selects few of the rows. Any help. Appreciate it. Thanks, UPDATE table1 out SET (col1, col2, col3) = (SELECT col1, col2, col3 FROM (SELECT col1, col2, col3 FROM table1 in, table2 a, table3 b …

Hey everyone, Is this the right way how to make a trigger in Oracle? [CODE] CREATE OR REPLACE TRIGGER "TRI_AMOUNT_REDUCTION" BEFORE INSERT ON "PURCHASE" FOR EACH ROW BEGIN IF (:NEW.CLIENTNO =SELECT C.CNAME, T.CLIENTNO, T.TOTAL FROM CLIENT C, ( SELECT A.* FROM (SELECT CLIENTNO, SUM(AMOUNT) AS TOTAL FROM PURCHASE GROUP BY …

Hey everyone, I'm trying to count all Purchases and find ReceiptNo the ones that start with '434'.. [CODE] Select count(p.purchaseNo) from purchase p (select INSTR(TO_CHAR(p.recceiptNo), "434") >= 10; [/CODE] But this line doesn't only select all receipt number starting with 434.. it selects all receipt no that has 434 in …

Hai, I Have an sp like this: [CODE]ALTER procedure [dbo].[SP_ClassInsertion] @ClassType varchar(50), @ClassTypeId varchar(50), @ClassName varchar(50), @SchoolId varchar(50), @ClassID varchar(50), @BatchId varchar(50), @Userid uniqueidentifier, @ClassCategoryId varchar(50), @SchoolClassCategoryId varchar(50) as declare @STATUS int begin if exists(select Class_Id from SCH_Class where Class_Name=@ClassName and School_Id=@SchoolId and Is_Valid=1) begin set @STATUS=0 end else begin …

NOTE: If this is too long, scroll to the last paragraph where hopefully I've summarized my problem. I have a simple problem. I have two tables, one is for the "header" and one is for the "details" of the header. I need a view to return all details and the …

hi i am working on db design of multilingual site. For creating multilingual site we usually use xml or different col in table represting language. I am thinking of using diferernt db for each languge,it will simplifies many things. so my question is that, is this thing of using muliple …

I current have a column in my database called ConveyorNumber, which could be something like CMD1234, CMD1256, CMD1136 etc. I also have a column called Asset_type which relates to the conveyor number. So depending on the conveyor number the asset type will change, its a description of the conveyor type. …

Can anyone see anything wrong with this statement? [CODE] CREATE OR REPLACE VIEW V_DEPT_AMOUNT AS SELECT DEPTNO AS DNO, DNAME AS DNAME, MAX(AMOUNT) AS MAX_AMOUNT, AVG(AMOUNT) AS AVG_AMOUNT, MIN(AMOUNT) AS MIN_AMOUNT, SUM(AMOUNT) AS TOTAL_AMOUNT FROM DEPT D, EMP E, PURCHASE P WHERE D.DEPTNO = E.DEPTNO GROUP BY DNAME, DEPTNO; [/CODE] …

Hey guys, Alright I have a tricky assignment to do in a view that will enable my next program to run MUCH faster and more reliable... Now I need to list all Items (1 table) and their locations where they are stored (2nd table) in a way that enables 1 …

I have changed a column in my database from the primary Key to Index, as i need to allow the same reference in the column for multiple entries with different dates against them, but now i need to adjust my queries. how do i do this?

Guys, what particular drive/folder to stored the text/csv/excel file that the bulk insert will be reading? what if in my local drive is this applicable? or it should be in the shared folder in the server. Thanks, JOV

This is problem that I have struggled with for some time, but im sure the answer is very simple... I have a sub report that performs calculations on 2 fields ( [Rate] [Hours Worked] ) from a table called [Hours]. The report is GROUPED by [Rate] and is as follows: …

hey everyone, I'm trying to get my head around this Question but it is not making that much sense .. Each receipt is issued from a receipt book whose number is encoded in the first three digits of the ReceiptNo field in the PURCHASE table. For example, the receipt numbered …

Hey, [CODE] CREATE OR REPLACE TRIGGER "TRI_AMOUNT_REDUCTION" AFTER INSERT ON "PURCHASE" FOR EACH ROW BEGIN IF (:NEW.CLIENTNO = 122336) THEN :NEW.AMOUNT := AMOUNT * 0.1; END IF; END; / [/CODE] I have a query that selects my top client then want to pass the clientNo to the trigger. Is it …

Hey everyone, I'm just wondering if there is a way to get around this problem. I'm running SQLplus on UNIX server and SSHing into the server. Whenever I query Oracle DB, a list of results/bunch of tables comes up but I can not scroll up to the top of tables..... …

Hello, I think I made a mistake while connecting my tables with foreign keys :S here's the DB- table 1: [U]Id[/U],[U] year[/U],..... (2 Primary keys) table 2: [U]tbl2Id[/U], ... now the table that connects tbl1 and 2 with m..m relation- table 3: [U]Id[/U],[U] year[/U],[U]tbl2Id[/U] I set the foregn keys for …

Good day. I'm new here and I'm nt sure this post should belong in here or not. I had done my programming on the virtual machine while having mysql database set up in my PC. I was unable to get the data from my PC unless I installed another database …

Can anyone help me with the database structure of a personal finance application?

I have no idea how to do this so please forgive my not trying before i ask. I would like to create a system that will allow user to vote, count and tell who won. i can do all that is stated. now i would like to add the functionality …

Hi everyone, I've been trying different commands that check inputs that will be entered into the tables I've got. I want to check that Amount/Ename columns can not be NULL. What's <> do in SQL? [CODE] // first command ALTER TABLE Emp ADD CONSTRAINT CK_AMOUNT CHECK ( AMOUNT NOT NULL); …

My query below has been working okay for 1 year now and suddenly it is giving an exception: [CODE] (SELECT distinct(R.imei),R.mobile,S.msg,S.reply,M.model, left(S.receiveTime,10) as receiveDate, right(S.receiveTime,8) as receiveTime,'' as 'Reason' from dbSAMSUNGmain.tREGDATA R,dbSAMSUNGmain.tMODELS M,dbSAMSUNGmain.tSMS_TRANSACTIONS S where S.mobile=R.mobile and replace(replace(S.msg,'/',''),' ','') LIKE concat('%',R.imei,'%') and (S.reply like '%Registration successful%' || S.reply like '%Reg …

hi guys, i am trying to write a query that would basically display me pas 4 week, however if lets say today is Wednesday the pas for week would have to start from last Friday, none the less if i have to run the query on monday i would want …

my table design is [CODE] date income expenses 2011-08-5 1000 500 2011-09-6 7000 400 2011-10-7 2000 300 [/CODE] I have daily expenses and Income in the above table.I need to retrieve monthly(sum of daily) income and expenses for particular year. How should i write query for that . Pls help …

Please could you tell me where I am going wrong I want to open 1 of 2 forms depending if a certain text is present in a field of a table. If text "Expired" is present in the 'Name' field of Table 'MAIN' then open form 'Expired' and if is …

Hello, I'm using MySQL for a database containing 1 table with 10 columns. The point is to get a view of a few columns and use it in a web application So, I need 2 columns from this table - Pad(varchar) and Sequence(integer), then I create 8 views like this: …

Hi I have a issue that my application in currenlty doing number validation with an issue. I have a query that checks number is stored procedures but need more checks. [-d0-9] and checking that the lenth is 10 characters, all that is working as it should. The issue I'm having …

Hi all. I am trying to create a search that displays 3 columns. Analysis, Amount, Amount of Positiv results I have nailed down Analysis, Amount, but have problems displaying Pos. result. [CODE] SELECT DISTINCT SP.ANALYSIS AS Analysis,COUNT(SP.ANALYSIS) AS Amount FROM SAMPLE S INNER JOIN SAMPLE_PARTS SP ON SP.SAMPLEID = S.SAMPLEID …

Hi, Is there a way i can pass variable column names to a stored proc? Here's what i want to do: i have lots of tables and for each of those i want to select out a few columns. e.g from table titles i want to pick out title_name and …

i have a query is to lookup CREDIT_CARD table using CREDIT_CARD_ID and get the MIN_REPAY_AMT. if SUM(AMOUNT_DEPOSITED) < MIN_REPAY_AMT for that month Then populate 1000 else 0 i have attempted the query below kindly help me out and i am getting the following error. Error: [CODE]ERROR at line 2: ORA-00923: …

Hi everyone! i am developing an application using java and mysql as a database server. the problem is i want to change the default date format in mysql. i created a table like this; create table registration (registrationNo integer primary key, customerName varchar[50] not null, regDate DATE not null); so …

Hello, I need to connect to a DSN on a remote computer. I have found a lot of a lot of info on how to connect to a remote database from a local DSN, but nothing on connecting to a remote DSN. I am using asp.net, but help on solving …

I am having a bit of difficulty in getting the right value. What I want to query is, the users who expire in 7 days, for example on 4th Oct 2011, the query should display the result of that particular day only. Right now I am querying as below: select …

Hi, can anybody, please help me out. [CODE][B]select * from log where id <='%07%' [/B][/CODE] Am facing the syntax error, what is the actual query? my id look like this: 1.mtg10kl 2.mtg07kl 3.mtg06kl 4.mtg09kl I need the output this: 1.mtg07kl 2.mtg06kl please help me out .thanks in advance

Hi, I installed MySQL from the MySQL site. The setup procedure required me to set an username and password, which I set as X and Y respectively (say). Then when I opened the mySQL command line client, it asked for password, and after providing Y, I was allowed in. Once …

Hi Guys, I have imported an excel document into MySQL to tidy up and make better use of the data but I am having problems running an update. All tables used as examples below; [U]Tables[/U] asset_register phone sim team The asset_register contains the following cols; [CODE] id INT, serialNo VARCHAR, …

This is homework. Here is the problem: Produce a list of employee last name, first name and department name. Use appropriate, user friendly column aliases. This is from the tables [ICODE]DEMO.EMPLOYEE[/ICODE] and [ICODE]DEMO.DEPARTMENT[/ICODE]. The relevant column names are [ICODE]LAST_NAME, FIRST_NAME, DEPARTMENT_ID[/ICODE] (from [ICODE]DEMO.EMPLOYEE[/ICODE]) and [ICODE]DEPARTMENT_ID, NAME[/ICODE] from [ICODE]DEMO.DEPARTMENT[/ICODE]. As you …

I have a function C++ file, created in VS 2005. But I am not sure how to register that function in mysql, so that it get stored somewhere in mysql database and when the query runs, it should retrieve the func definition from the database itself.. Please guide me with …

Hi, I have a table QuestionAsked which contains a column DateofAsking. Now what i need is to extract each date value and check whether the difference between that day and the current day is 14 or not. If yes than i have to update a particular column in that table.I …

Hi there, Im working on a web database application and Im encountering problems to connect the database and the ASP.net page. The database is being detected but not the tables inside the database. Could anybody help me?

Guys, I have 2 tables. My objective is to get the information from Table1 and place it to table2. the 2 tables have the same Itemnumber and DefectCode. How could make this in correct scripts cause when i make the script it triple the records. [CODE] TABLE1 ItemNumber---------Price---PO-------Code-Qty P300-1710-DROID-U--302.05--9100962--506--1 P300-1710-DROID-U--302.05--9100962--553--4 …

My table has 4 types of "r1": 'Standard', 'HD', 'HD + DVR', 'None' There are 3 types of "status": 'NTC', 'Repair', 'In Progress' All I am trying to do is retrieve all of the rows where r1 = 'Standard' and matches all three of the "status": [code] SELECT * FROM …

Hi Everyone, I am writing a program in VBA (within Access 2010) that grabs a specific .txt file, split up the fields as per given specification and imports the values in to an Access table. So far, I can open the file and see what the program is seeing and …

hi everyone they said in relational database there is no "last" concept.. and the only way to retrieve the last data entered in database is by IDENTITY and sone says TIMESTAMP... which one i would prefer to use?

I need to update a column in access where I should be removing the 7 leading characters in a field.Example: Column value before: AB34567213. Colum value after: 213 So, I tried using VB in Access and this is what I got so far: [CODE]Private Sub delete_leading_chars() Update table_name Set col_name …

OK, I have a table that has: id (int), name (varchar), parent (int) If parent contains NULL it IS the "parent" otherwise it is the child of a parent So: id=1 name=Meals parent=0 Means the above record is a "parent" (there are 100's of parents). So far, so good? Next …

What is the difference between oracle and sql? I am new to this field.Please help!!!!!!!!!!!!!!!!!!!!

I just stumbled over this: [ICODE]mysql> select "abc" = 0; +-----------+ | "abc" = 0 | +-----------+ | 1 | +-----------+ 1 row in set, 1 warning (0.06 sec) mysql> show warnings; +---------+------+-----------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: …

Guys, How to make an scripts if itemnumber have no '-U' then i will place this on the right side while if there's an exising '-U' then no replacement to be done. [CODE] ItemNumber ------------------ P300-4410-DROIDERIS P300-4110-TOUR P333-4410-EVO-U P333-4129-8530PUR-U P333-4170-9330GRY-U P333-4110-8350-U RESULTS: ItemNumber ------------------ P300-4410-DROIDERIS-U P300-4110-TOUR-U P333-4410-EVO-U P333-4129-8530PUR-U P333-4170-9330GRY-U P333-4110-8350-U …

Hi Guys, There are unwanted character in my columns. this character is came from the bulk insert. How tdo i removed this character using scripts. Please see sample below. [CODE] ItemCode--Item Number ---------------------------------------------------------- 123--Phone power on but vibrate is too weak P300-3110-VX8575 124--place a test call. P300-1710-DROID 125--power issues.touchscreen not …