is these two queries same? - Database Discussion Boards

It's an internal command - you can't modify the behaviour of the internal commands otherwise you could end up breaking other functionality that relied on that behaviour.

Forgive your enemies - it messes with their heads

My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility

Could I maybe overload it with another parm?

Humble Programmer

You tell us. Look at the docs for it. They tell you what it does. If it isn't there then it doesn't do it.

http://dev.mysql.com/doc/refman/5.0/en/show.html[^]

Do you have the source-code of the function?

There's your answer Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

Hello CP,

I've set up a job to perform a stored procedure at a given time, nothing special.
In the step (which triggers the SP) I've specified the output file in the 'advanced' section.
The output in the file is the execution time (moment) and the 'select' form the SP.
My problem is the output file format.

Does anyone know how to set it to CSV or perhaps another solution?

Is the goal to get the data output from the stored procedure into a CSV file? That is not possible (in a useable format anyway) in the job step's advanced properties. Specifying a file path and file name there allows SQL to save the detailed information regarding the job's execution to a text file. To get your stored proc output data into a CSV file, a different coding approach is needed (like SSIS, BCP, or the like). Or, did you actually want to get the detailed information about the job running into a CSV file?

Apologies if I've misunderstood your question. Cool | :cool:

modified on Thursday, June 23, 2011 5:41 PM

You understood it perfectly.
I had been looking at BCP and tried to format it correctly (from the command prompt, not sqlcmd), but it bugged on my nested stored procedures.
Haven't looked at SSIS yet, gonna do that now.
But I'm getting off-topic.

You answered my question.

Hi all,

Is there any difference between the following two sets of queries?

select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
                        a.RESIDSTS, d.ROOM_DESC, d.ROOM_PHONE
                        from SMASTER a 
                        join NMASTER b on a.ID_NUM = b.ID_NUM
                        join BMASTER c on a.ID_NUM = c.ID_NUM
                        join RMASTER d on a.LOC_CDE = d.LOC_CDE and a.BLDG_CDE = d.BLDG_CDE and a.ROOM_CDE = d.ROOM_CDE
                        where LOWER(b.LAST_NAME) like '%ram%'
                        and a.RESIDSTS = 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))
 UNION 
select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
                        a.RESIDSTS as STATUS, '' , '' 
                        from SMASTER a 
                        join NMASTER b on a.ID_NUM = b.ID_NUM
                        join BMASTER c on a.ID_NUM = c.ID_NUM
						join RMASTER d on a.ID_NUM = a.ID_NUM
                        where LOWER(b.LAST_NAME) like '%ram%'
                        and a.RESIDSTS <> 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))

and

select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
                        a.RESIDSTS, d.ROOM_DESC, d.ROOM_PHONE
                        from SMASTER a 
                        join NMASTER b on a.ID_NUM = b.ID_NUM
                        join BMASTER c on a.ID_NUM = c.ID_NUM
                        join RMASTER d on a.LOC_CDE = d.LOC_CDE and a.BLDG_CDE = d.BLDG_CDE and a.ROOM_CDE = d.ROOM_CDE
                        where LOWER(b.LAST_NAME) like '%ram%'
                        and (a.RESIDSTS = 'R' or a.RESIDSTS <> 'R') and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))

well in one there is "UNION" used but will there any difference in the output or are these both queries same as per the output is concered?

suchita

Yes, those should give the same result. They might be a bit different sorted I think, but in the end the same data should be in the output.

Edit: I oversaw one small thing. The second query in the union doesn't return the values of d.ROOM_DESC, d.ROOM_PHONE, but rather two empty strings in those columns. In your combined query these columns will be filled.

ya thank you..

suchita

Hi all,

I have two sets of query which if run alone, runs without any errors but when i try to use UNION between these two queries, it gave me error like this:

Error converting data type varchar to numeric.

The sql queries are

select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
                        a.RESIDSTS, d.ROOM_DESC, d.ROOM_PHONE
                        from SMASTER a 
                        join NMASTER b on a.ID_NUM = b.ID_NUM
                        join BMASTER c on a.ID_NUM = c.ID_NUM
                        join RMASTER d on a.LOC_CDE = d.LOC_CDE and a.BLDG_CDE = d.BLDG_CDE and a.ROOM_CDE = d.ROOM_CDE
                        where LOWER(b.LAST_NAME) like '%ram%'
                        and a.RESIDSTS = 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))
 UNION 
select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
                        a.RESIDSTS as STATUS, '' , '' 
                        from SMASTER a 
                        join NMASTER b on a.ID_NUM = b.ID_NUM
                        join BMASTER c on a.ID_NUM = c.ID_NUM
						join RMASTER d on a.ID_NUM = a.ID_NUM
                        where LOWER(b.LAST_NAME) like '%ram%'
                        and a.RESIDSTS &lt;&gt; 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))

but when i run

select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
                        a.RESIDSTS, d.ROOM_DESC, d.ROOM_PHONE
                        from SMASTER a 
                        join NMASTER b on a.ID_NUM = b.ID_NUM
                        join BMASTER c on a.ID_NUM = c.ID_NUM
                        join RMASTER d on a.LOC_CDE = d.LOC_CDE and a.BLDG_CDE = d.BLDG_CDE and a.ROOM_CDE = d.ROOM_CDE
                        where LOWER(b.LAST_NAME) like '%ram%'
                        and a.RESIDSTS = 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))

or

select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
                        a.RESIDSTS as STATUS, '' , '' 
                        from SMASTER a 
                        join NMASTER b on a.ID_NUM = b.ID_NUM
                        join BMASTER c on a.ID_NUM = c.ID_NUM
						join RMASTER d on a.ID_NUM = a.ID_NUM
                        where LOWER(b.LAST_NAME) like '%ram%'
                        and a.RESIDSTS <> 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))

it runs.

Can anyone help me solve this out?

suchita

I figure out that if I take

d.ROOM_PHONE

out from the code, it ran well. But why it gave error in there ? what if I need to keep that piece and get the result without error?

select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
                        a.RESIDSTS, d.ROOM_DESC, d.ROOM_PHONE
                        from SMASTER a 
                        join NMASTER b on a.ID_NUM = b.ID_NUM
                        join BMASTER c on a.ID_NUM = c.ID_NUM
                        join RMASTER d on a.LOC_CDE = d.LOC_CDE and a.BLDG_CDE = d.BLDG_CDE and a.ROOM_CDE = d.ROOM_CDE
                        where LOWER(b.LAST_NAME) like '%ram%'
                        and a.RESIDSTS = 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))
 UNION 
select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
                        a.RESIDSTS as STATUS, '' ,''
                        from SMASTER a 
                        join NMASTER b on a.ID_NUM = b.ID_NUM
                        join BMASTER c on a.ID_NUM = c.ID_NUM
						join RMASTER d on a.ID_NUM = a.ID_NUM
                        where LOWER(b.LAST_NAME) like '%ram%'
                        and a.RESIDSTS &lt;&gt; 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))

suchita

What is the type for the column ROOM_PHONE. From your error, I bet it is numeric. In the second select change the final '' column to be to_number(NULL). That might make a difference. Smile | :)

Chris Meech
I am Canadian. [heard in a local bar]

In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

I have searched on the net for an answer to this, and in Code Project and I can't seem to find a definite answer. Plenty about data types in tables but little or nothing about stored procedure parameters.

I am converting an SQL Server based program to run with MySql and one of the tables accessed by a procedure (routine) has a field that is the char type that is used as one the parameter fields. In procedures in SQL Server there is the SqlDBType.Char but there does not appear to be any equivalent of that in MySql (5.5)

Should I just use VarChar?

char(10) will deliver a string 10 characters long, padded with spaces if there is no data, varchar will deliver just the data. You need to asses whether this change will affect your output. I only use char for single character fields.

Never underestimate the power of human stupidity
RAH

Thanks for responding. I understand the differences between char and varchar but what I am trying to find out is what type I should give a parameter.

I have a char(1) field that accepts data in a routine. In an SQL Server stored procedure I would give this the SqlDBType.Char type. There seems to be no equivalent in MySQL so my question was do I just use MySqlDBType.VarChar?

I think you have to use a varchar, I have no experience with MySQL, but you need to be aware that the field could end up as a null or an empty string which it cannot do if it were a char. So your client needs to be aware of this and deal with it. Reality is that you are probably using the field as a flag and it wall ALWAYS have a value.

Never underestimate the power of human stupidity
RAH

Pretty much what I was thinking. Thanks for you help.

I am currently writing an application which connects to a Microsoft Access 2007 database. After doing a bit of research, I have decided (and I have been given the go-ahead) to work on transferring everything over to an installation of Microsoft SQL Server 2008 Express. I am successfully able to import the existing data into SQL Server via SQL Server Management Studio Express. I set up all the table relationships and so forth.

My question is in regards to the relationships. There is one main table, which is the basis of the application's data. The fields in that table, Repairs, include relationships to other tables (e.g. [Failure Codes]). In Access, the linked fields are setup as Lookup columns, pulling data from the appropriate tables. The fields display the [Failure Description] field (text) in the Repairs table, but of course the field's value is the [ID] (Primary Key) from the [Failure Codes] table.

Is there any way to do the same in SQL Server? This is not the most important issue. It is simply a curiosity.

The main thing is that my boss is most comfortable with Access. He has never really worked with SQL Server. I told him how we can create an Access database on his computer which will link to all the appropriate SQL Server data (I tested it, it works perfectly). However, when he views the Repairs table he is seeing the [Failure Codes].[ID] value instead of how it was before, displaying the [Failure Codes].[Failure Description] field text while storing its value as the [ID]. Is there a way to change this? He often runs queries to find records matching criteria such as a date or date range. And when he does, Access pulls the [ID] instead of the description text.

The main reason I ask is for the fact that he generates reports periodically. I know he uses the Report Wizard in Access. However, when doing so, the report displays the [Failure Code] column's values for each record as the [ID] rather than the [Failure Description]. Now, I know how to add more fields and such to the report, as I am sure he does as well. But are we missing something? Or is it best to put the fields in manually, in order for them to look up the appropriate values?

You will have to develop forms/queries etc to display the data as you would like to see it. Access allows you to cheat a little by automating this from within the table design.

As an aside, you should NEVER EVER EVER let someone have direct access to the tables - it's a recipe for disaster...

Silence is golden... but duct tape is silver!!

Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!

I figured it was an Access "cheat" rather than some sort of standard so to speak. But it didn't hurt to ask. I will just make sure he is aware of the fact that he will need to modify the reports a bit to show the data that he wants. Well, I guess a query, as you suggested, would come into play there as well, correct? I mean, create a report that pulls its data from the query, which displays the data as he would like to see it?

The only people who have direct access to the raw data (tables and the like) are my boss and myself. He created the original Access database which I am transferring over to SQL Server. The rest of the employees will use the application I am writing.

Sorry, I'm quite new to the more intermediate-advanced database techniques and such. Smile | :)

Nothing to be sorry about - everyone has to start somewhere!!

For a report, you can create a query that has the data as you want it (ie: link the two tables together in the query, and instead of showing the ID field, show the related field that has the text description). This has the benefit that if you export the query to Excel the data will still be formatted correctly.

On a form (this works for a report also, but you don't get the benefit with exporting that I referred to), you would have a query that returns the raw data for the form, then a combo box (with its own query to get the data from the related table). Set the data source for the combo to the related query, and bind the combo box to the id field in the main table.

Hope this points you in the right direction!

Silence is golden... but duct tape is silver!!

Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!

Alright, thanks for the help! I understand completely, it makes sense entirely. I can probably handle it from here, at least for awhile. But I'll return if anymore assistance is needed.

Good for you Matt, you have a learning curve in front of you but it will definitely be worth it. When you have the database and app working it will be a natural step to move the report(s) to a proper platform (SSRS or Active etc, NOT Crystal Reports).

Never underestimate the power of human stupidity
RAH

Now that you have the data in SQL Server you need to create the Views (these are something like queries). In the view you have the main table and add in all the tables that have foreign key (lookup) connections to it. If you use the view designer SQL will create the link for you.

It is important that when creating a view the view has the same number of records as the transaction table, this tells you that your joins are correct. Then just add in the description fields you want the user to see.

The access app should use the views instead of the tables to see the data.

Never underestimate the power of human stupidity
RAH