|
i have 3 tables named as patient, medicine, attachment
patient table has columns name as
patient_id patient_name
medicine tables has columns
med_id medicine_name patient_id
and attachment has columns
attachment_id attachment patient_id
now i want data as
patient_name medicine_name attachment
where attachment_table has 2 or more columns who have same patient_id
same as happn with attachment table
but the main problem is
i want to get all the data into a single row
even if attachment has particular patient or no
same as if medicine table has particular patient_id or no
so can any one help me in it
|
|
|
|
|
Patient
Patient_id
Patient_name
Medicine
med_id
medicine_name
patient_id
attachment
attachment_id
attachment
patient_id
Let's use "Coffee" as a sample medication; if both you and me were to be medicated with coffee, the medicine would need to be inserted into the "medicine" table twice. That's a modelling error.
Arun kumar Gautam wrote: now i want data as
patient_name medicine_name attachment
I'd suggest you first fix your database-model. You can try Normalization[^] to correct it, or hope that someone else comes up with a different design.
FWIW, it'd be best to register which patient takes which medication in a separate table. Once you have that, it's easy to join the tables and display them in a single row.
Further, I'd also recommend to change the naming-pattern of the fields. As is, the name of the table is often repeated in the fieldname, wich is redundant.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi Arun kumar
Could you provide sample data for understanding purpose and from that how you want the data could you give sample output
Regards,
Prakash.ch
Prakash.ch
|
|
|
|
|
I know how to make a Stored Procedure that its data affect in one TABLE.
The problem is that I'm making a three tier project, and I'm inserting, Updating Reading and Deleting data to tables using Stored Procedure.
My question is, how to make a tored procedure that its data are affecting two tables?
For example. TABLE1 has coulmns Name and Age, and TABLE2 has columns Year and Time.
How to programm one procedure using parameters of two tables.
P.S. Is it possible to create a procedure that its data can be stored in two tables?
Thank you in advance for your time.
|
|
|
|
|
Have no idea what database program you are using so I will speak about MS SQL Server.
A stored procedure may touch many tables. One set of data may be put into many tables.
If Table1 and Table2 do not have a shared column (PersonID for example) it becomes very hard to join the two.
|
|
|
|
|
I solved the problem. Thank you a lot.
I used the nested BEGIN and END between two tables.
Cheers.
|
|
|
|
|
What nested BEGIN and END..? used in your Stored Procedure.
|
|
|
|
|
I want to insert the sql Error Message into the Error Table and return the user defined Error Message. Below is my code, if it sucess then Return Null else it should return Error message like 'Error on Inserting' at the same time it should save the actual error details in the Error table
Create PROCEDURE addTitle
AS
SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION
Create table #temp(ID int,fName varchar(20))
Insert into #temp(ID,fName)values('a1','test')
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
Insert into apl_Error(Error_Desc,Error_Date) select RTrim(LTrim(Cast(ERROR_STATE() as CHAR(10))))+','+ RTrim(LTrim(Cast(ERROR_LINE() as CHAR(5))))+','+ERROR_PROCEDURE()+','+ERROR_MESSAGE(),GETDATE()
END CATCH
Return
|
|
|
|
|
You will need a "translation" table that gives the message you want based on the error.
|
|
|
|
|
Hi,
Try like this... If you need add TRANSACTION also in this Code.
CREATE PROCEDURE addTitle
@ErrorDtls VARCHAR(100) OUTPUT
BEGIN
BEGIN TRY
Create table #temp(ID int,fName varchar(20))
Insert into #temp(ID,fName)values('a1','test')
SELECT @ErrorDtls = NULL
END TRY
BEGIN CATCH
Insert into apl_Error(Error_Desc,Error_Date)
SELECT RTrim(LTrim(Cast(ERROR_STATE() as CHAR(10))))+','+ RTrim(LTrim(Cast(ERROR_LINE() as CHAR(5))))+','+ERROR_PROCEDURE()+','+ERROR_MESSAGE(), GETDATE()
SELECT @ErrorDtls ='Error on Inserting'
END CATCH
END
DECLARE @ErrorDtls VARCHAR(100)
EXEC addTitle @ErrorDtls=@ErrorDtls OUTPUT
SELECT @ErrorDtls
GVPRabu
|
|
|
|
|
Hi
First create table
CREATE TABLE apl_Error
(
Error_Desc VARCHAR(MAX),
Error_Date DATE
)
After that create this procedure
CREATE PROC SAMPLE
@ERROR_DESC VARCHAR(MAX) OUTPUT
AS
BEGIN
BEGIN TRY
CREATE TABLE #TABLE(ID int,fName varchar(20))
INSERT INTO #TABLE VALUES('A1','CHOICE')
SELECT @ERROR_DESC=NULL
END TRY
BEGIN CATCH
INSERT INTO apl_Error(Error_Desc,Error_Date)
SELECT cast( RTRIM(LTRIM(CAST(ERROR_STATE() as CHAR(10)))) +','+ RTRIM(LTRIM(CAST(ERROR_LINE() AS CHAR(5))))+','+ERROR_PROCEDURE()+','+ERROR_MESSAGE() as varchar(max)),GETDATE()
SELECT @ERROR_DESC='Error on Inserting'
END CATCH
END
to execute this procedure
DECLARE @NAME VARCHAR(MAX)
EXEC SAMPLE @ERROR_DESC=@NAME OUTPUT
SELECT @NAME AS Error_Description
then see apl_Error table now you can see the message and date.
Error_Desc Error_Date
1,8,SAMPLE,Conversion failed when converting the varchar value 'A1' to data type int. 2013-07-05
|
|
|
|
|
TSQL is my biggest weakness here. I really don't enjoy writing it but you have to know it to get things done.
I'm looking for some help here, on making a better search system, and I'm not sure which direction to go. I guess at first, I should get the TSQL right, or better at least.
I have a FTS catalog with partNumber, Title, Description, VendorName for the productInfo table.
I wrote this 2 years ago, but it's pretty weak, and doesn't work well.
For instance, if I search part number "06-CR10", it produces nothing, but if I search "CR-10", it produces a result.
I have trouble with 2 words or phrases like "tile spacers" using CONTAIN, but it works fine using FREETEXT.
my question is:
Should I be detecting multiple words, and use different TSQL based on word counts? Or just better TSQL?
I'm just looking for suggestions.
Dim mySelectQuery As String = "SELECT " & _
"ProductID, Category, SubCategory, ShortDescription, LongDescription, Thumbnail, PartNumber, Price " & _
"FROM ProductInfo WHERE " & _
"FREETEXT(ShortDescription, @Query) " & _
"OR FREETEXT(LongDescription, @Query) " & _
"OR FREETEXT(ProductHTML, @Query) " & _
"OR FREETEXT(PartNumber, @Query) " & _
"ORDER BY RANK() OVER (Order BY PartNumber, ShortDescription, LongDescription) DESC"
|
|
|
|
|
Short answer: use both CONTAIN and FREETEXT.
Less short answer: The idea is to call a PROCEDURE which complements the use of the higher energy union of the two methods.
Until discovering INFORMATION_SCHEMA ("Views") I was adhereing, for many years, to using LIKE without even considering FULLTEXT search-enabled tables, if you can believe that. Besides the overhead requirement of LIKE and the stored procedure call (yeah, using "String" would be ok), I had only the sys.x catalog from which to draw the associations into a database relationship.
Best answer: hey, whatever works for you.
|
|
|
|
|
Sounded complex on the fist reading. But very informative.
I'll do some more reading on some of the words you used, to learn more about it.
But in the meantime, I experimented with FREETEXT, CONTAINS and LIKE an sort of solved my partNumber issue by using LIKE for the partNumber, so now the part number works.
Dim mySelectQuery As String = "SELECT COUNT(ProductID) FROM ProductInfo WHERE " & _
"FREETEXT(ShortDescription, @Query) " & _
"OR FREETEXT(LongDescription, @Query) " & _
"OR FREETEXT(ProductHTML, @Query) " & _
"OR PartNumber LIKE @Query2"
I will try searching each column with specific ways to obtain the result I'm looking for and keep refining it for now.
|
|
|
|
|
Also, use the BOL (Books on Line) for ssmse. Specifically look up INFORMATION_SCHEMA. Chock full of pre-associations the View approach to sys.objects is.
Ironically, it was the "SPECIFIC_CATALOG" of "View" (really I still don't quite comprehend what that word means ...) that happened concurrently with my first attempt to use FULLTEXT indexes, and quick succeed, that made me toss my entire search system at the time.
But yeah, use all three; an even better idea.
|
|
|
|
|
Thanks
I'll take a peek first, might be overwhelming to comprehend, but I should be able to absorb some of it.
|
|
|
|
|
I have richtextbox and i save content of it into sql database, now i want to encode string and represent as html encoded in rdlc report.
Any help?
|
|
|
|
|
hi experts,
my stored procedure is very big...i want to debug it..when i start debugging it , i am getting the following error
failed to start debugger
additional information:
error hresult e_fail has been returned from a call to a com component.(mscorlib)
i googled regarding that...i am not able to debug...
my db is remote sql server 2008 r2
coud anyone help me pls
|
|
|
|
|
Member 8701813 wrote: my stored procedure is very big.
That's a design-problem. Cut it into smaller pieces that are easier to debug/verify.
Member 8701813 wrote: i am not able to debug...
It means that the debugger doesn't work; I'd suggest running the sproc and see where it fails, and with what error. Past that part in a post here, and we'll have a look 
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
hi,
you are telling that your sp is big instead of at one time u debugging can u cut that sp and check which part is throwing error in your Sp because i had also tried like that only when i faced this situation. I think it will help you.
Regards,
S Azarudhin
|
|
|
|
|
see my trick posted in code project, it may help you, however you will have to modify the existing code to call the logger on all the places where you feel the exception handling is weak or may need some values to trace the reason.
Logging error, debug, info messages into log tables
Sudhir Kankal
|
|
|
|
|
try to print messages over the screen it really helps you!!!!
Mani Prabhakar
|
|
|
|
|
The System.Data.OracleClient in .NET is now obsolete (deprecated).
So I installed the ODP.NET from Oracle (I have both 4.112.3.0 and 2.112.3. versions of Oracle.DataAccess.dll).
When running VS, I see both under the Add References (not really sure of what the difference between version).
I am using .NET Framework 4.0 so I included 4.112.3.0 version in the project.
Problem is that the Microsoft and .NET framework versions yield different results.
For instance the Oracle version won't permit || in the data set (aka SELECT A || B || C || 'x' as Dummy from ...) - have to use Concat(Concat (A, B), C), ... etc to make valid table adapter SQL statement in the ODP.NET version.
Additionally, it appears not to function identically so worried about potential errors being introduced in the datasets.
Is there any clear article identifying the differences between the two libraries (most articles say just to replace but that is the issue, they are not identical).
|
|
|
|
|
Dale Haessel wrote: I have both 4.112.3.0 and 2.112.3. versions of Oracle.DataAccess.dll
The first number in the Oracle ODP version indicates which framework version it's intended for.
Dale Haessel wrote: For instance the Oracle version won't permit || in the data set (aka SELECT A ||
B || C || 'x' as Dummy from ...) - have to use Concat(Concat (A, B), C), ... etc
to make valid table adapter SQL statement in the ODP.NET version.
I never had that problem, what error message do you get?
Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln
|
|
|
|
|
Using VS dataset / table adapter design wizard, it gives:
The wizard detected the following problems when configuring the TableAdapter: "RCM_MRO_BOM": Details:
Generated SELECT Statement
Error in SELECT clause: expression near '|'.
Missing FROM CLause.
Error in SELECT clause: expression near ','.
Unable to parse query text.
...
Wizard Configuration Text =
SELECT BOM_REF_KEY, DESCRIPTION, BOM_REVIEWED, COMMENTS || ' ' as X, 1 as MatchType FROM SAPRCM.RCM_MRO_BOM a
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
