SQL Help

The substr calls are giving you the first 9 characters of ID, the first 15 characters of the first name, and the first 60 characters of the last name.

I guess I should also clarify that those values are assigned to the fields "ID", "FIRST", and "LAST" respectively.

 

Substring is just telling it to return the first x number of characters from a column. The column name afterwards is an alias so you can work with easier to write names, so you can reference "frkiden.f_get_spriden_first_name" as "first" in the rest of your statement.

The error is probably because you're still referencing frkiden in the select statement when it's not present in the from clause.

Also, generally if you want to get related data from two different tables you'd use a join statement. Something like:

Code:

Select columns
From orders_current
Left Outer Join orders_previous
On orders_current.pers_last_order <> orders_previous.last_order

That seems like it would give you some odd data though, what are you trying to accomplish by getting data from those two tables?

 

substring is text function returning a portion of the text field. The Name after the substring functions are aliases. There are joins you can perform, inner, left, right and outer depend what you are looking for. Search for join on google or provide more details on the tables that needs to be joined for comparison.

 

That's just another column that is being selected.

You would have to have the user's ID in both tables.

Code:

Select columns
From SPRIDEN_CURRENT
Inner Join SPRIDEN_PREVIOUS
On SPRIDEN_CURRENT.userID = SPRIDEN_PREVIOUS.userID
And SPRIDEN_CURRENT.PERS_LAST_NAME <> SPRIDEN_PREVIOUS.SPRIDEN_LAST_NAME
And SPRIDEN_PREVIOUS.SPRIDEN_NTYP_CODE= 'TRANS'

 

That comma after "LAST" will throw an error. Can you include the error message if that doesn't solve it?

 

You need a common field like ID for something like this.

For example:

Select SPRIDEN_CURRENT.ID, SPRIDEN_Previous.ID
from SPRIDEN_CURRENT inner join SPRIDEN_PREVIOUS
on SPRIDEN_CURRENT.ID = SPRIDEN_PREVIOUS.ID
and SPRIDEN_CURRENT.NTYP_CODE = SPRIDEN_PREVIOUS.NTYP_CODE
where SPRIDEN_PREVIOUS.Name <> SPRIDEN_CURRENT.Name

Would give you names different under the same ID and NTYP_CODE

 

If you are still a beginner with SQL and need a handy reference book, I really like the SAMS: "Teach Yourself SQL in 10 Minutes" book.

Broken up into a bunch of 10 minute lessons about SQL. Creates a good foundation, etc.

 

If you need to learn on the fly, http://www.w3schools.com/sql/default.asp is a good reference tool as well.

 

One more thing, I'm not sure what you're trying to accomplish with "WHERE pers_last_order<>last_order", but if "pers_last_order" is on one table and "last_order" is on another, you will need to join those two tables so you would be querying horizontal records that contain both items.

So it'd be something like this:

Code:

SELECT DISTINCT(orders_pidm),
substr(frkiden.f_get_spriden_id(orders_pidm),1,9) ID,
substr(frkiden.f_get_spriden_first_name(orders_pidm),1,15) FIRST,
substr(frkiden.f_get_spriden_last_name(orders_pidm),1,60) LAST,

FROM orders_current t1 INNER JOIN orders_previous t2
ON t1.pers_last_order = t2.last_order

WHERE pers_last_order<>last_order;

There might be some deviations depending on the relationship between the two tables (whether they are 1-to-1, 1-to-many or many-to-many).

 

You'll need to replace "columns" with the list of columns you want to get. To make sure you can actually select data from the table try something simple like:

Code:

Select top 1 *
From SPRIDEN_CURRENT

 

Try this:

Code:

Select columns
From SPRIDEN_CURRENT
Inner Join SPRIDEN_PREVIOUS
On SPRIDEN_CURRENT_PIDM = SPRIDEN_PREVIOUS_PIDM
WHERE SPRIDEN_CURRENT.PERS_LAST_NAME <> SPRIDEN_PREVIOUS.SPRIDEN_LAST_NAME
And SPRIDEN_PREVIOUS.SPRIDEN_NTYP_CODE='TRANS';

 

I likey, thanks.

 

You'll get a syntax error if you put a comma after the last column you're selecting. Take out the comma at the end of, "Select DISTINCT (SPRIDEN_CURRENT_ID),"

 

You don't need the comma at the end of the select clause. The comma is only needed to separate columns.