converting data like a case statement

Hello.

I have data in a SSIS package that I need to alter to something else.

The source column is a VARCHAR(3) column and it only contains two possible values, "ACT" or "CLS".

The destination column is a CHAR(1) column. Where the value of the source column is 'ACT' I want to put '1' in the destination and where the value of the source column is 'CLS' I want to put '0'.

I can do this easily in T-SQL using a CASE statement but the source data is an Ingres database and CASE isn't a valid SQL keyword.

Can I use a data conversion task to do this in SSIS? and if so, what's the syntax?

Thanks

No, you need a derived column. Syntax is:

[ColumnName]=="ACT" ? 1 : 0

-Jamie

|||What Jamie said, but with quotes to be a little more meaningful if you're plugging them into a CHAR field.

[ColumnName] == "ACT" ? "1" : "0"

If, when you setup the derived column, you replace the CHAR field, it'll automatically set the type for you and ensure that you have no type errors.|||

Dear Phil Brammer,

what can be done for cascade case statement?

thanks,

|||

also how do I put this function in the derived column transformation editor?

"convert(char(10), dateadd(Year, 1, convert(datetime, A.txtdos)), 101)"

thanks,

|||

Jwalant Natvarlal Soneji wrote:

Dear Phil Brammer,

what can be done for cascade case statement?

thanks,

Use nested conditional operators.

-Jamie

|||

Dear Jamie Thomson,

how to use that. i tried with

bool condition ? true : bool condition ? true:........................

but it seems too length and also given error while executing and not at design time

thanks,|||

Jwalant Natvarlal Soneji wrote:

Dear Jamie Thomson,

how to use that. i tried with

bool condition ? true : bool condition ? true:........................

but it seems too length and also given error while executing and not at design time

thanks,

You need some parantheses.

boolean_expression ? true_result :

(boolean_expression ? true_result :

(boolean_expression ? true_result :

(boolean_expression ? true_result : false_result)

)

)

-Jamie

|||

Dear Jamie Thomson,

dont u think so the space given in derived column edior is not enought to write the whole query like this, or any other option available to write the same?

thanks,

|||

Jwalant Natvarlal Soneji wrote:

Dear Jamie Thomson,

dont u think so the space given in derived column edior is not enought to write the whole query like this, or any other option available to write the same?

thanks,

Correct, you have to write it on one line. I spread it over multiple lines to make it easier for you to read.

-Jamie

sqlsql

converting data like a case statement

Hello.

I have data in a SSIS package that I need to alter to something else.

The source column is a VARCHAR(3) column and it only contains two possible values, "ACT" or "CLS".

The destination column is a CHAR(1) column. Where the value of the source column is 'ACT' I want to put '1' in the destination and where the value of the source column is 'CLS' I want to put '0'.

I can do this easily in T-SQL using a CASE statement but the source data is an Ingres database and CASE isn't a valid SQL keyword.

Can I use a data conversion task to do this in SSIS? and if so, what's the syntax?

Thanks

No, you need a derived column. Syntax is:

[ColumnName]=="ACT" ? 1 : 0

-Jamie

|||What Jamie said, but with quotes to be a little more meaningful if you're plugging them into a CHAR field.

[ColumnName] == "ACT" ? "1" : "0"

If, when you setup the derived column, you replace the CHAR field, it'll automatically set the type for you and ensure that you have no type errors.|||

Dear Phil Brammer,

what can be done for cascade case statement?

thanks,

|||

also how do I put this function in the derived column transformation editor?

"convert(char(10), dateadd(Year, 1, convert(datetime, A.txtdos)), 101)"

thanks,

|||

Jwalant Natvarlal Soneji wrote:

Dear Phil Brammer,

what can be done for cascade case statement?

thanks,

Use nested conditional operators.

-Jamie

|||

Dear Jamie Thomson,

how to use that. i tried with

bool condition ? true : bool condition ? true:........................

but it seems too length and also given error while executing and not at design time

thanks,|||

Jwalant Natvarlal Soneji wrote:

Dear Jamie Thomson,

how to use that. i tried with

bool condition ? true : bool condition ? true:........................

but it seems too length and also given error while executing and not at design time

thanks,

You need some parantheses.

boolean_expression ? true_result :

(boolean_expression ? true_result :

(boolean_expression ? true_result :

(boolean_expression ? true_result : false_result)

)

)

-Jamie

|||

Dear Jamie Thomson,

dont u think so the space given in derived column edior is not enought to write the whole query like this, or any other option available to write the same?

thanks,

|||

Jwalant Natvarlal Soneji wrote:

Dear Jamie Thomson,

dont u think so the space given in derived column edior is not enought to write the whole query like this, or any other option available to write the same?

thanks,

Correct, you have to write it on one line. I spread it over multiple lines to make it easier for you to read.

-Jamie

Converting data

Hi all,
I have the following questions :
1)
I have a query :
SELECT staffl.prijslijst AS pricelist, staffl.ARTCODE AS PhantomItem,
(CASE items.class_01 WHEN 'ALL' THEN 'ALL' ELSE (SELECT
itemclasses.description FROM itemclasses WHERE itemclasses.itemClassCode =
items.class_01 AND itemclasses.CLASSid = 1) END) AS class_01,
staffl.bedr1,
(CASE items.class_10 WHEN 'ALL' THEN 'ALL' ELSE (SELECT
itemclasses.description FROM itemclasses WHERE itemclasses.itemClassCode =
items.class_10 AND itemclasses.CLASSid = 10) END)
AS class_10 FROM staffl INNER JOIN stfoms ON stfoms.prijslijst =
staffl.prijslijst INNER JOIN items ON items.itemcode = staffl.artcode
AND items.type = 'P' AND stfoms.type = 'S' AND stfoms.main_pricelist <> 1
AND SUBSTRING(staffl.ARTCODE, 1, LEN(staffl.prijslijst)) = staffl.prijslijst
AND items.IsSalesItem = 0
ORDER BY staffl.pricelist
Which give me next output :
Pricelist class_01 bedr1
0000 Item1 0
0000 Item2 15
0000 Item15 10
All "CLASS_01" items go to 15 for each pricelist.
I like to convert this within SQL with Query or DTS to next structure,
so Pricelist and Bedr1 in 1 row with CLASS01 in columns (15 total) :
Pricelist item1 item2 item15
0000 0 15 10
2)
Other query is :
SELECT cicmpy.debnr, cicmpy.PriceList FROM cicmpy LEFT OUTER JOIN cicntp ON
cicmpy.cnt_id = cicntp.cnt_id WHERE debcode IS NOT NULL
ORDER BY cicmpy.debcode
Output is :
debnr pricelist
1 2025
2 SALESPRICE
3 3001
What I need is to combine both queries to get 1 output as following,
so merge the 2 queries to 1 to get 1 combined output, with 1 special thing :
Where SALESPRICE = pricelist '0000' :
Deb Pricelist item1 item2 item15
1 2025 0 25 10
2 0000 0 10 10
1 3001 0 32 10
Can anybody help me ?
This is crosstab query
SELECT Pricelist ,MAX(CASE WHEN class_01 ='Item1' THEN bedr1 END)
'Item1,
..................... Do that for all items here
FROM Table
GROUP BY Pricelist
"Jeroen" <Jeroen@.discussions.microsoft.com> wrote in message
news:D64B9FBA-A315-4B5A-B088-2964054C3B3E@.microsoft.com...
> Hi all,
> I have the following questions :
> 1)
> I have a query :
> SELECT staffl.prijslijst AS pricelist, staffl.ARTCODE AS PhantomItem,
> (CASE items.class_01 WHEN 'ALL' THEN 'ALL' ELSE (SELECT
> itemclasses.description FROM itemclasses WHERE itemclasses.itemClassCode =
> items.class_01 AND itemclasses.CLASSid = 1) END) AS class_01,
> staffl.bedr1,
> (CASE items.class_10 WHEN 'ALL' THEN 'ALL' ELSE (SELECT
> itemclasses.description FROM itemclasses WHERE itemclasses.itemClassCode =
> items.class_10 AND itemclasses.CLASSid = 10) END)
> AS class_10 FROM staffl INNER JOIN stfoms ON stfoms.prijslijst =
> staffl.prijslijst INNER JOIN items ON items.itemcode = staffl.artcode
> AND items.type = 'P' AND stfoms.type = 'S' AND stfoms.main_pricelist <> 1
> AND SUBSTRING(staffl.ARTCODE, 1, LEN(staffl.prijslijst)) =
> staffl.prijslijst
> AND items.IsSalesItem = 0
> ORDER BY staffl.pricelist
> Which give me next output :
> Pricelist class_01 bedr1
> 0000 Item1 0
> 0000 Item2 15
> 0000 Item15 10
> All "CLASS_01" items go to 15 for each pricelist.
> I like to convert this within SQL with Query or DTS to next structure,
> so Pricelist and Bedr1 in 1 row with CLASS01 in columns (15 total) :
> Pricelist item1 item2 item15
> 0000 0 15 10
> 2)
> Other query is :
> SELECT cicmpy.debnr, cicmpy.PriceList FROM cicmpy LEFT OUTER JOIN cicntp
> ON
> cicmpy.cnt_id = cicntp.cnt_id WHERE debcode IS NOT NULL
> ORDER BY cicmpy.debcode
> Output is :
> debnr pricelist
> 1 2025
> 2 SALESPRICE
> 3 3001
> What I need is to combine both queries to get 1 output as following,
> so merge the 2 queries to 1 to get 1 combined output, with 1 special thing
> :
> Where SALESPRICE = pricelist '0000' :
> Deb Pricelist item1 item2 item15
> 1 2025 0 25 10
> 2 0000 0 10 10
> 1 3001 0 32 10
> Can anybody help me ?
>
|||Hi Uri,
not very into SQL (newbie).
I am not sure how to use this in my current query....
"Uri Dimant" wrote:

> This is crosstab query
> SELECT Pricelist ,MAX(CASE WHEN class_01 ='Item1' THEN bedr1 END)
> 'Item1,
> ..................... Do that for all items here
> FROM Table
> GROUP BY Pricelist
>
> "Jeroen" <Jeroen@.discussions.microsoft.com> wrote in message
> news:D64B9FBA-A315-4B5A-B088-2964054C3B3E@.microsoft.com...
>
>

Converting data

Hi all,
I have the following questions :
1)
I have a query :
SELECT staffl.prijslijst AS pricelist, staffl.ARTCODE AS PhantomItem,
(CASE items.class_01 WHEN 'ALL' THEN 'ALL' ELSE (SELECT
itemclasses.description FROM itemclasses WHERE itemclasses.itemClassCode =
items.class_01 AND itemclasses.CLASSid = 1) END) AS class_01,
staffl.bedr1,
(CASE items.class_10 WHEN 'ALL' THEN 'ALL' ELSE (SELECT
itemclasses.description FROM itemclasses WHERE itemclasses.itemClassCode =
items.class_10 AND itemclasses.CLASSid = 10) END)
AS class_10 FROM staffl INNER JOIN stfoms ON stfoms.prijslijst =
staffl.prijslijst INNER JOIN items ON items.itemcode = staffl.artcode
AND items.type = 'P' AND stfoms.type = 'S' AND stfoms.main_pricelist <> 1
AND SUBSTRING(staffl.ARTCODE, 1, LEN(staffl.prijslijst)) = staffl.prijslijst
AND items.IsSalesItem = 0
ORDER BY staffl.pricelist
Which give me next output :
Pricelist class_01 bedr1
0000 Item1 0
0000 Item2 15
0000 Item15 10
All "CLASS_01" items go to 15 for each pricelist.
I like to convert this within SQL with Query or DTS to next structure,
so Pricelist and Bedr1 in 1 row with CLASS01 in columns (15 total) :
Pricelist item1 item2 item15
0000 0 15 10
2)
Other query is :
SELECT cicmpy.debnr, cicmpy.PriceList FROM cicmpy LEFT OUTER JOIN cicntp ON
cicmpy.cnt_id = cicntp.cnt_id WHERE debcode IS NOT NULL
ORDER BY cicmpy.debcode
Output is :
debnr pricelist
1 2025
2 SALESPRICE
3 3001
What I need is to combine both queries to get 1 output as following,
so merge the 2 queries to 1 to get 1 combined output, with 1 special thing :
Where SALESPRICE = pricelist '0000' :
Deb Pricelist item1 item2 item15
1 2025 0 25 10
2 0000 0 10 10
1 3001 0 32 10
Can anybody help me ?This is crosstab query
SELECT Pricelist ,MAX(CASE WHEN class_01 ='Item1' THEN bedr1 END)
'Item1,
..................... Do that for all items here
FROM Table
GROUP BY Pricelist
"Jeroen" <Jeroen@.discussions.microsoft.com> wrote in message
news:D64B9FBA-A315-4B5A-B088-2964054C3B3E@.microsoft.com...
> Hi all,
> I have the following questions :
> 1)
> I have a query :
> SELECT staffl.prijslijst AS pricelist, staffl.ARTCODE AS PhantomItem,
> (CASE items.class_01 WHEN 'ALL' THEN 'ALL' ELSE (SELECT
> itemclasses.description FROM itemclasses WHERE itemclasses.itemClassCode =
> items.class_01 AND itemclasses.CLASSid = 1) END) AS class_01,
> staffl.bedr1,
> (CASE items.class_10 WHEN 'ALL' THEN 'ALL' ELSE (SELECT
> itemclasses.description FROM itemclasses WHERE itemclasses.itemClassCode =
> items.class_10 AND itemclasses.CLASSid = 10) END)
> AS class_10 FROM staffl INNER JOIN stfoms ON stfoms.prijslijst =
> staffl.prijslijst INNER JOIN items ON items.itemcode = staffl.artcode
> AND items.type = 'P' AND stfoms.type = 'S' AND stfoms.main_pricelist <> 1
> AND SUBSTRING(staffl.ARTCODE, 1, LEN(staffl.prijslijst)) =
> staffl.prijslijst
> AND items.IsSalesItem = 0
> ORDER BY staffl.pricelist
> Which give me next output :
> Pricelist class_01 bedr1
> 0000 Item1 0
> 0000 Item2 15
> 0000 Item15 10
> All "CLASS_01" items go to 15 for each pricelist.
> I like to convert this within SQL with Query or DTS to next structure,
> so Pricelist and Bedr1 in 1 row with CLASS01 in columns (15 total) :
> Pricelist item1 item2 item15
> 0000 0 15 10
> 2)
> Other query is :
> SELECT cicmpy.debnr, cicmpy.PriceList FROM cicmpy LEFT OUTER JOIN cicntp
> ON
> cicmpy.cnt_id = cicntp.cnt_id WHERE debcode IS NOT NULL
> ORDER BY cicmpy.debcode
> Output is :
> debnr pricelist
> 1 2025
> 2 SALESPRICE
> 3 3001
> What I need is to combine both queries to get 1 output as following,
> so merge the 2 queries to 1 to get 1 combined output, with 1 special thing
> :
> Where SALESPRICE = pricelist '0000' :
> Deb Pricelist item1 item2 item15
> 1 2025 0 25 10
> 2 0000 0 10 10
> 1 3001 0 32 10
> Can anybody help me ?
>|||Hi Uri,
not very into SQL (newbie).
I am not sure how to use this in my current query....
"Uri Dimant" wrote:

> This is crosstab query
> SELECT Pricelist ,MAX(CASE WHEN class_01 ='Item1' THEN bedr1 END)
> 'Item1,
> ..................... Do that for all items here
> FROM Table
> GROUP BY Pricelist
>
> "Jeroen" <Jeroen@.discussions.microsoft.com> wrote in message
> news:D64B9FBA-A315-4B5A-B088-2964054C3B3E@.microsoft.com...
>
>

Converting data

Hi all,
I have the following questions :
1)
I have a query :
SELECT staffl.prijslijst AS pricelist, staffl.ARTCODE AS PhantomItem,
(CASE items.class_01 WHEN 'ALL' THEN 'ALL' ELSE (SELECT
itemclasses.description FROM itemclasses WHERE itemclasses.itemClassCode = items.class_01 AND itemclasses.CLASSid = 1) END) AS class_01,
staffl.bedr1,
(CASE items.class_10 WHEN 'ALL' THEN 'ALL' ELSE (SELECT
itemclasses.description FROM itemclasses WHERE itemclasses.itemClassCode = items.class_10 AND itemclasses.CLASSid = 10) END)
AS class_10 FROM staffl INNER JOIN stfoms ON stfoms.prijslijst = staffl.prijslijst INNER JOIN items ON items.itemcode = staffl.artcode
AND items.type = 'P' AND stfoms.type = 'S' AND stfoms.main_pricelist <> 1
AND SUBSTRING(staffl.ARTCODE, 1, LEN(staffl.prijslijst)) = staffl.prijslijst
AND items.IsSalesItem = 0
ORDER BY staffl.pricelist
Which give me next output :
Pricelist class_01 bedr1
0000 Item1 0
0000 Item2 15
0000 Item15 10
All "CLASS_01" items go to 15 for each pricelist.
I like to convert this within SQL with Query or DTS to next structure,
so Pricelist and Bedr1 in 1 row with CLASS01 in columns (15 total) :
Pricelist item1 item2 item15
0000 0 15 10
2)
Other query is :
SELECT cicmpy.debnr, cicmpy.PriceList FROM cicmpy LEFT OUTER JOIN cicntp ON
cicmpy.cnt_id = cicntp.cnt_id WHERE debcode IS NOT NULL
ORDER BY cicmpy.debcode
Output is :
debnr pricelist
1 2025
2 SALESPRICE
3 3001
What I need is to combine both queries to get 1 output as following,
so merge the 2 queries to 1 to get 1 combined output, with 1 special thing :
Where SALESPRICE = pricelist '0000' :
Deb Pricelist item1 item2 item15
1 2025 0 25 10
2 0000 0 10 10
1 3001 0 32 10
Can anybody help me ?This is crosstab query
SELECT Pricelist ,MAX(CASE WHEN class_01 ='Item1' THEN bedr1 END)
'Item1,
..................... Do that for all items here
FROM Table
GROUP BY Pricelist
"Jeroen" <Jeroen@.discussions.microsoft.com> wrote in message
news:D64B9FBA-A315-4B5A-B088-2964054C3B3E@.microsoft.com...
> Hi all,
> I have the following questions :
> 1)
> I have a query :
> SELECT staffl.prijslijst AS pricelist, staffl.ARTCODE AS PhantomItem,
> (CASE items.class_01 WHEN 'ALL' THEN 'ALL' ELSE (SELECT
> itemclasses.description FROM itemclasses WHERE itemclasses.itemClassCode => items.class_01 AND itemclasses.CLASSid = 1) END) AS class_01,
> staffl.bedr1,
> (CASE items.class_10 WHEN 'ALL' THEN 'ALL' ELSE (SELECT
> itemclasses.description FROM itemclasses WHERE itemclasses.itemClassCode => items.class_10 AND itemclasses.CLASSid = 10) END)
> AS class_10 FROM staffl INNER JOIN stfoms ON stfoms.prijslijst => staffl.prijslijst INNER JOIN items ON items.itemcode = staffl.artcode
> AND items.type = 'P' AND stfoms.type = 'S' AND stfoms.main_pricelist <> 1
> AND SUBSTRING(staffl.ARTCODE, 1, LEN(staffl.prijslijst)) => staffl.prijslijst
> AND items.IsSalesItem = 0
> ORDER BY staffl.pricelist
> Which give me next output :
> Pricelist class_01 bedr1
> 0000 Item1 0
> 0000 Item2 15
> 0000 Item15 10
> All "CLASS_01" items go to 15 for each pricelist.
> I like to convert this within SQL with Query or DTS to next structure,
> so Pricelist and Bedr1 in 1 row with CLASS01 in columns (15 total) :
> Pricelist item1 item2 item15
> 0000 0 15 10
> 2)
> Other query is :
> SELECT cicmpy.debnr, cicmpy.PriceList FROM cicmpy LEFT OUTER JOIN cicntp
> ON
> cicmpy.cnt_id = cicntp.cnt_id WHERE debcode IS NOT NULL
> ORDER BY cicmpy.debcode
> Output is :
> debnr pricelist
> 1 2025
> 2 SALESPRICE
> 3 3001
> What I need is to combine both queries to get 1 output as following,
> so merge the 2 queries to 1 to get 1 combined output, with 1 special thing
> :
> Where SALESPRICE = pricelist '0000' :
> Deb Pricelist item1 item2 item15
> 1 2025 0 25 10
> 2 0000 0 10 10
> 1 3001 0 32 10
> Can anybody help me ?
>|||Hi Uri,
not very into SQL (newbie).
I am not sure how to use this in my current query....
"Uri Dimant" wrote:
> This is crosstab query
> SELECT Pricelist ,MAX(CASE WHEN class_01 ='Item1' THEN bedr1 END)
> 'Item1,
> ..................... Do that for all items here
> FROM Table
> GROUP BY Pricelist
>
> "Jeroen" <Jeroen@.discussions.microsoft.com> wrote in message
> news:D64B9FBA-A315-4B5A-B088-2964054C3B3E@.microsoft.com...
> > Hi all,
> >
> > I have the following questions :
> >
> > 1)
> > I have a query :
> >
> > SELECT staffl.prijslijst AS pricelist, staffl.ARTCODE AS PhantomItem,
> > (CASE items.class_01 WHEN 'ALL' THEN 'ALL' ELSE (SELECT
> > itemclasses.description FROM itemclasses WHERE itemclasses.itemClassCode => > items.class_01 AND itemclasses.CLASSid = 1) END) AS class_01,
> > staffl.bedr1,
> > (CASE items.class_10 WHEN 'ALL' THEN 'ALL' ELSE (SELECT
> > itemclasses.description FROM itemclasses WHERE itemclasses.itemClassCode => > items.class_10 AND itemclasses.CLASSid = 10) END)
> >
> > AS class_10 FROM staffl INNER JOIN stfoms ON stfoms.prijslijst => > staffl.prijslijst INNER JOIN items ON items.itemcode = staffl.artcode
> > AND items.type = 'P' AND stfoms.type = 'S' AND stfoms.main_pricelist <> 1
> > AND SUBSTRING(staffl.ARTCODE, 1, LEN(staffl.prijslijst)) => > staffl.prijslijst
> > AND items.IsSalesItem = 0
> > ORDER BY staffl.pricelist
> >
> > Which give me next output :
> >
> > Pricelist class_01 bedr1
> > 0000 Item1 0
> > 0000 Item2 15
> > 0000 Item15 10
> >
> > All "CLASS_01" items go to 15 for each pricelist.
> >
> > I like to convert this within SQL with Query or DTS to next structure,
> > so Pricelist and Bedr1 in 1 row with CLASS01 in columns (15 total) :
> >
> > Pricelist item1 item2 item15
> > 0000 0 15 10
> >
> > 2)
> > Other query is :
> >
> > SELECT cicmpy.debnr, cicmpy.PriceList FROM cicmpy LEFT OUTER JOIN cicntp
> > ON
> > cicmpy.cnt_id = cicntp.cnt_id WHERE debcode IS NOT NULL
> >
> > ORDER BY cicmpy.debcode
> >
> > Output is :
> >
> > debnr pricelist
> > 1 2025
> > 2 SALESPRICE
> > 3 3001
> >
> > What I need is to combine both queries to get 1 output as following,
> > so merge the 2 queries to 1 to get 1 combined output, with 1 special thing
> > :
> > Where SALESPRICE = pricelist '0000' :
> >
> > Deb Pricelist item1 item2 item15
> > 1 2025 0 25 10
> > 2 0000 0 10 10
> > 1 3001 0 32 10
> >
> > Can anybody help me ?
> >
> >
>
>

Converting Access SQL to T-Sql

Any help converting the following sql to T-Sql would be helpful. I created it in Access ant works great but cant get the case to work. Need to put it into a accounting program that uses T-Sql. The purpose it to come up with a new field called STATUS based on key words in the "decoded" column.

Thanks!

Status: IIf([TableName]![ColumnName] Like "*PA'D*","PA'D",IIf([TableName]![ ColumnName] Like "*SOLD*","SOLD",IIf([TableName]![ ColumnName] Like "*DNU*","DNU","ACTIVE")))case when TableName.ColumnName Like '%PA''D%'
then 'PA''D'
when TableName.ColumnName Like '%SOLD%'
then 'SOLD'
when TableName.ColumnName Like '%DNU%'
then 'DNU'
else 'ACTIVE' end as Status|||There is a saying that data should be stored in your tables, not in your code. Is there a specific reason you do not have a table for these status codes that you can then reference in your queries? What if one of those status codes changes or a new code needs to be added?sqlsql

convert table data into complex xml document

I would like to convert the following two tables into the XML document below
without a bunch of UNIONS. The reason being in my case i have hundereds of
tables w/ different fields all going into a single XML document. Is there
any way to use user defined functions or something to construct just one XML
document? Or is there a way i can convert all the foos into a string, all
the bars into a string etc. then concatinate them and return them as an xml
document?
TABLE FOO
x y
________________ ____________________
1 11
2 22
TABLE BAR
a b
________________ ____________________
3 33
4 44
Using Open XML how do you convert this into:
<rootfoobar>
<foos>
<foo x='1' y='11'/>
<foo x='2' y='22'/>
</foos>
<bars>
<bar a='3' b='33'/>
<bar a='4' b='44'/>
</bars>
</rootfoobar>
The UNIONS are not so bad (the mainteance of the query is rather ugly
though).
In SQL Server 2000, you either use the mapping schemas of the mid-tier
SQLXML component or FOR XML EXPLICIT queries.
If the data has no correlation, you can also use the XML templates in SQLXML
to put individual query results together.
In SQL Server 2005, you will be able to nest FOR XML and use the new PATH
mode that avoids the UNION all. See my blog entries and links to an MSDN
whitepaper on http://sqljunkies.com/weblog/mrys
HTH
Michael
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:%23l2gkbrbEHA.3716@.TK2MSFTNGP11.phx.gbl...
>I would like to convert the following two tables into the XML document
>below
> without a bunch of UNIONS. The reason being in my case i have hundereds of
> tables w/ different fields all going into a single XML document. Is there
> any way to use user defined functions or something to construct just one
> XML
> document? Or is there a way i can convert all the foos into a string, all
> the bars into a string etc. then concatinate them and return them as an
> xml
> document?
> TABLE FOO
> x y
> ________________ ____________________
> 1 11
> 2 22
>
>
> TABLE BAR
> a b
> ________________ ____________________
> 3 33
> 4 44
>
> Using Open XML how do you convert this into:
> <rootfoobar>
> <foos>
> <foo x='1' y='11'/>
> <foo x='2' y='22'/>
> </foos>
> <bars>
> <bar a='3' b='33'/>
> <bar a='4' b='44'/>
> </bars>
> </rootfoobar>
>
>

convert keywords to UpperCase

I don't know if this is in the correct forum, but is there a simple utility to convert all reserved words to upper case in SQL Server 2005?

The find and replace is not the best option as there could be some field values/comments that would get converted.

With > 100 stored procedures/views something like this would be very beneficial. :)

Thanks
I don′t know of any tool like this, you could sure write one on your own if you have the bunch of reserverd words. But pro-actively you could use some other tool which keeps track of writing the keyword upper case while you are typing, like SQLPrompt which is free now:

http://www.sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/EntryID/8/Default.aspx

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Convert If-Else into Case statement

Can I convert this IF-ELSE statement into a CASE statement? Thanks for
your help!
declare @.source varchar(150)
declare @.destination varchar(150)
if (select @.@.servername) = 'A'
begin
set @.source = 'folder1\file.txt'
set @.destination = 'folder2'
end
else
if (select @.@.servername) = 'B'
begin
set @.source = 'folder3\file2.txt'
set @.destination = 'folder4'
end
else
if (select @.@.servername) = 'C'
begin
set @.source = 'folder4\.*'
set @.destination = 'folder4'
end
*** Sent via Developersdex http://www.examnotes.net ***Give this a try...
declare @.source varchar(150)
declare @.destination varchar(150)
select @.source = case @.@.servername
when 'A' then 'folder1\file.txt'
when 'B' then 'folder3\file2.txt'
when 'C' then 'folder4\.*'
end
, @.destination = case @.@.servername
when 'A' then 'folder2'
when 'B' then 'folder4'
when 'C' then 'folder4'
end
--Brian
(Please reply to the newsgroups only.)
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:OMYWbhTuFHA.460@.TK2MSFTNGP15.phx.gbl...
> Can I convert this IF-ELSE statement into a CASE statement? Thanks for
> your help!
>
> declare @.source varchar(150)
> declare @.destination varchar(150)
> if (select @.@.servername) = 'A'
> begin
> set @.source = 'folder1\file.txt'
> set @.destination = 'folder2'
> end
> else
> if (select @.@.servername) = 'B'
> begin
> set @.source = 'folder3\file2.txt'
> set @.destination = 'folder4'
> end
> else
> if (select @.@.servername) = 'C'
> begin
> set @.source = 'folder4\.*'
> set @.destination = 'folder4'
> end
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||declare @.source varchar(150)
declare @.destination varchar(150)
SELECT @.source =
CASE @.@.servername
WHEN 'A' THEN 'folder1\file.txt'
WHEN 'B' THEN 'folder3\file2.txt'
WHEN 'C' THEN 'folder\4.*'
END,
@.destination =
CASE @.@.servername
WHEN 'A' THEN 'folder2\'
WHEN 'B' THEN 'folder4\'
WHEN 'C' THEN 'folder4'
END|||Heh,
Sorry for the duplication, I guess I was typing my response whilst
Brian was posting his!
Still, we both posted the same thing so I guess that's confirmation it
works!|||THANX A LOT!!!!
*** Sent via Developersdex http://www.examnotes.net ***|||...or we're both just feeling lucky this morning. ;)
--Brian
(Please reply to the newsgroups only.)
"RobJH" <robertjhenderson@.gmail.com> wrote in message
news:1126709760.080074.261130@.g14g2000cwa.googlegroups.com...
> Heh,
> Sorry for the duplication, I guess I was typing my response whilst
> Brian was posting his!
> Still, we both posted the same thing so I guess that's confirmation it
> works!
>

convert datetime to age

I have used the following query to get an age from datetime:
select age
from (select case when dateadd(year, datediff(year, birthdate, getdate()),
birthdate) > getdate() then datediff(year, birthdate, getdate())
-1 else datediff(year, birthdate, getdate()) end as age
from userprofile) as age
I'm trying to get two fields from table userprofile (userid, birthdate) and
viewed as
userid and the age.
gone blank on trying to figure out how to implement the previous query with
mulitple fields from a table.Why are you using a subquery?
select userid, birthdate as age case when dateadd(year, datediff(year,
birthdate, getdate()),
birthdate) > getdate() then datediff(year, birthdate, getdate()) -1 else
datediff(year, birthdate, getdate()) end as age
from userprofile
"chad" wrote:

> I have used the following query to get an age from datetime:
> select age
> from (select case when dateadd(year, datediff(year, birthdate, getdate()),
> birthdate) > getdate() then datediff(year, birthdate, getdate())
> -1 else datediff(year, birthdate, getdate()) end as age
> from userprofile) as age
> I'm trying to get two fields from table userprofile (userid, birthdate) an
d
> viewed as
> userid and the age.
> gone blank on trying to figure out how to implement the previous query wit
h
> mulitple fields from a table.|||Select DateDiff(yy, BirthDate, @.TargetDate)
- Case
When Cast(
Cast(DatePart(yyyy, BirthDate) As Char(4))
+ Right('00' + Cast(DatePart(mm, @.TargetDate) As
VarChar(2)), 2)
+ Right('00' + Cast(DatePart(dd, @.TargetDate) As
VarChar(2)), 2) As SmallDateTime)
< @.TargetDate
Then 1
Else 0
End
From Table
Where @.TargetDate is the date you are comparing against. You could replace
this with GetDate() if you simply wanted their age as of now.
The second part creates an ISO date (yyyymmdd) in the year of the target
date for the month and day of the birth date. If the birth day in the target
year has not transpired, it subtracts one from the total. This technique is
impervious to leap years (well, as impervious as SQL's date interpretation)
and ensures that the value that comes out is one that a human would
interpret as the correct age.
Thomas
"chad" <chad@.discussions.microsoft.com> wrote in message
news:9048E685-D96B-40F4-9B6C-7C6D56D859E4@.microsoft.com...
>I have used the following query to get an age from datetime:
> select age
> from (select case when dateadd(year, datediff(year, birthdate, getdate()),
> birthdate) > getdate() then datediff(year, birthdate, getdate())
> -1 else datediff(year, birthdate, getdate()) end as age
> from userprofile) as age
> I'm trying to get two fields from table userprofile (userid, birthdate)
> and
> viewed as
> userid and the age.
> gone blank on trying to figure out how to implement the previous query
> with
> mulitple fields from a table.|||the original query was for age conversion only and it worked well.
there was syntax errors in your query, where's the THEN expression.
appreciate the help.
"HP" wrote:
> Why are you using a subquery?
> select userid, birthdate as age case when dateadd(year, datediff(year,
> birthdate, getdate()),
> birthdate) > getdate() then datediff(year, birthdate, getdate()) -1 else
> datediff(year, birthdate, getdate()) end as age
> from userprofile
>
> "chad" wrote:
>|||errors out at row 2893 out of 9998.
The conversion of char data type to smalldatetime data type resulted in an
out-of-range smalldatetime value.
"Thomas C" wrote:

> Select DateDiff(yy, BirthDate, @.TargetDate)
> - Case
> When Cast(
> Cast(DatePart(yyyy, BirthDate) As Char(4))
> + Right('00' + Cast(DatePart(mm, @.TargetDate) As
> VarChar(2)), 2)
> + Right('00' + Cast(DatePart(dd, @.TargetDate) As
> VarChar(2)), 2) As SmallDateTime)
> < @.TargetDate
> Then 1
> Else 0
> End
> From Table
> Where @.TargetDate is the date you are comparing against. You could replace
> this with GetDate() if you simply wanted their age as of now.
> The second part creates an ISO date (yyyymmdd) in the year of the target
> date for the month and day of the birth date. If the birth day in the targ
et
> year has not transpired, it subtracts one from the total. This technique i
s
> impervious to leap years (well, as impervious as SQL's date interpretation
)
> and ensures that the value that comes out is one that a human would
> interpret as the correct age.
>
> Thomas
>
> "chad" <chad@.discussions.microsoft.com> wrote in message
> news:9048E685-D96B-40F4-9B6C-7C6D56D859E4@.microsoft.com...
>
>|||Figured it out:
SELECT Age=datediff(year,Birthdate,getdate()),
userid
FROM UserProfile
WHERE UserProfile.Birthdate IS NOT NULL AND
datediff(year,Birthdate,getdate())>=0
ORDER BY userid
"chad" wrote:

> I have used the following query to get an age from datetime:
> select age
> from (select case when dateadd(year, datediff(year, birthdate, getdate()),
> birthdate) > getdate() then datediff(year, birthdate, getdate())
> -1 else datediff(year, birthdate, getdate()) end as age
> from userprofile) as age
> I'm trying to get two fields from table userprofile (userid, birthdate) an
d
> viewed as
> userid and the age.
> gone blank on trying to figure out how to implement the previous query wit
h
> mulitple fields from a table.