SQL Question (1 Viewer)

[LAUNCH]

How can I get matlxfer.xtype to equal 'Z97' if matlxfer.xtype = 'INVSUB' and matlxfer.xfer_vndlot > '199' and 551 if it's less.

SELECT matlxfer.fm_cost_ctr ,        matlxfer.to_cost_ctr ,               CASE matlxfer.xfer_vndlot        WHEN '200' THEN '1200'        WHEN '201' THEN '1201'        WHEN '202' THEN '1202'        WHEN '203' THEN '1203'        WHEN '204' THEN '1204'        WHEN '205' THEN '1205'        WHEN '206' THEN '1206'        WHEN '207' THEN '1207'      ELSE matlxfer.xfer_vndlot      END AS xfer_vndlot   ,        CASE matlxfer.xtype          WHEN 'ADDINV' THEN '552'          WHEN 'INVSUB' THEN '551'

xfer_vndlot    ADJUSTMENT108    551109    551110    5511200    5511201    5511202    5511203    5511204    5511205    5511206    5511207    551

 

[LAUNCH]

Got it.

CASE matlxfer.xtype          WHEN 'ADDINV' THEN '552'          WHEN 'INVSUB' THEN                 CASE WHEN matlxfer.xfer_vndlot > 199 THEN 'z97' ELSE '551' ENDEND

 

[ClownCausedChaos2]

That's what I was gonna say.

 

[Patriotsfatboy1]

I get an error 342 when I run that. Which db are you running it on and do you have sa rights?

 

[MTskibum]

I get an error 342 when I run that. Which db are you running it on and do you have sa rights?

It looks like sql server or sql server compact. I do not use CASE when writing oracle queries as DECODE does a better job.

 

[Teumessian Fox]

I think CASE is easier to read in some instances

eta Code reviews are lame, but I'd fail the OP for his indentation method. Its all over the place.

 

[DropKick]

Decode only does a better job when there are "one to one" mappings. Otherwise it can get awkward. The case statement is much more flexible.

You can structure the case statement differently. Personal preference and coding style will vary. Plus, I like to explicitly manage all conditions. For example

CASE
WHEN matlxfer.xtype = 'ADDINV' THEN '552'
WHEN matlxfer.xtype = 'INVSUB' and matlxfer.xfer_vndlot > 199 THEN 'z97'

WHEN matlxfer.xtype = 'INVSUB' then '551'

ELSE null
END

 

[The Commish]

I get an error 342 when I run that. Which db are you running it on and do you have sa rights?

It looks like sql server or sql server compact. I do not use CASE when writing oracle queries as DECODE does a better job.

Maybe if there are one or two comparisons. It's not close if you have several.

 

[Slapdash]

I use case statements religiously, across all platforms

 

[Dinsy Ejotuz]

Didn't want to start a new thread, so figured this would do...

I'm teaching myself SQL from ground zero and am working through Code Academy's intro stuff. It's about 3 hours worth and covers things through querying multiple tables via joins.

If I'm an analyst that will only ever use SQL to get data from large relational DBs (rather than building or maintaining DBs, etc), how much more do I need?

If I'm pulling data from multiple DBs is there any advantage to using SQL to merge and clean the data vs. R or Python or something like that?

Any advice on what the next tutorial/series should be?

 

[Slapdash]

Yeah, you really don't need much more. SQL is pretty easy. Stuff like writing stored procedures or using something like SAS is helpful to combining and manipulating data from different places. Are the databases all on the same platform?

 

[Dinsy Ejotuz]

Yeah, you really don't need much more. SQL is pretty easy. Stuff like writing stored procedures or using something like SAS is helpful to combining and manipulating data from different places. Are the databases all on the same platform?

I don't know if they're all on the same platform yet, but know enough R to do most of what I'd need to do if they aren't. As long as I can pull the data from each.

I expect stored procedures and canned data cleaning/merging would be pretty helpful. Are they as straightforward as the basic stuff I'm seeing now? Seems dead easy so far.

 

[The Commish]

I'll say that it's pretty important to understand the structure of your database, how the tables are linked, where the indexes are etc. That goes a long way in writing efficient sql. I will also say that most of the tools the rdbms vendors provide with their software to move data will always be better than straight sql. You might consider using those tools to pull your data rather than sql.

 

[DropKick]

Didn't want to start a new thread, so figured this would do...

I'm teaching myself SQL from ground zero and am working through Code Academy's intro stuff. It's about 3 hours worth and covers things through querying multiple tables via joins.

If I'm an analyst that will only ever use SQL to get data from large relational DBs (rather than building or maintaining DBs, etc), how much more do I need?

If I'm pulling data from multiple DBs is there any advantage to using SQL to merge and clean the data vs. R or Python or something like that?

Any advice on what the next tutorial/series should be?

As you probably already know, SQL is easy to learn and you can quickly become proficient at the basics. Yet, after many years, I'm still amazed at how robust SQL can be in more advanced problem solving.

My advice is to start using it and learn OTJ with creativity.

 

[Jayded]

I'll say that it's pretty important to understand the structure of your database, how the tables are linked, where the indexes are etc. That goes a long way in writing efficient sql. I will also say that most of the tools the rdbms vendors provide with their software to move data will always be better than straight sql. You might consider using those tools to pull your data rather than sql.

Mostly agree up to your last point. Most of those vendors still rely upon the user or someone setting up the connection to understand the schema noted in your earlier point.

I'll also toss out that SQL 2014 also began containing some efficiencies under the hood where a user can write a crappy query and it can make the call optimized before hitting the DB.

 

[The Commish]

I'll say that it's pretty important to understand the structure of your database, how the tables are linked, where the indexes are etc. That goes a long way in writing efficient sql. I will also say that most of the tools the rdbms vendors provide with their software to move data will always be better than straight sql. You might consider using those tools to pull your data rather than sql.

Mostly agree up to your last point. Most of those vendors still rely upon the user or someone setting up the connection to understand the schema noted in your earlier point.

I'll also toss out that SQL 2014 also began containing some efficiencies under the hood where a user can write a crappy query and it can make the call optimized before hitting the DB.

Not sure what you mean with this.

 

[LAUNCH]

nm