|
<b>How to write stored procedure in mysql workbench. SELECT, INSERT, UPDATE,DELETE</b>
create table `admin_sales`(
`sales_id` int(10) unsigned NOT NULL auto_increment,
`sales_name` varchar(500) NOT NULL,
`sales_father_name` varchar(500) NOT NULL,
`sales_mobile_no` varchar(20) NOT NULL,
`sales_date_of_birth` varchar(50) NOT NULL,
`sales_date_of_joining` varchar(50) NOT NULL,
`sales_present_address` varchar(500) NOT NULL,
`sales_area` varchar(100) NOT NULL,
`sales_city` varchar(100) NOT NULL,
`sales_state` varchar(100) NOT NULL,
`sales_country` varchar(500) NOT NULL,
`sales_pin_code` varchar(50) NOT NULL,
`submission_date` DATETIME ,
`modified_date` DATETIME ,
`modified_by` varchar(100) NOT NULL,
PRIMARY KEY (`sales_id`)
)
|
|
|
|
|
I'd use a text editor. But why would you want to do that anyway?
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
Hello every body , please i need your help with Mango db , i am new with mango , so please if some body of you have already experience , please MSG me .
Thank you in advnce
|
|
|
|
|
That is not how the forum works, there are a bunch of volunteers who try and answer SPECIFIC question on a subject. Maybe you can get a book on MangoDB.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi,
I need to select data from two tables am using the following query:
select c.customer_id ,c.father_name ,c.address,c.phone_number ,c.mobile_number ,c.id_proof ,c.area ,c.ip_address as 'IP Address',c.mac_address as 'MAC Address',c.package_type as 'Package Type',c.name as Name,c.activation_date as 'Activation Date',c.status as 'Status',c.installation_cost as 'Installation Cost',c.totalamount_paid as 'Total Amount Paid',c.monthly_amount as 'Monthly Amount',c.lastpaid_date as 'Last Paid Date',c.lastpaid_amount as 'Last Paid Amount',c.nextpay_date as 'Next Pay Date',c.totaldue_amount as 'Total Due Amount',t.agent_id as 'Agent ID',t.token_number as 'Token Number',t.machine_id as 'Machine ID' from customer c,transaction t where c.customer_id=t.customer_id and DATE(t.paid_date)BETWEEN '2014-02-02'AND '2014-04-02' ;
It's giving correct information but each record is displayed twice , i want a record only once.
|
|
|
|
|
You have two matching rows in the transaction table for each customer, and you've asked SQL to return all of them, so it's hardly surprising that you get two rows back per customer. If you only want the most recent transaction for each customer, there are ways to do that. However, it would help if you told us which DBMS you're using.
Also, try to avoid using old-style joins:
... FROM customer c,transaction t WHERE c.customer_id = t.customer_id And ...
Use an INNER JOIN instead:
... FROM customer c INNER JOIN transaction t ON c.customer_id = t.customer_id WHERE ...
It makes it much easier to separate the joining conditions from the filtering conditions, and to switch between different types of join.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Mysql database we are using.yes my transaction table is having two records per customer id, but paid amounts and other fields are different ,
but here the query is giving last record values for two records having same customerid.
Transaction table doesn't contain primary key,so there exists more than one record per customer id.
and i want most recent record from Database.
modified 3-Apr-14 3:41am.
|
|
|
|
|
I'm not overly familiar with MySql, but something like this should do the trick:
SELECT
...
FROM
customer c
INNER JOIN transaction t
ON t.customer_id = c.customer_id
INNER JOIN
(
SELECT customer_id, Max(paid_date) As LastPaid
FROM transaction
WHERE DATE(paid_date) BETWEEN '2014-02-02' AND '2014-04-02'
GROUP BY customer_id
) t2
ON t2.customer_id = t.customer_id
And t2.LastPaid = t.paid_date
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I guess it's safe to assume that it's monthly payments, and that BETWEEN '2014-02-02'AND '2014-04-02' you'd have two payments per customer_id.
|
|
|
|
|
Where do you display the result? In a .Net application written by you / your colleagues? In MySQL Query browser? ...?
|
|
|
|
|
am generating EXcel/ PDF file,in .net application written by me,
modified 3-Apr-14 5:19am.
|
|
|
|
|
Consequently, the first thing to check is if the data returned from the query to your application are correct, and then where that duplication happens. That's a lot of debugging...
|
|
|
|
|
|
Hi All,
I want to generate a script for an existing database in oracle 10g,
Please let me know how to generate this script using oracle 10g sql navigator interface or using script.
Thank you
|
|
|
|
|
|
I work for an Insurance Company and creating a custom application. When we apply a coverage to a policy it is stored in a table with a Limit, Deductible, and Premium field. However, there are many coverages that do not follow the norm. They use different fields for Limit, Deductible, and Premium and are even stored in different tables. (Don’t ask why and don’t even try to understand the fact that they are STILL doing this so I need to leave logic to allow for additional coverages like this.) There is also no rhyme or reason to what fields they choose so varies greatly.
So, we have decided to create “LookUp” tables that will store all the information we need for a coverage. This avoids numerous joins – we were having issues with speed and performance of the application.
I need to find a way to prepopulate all these fields into a table. I would like the best option performance-wise. This will end up being a Nightly job – so when we receive a new snapshot of data we will run this query and prepopulate/update the values of the table.
Here is an example of my table, it contains the Coverage name (Coverage), the table (File), and the fields for the Limit, Deductible, and Premium. The PolicyNum field is a concatenation of LOB + Policy + Module. I need all three of these fields to join to a policy. I wanted to use a table since they will be adding new coverages. Keep in mind, I created this tables so can add more fields to it.
Here is the table I am referring to:
USE [PolicySummary]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Coverage](
[CoverageID] [int] IDENTITY(1,1) NOT NULL,
[LOB] [nvarchar](255) NULL,
[Coverage] [nvarchar](255) NULL,
[Description] [nvarchar](255) NULL,
[File] [nvarchar](255) NULL,
[Limit] [nvarchar](255) NULL,
[Deductible] [nvarchar](255) NULL,
[Premium] [nvarchar](255) NULL,
[IsMultLimit] [bit] NULL,
[IsMultDeductible] [bit] NULL,
[IsMultPremium] [bit] NULL,
[InsuranceLine] [nvarchar](255) NULL,
[PolicyNum] [nvarchar](255) NULL,
CONSTRAINT [PK_Coverage] PRIMARY KEY CLUSTERED
(
[CoverageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
So in a nutshell, a policy may have the coverage BP0713. I then to look at this table and see that I need to run the following query:
SELECT BYAGVA as limit, BYPPTX as Deductible, BYA3VA as Premium FROM ASBYCPP WHERE BYARTX+BYASTX+BYADNB = @PolNum
The table contains the table name, the 3 fields, and the concatenation for the Policy Number.
Currently, I am looping through each coverage in a policy and selecting the values one at a time in a WHLIE Loop. Works but it is slow and I am hitting the query numerous times just to open the app (numerous units per policy so will be ran for each unit).
SELECT @SQL = N'SELECT @Limit=' + ISNULL(Limit, '''''') + N',@Deductible=' + ISNULL(Deductible, '''''') + N',@Premium=' + ISNULL(Premium, '''''') + N' FROM PHI_PIJ.dbo.' + [File] + N' WHERE ' + PolicyNum + N' = ''' + @PolNum +'''' + N' AND ' + @CovField + N'='''+Coverage +''''
FROM PolicySummary.dbo.Coverage
WHERE Coverage = @Coverage
AND LOB = @LOB
EXEC sp_executesql @SQL, N'@Limit nVarChar(255) output,@Deductible nVarChar(255) output,@Premium nVarChar(255) output', @Limit output, @Deductible output, @Premium output
INSERT INTO #OptCov2 VALUES(@Coverage, @Description, @LOB, @Limit, @Deductible, @Premium)
Can I get help with finding a way to populate my “Lookup” table each night so all I would have to do is “SELECT Limit, Deductible, PREMIUM FROM CoverageLookUp WHERE PolicyNum = @PolicyNum”
Here’s what I’ve got so far. It takes nearly 4 minutes to run the top 1000 records and there are a total of 614,497 records to run.
SELECT @SQL = N'SELECT @Limit=' + ISNULL(Limit, '''''') + N',@Deductible=' + ISNULL(Deductible, '''''') + N',@Premium=' + ISNULL(Premium, '''''') + N' FROM PHI_PIJ.dbo.ASBYCPP' + N' WHERE ' + PolicyNum + N' = ''' + @PolicyKey +'''' + N' AND BYAOTX' + N'='''+Coverage +''''
FROM PolicySummary.dbo.Coverage
WHERE Coverage = @Coverage
AND LOB = 'BOP'
EXEC sp_executesql @SQL, N'@Limit nVarChar(255) output,@Deductible nVarChar(255) output,@Premium nVarChar(255) output', @Limit output, @Deductible output, @Premium output
UPDATE PolicySummary.dbo.CoverageLkup
SET Limit = @Limit, Deductible = @Deductible, Premium = @Premium
WHERE CovAbbrev = @Coverage
AND PolicyKey = @PolicyKey
|
|
|
|
|
Where is the table? 
It sounds reasonable so far.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
I have edited my question to include the script I use to create the table. Sorry about that.
|
|
|
|
|
Ah, good, how about some sample data? Made up values would be fine of course.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
I had issues pasting my entire query so I just pasted the piece inside the loop. It works but seriously takes ways too long. I need to find a way from looping through them all and probably not using dynamic SQL but can't figure out a way yet.
|
|
|
|
|
I can probably help a bit once I can get other things off my mind.
Yes, removing the looping is likely to help quite a bit.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
Dear if you want to learn sql by online free tutorial. this tutorial may be helpful to you.
www.javatpoint.com/sql-tutorial
post your queries on javatpoint forum.
www.javatpoint.com/forum
|
|
|
|
|
Probably wouldn't help me, but maybe the OP.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
Don't forget to create some indexes too, in order to speed things up.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
hey , i have a query that collects data from 3 tables using one inner join and left join but the problem that there are many duplicate rows
<
SELECT tbl_salles_bill.sb_id, ( tbl_salles_bill.sb_price_after_alll_dicount - tbl_salles_bill.sb_paid_money) as Dept,
tbl_customer.cust_fName + ' ' + tbl_customer.cust_mName + ' ' + tbl_customer.cust_lName AS CustomerName, tbl_customer.cust_city, tbl_receiving_money_receipt.rmr_id,
tbl_receiving_money_receipt.rmr_value AS Creditor
FROM tbl_salles_bill INNER JOIN
tbl_customer ON tbl_salles_bill.cust_id = tbl_customer.cust_id and tbl_salles_bill.sb_status='Paid'
left JOIN
tbl_receiving_money_receipt ON tbl_customer.cust_id = tbl_receiving_money_receipt.cust_id and tbl_receiving_money_receipt.rmr_IsPaidForWhat = 'Paid without recieving goods'
WHERE (tbl_customer.cust_id = 6)
thanks for your time.
>
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
