We help IT Professionals succeed at work.

Powershell SQL Select Command to Combine Two Columns with Plus (+) Operator

uwsppm
uwsppm asked
on
I just want to do a simple join of two columns in a select statement to create a new column, but the plus (+) operator doesn't seem to work inside of a select statement in Powershell.


$temp = "prsn_phone_area_cd + prsn_phone_phone_txt"
@"
Select $temp
    FROM prsn_phone
"@

This results in this error:
Exception calling "Fill" with "1" argument(s): "ORA-01722: invalid number
"

$temp = "prsn_phone_area_cd + prsn_phone_phone_txt"
@"
Select $temp
    FROM prsn_phone
"@

This results in this error:
Exception calling "Fill" with "1" argument(s): "ORA-01722: invalid number
"

Open in new window

Comment
Watch Question

Author

Commented:
This is against a SQL database.
Does this work?

$temp = "[prsn_phone_area_cd] + [prsn_phone_phone_txt]"
@"
Select $temp
    FROM prsn_phone
"@

Open in new window

Author

Commented:
I mean against an Oracle DB.  Sorry about that.

Author

Commented:
Unfortunately, adding the [] does not work:
Exception calling "Fill" with "1" argument(s): "ORA-00936: missing expression"
Sorry don't use Oracle only SQL here so you'll need to figure out the right transaction format to get it to work, anything that works in a normal query should work in a PowerShell based query as well though.

Author

Commented:
That is the problem - I know how to make it work in the normal Oracle SQL queries - the plus (+) doesn't work inside of powershell in the SQL query.
I'm really sorry I can't help you further with this due to lack of using Oracle whatsoever, I have a query like this running via PowerShell against SQL but as you pointed out they are two different things.

Maybe someone else can help you instead.

Author

Commented:
And you use the [] square braces successfully? Is that the example you gave me?
Yeah square braces seem to work OK for me in my queries.

This works for me no problem for example:

$DatabaseServerName = "ENTERSERVERNAME"
$DatabaseName = "ENTERDATABASENAME"

#SQL Connection
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server=$DatabaseServerName;database=$DatabaseName;trusted_connection=True;Connection Timeout=120;"

#Open Connection
$Connection.Open()

$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = "
SELECT
[DriveLetter] + [DriveClass]
FROM Disks"

$Reader = $Command.ExecuteReader()
	
$DataTable = New-Object System.Data.DataTable 
$DataTable.Load($Reader)

$DataTable

#Close Connection
$Connection.Dispose()

Open in new window

Nice one, changed stuff about a bit. Functions should generally be above all other code because PowerShell plays the code in the order it's presented rather than compiling it.

Fixed up your mismatched names for things, as you'd taken some of my examples quite literally and used the same names rather than renaming based on what you were doing.

Put all the customisation stuff generally into a section near the top called #Customise Vars. Please replace my values in there with your own correct ones.

You may also need to adjust a couple of the other bits such as the SQL query that is used.

I have tested this and it works fine for me:

Function ConvertDatatableTo-HTML
{
param
(
	$Datatable
)
	#Column Data
	$ColumnCount = $Datatable.Columns.Count
	$Columns = $Datatable.Columns | % {$_.ColumnName}

	#Start Table
	$Code = "<table>"
	
	#Columns
	$Code += "<tr>"
	foreach ($Column in $Columns)
	{
		$Code += "<th>" + $Column + "</th>"
	}
	$Code += "</tr>"
	
	#Rows
	foreach ($Row in $Datatable)
	{
		$Code += "<tr>"
		
		for ($i = 0; $i -lt $ColumnCount; $i ++)
		{
			$Code += "<td>" + $Row.Item($i) + "</td>"
		}
		
		$Code += "</tr>"
	}
	
	#End Table
	$Code += "</table>"
	
	#Return the Table HTML
	return $Code
}

#Customise Vars
$DatabaseServerName = "DBSERVERNAME"
$DatabaseName = "DBNAME"
$EmailFrom = 'Sender@domain.com'
$EmailTo = 'Recipient@domain.com'
$EmailSubject = 'Test Subject'
$SMTPServer = 'SMTP Server'

#SQL Connection
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server=$DatabaseServerName;database=$DatabaseName;trusted_connection=True;Connection Timeout=120;"

#Open Connection
$Connection.Open()
		
#SQL Command
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = "SELECT * FROM Disks"
			
$Reader = $Command.ExecuteReader()

$Datatable = New-Object System.Data.DataTable
$Datatable.Load($Reader)

#Close the database connection 
$Connection.Close()

$HTML = @"
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Page Title</title>

<style type="text/css">
body
{
	background: #fff;
	padding: 0px;
	border: 0px;
	margin: 0px;
}

table
{
	background: #FFFFFF;
	margin: 5px;
	border-collapse: collapse;
 	border-spacing: 0px;
	font-family: Tahoma;
	font-size: 13px;
	width: 500px;
}

td
{
	padding: 4px;
	border: 1px solid #F0F0F6;
	margin: 2px;
	font-family: Tahoma;
	font-size: 13px;
}

th
{
	padding: 4px;
	border: 1px solid #F0F0F6;
	margin: 2px;
	background: #0054a1;
	color: #fff;
}

h1
{
	font-family: Tahoma;
	font-size: 18px;
	color: #6CF;
}

h2
{
	font-family: Tahoma;
	font-size: 12px;
	color: Orange;
}
</style>

</head><body>
"@

#Add SQL Datatable
$HTML += "<h1>Section Title</h1>"
$HTML += (ConvertDatatableTo-HTML $Datatable)

#End HTML
$HTML += "</body></html>"

#Mail Message
$EmailBody = $HTML
$Message = New-Object Net.Mail.MailMessage($EmailFrom, $EmailTo, $EmailSubject, $EmailBody)
$Message.IsBodyHtml = $True
$SMTP = New-Object Net.Mail.SmtpClient($SMTPServer)
$SMTP.Send($Message)

Open in new window

Commented:
You tipped it for me - although the [] square braces don't seem to work for the oracledataadapter, that did prompt me to change the GOOGLE query I was using and I found the solution: http://www.powergui.org/thread.jspa?threadID=9989

The || double lines work!
Select prsn_phone_area_cd || ' ' || prsn_phone_phone_txt as HOME_PHONE
    FROM prsn_phone
Good stuff, sorry about previous post it was for another thread, posted in wrong section.
P.S if your question is answered please can you mark my answer as accepted?

Author

Commented:
I found the final solution myself after something in chrismerritt's code triggered a new thought pattern for me.