I went for the later in this function, but found out later that I could of just declared and set all the card data first
and then run the UPDATE using the variables.
So I ended up with this, I shortened it to keep it more brief
UPDATE " & _
OrderInfo " & _
, Cardlabel=(SELECT Label FROM CardInfoPerm WHERE CardID=@CardID)
, CardBrand=(SELECT CardBrand FROM CardInfoPerm WHERE CardID=@CardID)
, CardExpMonth=(SELECT CardExpMonth FROM CardInfoPerm WHERE CardID=@CardID)
, CardExpYear=(SELECT CardExpYear FROM CardInfoPerm WHERE CardID=@CardID)
But later the next day I ended up writing something that does everything in one shot, far beyond I could imagine that I can do. So I modified the above to this below
DECLARE " & _
, @CardBrand VarChar(80)
, @CardExpMonth VarChar(80)
, @CardExpYear VarChar(80)
, @New_OrderNumber VarChar(80);
SET @CardLabel = (SELECT Label FROM CardInfoPerm WHERE CardID=@CardID);
SET @CardBrand = (SELECT CardBrand FROM CardInfoPerm WHERE CardID=@CardID);
SET @CardExpMonth = (SELECT CardExpMonth FROM CardInfoPerm WHERE CardID=@CardID);
SET @CardExpYear = (SELECT CardExpYear FROM CardInfoPerm WHERE CardID=@CardID);
IF EXISTS(SELECT * FROM OrderInfo WHERE OrderNum=@OrderNum)
Then, I was able to do more,
delete previous cart items,
generate a new order number,
copy the cart contents over
return the new order number
all in one shot now, use to take me like 8 functions to do it all. I'm amazed at how fast it runs now.
I would post it, but it's too large
I'm stoked on how it came out, and learned a lot on my own. I think I got the semi-colons right this time, I need to read up on that.
I have created a UNION query as the source for a report that is written to a Word Document using VBA. The UNION joins a Table (historical information from before the current database Application was written) and the output of a Query that extracts corresponding information from the live data. I have recently added a new text field to both 'sides'. The value of the text field for the Query (either "S" or "") is generated in an IIF() statement within it.
When I Open the UNION query via the Access interface, it works as intended - the new text field is set to the correct value for records derived from either source. When I open a ADO Recordset based on the UNION query in VBA, the records derived from the Table are correct, but the contents of the new field in the records derived from the Query is always a Null String (N.B. not Null). If I convert the Query which is the second source of the UNION into a Make Table Query, run it, and then UNION the two Tables, everything again works as it should. Does this make any sense to anyone?
How to execute the below piece of code correctly. Please help me.
if (@Id = 0)
BEGINSELECT PatchAppId, PatchName,HostName FROM Vw_ProductionAutoMailData1
endelsebeginSELECT PatchAppId, PatchName,HostName FROM Vw_ProductionAutoMailData2
Hi I have legacy database that contain millions of records it has a column (DATA TYPE IS nvarchar) in database that has values in four date formats (5/29/2014) and (2014-05-15) and (16/4/2014) and (Apr 7 2014 4:42PM)
I want to convert it into (16/04/2014). When i use convert function it gives error message
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the nvarchar value 'Apr 7 2014 4:42PM' to data type int. We have so many scattered format so we cannot run a single query for specific data. so kindly provide solution for this problem.
these formats i have been using this statement to convet data.
select CONVERT(varchar(10), CONVERT(date,FormReceivedDate, 101), 103) from Applicant where FormReceivedDate=1
The other two formats will be impossible to convert unless your data contains another field indicating whether they're dd/MM/yyyy or MM/dd/yyyy. Without that additional field, there would be no way to know whether 01/02/2014 should be 1st February or 2nd January.
If you do have an additional field indicating the format of the date, then your options depend on the version of SQL that you're using. SQL 2012 and 2014 have the PARSE method[^]:
If you convert the garbage you have to a single string format and store it as varchar you are perpetuating the same garbage as you are having to deal with now. USE THE DATETIME DATA FORMAT.
And yes I did mean to shout.
You might need to do a number of passes through the data using the suggestion from the other replies to convert the data. Records that are ambiguous may require the mark I eyeball to get the context of the date from other data in the record.
Altogether a thoroughly nasty job 100% caused by not storing the data in the correct format. I know it is a legacy app but if you can add new fields in the correct format then your life will be possible and the users cannot repeat the input of garbage.
Never underestimate the power of human stupidity
I came across a problem when I loaded my records from SSIS to SQL DB.
My source file is a txt file.
I use 5 fields to make my records unique but unfortunately there are some records that can't be made unique.
Even the records that aren't unique must be loaded into my table although in SSIS the duplicate records are written to an error log and are excluded out of the load into the DB.
This is a small example of a few records:
record 1 and 2 are unique, 3 and 4 not.
In SQL I use a View to transform the data and load only the data I need from the txt file:
CAST(NULLASNumeric(8, 0)) AS DT_Start_Dates
, CAST(NULLASNVARCHAR(6)) AS TI_Start_Times
, CAST(NULLASNumeric(8, 0)) AS DT_Code_Dates
, NULLAS FK_Article
, NULLAS FK_FlowType
, CAST(CAST(RIGHT(dbo.AD.DATE_CREATED, 4) + SUBSTRING(dbo.AD.DATE_CREATED, 4, 2)
+ LEFT(dbo.AD.DATE_CREATED, 2) ASint) ASNumeric(8, 0)) AS CREATE_DATE
, CAST(dbo.LPAD(LEFT(dbo.AD.TIME_CREATED, 2) + SUBSTRING(dbo.AD.TIME_CREATED, 4, 2)
+ RIGHT(dbo.AD.TIME_CREATED, 2) , 6, '0') ASNVARCHAR(6)) AS CREATE_TIME
, CAST(CAST(RIGHT(dbo.AD.CODE_DATE_RECEIVED, 4) + SUBSTRING(dbo.AD.CODE_DATE_RECEIVED, 4, 2)
+ LEFT(dbo.AD.CODE_DATE_RECEIVED, 2) ASint) ASNumeric(8, 0)) AS CODE_DATE
, CAST(dbo.AD.VENDOR_ID ASnvarchar(255)) AS Vendor_ID_Cd
, CAST(CASEWHEN dbo.AD.WH ISNULLTHEN0ELSE dbo.AD.WH ENDASnvarchar(255)) AS WH_Cd
, dbo.AD.PO AS BK_PO_Cd
, dbo.AD.RECEIPT AS BK_Receipt_Cd
, CAST(dbo.Devide_Shipping_Unit(dbo.AD.SHIPPING_UNIT, dbo.AD.ARTICLE) ASNvarchar (255)) AS BK_Shipping_Unit_Cd
, dbo.AD.SSCC_NR AS BK_License_Plate_Cd
, dbo.AD.ARTICLE AS BK_Article_Cd
, CAST(dbo.Devide_Shipping_Unit(dbo.AD.SHIPPING_UNIT, dbo.AD.ARTICLE) ASNvarchar (255)) AS SHIPPING_UNIT
, CASE DWH.dbo.D_Article.FlowType WHEN'SAP'THEN ISNULL(CAST(dbo.AD.QTY_PBS ASint), 0)
ELSE ISNULL(CAST(dbo.AD.QTY_PBL ASint), 0) ENDAS M_Nr_Of_Colli
, CAST(ISNULL(dbo.AD.CW, 0) ASnumeric(15,2)) / 100AS M_Received_Weight
, 1AS M_Nr_of_Pallets
(SELECT dbo.GETRESTARTTIMESTAMP(N'F_Received_Colli') AS restartTS) AS tmp CROSSJOIN
DWH.dbo.D_Article ON (dbo.AD.ARTICLE = DWH.dbo.D_Article.BK_Article_Cd) and CAST(dbo.Devide_Shipping_Unit(dbo.AD.SHIPPING_UNIT, dbo.AD.ARTICLE) ASint) = Cast(DWH.dbo.D_Article.BK_Unit_Ship_Case_Cd ASint)
WHERE (dbo.AD.HEADER = '1010')
The fieldNames starting with BK_ are the fields I'm using to make a record unique. So I have 5 BK fields to make a record unique in the View:
I think I can solve this by sorting on the BK's and then checking if the record exists.
If the record exists then the amount of field "QTY_PBS" should be added to the first record.
In case of my example it would be 1 record with an amount of 20 instead of 2 records with amount 4 and 16.
I tried to create a SP to resolve this issue but I'm failing misserably (lack of knowledge).
Can somebody please help me with this???
/****** Object: StoredProcedure [dbo].[SP_Check_Duplicates] Script Date: 5/08/2014 16:19:41 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE Check_Duplicates
Set @License_Plate = '054001090102087337'
Set @Article = '0006681'
Set @PO = '0861819693'
Set @Receipt = '71663'
Set @M_Nr_Of_Colli = 16
IF EXISTS (SELECT [M_Nr_of_Colli] FROM F_Received_Colli_VW
WHERE [BK_License_Plate_Cd]=@License_Plate AND BK_Article_Cd = @Article)
INSERT INTO F_Received_Colli_VW VALUES(@License_Plate,@Article,@PO, @Receipt, @M_Nr_Of_Colli)
PRINT 'New Record Insert Successfuly'
PRINT 'Id All Ready Exist'
Could you help figure out why my code not working?
DECLARE @xmlPolicyType XML
SET @xmlPolicyType= REPLACE('<?xml version="1.0" encoding="utf-16"?>
DECLARE @xmlHandle INT
IF NOT @xmlPolicyType IS NULL
DECLARE @PolicyTypes TABLE
/* Create the XmlDocument */
EXEC sp_xml_preparedocument @xmlHandle output, @xmlPolicyType
/* Use the OPENXML method to query the XmlDocument starting at /NewDataSet/PolicyType node. */
--INSERT INTO @PolicyTypes
FROM OPENXML (@xmlHandle, '/NewDataSet/PolicyType',1)
WITH ( PolicyType CHAR(5) '@PolicyType' )
/* Remove the document from memory */
EXEC sp_xml_removedocument @xmlHandle
SELECT t.value('(PolicyType/text())','nvarchar(120)')AS PolicyType
FROM @xmlPolicyType.nodes('/NewDataSet/PolicyType')AS TempTable(t)
Your OPENXML query doesn't work because the node value contains white-space, which is not getting trimmed. Since you're only taking the first 5 characters, you'll get a string containing nothing by white-space.
Your .nodes() query doesn't work because you're getting the PolicyType nodes, and then trying to find the third PolicyType node within that node.
SET @xmlPolicyType= REPLACE('<?xml version="1.0" encoding="utf-16"?>
, Char(13), ''), Char(10), ''), Char(9), ''))
FROM@xmlPolicyType.nodes('/NewDataSet/PolicyType') As T (PolicyType)
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Last Visit: 31-Dec-99 18:00 Last Update: 25-Sep-22 22:50