Stefan Motz
asked on
SQL Syntax, Function, Insert data
Hi Experts,
I select the following 3 columns from two tables:
Table1.AliasName
Table1.HostName
Table2.ProductType
I'm inserting these 3 columns to Table3, where I added a new column, namely ProductId.
So, Table3 has 4 columns: AliasName, HostName, ProductType, ProductId
The content of the ProductId column has to be generated based on what my query returns from the AliasName, HostName, ProductType columns.
For this I would need a function.
This it the pseudo code:
Is ProductType "Vegetable" OR "Dairy" AND HostName start with "PACKAGE"
If/Validate/Parse that AliasName is 6 HEX digits (0-F)
Return "AAA.BBB.CCC.AAAAAA.1"+Ali asName
Is ProductType "Fruit", "Meat", "Grocery" or "Plants"
If HostName starts with "USA"
If/Validate/Parse AliasName is 6 NUMERIC (0-9) digits
Return "AAA.BBB.CCC.BBBBBB.1"+Ali asName
Is HostName start with "PACKAGE"
If/Validate/Parse that AliasName is 6 HEX digits (0-F)
Return "AAA.BBB.CCC.CCCCCC.1"+Ali asName
If/Validate/Parse that AliasName is 5 or 7 UPPERCASE ALPHA (A-Z)
Return "AAA.BBB.CCC.DDDDDD.1"+Ali asName
What is the correct syntax of the query to achieve this. I would appreciate your help.
I select the following 3 columns from two tables:
Table1.AliasName
Table1.HostName
Table2.ProductType
I'm inserting these 3 columns to Table3, where I added a new column, namely ProductId.
So, Table3 has 4 columns: AliasName, HostName, ProductType, ProductId
The content of the ProductId column has to be generated based on what my query returns from the AliasName, HostName, ProductType columns.
For this I would need a function.
This it the pseudo code:
Is ProductType "Vegetable" OR "Dairy" AND HostName start with "PACKAGE"
If/Validate/Parse that AliasName is 6 HEX digits (0-F)
Return "AAA.BBB.CCC.AAAAAA.1"+Ali
Is ProductType "Fruit", "Meat", "Grocery" or "Plants"
If HostName starts with "USA"
If/Validate/Parse AliasName is 6 NUMERIC (0-9) digits
Return "AAA.BBB.CCC.BBBBBB.1"+Ali
Is HostName start with "PACKAGE"
If/Validate/Parse that AliasName is 6 HEX digits (0-F)
Return "AAA.BBB.CCC.CCCCCC.1"+Ali
If/Validate/Parse that AliasName is 5 or 7 UPPERCASE ALPHA (A-Z)
Return "AAA.BBB.CCC.DDDDDD.1"+Ali
What is the correct syntax of the query to achieve this. I would appreciate your help.
ASKER
Thank you for your quick response. I'm still trying to make it work.
My 4th column name has to be called "ProductId" and the record stored in it has to have the following format:
AAA.BBB.CCC.AAAAAA.1.XXXX
where XXXX is the AliasName
Is the syntax correct if after THEN I write 'AAA.BBB.CCC.AAAAAA.1.+Ali asName' ?
Or is it correct if I write
THEN 'AAA.BBB.CCC.AAAAAA.1.'+Al iasName ?
At the end I should write: END AS ProductId, right?
My 4th column name has to be called "ProductId" and the record stored in it has to have the following format:
AAA.BBB.CCC.AAAAAA.1.XXXX
where XXXX is the AliasName
Is the syntax correct if after THEN I write 'AAA.BBB.CCC.AAAAAA.1.+Ali
Or is it correct if I write
THEN 'AAA.BBB.CCC.AAAAAA.1.'+Al
At the end I should write: END AS ProductId, right?
Yes, you've got it exactly right. Concatenate AliasName and specify the desired column name after the "AS".
WHEN ...
THEN 'AAA.BBB.CCC.AAAAAA.1.' + AliasName
...
END AS ProductID
WHEN ...
THEN 'AAA.BBB.CCC.AAAAAA.1.' + AliasName
...
END AS ProductID
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry, I viewed it from my cell phone and accidentally I clicked the wrong button. Are the points awarded now? I apologize, my mistake.
Yes, sorry, didn't realize you used your phone, that really is rather tricky. It's all good now.
CASE
WHEN ProductType IN ('Dairy', 'Vegetable') AND
HostName LIKE 'PACKAGE%' AND
AliasName LIKE '[0-9ABCDEF][0-9ABCDEF][0-
THEN 'AAA.BBB.CCC.AAAAAA.1'
WHEN ProductType IN ('Fruit', 'Grocery', 'Meat', 'Plants') AND
HostName LIKE 'USA%' AND
AliasName LIKE '[0-9][0-9][0-9][0-9][0-9]
THEN 'AAA.BBB.CCC.BBBBBB.1'
WHEN HostName LIKE 'PACKAGE%' AND
AliasName LIKE '[0-9ABCDEF][0-9ABCDEF][0-
THEN 'AAA.BBB.CCC.CCCCCC.1'
WHEN AliasName COLLATE Latin1_General_CS_AS LIKE '[A-Z][A-Z][A-Z][A-Z][A-Z]
AliasName COLLATE Latin1_General_CS_AS LIKE '[A-Z][A-Z][A-Z][A-Z][A-Z]
AliasName COLLATE Latin1_General_CS_AS LIKE '[A-Z][A-Z][A-Z][A-Z][A-Z]
THEN 'AAA.BBB.CCC.DDDDDD.1'
END AS AliasName