July 26, 2010 at 5:38 am
tasnim.siddiqi (7/26/2010)
Hi,I have also run your code, with the Output Clause. I get the following error
Msg 4104, Level 16, State 1, Procedure stored_proc2, Line 50
The multi-part identifier "Temp_Event_Import.Cert_Status" could not be bound.
This is the reason I used my code, as I was getting similar errors previously.
That was not a good reson to use your code. If you would carefully look into the query I gave, you would find that in the SET part I forgot to use table alias. Also, as I didn't have your table structure I didn't know that there is identity column in Master_Application_List.
Here the query which should work for you (#UpdateResults can't be simply created by "select into" as it will have idenity column as well...):
CREATE TABLE #UpdateResults
(
[AppName] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AppVer] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AIT_ID] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Cert_Status] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Cert_Date] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[App_Status] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Remediation] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[App_ID] [int] NOT NULL
)
UPDATE MAL
SET Cert_Status = TEI.Cert_Status
OUTPUT INSERTED.*
INTO #UpdateResults
FROM Master_Application_List AS MAL
JOIN Temp_Event_Import TEI
ON MAL.AppName = TEI.Discovered_App_Name
AND MAL.AppVer = TEI.Version_Number
AND MAL.AIT_ID = TEI.AIT_Number
WHERE MAL.Cert_Status IS NULL
OR (TEI.Cert_Status = 'Y' AND MAL.Cert_Status = 'N')
SELECT * FROM #UpdateResults
JOIN works here exactly as explained in BOL (I have joined on three columns as it did look logical to me).
The rest of conditions are placed into WHERE clause.
July 26, 2010 at 7:26 pm
Hi,
Just tested your code. It works fine now. But I dont understand, because the only difference is in the way you declared the table. Now you creating a table with create and defining the columns. Previously the way the table (which is a variable) was created was simple and different. Can you explain me the difference, why its working now and where are you declaring an Alias?
Also care to explain the concept of JOIN, with respect to the code u gave me. I just can't see the big picture, but ur code is awesome!
PS:Sorry for bugging so much but im a novice in the field of SQL.
July 27, 2010 at 12:34 am
Hi,
Need help with converting from character string to datetime.
I used the following code:
WHERE ((convert(datetime,TEI.Cert_Date,101) > convert(datetime,MAL.Cert_Date,101)) OR MAL.Cert_Date IS NULL)
The above code fails to do conversion...
July 27, 2010 at 1:24 am
How do you mean 'fails to do the conversion' ? Do you have data that does not match a date format in your 'date' fields? You should really use the correct datatypes.
Additionally, you should avoid any form of function usage with a search argument (SARG) as these will force a non-optimal scan.
Also , it would be better if you started a new thread for each distinct question as you will reach a wider audience. I know that if there is a long running thread and i havent already had some previous input then i tend to ignore it.
July 27, 2010 at 1:54 am
Hi,
In my table, I have a date column but it is of type Varchar (Max). I know it and I want to keep the datatype for the table like this. Challenge is I want to compare which dates are earlier so I want to do a comparison...
so TEI.Cert_Date>MAL.Cert_Date and both are Varchar (Max). I want to keep the actual table the same, yet achieve the above. So since string comparison like this will give erroneous outputs, I did the above string comparison using this:
convert(datetime,TEI.Cert_Date,101) > convert(datetime,MAL.Cert_Date,101)
where 101 refers to style of the datetime, i.e mm/dd/year
TEI.Cert_Date would contain string value that would get converted to datetime and then compared. Similarly for MAL.Cert_Date.
But I am getting error because the input datas, from Cert_Date column, are messed up, they come in the form of string/varchar and has words or NULL value which can't really be converted to date. This is why I am getting error as the case fails for some inputs. Is there anyway I can get around this problem?
July 27, 2010 at 2:00 am
This is the wrong place to be validating data cleanliness and you should be using the correct datatypes. This has many many advantages.
You could test the data using the isdate function http://msdn.microsoft.com/en-us/library/ms187347.aspx
July 27, 2010 at 4:10 am
tasnim.siddiqi (7/26/2010)
Hi,Just tested your code. It works fine now. But I dont understand, because the only difference is in the way you declared the table. Now you creating a table with create and defining the columns. Previously the way the table (which is a variable) was created was simple and different. Can you explain me the difference, why its working now and where are you declaring an Alias?
Also care to explain the concept of JOIN, with respect to the code u gave me. I just can't see the big picture, but ur code is awesome!
PS:Sorry for bugging so much but im a novice in the field of SQL.
1. When you use "select into" it will create almost the copy of the table you are selecting from. As your original table has identity column, using "select into" will make temp table having identity column as well. Explicit insert into the identity column is only allowed when identity insert on a table is switched on and column list supplied. So, if you want to use "SELECT INTO" here, you need do it like this:
SELECT *
INTO #UpdateResults
FROM Master_Application_List
WHERE 1=0
SET IDENTITY_INSERT #UpdateResults ON
UPDATE MAL
SET Cert_Status = TEI.Cert_Status
OUTPUT INSERTED.*
INTO #UpdateResults ([AppName],[AppVer],[AIT_ID],[Cert_Status],[Cert_Date],[App_Status],[Remediation],[App_ID])
FROM Master_Application_List AS MAL
JOIN Temp_Event_Import AS TEI
ON MAL.AppName = TEI.Discovered_App_Name
AND MAL.AppVer = TEI.Version_Number
AND MAL.AIT_ID = TEI.AIT_Number
WHERE MAL.Cert_Status IS NULL
OR (TEI.Cert_Status = 'Y' AND MAL.Cert_Status = 'N')
SET IDENTITY_INSERT #UpdateResults OFF
SELECT * FROM #UpdateResults
2. Table aliases are "declared" straight after the table name in FROM clause (use of "AS" keyword is optional). In the sample I've gave, I've used abbrevations of the table names for the aliases: MAL and TEI. On using aliases: http://www.w3schools.com/sql/sql_alias.asp
3. JOIN is used as supposed to be used. There is nothing special there. It joins Master_Application_List table to Temp_Event_Import on columns which look like logical relationship keys. You will find a lot opf details on how to use different type of JOINs in BOL.
Viewing 7 posts - 61 through 66 (of 66 total)
You must be logged in to reply to this topic. Login to reply